How to Link to a Specific Sheet in a Google Spreadsheet in Apps Script – Definitive Guide

Each sheet in a Google Spreadsheet has a unique id called gid.

You can link to a specific sheet in a Google Spreadsheet by appending the /#gid=uniquegid at the end of the Spreadsheet URL.

URL Example

https://docs.google.com/spreadsheets/d/1MsqQnQTDPb6fhjfcdghkfhncXgg2AFsVoM3sFwnfvAI/edit#gid=938828956

This tutorial teaches you how to create a link to a specific sheet in Google Spreadsheet in the apps script. You can also use the demonstrated example function to create a link to each sheet available in the spreadsheet in a specific sheet.

Finding the gid of a specific sheet

There are two ways to find the gid of a specific sheet.

Method 1

Open the Spreadsheet in a browser and open your desired spreadsheet. Check the URL in the browser address bar. You can find the gid of the selected sheet.

Method 2

Another method to find the gid of a sheet is to use an apps script function. You can use this method to find the gid and dynamically create links to the sheets.

To find the sheet gid using the apps script,

1. Get the Spreadsheet object using the SpreadsheetApp.getActiveSpreadsheet() method
2. Get your sheet object using the sheet name and the apps script method getSheetByName()
3. Invoke the getSheetId() method to retrieve the gid of the specific sheet.

Link to a Specific Sheet in Google Spreadsheet using Apps Script

You have the spreadsheet URL and the gid of the desired sheet in the Google Spreadsheet.

To link to a specific sheet in Google Spreadsheet in the apps script, you must create a Hypher link formula with the link and the anchor text to show.

The following method demonstrates how to Link to a specific sheet with the Sheet name as the anchor text.

1. Get the sheet using its name
2. Find the gid of the sheet using getSheetId() as explained in the previous section
3. Create a Hyperlink formula ’=HYPERLINK("' + spreadsheet.getUrl() + '/#gid=' + gid + '","' + sheetName + ‘”)’ using the Spreadsheet URL, and append the gid at the end. Use the sheet name as the anchor text of the hyperlink
4. Get the sheet object in which you need to place the link.
5. Use the getRange() method to get the cell where you need to place the link and set the hyperlink as the cell’s value.

Code

function addLinkToSheet() {

  var sheetName = "Sheet2"; 

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = spreadsheet.getSheetByName(sheetName);

  if (sheet) {
    var gid = sheet.getSheetId();

    Logger.log("The gid of sheet '" + sheetName + "' is: " + gid);

  } else {

    Logger.log("Sheet '" + sheetName + "' not found.");
  }

  var sheetlink = '=HYPERLINK("' + spreadsheet.getUrl() + '/#gid=' + gid + '","' + sheetName + '")';

  var linksSheet = spreadsheet.getSheetByName("Links");

  linksSheet.getRange("A1").setValue(sheetlink);
}

This is how you can create a link to a specific sheet.

Create Links to All Sheets in a Google Spreadsheet Using Apps Script

To create links to all sheets in Google spreadsheet using apps script, use can iterate over the sheets object and create links as demonstrated in the following example.

It creates a link to all the available sheets and places it in the sheet called Links.

1. Get all the sheets in a spreadsheet using the getSheets() method
2. Iterate over the sheet and find the gid of the sheet.
3. Invoke the setLinks() method with the gid and the sheet name.
4. The setLinks() method creates a hyperlink for the given sheet name and places a link in the Links sheet

Code

function createLinksToAllSheets() {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var sheets = spreadsheet.getSheets();

  for (var i = 0; i < sheets.length; i++) {

    var sheet = sheets[i];

    var sheetName = sheet.getName();

    var gid = sheet.getSheetId();

    setLinks("Links", i+1, gid, sheetName);
  }
}

function setLinks(linksSheetName, rowNumber, gid, targetSheetName) {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var linksSheet = spreadsheet.getSheetByName(linksSheetName);

  var sheetlink = '=HYPERLINK("' + spreadsheet.getUrl() + '#gid=' + gid + '","' + targetSheetName + '")';

  var cellName = "A" + rowNumber;

  linksSheet.getRange(cellName).setValue(sheetlink);
}

Output

This is how you can create links to all sheets available in the Spreadsheet.

Additional Resources

• How to Get the Range to Set Value of a Cell In Google Apps Script
• How to Check if a Cell is Empty in Google Apps Script(Google Sheets)