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

GasTips / Spreadsheet / workaround
Google Apps Script / Spreadsheet

Gists

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

 Share!