{"id":270,"date":"2024-02-27T17:45:27","date_gmt":"2024-02-27T17:45:27","guid":{"rendered":"https:\/\/technology.corporatesstore.com\/?page_id=270"},"modified":"2024-03-02T05:31:20","modified_gmt":"2024-03-02T05:31:20","slug":"apps-script","status":"publish","type":"page","link":"https:\/\/technology.corporatesstore.com\/index.php\/webhosting-fundamentals\/apps-script\/","title":{"rendered":"Apps Script"},"content":{"rendered":"\n

List Published URLs for Tabs<\/strong><\/p>\n\n\n\n

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<\/a><\/p>\n\n\n\n

function ListPublishedURLs() {<\/mark><\/p>\n\n\n\n

\u00a0 const baseUrl = “<Published URL of Entire Document>”; \/\/ Please modify this for your URL.<\/mark><\/p>\n\n\n\n

\u00a0 return SpreadsheetApp.getActiveSpreadsheet().getSheets().map(s => `${baseUrl}?single=true&gid=${s.getSheetId()}`);<\/mark><\/p>\n\n\n\n

}<\/mark><\/p>\n\n\n\n

Copy and paste the above script to the script editor of Google Spreadsheet as follows:<\/p>\n\n\n\n

Sheet –> Extenstions –> Apps Script –> Files –> code.gs<\/a><\/p>\n\n\n\n

Replace baseURL with the published URL of entire google sheet as follows:<\/p>\n\n\n\n

File –> Share –> Publish to Web –> Link –> Entire Document –> Copy the Published URL.<\/p>\n\n\n\n

Add sheets to services as follows:<\/p>\n\n\n\n

Services –> Google Sheets API –> Add.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Give a name to the Project, and Save:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Run the Project, and Review Permissions as follows:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Choose and Account:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Allow access to the project as follows:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Verify the execution log as follows:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

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.<\/p>\n\n\n\n

List Tabs with URL<\/strong><\/p>\n\n\n\n

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’.<\/a><\/p>\n\n\n\n

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(); }<\/mark><\/p>\n\n\n\n

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.<\/p>\n","protected":false},"excerpt":{"rendered":"

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() { \u00a0 const baseUrl = “<Published URL of Entire Document>”; \/\/ Please modify this for your URL. \u00a0 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 –> […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":240,"menu_order":0,"comment_status":"open","ping_status":"closed","template":"","meta":{"_angie_page":false,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"footnotes":""},"class_list":["post-270","page","type-page","status-publish","hentry"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t