Is there a custom function or script that returns gid of a specific sheet in Google Sheets?

Question

I have been poring through the vast sea of knowledge on the Internet to get what I need but to no avail.

Context

In a single Google Sheets file, I have one master sheet and various other sheets named after the last names of people.

In the master sheet, I created a cell in which I can type the name of any person. I would like to look up sheets for multiple individuals, so my search is dynamic. Anyway, let's call this cell the SEARCH CELL.

Right next to the SEARCH CELL, I have another cell in which I would like to add a hyperlink to another sheet which bears the last name entered in the SEARCH CELL. Let's call this cell with the hyperlink the HYPERLINK CELL. So, when I click on the hyperlink in this cell, I would like to jump to the sheet with the last name of the individual that matches what had been entered in the SEARCH CELL.

The help I need is with this HYPERLINK CELL.

=hyperlink("#gid=               ")

How do I extract the gid of each sheet and enter in the blank space in the code above?

I am told that there is no Google Sheets function for this, so a custom function or script is needed. Can anyone please provide one that works (i.e. runs without errors) when it is entered in the Script Editor?

player0 player0 · Accepted Answer · 2019-11-05T13:08:31

script:

function SHEETLIST() {
try {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets()
  var out = new Array( sheets.length+1 ) ;
  out[0] = [ "NAME" , "#GID" ];
  for (var i = 1 ; i < sheets.length+1 ; i++ ) out[i] = 
  [sheets[i-1].getName() , sheets[i-1].getSheetId() ];
  return out
}
catch( err ) {
  return "#ERROR!" }}

formula:

=SHEETLIST()

turn sheet names into active hyperlinks:

=ARRAYFORMULA(HYPERLINK("#gid="&
 QUERY(INDEX(SHEETLIST();;2); "offset 1"); 
 QUERY(INDEX(SHEETLIST();;1); "offset 1")))

and if you want to bind it to manual input you can use VLOOKUP like

=ARRAYFORMULA(IFNA(VLOOKUP(A1, HYPERLINK("#gid="&
 QUERY(INDEX(SHEETLIST();;2); "offset 1"); 
 QUERY(INDEX(SHEETLIST();;1); "offset 1")); 1; 0)))

where A1 is your "search cell"

Is there a custom function or script that returns gid of a specific sheet in Google Sheets?

1 Answers