Google Sheets v4 Update Effect JSON Endpoint Data? Shut down on June 8, 2021

Question

I read this article about a Google Sheet API v4 update. Do I need to change the current code structure?

I am using simple JSON data for displaying Google sheet data on the web. I made some PHP and JavaScript base project on the web, using the below code below:

.HTML
 var sf = https://spreadsheets.google.com/feeds/list/1l7VfPOI3TYtPuBZlZ-JMMiZW1OK6rzIBt8RFd6KmwbA/1/public/values?alt=json

 $.getJSON(sf, function(data) {
 var sd = data.feed.entry;

 key = data.feed.entry[i]['gsx$key']['$t'];
 name = data.feed.entry[i]['gsx$name']['$t'];
 img = data.feed.entry[i]['gsx$img']['$t'];
 rice = data.feed.entry[i]['gsx$price']['$t'];

.php

$url = 'https://spreadsheets.google.com/feeds/list/1l7VfPOI3TYtPuBZlZ-JMMiZW1OK6rzIBt8RFd6KmwbA/1/public/values?alt=json';
$file = file_get_contents($url); 
$json = json_decode($file);

$rows = $json->{'feed'}->{'entry'};

$key = $row->{'gsx$key'}->{'$t'};
$price = $row->{'gsx$price'}->{'$t'};
$img = $row->{'gsx$img'}->{'$t'};
$name = $row->{'gsx$name'}->{'$t'};

Do I really need to update anything?

Answer 1

I believe your goal as follows.

• You want to change the endpoint from Sheets API v3 to others.

Issue and workaround:

At Sheets API v3, this API could be used without the access token and API key. But in the case of Sheets API v4, the access token and/or API key are required to be used. It seems that this is the current specification. When I saw your script, the access token and API key are not used. So in this answer, as a workaround, I would like to propose the method for retrieving the values from the Spreadsheet without the access token and API key. The endpoint is as follows.

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

This is the endpoint used with the Query Language. As an important point, in this case, the Google Spreadsheet is required to publish as the Web publish. Please be careful this. When I saw the URL of your Spreadsheet, it seems that the Spreadsheet is published as the Web publish. So your Spreadsheet can be used with the following script.

In this answer, the script of Javascript is used.

Sample script 1:

In this sample script, the values of Spreadsheet are directly retrieved as the CSV data.

var sf = "https://docs.google.com/spreadsheets/d/1l7VfPOI3TYtPuBZlZ-JMMiZW1OK6rzIBt8RFd6KmwbA/gviz/tq?tqx=out:csv";
$.ajax({url: sf, type: 'GET', dataType: 'text'})
.done(function(csv) {
  console.log(csv);
})
.fail((e) => console.log(e.status));

Sample script 2:

In this sample script, the values of Spreadsheet are retrieved as JSON data, and parsed it as the header row and values.

var sf = "https://docs.google.com/spreadsheets/d/1l7VfPOI3TYtPuBZlZ-JMMiZW1OK6rzIBt8RFd6KmwbA/gviz/tq?tqx=out:json";
$.ajax({url: sf, type: 'GET', dataType: 'text'})
.done(function(data) {
  const r = data.match(/google\.visualization\.Query\.setResponse\(([\s\S\w]+)\)/);
  if (r && r.length == 2) {
    const obj = JSON.parse(r[1]);
    const table = obj.table;
    const header = table.cols.map(({label}) => label);
    const rows = table.rows.map(({c}) => c.map(e => e ? (e.v || "") : "")); // Modified from const rows = table.rows.map(({c}) => c.map(({v}) => v));

    console.log(header);
    console.log(rows);
  }
})
.fail((e) => console.log(e.status));

Sample script 3:

For example, when Sheets API v4 is used, the sample script is as follows. In this case, the API key is used. And it is required to publicly shared the Spreadsheet. Please be careful this.

var url = "https://sheets.googleapis.com/v4/spreadsheets/1l7VfPOI3TYtPuBZlZ-JMMiZW1OK6rzIBt8RFd6KmwbA/values/Sheet1?key=###"
$.getJSON(url, function(data) {
  console.log(data.values);
});

References:

• Query Language Reference
• Response Format
• Method: spreadsheets.get of Sheets API v4