Google sheet : How to add link to jump to specific cell of other sheet

0
votes

While working on my spreadsheet, I want a 'button' to link or jump to specific cell of other sheet in the same spreadsheet.

If I add image over cell then I can assign a script function and it works fine. But I want the image to be inside a cell not over the cells. The problem is if I put the image inside the cell, I no longer can click it (or can't assign script).

3
google-apps-scriptgoogle-sheets

3 Answers

3
votes

For linking to a cell, use =HYPERLINK() formula:

=HYPERLINK("#gid=[SHEET_ID]&range=[A1_NOTATION]")

where,

  • [SHEET_ID] is the id of a sheet usually shown in the url hash(eg:1134456 in https://docs.google.com/spreadsheets/d/[SPREADSHEET_ID]/edit#gid=1134456)

  • [A1_NOTATION] is range's A1 notation. eg: A1

2
votes

I believe your goal as follows.

  • For Google Spreadsheet, you want to put an image button into a cell, and want to jump to other cell on other sheet in the same Spreadsheet when the button is clicked.

Issue and workaround:

In the current stage, unfortunately, the image put in a cell cannot be assigned by a function. So in this case, as a workaround, I would like to propose to use the OnSelectionChange trigger of the simple trigger.

Usage:

In order to use this workaround, please do the following flow.

1. Put image to a cell.

Please put an image to a cell. In this case, as a sample, please put an image into a cell "A1" of "Sheet1".

2. Sample script.

Please copy and paste the following script to the script editor of Spreadsheet.

function onSelectionChange(e) {
  const range = e.range;
  if (range.getSheet().getSheetName() != "Sheet1" || range.getA1Notation() != "A1") return;
  const jumpTo = "Sheet2!A1";
  e.source.getRange(jumpTo).activate();
}

3. Testing.

In this sample script, when the cell "A1" of "Sheet1" is clicked, the function of onSelectionChange is automatically run by firing the trigger, and the cell of "A1" in "Sheet2" is activated.

Note:

  • This is a simple sample script for testing this workaround. So please modify this for your actual situation.

Reference:

0
votes

to jump one sheet to another sheet through script try these lines on specific cell click event

//get active sheet
var currentSheet = SpreadsheetApp.getActive();
currentSheet.setActiveSheet("Sheet2"); // Sheet2 is name of next sheet