0

I have this formula that extracts the link from a word with hyperlink. Now when I'm trying to move it to another spreadsheet and run it I get an 'Exception: Range not found' error. I was reading this might be cuz I need a 'getRangeList' insted of getRange, but it doesn't work this way either. Now I get the 'Exception: The parameters (number) don't match the method signature for SpreadsheetApp.Sheet.getRangeList.' Here's the code I'm using:

function URL(refrence) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var a1 = formula.replace("=url(","");
  a1 = a1.replace(")","");
  var url = sheet.getRange(a1).getRichTextValue().getLinkUrl();
  return url
}
Debora S.
  • 115
  • 8
  • The variable a1 may not have the correct range value. You have a sample value for the formula cell ? – Karan Oct 26 '21 at 16:06
  • `a1` is technically not a range, but a _replaced_ value from formula, which is still not a valid range. Try using `getActiveRange()` instead of `getRange(a1)`. – Rodrigo Biffi Oct 26 '21 at 16:42
  • @RodrigoBiffi if I change just that I get the "TypeError: Cannot read property 'getLinkUrl' of null (line 6)." – Debora S. Oct 27 '21 at 08:19

1 Answers1

2

FINDINGS

  • The part getRange(a1) is incorrect as the variable a1 contains an invalid Sheet range.

SUGGESTION

If your main goal is to easily extract the URL from a cell with hyperlink like =HYPERLINK("http://www.google.com/","Google") you may use these tweaked scripts below:

These scripts were derived from the answers of these posts How to extract URL from Link in Google Sheets using a formula & Google Apps Script Regular Expression

This version of the script will only show the url value from the Apps Script editor's log results:

function URL() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = sheet.getActiveRange().getFormula();
  var regExp = new RegExp("\"(.*)\",\"", "gm");
  var url = regExp.exec(formula)[1];
  Logger.log(url)
}

Note: Before running, make sure the cell with hyperlink is selected on your sheet (in my case it was on cell A1).

Sample result on the logs:

enter image description here

This another version will let you to use your script as a custom function =URL() on your spreadsheet file:

function URL(refrence) {
  var regExp = new RegExp("\"(.*)\",\"", "gm");
  var url = regExp.exec(refrence)[1]
  return url
}

Sample Result as a custom function:

=URL(FORMULATEXT(A1))

enter image description here

Reference:

FORMULATEXT function

SputnikDrunk2
  • 3,398
  • 1
  • 5
  • 17