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.
Table of Contents
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,
- Get the Spreadsheet object using the
SpreadsheetApp.getActiveSpreadsheet()
method - Get your sheet object using the sheet name and the apps script method getSheetByName()
- 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.
- Get the sheet using its name
- Find the gid of the sheet using
getSheetId()
as explained in the previous section - 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 - Get the sheet object in which you need to place the link.
- 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.
- Get all the sheets in a spreadsheet using the getSheets() method
- Iterate over the sheet and find the gid of the sheet.
- Invoke the
setLinks()
method with the gid and the sheet name. - 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.