Is it possible to write a script to write the google sheet address to a specific cell?

0
votes

I'm adding hyperlinks to my google sheet that are copied throughout the worksheet. I have a table of contents with sheet names in A2:A and would like to write a google app script to write the specific sheet URL to the corresponding cell in B2:B. The hyperlinks throughout my workbook reference the TOC based on a vlookup and function correctly as long as the correct URL is held in B2:B.

I use the workbook as a template that is copied as new projects are developed. The links should change every time the workbook is copied.

1
google-apps-scriptgoogle-sheets

1 Answers

0
votes

A list of sheet names and sheet URLs

This will create your list:

function getSheetNamesAndUrls() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var shts=ss.getSheets();
  var vA=[['SheetName','SheetURL']];//if you don't want headers remove this
  for(var i=0;i<shts.length;i++) {
    vA.push([shts[i].getName(),'docs.google.com/spreadsheets/d' + ss.getId() + '/edit#gid=' + shts[i].getSheetId()]);
  }
  sh.getRange(1,1,vA.length,vA[0].length).setValues(vA);//if you already have headers then make this 2,1,vA.length...
}