I have been trying to use a getlink function for extracting the URL of a hyperlink. The cell from which I need to extract a link is not configured with a hyperlink formula.

Screenshot Spreadsheet with link to URL (hovered above cell)

However when I use the script below, I always get:

*TypeError: Cannot read properties of null (reading 'getLinkUrl') (line 5). Why is that? *

The formula that I use for this function:

=GETLINK(CELL("Address";R2))

Script that I use:

function GETLINK(input){
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(input);
  var value = range.getRichTextValue();
  var url = value.getLinkUrl(0,1);
  return url;
}

CodePudding user response：

Modification points:

From your following sample image and your error message of TypeError: Cannot read properties of null (reading 'getLinkUrl'),

I thought that your current issue might be due to the number value. In the current stage, when the cell value is the number value, range.getRichTextValue() returns null. (This has already been reported to the Google issue tracker. Ref) In this case, an error like your error message is shown even when the cell has a hyperlink.

In order to remove this issue and retrieve the hyperlink, how about the following modification?

Modified script:

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

In this modification, this script cannot be used as the custom function because of setNumberFormat. So, I proposed a sample script for retrieving the URLs from the source range and putting the URL into the destination range.

When you use this script, please set the values of srcRange and dstRange, and run the script by the script editor. By this, the script is run and retrieves the URL from srcRange, and the retrieved URLs are put to dstRange.

function sample() {
  var srcRange = "Sheet1!A1:A10"; // Please set the source range you want to use.
  var dstRange = "Sheet2!B1:B10"; // Please set the destination range you want to use.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getRange(srcRange);
  var orgNumberFormats = range.getNumberFormats();
  var values = range.setNumberFormat("@").getRichTextValues();
  var urls = values.map(r => r.map(c => c && (c.getLinkUrl() || "")));
  range.setNumberFormats(orgNumberFormats);
  ss.getRange(dstRange).setValues(urls);
}

• When this script is run, the number formats of srcRange are saved, the cell values are converted to the string values, and the rich text values are retrieved from srcRange. And then, the URL is retrieved and the URLs are put to dstRange.

Note:

• In the case that your error is removed from your showing script, the following script can be used. But, in this case, when the cell value is the number value, no value is returned even when the cell has a hyperlink. Please be careful about this.

    function GETLINK(input) {
      var sheet = SpreadsheetApp.getActiveSheet();
      var range = sheet.getRange(input);
      var value = range.getRichTextValue();
      return value && value.getLinkUrl(0, 1);
    }
  

References:

• map()
• getNumberFormats()
• setNumberFormats(numberFormats)