404 while trying to fetch sheet data as json

user_16675025 0
Viewed 653

I'm trying to load my sheet contents by calling it as a json endpoint.

fetch("https://spreadsheets.google.com/feeds/list/MYSHEETID/1/public/values?alt=json")
                
                .then(res => res.json())
                .then(json => {
                    const data = [] /* this array will eventually be populated with the contents of the spreadsheet's rows */

I'm using the method described at https://benborgers.com/posts/google-sheets-json

It works about 30% of the time and I get a 404 the other 70% of the time. I've been struggling with it for days and can't figure out why it's failing.

google-sheets-api
3 Answers

I changed to the API V4, but I noticed the author of the link you provided updated the code.

https://benborgers.com/posts/google-sheets-json

const spreadsheetId = '...'
fetch(`https://docs.google.com/spreadsheets/d/${spreadsheetId}/gviz/tq?tqx=out:json`)
    .then(res => res.text())
    .then(text => {
        const json = JSON.parse(text.substr(47).slice(0, -2))
    })
user_150842900

You need to share your spreadsheet to at least allow everyone to read. Then

function getJson(id,gid){
  var txt = UrlFetchApp.fetch(`https://docs.google.com/spreadsheets/d/${id}/gviz/tq?tqx=out:json&tq&gid=${gid}`).getContentText();
  var jsonString = txt.match(/(?<="table":).*(?=}\);)/g)[0]
  var json = JSON.parse(jsonString)
  return(json)
}
  • with id of the spreadsheet and gid of your sheet (may be 0)
  • note that first row is dedicated to labels, then rows[0] corresponds to row#2 and c[0] corresponds to column A
  • if you want to retrieve B1 (column 2 row 1) : json.cols[1].label
  • if you want to retrieve B2 (column 2 row 2) : json.rows[0].c[1].v
user_150193800
user_167557890
Related