How do I automatically turn a URL I input into a cell into a hyperlinked text in Google Sheets?

ScriptSalad 0
Viewed 49

Please see image for reference. I want the cell to always read "Link" in columns B through E whenever any URL is posted in those cells. I was told this needs to be an Apps Script; however, I'm not exactly sure on how to go about doing this.

So as an example: I post a random URL, randomURL1.com, in cell B3 I want it to automatically change that to read "Link" and be a clickable text.

Thanks!

https://i.stack.imgur.com/ZYKbs.png

spreadsheet google-sheets-formula google-sheets google-apps-script
1 Answers

I believe your goal is as follows.

  • When an URL is put to a cell, you want to convert the URL to a text which has a hyperlink of the URL.

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet and please set the sheet name. And, please save the script. When you use this script, please put an URL to a cell. By this, the URL is converted to a text of "Link" with the hyperlink.

function onEdit(e) {
  const sheetName = "Sheet1"; // Please set the sheet name.
  const { range } = e;
  const sheet = range.getSheet();
  const value = range.getValue();
  if (sheet.getSheetName() != sheetName || !value.match(/^https?:\/\/.+/)) return;
  const r = range.getRichTextValue().copy().setText("Link").setLinkUrl(value).build();
  range.setRichTextValue(r);
}

  • For example, when an URL like https://sample.com is put to a cell, this script is run and a text of "Link" with the URL is put to the cell.

  • In this script, RichText is used. If you want to use a formula of HYPERLINK, please modify as follows.

    • From

        const r = range.getRichTextValue().copy().setText("Link").setLinkUrl(value).build();
  range.setRichTextValue(r);

    • To

        range.setFormula(`=HYPERLINK("${value}","Link")`);

  • In this script, in order to check the URL, http:// or https:// are checked. So, please put the URL including http:// or https://.

References:

Tanaike0
Related