Did Google Sheets stop allowing json access?

Question

I have an app that opens the json version of a spreadsheet that I've published to the web. I used the instructions on this website: https://www.freecodecamp.org/news/cjn-google-sheets-as-json-endpoint/

It's been working fine for a couple months, but today I realized that the url of my json file is no longer working since yesterday. It gives the message, "Sorry, unable to open the file at this time. Please check the address and try again." The regular link to view the spreadsheet as a webpage still works though.

Did Google drop support for this feature? Is there another way to get the data of a spreadsheet in json format through a URL? I started looking into the Google Developer API, but it was really confusing.

You can get the answer for the similar question. https://stackoverflow.com/a/70985269/12913325 — Jay_Panchal, Feb 08 '22 at 05:37
Although it's still possible to get the JSON, I recommend switching to the CSV API. See [How to export a csv from Google Sheet API?](https://stackoverflow.com/a/61107170/6243352) to build the URL, then Papaparse to parse the CSV. — ggorlen, Jun 01 '22 at 00:02

doubleunary · Accepted Answer · 2022-02-04T08:57:51.733

23

You are using the JSON Alt Type variant of the Google Data protocol. This protocol is dated and appears to no longer work reliably. The GData API Directory tells:

Google Spreadsheets Data API: GData version is still live. Replaced by the Google Sheets API v4.

Google Sheets API v4 is a modern RESTful interface that is typically used with a client library to handle authentication and batch processing of data requests. If you do not want to do a full-blown client implementation, David Kutcher offers the following v4 analog for the GData JSON Alt Type, using jQuery:

GData (old version, not recommended):

var url = 'https://spreadsheets.google.com/feeds/list/' +
           spreadsheet_id + '/' + tab_ordinal + '/public/values?alt=json';
($.getJSON(url, 'callback=?')).success(function(data) {
  // ...
};

V4 (new version, recommended):

var url = 'https://sheets.googleapis.com/v4/spreadsheets/' +
           spreadsheet_id + '/values/' + tab_name +
           '?alt=json&key=' + api_key;
($.getJSON(url, 'callback=?')).success(function(data) {
  // ...
};

...where:

spreadsheet_id is the long string of letters and numbers in the address of the spreadsheet — it is the bit between /d/ and /edit
tab_ordinal is number of the sheet — the first sheet that appears in the tab bar is sheet number 1, the second one is 2, and so on
tab_name is the name of the sheet, i.e., the name you see in the tab bar at the bottom of the window when you have the spreadsheet open for editing
api_key is the API key you get from from Google Cloud Platform console

Note that the JSON output format differs between the two versions.

With the GData pattern, the spreadsheet needs to be shared as File > Share > Publish to the web.

With the V4 pattern, the spreadsheet needs to be shared as File > Share > Share with others > anyone with the link can view.

edited Feb 04 '22 at 08:57

answered Aug 18 '21 at 08:12

doubleunary

13,842
3
18
51

Nice, so I'm able to access the data using the new URL format. But the app I'm using just takes a URL and the "coordinates" of a piece of data in a JSON file and retrieves that data. As in, something like ".feed.entry[17].gs$cell.$t". But this new format doesn't label each section of data. Is there something I can add to the URL that makes it a "classic" JSON file like what I had before? – Dima Aug 18 '21 at 16:52
No, I do not think so. It is a different API with a different interface and a different result format. You will have to talk to the people who make the "app" you mention. – doubleunary Aug 18 '21 at 17:20
Oh ok, thank you. I'll probably just use regex instead of their inbuilt JSON function. – Dima Aug 18 '21 at 17:25
1

It would appear that the v4 API requires an API key to be sent? Or is there a way that you can register your API so a key is not needed to access some spreadsheet data? – Jeff White Aug 19 '21 at 20:23
hii @doubleunary i tried his but it gives me error with code 403 with this json format , how do i get the access of the sheet. The jsonFormat is "error": { "code": 403, "message": "The caller does not have permission", "status": "PERMISSION_DENIED" } } – Jay_Panchal Feb 04 '22 at 06:42
@JAY_Panchal check your sharing settings. Edited the answer. – doubleunary Feb 04 '22 at 08:59
yes i was not sharing it to public it was private thanks for the help @doubleunary – Jay_Panchal Feb 04 '22 at 09:38

score 16 · Answer 2 · answered Mar 03 '22 at 09:04

As of March 2022:

If you dont want to create a key you can use this URL format:

https://docs.google.com/spreadsheets/d/{spreadsheetId}/gviz/tq

which downloads a json.txt file of the format

google.visualization.Query.setResponse({json});

From that you would have to slice out the json

-OR --

Just configure a key as per the Official docs.

Go to Google Console and create a project (or use an existing one)
Goto Credenetials page and create a API Key
Include Sheets API from library
And Voila!

You can now get json using URL Format:

https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{sheetName}?alt=json&key={theKey}

Edit: The Sheet should be public and Anyone with link can view

Mike Steelson · Answer 3 · 2021-09-19T13:02:05.973

Without jQuery ...

var url = 'https://docs.google.com/spreadsheets/d/'+id+'/gviz/tq?tqx=out:json&tq&gid='+gid;

with id of the spreadsheet and gid of the sheet https://codepen.io/mikesteelson/pen/wvevppe example :

var id = '______your_speadsheet_id________';
var gid = '0';
var url = 'https://docs.google.com/spreadsheets/d/'+id+'/gviz/tq?tqx=out:json&tq&gid='+gid;
fetch(url)
  .then(response => response.text())
  .then(data => document.getElementById("json").innerHTML=myItems(data.substring(47).slice(0, -2))  
  );
function myItems(jsonString){
  var json = JSON.parse(jsonString);
  var table = '<table><tr>'
  json.table.cols.forEach(colonne => table += '<th>' + colonne.label + '</th>')
  table += '</tr>'
  json.table.rows.forEach(ligne => {
    table += '<tr>'
    ligne.c.forEach(cellule => {
        try{var valeur = cellule.f ? cellule.f : cellule.v}
        catch(e){var valeur = ''}
        table += '<td>' + valeur + '</td>'
      }
    )
    table += '</tr>'
    }
  )
  table += '</table>'
  return table
}

Nice, this seems to mostly work, but do you know if there's a way to get the data without "/*O_o*/ google.visualization.Query.setResponse(" preceding the data? — Dima, Aug 28 '21 at 20:10
Take this part of the response `data.substring(47).slice(0, -2)` or `data.substring(117).slice(0, -3)` and parse it as a json. — Mike Steelson, Aug 29 '21 at 00:33

score 2 · Answer 4 · answered Aug 27 '21 at 03:52

2

gdata is the older version of Sheets API and it's shut down. See Google's announcement here https://cloud.google.com/blog/products/g-suite/migrate-your-apps-use-latest-sheets-api

answered Aug 27 '21 at 03:52

Will Tan

21
1

Did Google Sheets stop allowing json access?

4 Answers4

Linked

Related