CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Spiek Vier
Spiek Vier

Reputation: 65

Google sheets can I use hyperlink to jump from one cell to another and copy data to the other cell

I would like by click on a hyperlink in cell A2 to jump to cell B2 and copy the data from cell C2 in B2. Is that possible?

Upvotes: 0

Views: 180

Answers (1)

Tanaike
Tanaike

Reputation: 201358

I believe your goal as follows.

  • When the hyperlink of the cell is clicked, moves to the cell "A2".
  • And, you want to copy the value of cell "C2" to the cell "B2".

For this, how about this answer?

Issue and workaround:

In your case, unfortunately, I think that your goal might noe be able to be achieved with only the built-in function. So I would like to propose to achieve your goal using Google Apps Script. And here, I would like to propose to use the OnSelectionChange event trigger for achieving your goal.

Usage:

1. Prepare hyperlink to a cell.

Please create the cell which has the hyperlink to the cell "A2". In this case, the formula of this is as follows. In this case, as a test case, please put the following formula to "C6", and the jumped cell is the same sheet of this formula.

=HYPERLINK("https://docs.google.com/spreadsheets/d/###/edit#gid=###&range=A2","Jump to A2")

If you use this URL, please set the Spreadsheet ID and sheet ID.

2. Sample script.

Please copy and paste the following script to the script editor of Google Spreadsheet, and save it.

function onSelectionChange(e) {
  const range = e.range;
  if (range.getA1Notation() == "A2") {
    range.offset(0, 2).copyTo(range.offset(0, 1));
  }
}

3. Test.

Please click the hyperlink at the cell "C6". By this, the cell "A2" is activated and the function of onSelectionChange is run by the event trigger. Then, the value of cell "C2" is copied to the cell "B2". The demonstration of this is as follows. In this case, even when the cell "A2" is directly selected, the script works.

enter image description here

References:

Upvotes: 2

Related Questions