Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Last active January 31, 2023 07:27
Show Gist options
  • Save tanaikech/82a74e64abcacabd51be8ff92c73691a to your computer and use it in GitHub Desktop.
Save tanaikech/82a74e64abcacabd51be8ff92c73691a to your computer and use it in GitHub Desktop.
Workaround: Retrieving Hyperlink from Cell of Number Value using Google Apps Script

Workaround: Retrieving Hyperlink from Cell of Number Value using Google Apps Script

This is a workaround for retrieving the hyperlink from the cell of a number value using Google Apps Script.

As a sample situation, it supposes that a cell "A1" has a number value like 123, and a hyperlink of https://tanaikech.github.io is set to the cell. In order to retrieve the hyperlink from the cell, it is required to use the methods of getRichTextValue() and getRichTextValues(). But, in the current stage, when the cell value is a number value, when the RichText is retrieved by getRichTextValue(), null is returned. By this, unfortunately, the hyperlink of the cell cannot be retrieved. This has already been reported in the Google issue tracker. Ref

In this post, I would like to introduce a workaround for retrieving hyperlinks from such cells.

Sample script:

function sample() {
  const srcRange = "Sheet1!A1:A10"; // Please set the source range.

  const range = SpreadsheetApp.getActiveSpreadsheet().getRange(srcRange);
  const orgNumberFormats = range.getNumberFormats();
  const values = range.setNumberFormat("@").getRichTextValues();
  const urls = values.map((r) => r.map((c) => c && (c.getLinkUrl(0, 1) || "")));
  range.setNumberFormats(orgNumberFormats);
  
  console.log(urls);
}
  • When this script is run, first, the number formats of the source range of the cells "A1:A10" of "Sheet1" are saved. And, all cell values are converted to the text using setNumberFormat("@"), and then, the rich test values are retrieved from the source range. And, the original number formats are set to the source range. By this, the hyperlinks can be retrieved from the cells of the number values.

Reference

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment