I've been using a public google spreadsheet as a JSON endpoint for several of my web projects for some time now. I liked this approach because I could retrieve data from a public spreadsheet without needing any kind of authentication or tokens to get the data, all I needed to do was publish the spreadsheet and then fetch from a simple URL:

fetch(`/${mode}/${id}/${sheetNum}/public/values?alt=json`).then((res)=>console.log(res));

Google is deprecating sheets v3, and I'm confused about how to migrate to v4. From this answer I gather that I need to provide an access token which is created via the google cloud console. But do I need to create some kind of special "app" or "workplace" or will any old API token do? I tried the following:

• Create a GCP project
• Enable Google Sheets API
• Hit Create Credentials
• fetch data using the following URL scheme:

fetch()

But this is giving me a 403 response.

I've been using a public google spreadsheet as a JSON endpoint for several of my web projects for some time now. I liked this approach because I could retrieve data from a public spreadsheet without needing any kind of authentication or tokens to get the data, all I needed to do was publish the spreadsheet and then fetch from a simple URL:

fetch(`https://spreadsheets.google./feeds/${mode}/${id}/${sheetNum}/public/values?alt=json`).then((res)=>console.log(res));

Google is deprecating sheets v3, and I'm confused about how to migrate to v4. From this answer I gather that I need to provide an access token which is created via the google cloud console. But do I need to create some kind of special "app" or "workplace" or will any old API token do? I tried the following:

• Create a GCP project
• Enable Google Sheets API
• Hit Create Credentials
• fetch data using the following URL scheme:

fetch(https://sheets.googleapis./v4/spreadsheets/SPREADSHEET_ID/values/RANGE?key=API_KEY)

But this is giving me a 403 response.

• javascript
• google-sheets

Share Improve this question Follow edited Aug 14, 2021 at 13:06 MeDead 19722 silver badges99 bronze badges asked Aug 13, 2021 at 17:49 ANimator120ANimator120 3,45133 gold badges3434 silver badges6565 bronze badges

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 6

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 API, not remended):

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

V4 (new API, remended):

var url = 'https://sheets.googleapis./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.

The old GData JSON Alt Type variant API requires that the spreadsheet is made public through File > Publish to the web. V4 requires that the file is shared with "anyone with the link can view" through File > Share.

FINDINGS

The URL scheme produces the error 403 because mostly likely the spreadsheet file isn't shared publicly like this:

I can also replicate the 403 error when my test sheet isn't shared publicly using the same URL scheme:

RECOMMENDATION:

You can double check the spreadsheet file you're accessing and check it's sharing permission, making sure it's set to Anyone with the link:

TEST RESULT

Publicly shared test sheet

After setting up the spreadsheet file's sharing permission to Anyone with the link using the same v4 URL scheme: