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 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.