I need to add formula to the same cell in all of the sheets in a google sheets workbook

Question

I have a workbook that contains around 150 sheets and I’m trying to create an index of all of the sheets with a hyperlink from the index to each of the individual sheets.

In the index sheet i have a list of all the sheet names in column A and the formula INDIRECT("’"&A1&"'!AF1”) in column B. Then in cell AF1 of each sheet i have used script (I’ve posted this script below) to populate this cell with the spreadsheetID (URL + ID) of that spreadsheet.

Now i have the problem of adding the formula "=sheetURL()” to cell AF1 of all sheets in my workbook without having to do so manually.

function sheetURL()
{
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet().getSheetId();

 return ss.getUrl() + '#gid=' + sheet;
}

Robin Gertenbach Robin Gertenbach · Accepted Answer · 2016-10-10T14:27:03

You were close

function sheetURL(sheetName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets().filter(function(sheet) {
    return sheet.getName() == sheetName;
  })[0];

  return ss.getUrl() + '#gid=' + sheet.getSheetId();
}

This gets the "first" sheet with the desired name of which there can be only one anyway and then extracting the sheetId.

You can use it by calling =sheetURL(A1) in the index sheet and dragging down.

I need to add formula to the same cell in all of the sheets in a google sheets workbook

1 Answers