Get a cell value from a public Google spreadsheet using v 4 API

Question

I'm using this as a sample - https://docs.google.com/spreadsheets/d/1hbHqrnYa4oMUquZcq8WkTJk0kI0t9scGBwro-EH-ALA/edit#gid=0

How do I get a cell value, say A2 form this using API v4 ?

function myCallback(spreadsheetdata)
{
    console.log(spreadsheetdata);
    // spreadsheetdata.A2 
}

<script src="https://spreadsheets.google.com/feeds/cells/1hbHqrnYa4oMUquZcq8WkTJk0kI0t9scGBwro-EH-ALA/od6/public/values?alt=json-in-script&callback=myCallback"></script>

Answer 1

I believe your goal as follows.

• You want to retrieve the value of the cell "A2" from the Google Spreadsheet of https://docs.google.com/spreadsheets/d/1hbHqrnYa4oMUquZcq8WkTJk0kI0t9scGBwro-EH-ALA/edit#gid=0 using Javascript.

For this, how about this answer?

When the Spreadsheet is not publicly shared and not published to Web, the access token retrieved by OAuth2 and/or the service account is required to be used. In this case, the script becomes a bit complicated. But when I saw your Spreadsheet, it seems that the Spreadsheet is shared publicly and published to Web. In this case, the value can be retrieved without the access token. So the script becomes simpler.

Pattern 1:

In this pattern, the value of cell "A2" is retrieved without the authorization.

Sample script:

const url = "https://docs.google.com/spreadsheet/pub?key=1hbHqrnYa4oMUquZcq8WkTJk0kI0t9scGBwro-EH-ALA&range=A2&output=csv";
fetch(url)
.then(response => response.text())
.then(text => {
  console.log(text)
});

or

const url = "https://docs.google.com/spreadsheet/pub?key=1hbHqrnYa4oMUquZcq8WkTJk0kI0t9scGBwro-EH-ALA&output=csv";
fetch(url)
.then(response => response.text())
.then(text => {
  const ar = text.split("\n").map(r => r.split(","));
  console.log(ar[2][0]);
});

• When above scripts are used for your Spreadsheet, a of the cell "A2" is returned.

Pattern 2:

In this pattern, the value of cell "A2" is retrieved using the method of spreadsheets.values.get in Sheets API with the API key. In this case, please retrieve your API key, and set to apiKey.

Sample script:

<script async defer src="https://apis.google.com/js/api.js" onload="handleClientLoad()"></script>
<script>
function handleClientLoad() {
  const apiKey = "###";  // Please set your API key.

  gapi.load('client', () => {
    gapi.client.setApiKey(apiKey);
    gapi.client.load('sheets', 'v4', () => {
      gapi.client.sheets.spreadsheets.values.get({
        spreadsheetId: "1hbHqrnYa4oMUquZcq8WkTJk0kI0t9scGBwro-EH-ALA",
        range: 'Sheet1!A2',
      }).then(res => {
        const value = res.result.values[0][0];
        console.log(value);
      });
    });
  });
}
</script>

• When above script is used for your Spreadsheet, a of the cell "A2" is returned.

References:

• Publish Google Sheet document to the web using Google Sheets API
• Using Fetch
• Method: spreadsheets.values.get