CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Sri Sankaran
Sri Sankaran

Reputation: 8310

How to retain an hyperlink in Google sheet referenced data

Part of the text in a Google Sheet cell (A1) is hyperlinked. For example, say A1 is:

This is a Google Sheet

Now say that A1 is referenced elsewhere. For example, say B1's value is =A1. The problem is that B1 only displays the visible text of A1. The hyperlinking in A1 is not retained in B1. In other words, B1 displays as:

This is a Google Sheet

How can I ensure that the referenced cell shows the hyperlink as well?

The crucial point seems to be that not all of A1 is a hyperlink; only some of the text is a hyperlink. If A1 were hyperlinked in its entirety then B1 is correctly hyperlinked as well.

I have a simple example sheet that demonstrates the issue.

Upvotes: 0

Views: 299

Answers (2)

Marios
Marios

Reputation: 27380

I don't think that is possible based also on this answer using a google sheets formula.

But if you are open to workarounds and google apps script, then this very simple script will copy the value and it will reserve the text url:

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  sh.getRange('A2').copyTo(sh.getRange('B2'));
}

You can also create an onEdit trigger to automatically copy the formula upon edits.

Excuse me in advance if this wasn't the direction you were looking for.

Upvotes: 1

Jeffrey Daube
Jeffrey Daube

Reputation: 26

I agree with Mario, currently it appears the Google Apps Script solution is the only workaround. To build upon his work, consider also adding an if-check in the function so that apps script only copies A1 to B1 if A1 was the cell that was edited:

function onEdit(e) {
  console.log(e)
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  if (e.range.columnStart === 1
    && e.range.columnEnd === 1
    && e.range.rowEnd === 1
    && e.range.rowStart === 1) {
    sh.getRange('A1').copyTo(sh.getRange('B1'));
  }
}

You can console.log the event to see all the data available for interrogation when the onEdit trigger executes.

The if-check reduces the overhead by only making a second call to google sheets if A1 was indeed the cell of edit. Great workaround Mario!

Upvotes: 1

Related Questions