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.

CodePudding user response：

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.

CodePudding user response：

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()