jsongoogle-sheetsgoogle-sheets-apigoogle-data-api

Did Google Sheets stop allowing json access?


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.


Solution

  • 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:

    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.