I'm trying to get some data from google sheet using javascript. There is multiple worksheet in my sheet. I'm successfully getting all data for single worksheet. But i need to get all data from all worksheet. Here is my code for single worksheet.

$.ajax({
    type: 'get',
    url: '/{sheet_id}/values/sheet1?key={API_KEY}',
    async: false,
    success: function(response) {

    }
});

Can anyone help me to get all data for all worksheet?

I'm trying to get some data from google sheet using javascript. There is multiple worksheet in my sheet. I'm successfully getting all data for single worksheet. But i need to get all data from all worksheet. Here is my code for single worksheet.

$.ajax({
    type: 'get',
    url: 'https://sheets.googleapis./v4/spreadsheets/{sheet_id}/values/sheet1?key={API_KEY}',
    async: false,
    success: function(response) {

    }
});

Can anyone help me to get all data for all worksheet?

• javascript
• google-sheets
• google-sheets-api

1 Answer 1

I think that in your current situation, you are using spreadsheets.values.get method of Sheets API. When you want to retrieve all values from all sheets, for example, how about the following 2 patterns?

Pattern 1:

In this pattern, it uses spreadsheets.get method of Sheets API. In this case, all values can be retrieved by the following endpoint.

https://sheets.googleapis./v4/spreadsheets/### spreadsheetId ###?fields=sheets%2Fdata%2FrowData%2Fvalues%2FuserEnteredValue&key={YOUR_API_KEY}

• Fields of sheets/data/rowData/values/userEnteredValue is used for retrieving the values.

Pattern 2:

In this pattern, it uses spreadsheets.values.batchGet method of Sheets API. In this case, you are required to know each sheet name, because the sheet name is used in this method.

https://sheets.googleapis./v4/spreadsheets/### spreadsheetId ###/values:batchGet?ranges=Sheet1&ranges=Sheet2&key={YOUR_API_KEY}

• As the query parameter, please set it like ranges=Sheet1&ranges=Sheet2&....

Note:

• In your script, you use API key. In this case, the Spreadsheet is required to be publicly shared.

References:

• spreadsheets.get
• spreadsheets.values.batchGet

If I misunderstood your question, I apologize.

Edit:

• You selected the pattern 2.
• You want to create the query parameters by retrieving all sheet names in the Spreadsheet.

If my understanding is correct, how about retrieving the sheet name using spreadsheets.get method of Sheets API. The endpoint is

https://sheets.googleapis./v4/spreadsheets/### spreadsheetId ###?fields=sheets%2Fproperties%2Ftitle&key={YOUR_API_KEY}

In this case, sheets/properties/title is used as fields. By this, all sheet names can be retrieved. When this is reflected to your script, how about this modification? I think that this is a simple modification. So please modify it to your situation. And I think that there are several solutions for your situation. So please think of this as just one of them.

Modified script:

$.ajax({
  type: 'get',
  url: 'https://sheets.googleapis./v4/spreadsheets/### spreadsheetId ###?fields=sheets%2Fproperties%2Ftitle&key={API_KEY}',
  async: false,
  success: function(response) {
    const queryParams = response.sheets.reduce((s, e, i) => s += "ranges=" + e.properties.title + (i < response.sheets.length - 1? "&" : ""), "");
    $.ajax({
      type: 'get',
      url: 'https://sheets.googleapis./v4/spreadsheets/### spreadsheetId ###/values:batchGet?key={API_KEY}&' + queryParams,
      async: false,
      success: function(r) {
        console.log(r)
      }
    });
  }
});