How to navigate to a certain sheet in Google Sheets?

Question

In my previous related question, I asked how to navigate to a certain column, however, now I also want to navigate to a certain sheet.

So I need a link from a cell, and by clicking it, I want to jump to the first row of a column (hardcoded in that cell) from a hardcoded sheet in that same sheet.

The code for navigating to a certain column is:

="https://docs.google.com/spreadsheets/d/1J-lBYHVVGjX1e8e_ZpeiNJIywJxfSW9waliWbJ2ICmM/edit#gid=0&range="& ADRES(1; KOLOM(D$1); 4)

I expect something like SHEET? should be added but that function does not exist.

What code is needed? Preferably I would use the NAME of the sheet instead of a number.

Answer 1

Actually, there is no need to add anything - you already have all you need. Sheet references are handled by GID (Google Sheets' ID of particular sheet). The first sheet is always #gid=0 and any additional sheet gets generated unique ID (like for example: #gid=2089923721) so all you need to do is to navigate to it by changing GID number to correspond with the sheet you wish to jump to.

If you want to use sheet names instead of GIDs you will need to play with this script and VLOOKUP formula:

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!" 
}
}