0

I need to create a table of contents on my google spreadsheets but i have many sheets (over 300) so individually linking each sheet will take too long, how can i create hyperlinks using google apps script for a table of contents

stuboi234
  • 3
  • 2

1 Answers1

0

Try this. We first iterate over all the sheets in the spreadsheet. The trick here is using the function getSheetId in a sheet returns the gid instead of the Spreadsheet ID, so we just have to put it in the end of the hyperlink.

Just change the cell A for the one you need in the .getRange function:

function main(){

  var spreadsheet = SpreadsheetApp.getActive();
  var ss = spreadsheet.getSheets();
  var spsheetID = spreadsheet.getId();

  for (var i = 0; i < ss.length; i++){

       var ss_gid = ss[i].getSheetId();

       ss[0].getRange("A"+(i+1)).setFormulaR1C1('=HYPERLINK("https://docs.google.com/spreadsheets/d/'+spsheetID+'#gid='+ss_gid+'")');


  }

}
Jescanellas
  • 2,555
  • 2
  • 9
  • 20
  • If I'm not mistaken, this is a macro and won't work as a custom function. So you'd need to run the macro every time you insert a new sheet. See [How to add a table of contents to Google Sheets](https://stackoverflow.com/questions/62266249/how-to-add-a-table-of-contents-to-google-sheets) for the same question tackled through custom functions. – Fabien Snauwaert Jun 12 '20 at 09:27
  • I have never used macros. This script gets all the Sheets (over 300 according to the OP) and creates the URLs for all of them in the first Sheet. OP never mentioned he wanted to run this for each new Sheet, they already exist. Running this function once is enough for what he was asking. – Jescanellas Jun 12 '20 at 09:46
  • How do you use it? – Fabien Snauwaert Jun 12 '20 at 10:03
  • Similar to your linked question. Go to `Tools > Script Editor`, copy and paste the code and run the function. To run this every time a new Sheet is inserted you should use the [Instalalble Trigger](https://developers.google.com/apps-script/guides/triggers/installable#google_apps_triggers) onChange, but modifying my code. – Jescanellas Jun 12 '20 at 10:25
  • I see. You're running it by selecting the function in the script editor and clicking "Run". This is the same as using `Tools › Macros › Import` and then running it from `Tools › Macros`. – Fabien Snauwaert Jun 12 '20 at 12:43