List Published URLs for Tabs
Consider the below script as explained in https://stackoverflow.com/questions/74125629/list-of-all-tabs-publish-to-web-links-on-a-large-googlesheet-document-200-ta
function ListPublishedURLs() {
const baseUrl = “<Published URL of Entire Document>”; // Please modify this for your URL.
return SpreadsheetApp.getActiveSpreadsheet().getSheets().map(s => `${baseUrl}?single=true&gid=${s.getSheetId()}`);
}
Copy and paste the above script to the script editor of Google Spreadsheet as follows:
Sheet –> Extenstions –> Apps Script –> Files –> code.gs
Replace baseURL with the published URL of entire google sheet as follows:
File –> Share –> Publish to Web –> Link –> Entire Document –> Copy the Published URL.
Add sheets to services as follows:
Services –> Google Sheets API –> Add.
Give a name to the Project, and Save:
Run the Project, and Review Permissions as follows:
Choose and Account:
Allow access to the project as follows:
Verify the execution log as follows:
When you use this script, please put a custom function of =SAMPLE() at the address bar of Google Sheet. By this, the URLs are returned.
List Tabs with URL
Consider the following script as explained in https://support.google.com/docs/thread/25861942/create-a-list-of-the-tabs-in-a-google-workbook-updates-automatically-and-links-to-tabs?hl=en#:~:text=Check%20if%20a%20new%20menu,the%20first%20sheet%2Ftab’.
function onOpen() { SpreadsheetApp.getUi().createMenu(‘Tabs’) .addItem(‘List tabs’, ‘sheetNames’) .addToUi() } function sheetNames() { var ss, list, tar; ss = SpreadsheetApp.getActive() list = ss.getSheets().slice(1).map(function (s, ind) { return [‘=HYPERLINK(“#gid=’ + s.getSheetId() + ‘”, “‘ + s.getName() + ‘”)’]; }) tar = ss.getSheets()[0]; //List will be written to FIRST sheet in the workbook. tar.getRange(2, 4, tar.getLastRow(), 1).clearContent(); tar.getRange(2, 4, list.length, 1).setValues(list) } function onChange(e) { if ([‘REMOVE_GRID’, ‘INSERT_GRID’, ‘OTHER’].indexOf(e.changeType) > -1) { sheetNames() } } /*Only run this function ONCE. You can check if the trigger has been set correctly by clicking ‘Edit’ > ‘Triggers for current project’*/ function createOnChangeTrigger() { ScriptApp.newTrigger(“onChange”) .forSpreadsheet(SpreadsheetApp.getActive()) .onChange() .create(); }
Once the above code is used, Tab will appear in the desired Google Sheets. Click the Tab, it will display the Tab URLs in Column D of Google Sheet.