Question

0

186

Views

How to migrate google sheets API v3 to google sheets Api v4

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.com/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.com/v4/spreadsheets/SPREADSHEET_ID/values/RANGE?key=API_KEY)

But this is giving me a 403 response.

about 4 years ago · Juan Pablo Isaza

2 answers

Answer question

0

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:

about 4 years ago · Juan Pablo Isaza Report

Answer question

Find remote jobs

Accepted Answer · 2022-08-31T09:09:33.768Z

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 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 API, 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.

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.

0

186