NightDev Community
Ask on Discord

Fetching data from Google Spreadsheet

Asked Feb 1, 2021·12 replies·Last activity 5 years ago·Open in Discord ↗
Archived from the original community forum
Things may have changed since it was written. Still stuck? Ask the community on Discord.
Ask on Discord
Wavy ParrotOriginal post

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.

12 replies

Deft Penguin

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

Deft Penguin

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!`)
Wavy Parrot

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?

Deft Penguin

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

Wavy Parrot

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?

Deft Penguin

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

Wavy Parrot

I'd like to display the values of A1 -> G1

Deft Penguin

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!`)
Wavy Parrot

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

Deft Penguin

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!`)

Didn't find your answer? The community is on Discord.

Ask on Discord