Fetching data from Google Spreadsheet

Nightbot
1

Hi there,

I’m trying to fetch data from a Google Spreadsheet into Nightbots output. I’d like to use it (for example) for easy updates on a loadout in Warzone (without having to change the command every time the loadout changes, so I just update it in spreadsheets).
I’ve fixed a json output on Spreadsheets, now I’m hardstuck at the point where I want to display the values from row & column 1 (weapon), and the row 1 and column 2 (attachments). I can’t seem to figure out how Nightbot can fetch and filter this data.

Can anyone help me out on creating a command reading data from a google spreadsheet? Hope the explanation above is clear enough, ask me if theres any questions left.

2

Do you have a URL that outputs the data in plaintext?

1 Like
3

I do,

https://spreadsheets.google.com/feeds/cells/1UWiJ2vzQ3xD8KbsnIwjYKu4PDrY8VLVdaBT2QazEIcg/1/public/full?alt=json
this is the URL

Added a weapon (M16) and attachments as a test

4

I wrote this to print the cell values.

$(eval JSON.parse(decodeURIComponent(`$(querystring $(urlfetch json https://spreadsheets.google.com/feeds/cells/1UWiJ2vzQ3xD8KbsnIwjYKu4PDrY8VLVdaBT2QazEIcg/1/public/full?alt=json))`)).feed.entry.map(x=>x[`gs$cell`][`$t`]).join(` | `).slice(0,400)||`Error!`)
2 Likes
6

Thank you very much, this works like a charm. Will this command show the whole spreadsheet at once or is there a choice between cells?

7

The code as is outputs all cells but can be edited to output certain cells.

8

Can you tell me what to change in order to select for example A1 and B1? Or like a full row or a full column?

9

You can say which specific cell(s) you want to output and I’ll send code for it.

10

I’d like to display the values of A1 → G1

11

This prints row 1.

$(eval JSON.parse(decodeURIComponent(`$(querystring $(urlfetch json https://spreadsheets.google.com/feeds/cells/1UWiJ2vzQ3xD8KbsnIwjYKu4PDrY8VLVdaBT2QazEIcg/1/public/full?alt=json))`)).feed.entry.filter(x=>x[`gs$cell`][`row`]==1).map(x=>x[`gs$cell`][`$t`]).join(` | `).slice(0,400)||`Error!`)
2 Likes
12

you’re the best, thanks a lot.

13

how does this work for a column? just change the values of “row” to column and 1 to A or something?

14

No, both row & col values are numbers in the json. We can edit row to col to output the col 1 cells.

$(eval JSON.parse(decodeURIComponent(`$(querystring $(urlfetch json https://spreadsheets.google.com/feeds/cells/1UWiJ2vzQ3xD8KbsnIwjYKu4PDrY8VLVdaBT2QazEIcg/1/public/full?alt=json))`)).feed.entry.filter(x=>x[`gs$cell`][`col`]==1).map(x=>x[`gs$cell`][`$t`]).join(` | `).slice(0,400)||`Error!`)
2 Likes
15

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.