Linking to another tab in Google Sheets

Question

I have a Google Sheets workbook with more than 70 tabs, or sheets, in it. In one of them, I have a master list of all of them. For better reference, in the tab "PropertyID", and the column F, from 2 and downwards, I have the name of each other sheet in the workbook. I want to find a way to hyperlink each sheet into the PropertyID tab, so that if someone presses a link in this main "menu", it'd take them to the specific sheet. So far, I've been making a comment on each sheet, and using the "Link to comment" link to do what I want, but this process is tedious, must be done manually, and if I have to download the workbook to excel for easier editing, and then back to sheets, all the coments are turned into 'notes' and the links are lost. Any way I could accomplish this?

Answer 1

Here is a forum which is similar to your concern.

[Source.]

When you switch to a different sheet in Google Spreadsheets, pay attention to the URL in your browser's address bar. At the end of the URL you should see something like:

#gid=0

This number changes when you switch sheets, and specifies which sheet to display. Copy the entire URL and create a hyperlink to it with this formula:

=hyperlink("https://docs.google.com/spreadsheet/ccc?key=0AsaQpHJE_LShcDJ0dWNudHFZWVJqS1dvb3FLWkVrS0E#gid=0", "LINK TEXT")

You can also see in the link provided above how to do it using scripts. "This menu item will open a panel with a list of names of all the sheets in the current spreadsheet. It doesn't look like it, but if you click on one of the sheet names, that sheet will come to the front."

Here is a related thread which might also help:

• How do hyperlink from one tab to another?

Hope this helps!

Answer 2

Non-formula hyperlinks are now officially supported in Google Sheets, so you now have two more ways of doing this.

1. The manual way: select a cell, insert a link (Insert > Insert Link or Ctrl+K), and add a link to the target sheet #gid=<sheet id>. Unlike HYPERLINK formulas, the sheet will open in the current tab.

2. The automatic way: create a script (Tools > Script Editor) and use setLinkUrl() to link to a sheet.
   For instance, the following function iterates through all cells in column A of sheet "Foo" starting at the 2nd row, and assigns links based on the text of the row.

   function linkRange() {
     const startRow = 2,  // Start at the second row.
           column   = 1;  // Add links to column A.
   
     const spreadsheet = SpreadsheetApp.getActive(),
           sheet = spreadsheet.getSheetByName("Foo"),
           lastRow = sheet.getLastRow();
   
     for (let row = startRow; row <= lastRow; row++) {
       const range         = sheet.getRange(row, column),
             richTextValue = range.getRichTextValue(),
             targetSheet   = spreadsheet.getSheetByName(richTextValue.getText());
   
       if (targetSheet !== null) {
         const sheetId = targetSheet.getSheetId(),
               builder = richTextValue.copy().setLinkUrl(`#gid=${sheetId}`);
   
         range.setRichTextValue(builder.build());
       }
     }
   }
   

   Note that it will discard existing links and formulas in that column.

Answer 3

How about this: from my Analyser sheet that feeds many presentation sheets.

If the sheets are dynamically created/inserted, you can insert the #gid automatically.

EDIT 1 Sheet ref can be reduced to #gid; added filter

=ArrayFormula(
    SORT(
        FILTER(
            HYPERLINK("#gid="&Analyser!H4:H,Analyser!G4:G),Analyser!H4:H>0)
    )
)