Command to grab data from Google Spreadsheets

There's no direct way of doing it rather than making an API in the middle.

I will try to make it work.

Thanks for offering to help. Did you have any luck?

Spun up a quick program for this purpose only

base link - http://surajbhari.info:54321

endpoints
/ - gives json of sheet
/rf - same as /
/cf - same but gives in format of coloumn first
/r/n - n is the index. gives n'th row
/c/n - n is the index. gives n'th coloumn

attributes
id - id of the sheet, - IMPORTANT
sheet_id - index of sheet to work on. defaults to 0

Source code - Github

Closest I could go with this small af character allowance

$(eval q="$(querystring)";p=$(urlfetch json http://18.219.12.147:54321/?id=1I5SxpVLb0VasLR4f4qjdw6v5glMU9_BB00_C2M8PE4g&sheet_no=1);r=p.find(i=>i[0].toLowerCase()===q.toLowerCase());r[0]+r[1]+r[3])

I know this doesn't look nice. but I am getting limited by character limitation.

Give me some more time i think i can do something better. as this will not work for big names like Feet (No Bike, No Train, No Chute)

I present to you. one of the weirdest thing i could have made

$(eval q="$(query)";p=$(urlfetch json http://18.219.12.147:54321/?id=1I5SxpVLb0VasLR4f4qjdw6v5glMU9_BB00_C2M8PE4g&sheet_no=1); $(urlfetch https://pastebin.com/raw/eNBtAPTH))

Thank you for the effort you're putting into this! If it makes it easier I'm very unlikely to be grabbing any times outside of the top 60 or 70.

I can also see that the current command just grabs the overall speedrun time. Whilst that is definitely useful (maybe a seperate command like !time insert vehicle here), the main purpose I was envisioning was to show the split times from column G-Q. Does that look like it will be possible?

Since I now know how to eval pastebin code. I think sky is the limit now.

here's how it looks with sheet_no=0

that it can search with leading words.

don't add this right now. I am a little confused why my script can't get any more cell values that is from G to Q. I will have to look at my base code for this.

I think you want something like this if am not wrong

user - !search 9f
nightbot - RBSReece - 15:58.324 Highway-2:42, Wheelspin-2:54, Quarry CP-3:55, Hospital Line-5:38, Airport CP-6:31, Route 68-8:43, Chilliad CP-10:52, Off T. Tracks-12:25, Observ. CP-13:37, Zancudo Exit-14:54, Finish-15:58

it is possible if i can fix the first bug in first place ngl.

This is hopefully my last reply

$(eval q="$(query)";p=$(urlfetch json http://18.219.12.147:54321/?id=1I5SxpVLb0VasLR4f4qjdw6v5glMU9_BB00_C2M8PE4g&sheet_id=5); $(urlfetch https://pastebin.com/raw/X4vaUgKF))

I really can't invest more time into this. as i have exam tomorrow.
I am using the sheet named split. as it worked fine with it.
you can read the rest of the hidden code https://pastebin.com/raw/X4vaUgKF

This is fantastic, I really appreciate all of the work you've put in here. Thank you so much. So do I just need to copy the $(Eval).... section into a command in Nightbot and it should work?

it should

I encourage you to use your own server to host the api in middle. as i would not be actively maintaining it and fixing it.
if not now. in future.

Hi, hope your exam went well.

I've noticed that the command seems to not be working as expected - when the command shows the additional split times after the "Highway" split, it appears to show them as how much additional time they are compared to the previous time (e.g Highway 2:42, Wheelspin 0:12) when it should show each time individually (so Wheelspin should be 2:54).

I'd also like to clean up the formatting to make it look a bit nicer in chat, but I'm not sure how I'd go about that.

You also mentioned hosting the API on my own server. I don't have a server - is there any way of hosting it on my own PC? And if so, how would I do that? I assume that would result in the command only working if my PC is turned on? I'm generally good with computers but I have absolutely 0 coding knowledge so all of this is very foreign to me.

EDIT: You mentioned you're grabbing from the "Splits" spreadsheet - that is probably why they aren't showing correctly, as that's how they are displayed on that sheet. The "Times" or "Times Clean" sheet has the preferred layout of times to be displayed in the command

here the updated version. it uses the Times clean sheet

!editcom !search $(eval q="$(query)";p=$(urlfetch json http://18.219.12.147:54321/?id=1I5SxpVLb0VasLR4f4qjdw6v5glMU9_BB00_C2M8PE4g&sheet_id=3); $(urlfetch https://pastebin.com/raw/0BycRFS8))

no you can't just start the program on your computer and expect it to work. its way more complex than that. you have to take care of firewall and open a port (54321 in this case). in the end its just not possible to host it on your own computer.

I am not deleting it from my server anytime soon. so you are probably fine.

Hi - getting this error message now in my Twitch chat when I try to use the command "Unexpected identifier 'Connecting' [<context>:1:16]". Any ideas?

yeah my server was down for some reason.
my bad. I really don't know why.

i know why you are here
i have changed my server. so i changed the IP.
please use http://surajbhari.info:54321 instead of http://18.219.12.147:54321