Hyperlink to a specific sheet

Question

I would like to open a specific sheet of a Google Sheets from a hyperlink in another spreadsheet.

I have different links in my master spreadsheet and each should have a hyperlink to the same slave spreadsheet but to a different sheet.

I know hyperlink function but it doesn't go to a specific sheet.

Solution 1

You can use this custom script (Tools > Script Editor) function and connect it with e.g. custom drawing (Insert > Drawing... > Save and Close, then right click on new drawing> Assign Script... > "goToSheet2")

function goToSheet2() {
  goToSheet("Sheet2");
}

function goToSheet(sheetName) {
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  SpreadsheetApp.setActiveSheet(sheet);
}

Update:
In the newest version you can select cell and add link (Insert > Link) and select link to specific sheet directly:

Solution 2

The HYPERLINK function can link to another sheet in the same workbook; if you observe the URL of the spreadsheet, at the end of it there is #gid=x where x is unique for each sheet.

The problem is, it will open the sheet as a new instance of the spreadsheet in another tab, which is probably not desirable. The workaround would be to insert images or drawings as buttons, and assigning a script to them that will activate specific sheets.

Solution 3

I personnaly did this based on what @rejthy said: In scripts I created this function:

/**
 * Return the id of the sheet. (by name)
 *
 * @return The ID of the sheet
 * @customfunction
 */
function GET_SHEET_ID(sheetName) {
    var sheetId = SpreadsheetApp.getActive().getSheetByName(sheetName).getSheetId();
    return sheetId;
}

and then in my sheet where I need the link I did this: =HYPERLINK("#gid="&GET_SHEET_ID("Factures - "&$B$1);"Année en cours")