List of all tabs' "publish to web" links on a large googlesheet document (200 tabs)

Question

Is there a way to get a list of each of the hyperlinks created by the "publish to web" function on google sheets without selecting each tab individually and copying and pasting to a spreadsheet/word document. Ideally the output being all my tab names (circa 200 of them) and the link.

Any help or advice would be greatly appreciated.

Answer 1

If all you wish is tab names then this is a list of tab names:

function getTabNames() {
  const ss = SpreadsheetApp.getActive();
  Logger.log(ss.getSheets().map(sh => sh.getName()).join(','))
}

You could use openById() if you wish.

Answer 2

I believe your goal is as follows.

• You want to receive the Web Published URL for all sheets in a Google Spreadsheet using Google Apps Script.
• You want to put the URLs to the Spreadsheet.

Issue and workaround:

When a Google Spreadsheet is published to the web, a URL like https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml?gid=###&single=true is obtained. But, in the current stage, unfortunately, this cannot be retrieved using a script and API. Ref By this, it is required to manually create the URL.

In this answer, I would like to propose 2 patterns for achieving your goal.

Pattern 1:

In this pattern, a URL like https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml?gid=###&single=true is used. 2PACX-### is not the Spreadsheet ID. Please be careful about this.

First, please publish to the web for your Spreadsheet, and retrieve the URL of https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml?gid=###&single=true. In this pattern , https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml from https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml?gid=###&single=true is used.

Please copy and paste the following script to the script editor of Google Spreadsheet. And, please set your https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml to baseUrl. When you use this script, please put a custom function of =SAMPLE(). By this, the URLs are returned.

function SAMPLE() {
  const baseUrl = "https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml"; // Please modify this for your URL.

  return SpreadsheetApp.getActiveSpreadsheet().getSheets().map(s => `${baseUrl}?single=true&gid=${s.getSheetId()}`);
}

Pattern 2:

In this pattern, the URL like https://docs.google.com/spreadsheets/d/### fileId ###/pubhtml is used. In this case, Spreadsheet ID is used. By this, you are not required to do a hard copy of the URL.

Please copy and paste the following script to the script editor of Google Spreadsheet. When you use this script, please put a custom function of =SAMPLE(). By this, the URLs are returned.

function SAMPLE() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const baseUrl = `https://docs.google.com/spreadsheets/d/${ss.getId()}/pubhtml`;
  return ss.getSheets().map(s => `${baseUrl}?single=true&gid=${s.getSheetId()}`);
}

Note:

• In this case, when the sheet is not published, you cannot access the URL. Please be careful about this.

References:

• map()
• getSheetId()