How to get all tab names/gid's with google.visualization from google spreadsheet?

Question

I can get all data from tab spreadsheet using sheet=Jan24_12pm, but can't find out how to get all tab names or gid's of a spreadsheets? In this example there is 11 tabs/sheets

function drawChart() {
var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/169AP3oaJZSMTquxtrkgFYMSp4gTApLTTWqo25qCpjL0/gviz/tq?sheet=Jan24_12pm&tq=select%20*');
                query.send(drawTable);
            }

UPD: I am not the owner and the document is not published "We're sorry. This document is not published.". But I can access it with google.visualization.Query

Thanks

Answer 1

You can get this information easily with Sheets API.

Just make a call to spreadsheets.get and provide your spreadsheetId as a parameter. You will get the spreadsheet resource as a response, which contains a field called sheets, whose value is an array with information of each sheet. Inside sheetProperties you can find both sheetId and title (the name of the sheet).

The JSON response would be something like this:

{
  "sheets": [
    {
      "properties": {
        "sheetId": "specific-sheet-id",
        "title": "specific-sheet-name",
        // ... rest of sheet properties
      },
      // ... rest of sheet fields
    },
    // ... rest of sheets
  ],
  // ... rest of spreadsheet fields
}

Reference:

• spreadsheets.get
• Spreadsheet resource
• Sheet resource
• SheetProperties

I hope this is of any help.

Answer 2

• You want to retrieve all sheet names in the Spreadsheet without the API key and the access token.
• In your situation, the Spreadsheet is publicly shared.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Pattern 1:

In this pattern, the sheet names are retrieved by Web Apps. Web Apps is used as the wrapper. At the client side, you can retrieve the sheet names from the Spreadsheet using the Web Apps.

Usage:

1. Create new project of Google Apps Script.

Sample script of Web Apps is a Google Apps Script. So please create a project of Google Apps Script.

If you want to directly create it, please access to https://script.new/. In this case, if you are not logged in Google, the log in screen is opened. So please log in to Google. By this, the script editor of Google Apps Script is opened.

2. Copy and paste script.

Please copy and paste the following script.

function doGet(e) {
  var spreadsheetId = e.parameter.id;
  var sheets = SpreadsheetApp.openById(spreadsheetId).getSheets();
  var sheetNames = sheets.map(function(e) {return e.getSheetName()});
  return ContentService.createTextOutput(JSON.stringify({sheetNames: sheetNames})).setMimeType(ContentService.MimeType.JSON);
}

3. Deploy Web Apps.

1. On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
2. Select "Me" for "Execute the app as:".
3. Select "Anyone, even anonymous" for "Who has access to the app:".
4. Click "Deploy" button as new "Project version".
5. Automatically open a dialog box of "Authorization required".
   1. Click "Review Permissions".
   2. Select own account.
   3. Click "Advanced" at "This app isn't verified".
   4. Click "Go to ### project name ###(unsafe)"
   5. Click "Allow" button.
6. Click "OK".
7. Copy the URL of Web Apps. It's like https://script.google.com/macros/s/###/exec.
   • When you modified the Google Apps Script, please redeploy as new version. By this, the modified script is reflected to Web Apps. Please be careful this.

4. Sample script for your script.

In order to retrieve the sheet names from Web Apps at the client side, please use the following script.

var url = "https://script.google.com/macros/s/###/exec?id=169AP3oaJZSMTquxtrkgFYMSp4gTApLTTWqo25qCpjL0";
fetch(url)
.then(function(response) {
  return response.json();
})
.then(function(obj) {
  console.log(obj.sheetNames) // You can see the sheet names as an array.
});

• Please set the URL of Web Apps retrieved above. In above script, the Spreadsheet ID is sent using the query parameter.
• In this case, you can retrieve all sheet names without using the API key and the access token.

Pattern 2:

In this pattern, the sheet names are retrieved by publishing the Spreadsheet to Web.

Usage:

1. Publish Spreadsheet to Web.

Please publish the Spreadsheet to Web. Ref

2. Sample script for your script.

var url = "https://spreadsheets.google.com/feeds/worksheets/169AP3oaJZSMTquxtrkgFYMSp4gTApLTTWqo25qCpjL0/public/basic?alt=json";
fetch(url)
.then(function(response) {
  return response.json();
})
.then(function(obj) {
  const sheetNames = obj.feed.entry.map( e => e.title["$t"]);
  console.log(sheetNames) // You can see the sheet names as an array.
});

• In this case, all sheet names can be directly retrieved from Spreadsheet. But this pattern uses Sheets API v3. So now you can confirm that all sheet names can be retrieved by above script. But Sheets API v3 is shut down on March 3, 2020. So unfortunately, I cannot recommend to use this pattern. Also, at Sheets API v4, the access token and/or API key are required to retrieve the values from Spreadsheet. In this case, I think that iamblichus's answer is suitable.

References:

• Web Apps
• Taking advantage of Web Apps with Google Apps Script
• Make Google Docs, Sheets, Slides & Forms public
• Projection

If I misunderstood your question and this was not the direction you want, I apologize.

Answer 3

If you would just like to get all the tabs in the spreadsheet, the following script would get you the spreadsheet ID and a list of all the tabs in the spreadsheet. The spreadsheet ID for the link you shared

https://docs.google.com/spreadsheets/d/1yZv9w9zRKwrGTaR-YzmAqMefw4wMlaXocejdxZaTs6w/htmlview?usp=sharing&sle=true# is the portion in bold (1yZv9w9zRKwrGTaR-YzmAqMefw4wMlaXocejdxZaTs6w).

But it is retired, so the new link https://docs.google.com/spreadsheets/d/1wQVypefm946ch4XDp37uZ-wartW4V7ILdg-qYiDXUHM/htmlview?usp=sharing&sle=true ID is 1wQVypefm946ch4XDp37uZ-wartW4V7ILdg-qYiDXUHM.

function GetTabs(){
  var app = SpreadsheetApp;
  var ss = app.openById('1wQVypefm946ch4XDp37uZ-wartW4V7ILdg-qYiDXUHM');
  var name = ss.getName();
  Logger.log(name);
  var tabs = new Array()
  var sheets = ss.getSheets();
  for (var i=0 ; i<sheets.length ; i++) tabs.push( [ sheets[i].getName() ] )
  //return tabs  
  Logger.log(tabs)
}

I have tested, and was able to retrieve the tabs, you can view them from the Logs (ctrl+enter when in google script). Basically, the script calls the spreadsheet using its ID and then retrieve all the tabs from it. If you require any additional changes or if I misunderstood your question, feel free to clarify.