GoogleSheet : export links and texts separately from a rich text

/** 
 * Returns the URL of a hyperlinked cell, if it's entered with hyperlink command. 
 * Supports ranges
 * @param {A1}  reference Cell reference
 * @customfunction
 */
function linkURL(reference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i);
  try {
    var range = sheet.getRange(args[1]);
  }
  catch(e) {
    throw new Error(args[1] + ' is not a valid range');
  }

  var formulas = range.getRichTextValues();
  var output = [];
  for (var i = 0; i < formulas.length; i++) {
    var formula = formulas[i]
    var urls = [];
    var urlsStr = "";
    for (var j = 0; j < formula.length; j++) {
      var run = formula[j].getRuns();
      for (var k = 0; k < run.length; k++) {
        var url = run[k].getLinkUrl();
        if (url != null) {
          urlsStr += run[k].getText() + " " + run[k].getLinkUrl() + "\n";
        }
      }
    }
    output.push(urlsStr);
  }
  return output
}

Line 10:

var args = formula.match(/=\w+\((.*)\)/i);

... Limits the use of this function. I changed it to:

var args = formula.match(/\blinkURL\((.+?)\)/);

... so that it supports more complex use cases.

Trying this, but getting an error;

TypeError: Cannot read properties of null (reading '1') (line 15).

Trying this, but getting an error;

TypeError: Cannot read properties of null (reading '1') (line 15).

@mfuse-xx, Looks like args is null, so line 10 isn't matching as expected. Hard to debug further without seeing the spreadsheet. You can try changing line 10 to:

var args = formula.match(/\blinkURL\((.+?)\)/);

... And see if that helps.

Trying this, but getting an error;
TypeError: Cannot read properties of null (reading '1') (line 15).

@mfuse-xx, Looks like args is null, so line 10 isn't matching as expected. Hard to debug further without seeing the spreadsheet. You can try changing line 10 to:

var args = formula.match(/\blinkURL\((.+?)\)/);

... And see if that helps.

Hi @mrienstra also tried the code and changed line 10 to var args = formula.match(/\blinkURL\((.+?)\)/);

but still getting the same error :(
TypeError: Cannot read properties of null (reading '1') (line 15).

@mfuse-xx, @theweeklytea, if either of you can reproduce the problem in a spreadsheet you're willing to share, I'll take a look & see if I can figure out what's going wrong.

Were you planning to use urls array for something? If not, you can remove it :).

I cannot use the result of this function as a parameter of IMPORTRANGE. It works in two steps:
For example in the cell E25 I have a link.
With =linkURL(E25), I get the link, for example in E26 and then with
=IMPORTRANGE(E26; ADDRESS(72;1))
I get the correct result.
But with
=IMPORTRANGE(linkURL(E25); ADDRESS(72;1))
I get
#ERROR !
Any idea why ?
Thanks
Marc