CODEWITHSUNDEEP
javascriptgoogle-sheetsgoogle-sheets-api
MakeHellTal
MakeHellTal

Reputation: 626

spreadsheets.values.get but with other sheets

So I was trying to read values from a spreadsheet in JS (not Node), and I came across https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get. This looks like the right thing for me, but what if I'm trying to read values from different sheets?

For example, how would I use spreadsheets.values.get to read A1:D1 in Sheet 2 in https://docs.google.com/spreadsheets/d/e/2PACX-1vQLrcAW_bL5HSVHorBJisdwv8S5_6Th9EP2wiYLmJKktj41uXVepUNOx4USNGdsVAuDOH_qknWs3pGa/pubhtml Or is there another method I'm missing out on?

Upvotes: 0

Views: 1883

Answers (1)

Tanaike
Tanaike

Reputation: 201358

I believe your goal as follows.

  • You want to retrieve the values from the cells A1:D1 in the sheet of Sheet2.
  • You want to achieve this using the method of spreadsheets.values.get in Sheets API with Javascript which is not Node.js.
  • You have already been able to get values from Google Spreadsheet using Sheets API.

For this, how about this answer?

Pattern 1:

In this pattern, the function of makeApiCall() of the sample script (Browser) of this official document is used.

Sample script:

var params = {
  spreadsheetId: '###',  // Please set the Spreadsheet ID.
  range: 'Sheet2!A1:D1',  // Please set the range as a1Notation.
};
var request = gapi.client.sheets.spreadsheets.values.get(params);
request.then(function(response) {
  console.log(response.result.values);
}, function(reason) {
  console.error('error: ' + reason.result.error.message);
});
  • The spreadsheet ID is different from 2PACX-.... So please be careful this. Please check this official document.
  • In this case, it supposes that you have already done the authorization process for using Sheets API.

Pattern 2:

In this pattern, your URL like https://docs.google.com/spreadsheets/d/e/2PACX-... is used. From your URL in your question, it is found that the Spreadsheet is published to Web. In this case, only when the values are retrieved from the web published Spreadsheet, you can also use the following script.

Sample script:

const url = "https://docs.google.com/spreadsheets/d/e/2PACX-1vQLrcAW_bL5HSVHorBJisdwv8S5_6Th9EP2wiYLmJKktj41uXVepUNOx4USNGdsVAuDOH_qknWs3pGa/pub?gid=###&range=A1%3AD1&output=csv";
fetch(url).then(res => res.text()).then(txt => console.log(txt));
  • In this case, please set the sheet ID to ### of gid=###.

References:

Upvotes: 1

Related Questions