3

I have set up this demo spreadsheet.

Can someone please help me extract the URL?

enter image description here

0
1
  • add this script to your spreadsheet and then use this custom formula: =URL(A1)

    function URL(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.getFormulas();
    var output = [];
    for (var i = 0; i < formulas.length; i++) {
    var row = [];
    for (var j = 0; j < formulas[0].length; j++) {
      var url = formulas[i][j].match(/=hyperlink\("([^"]+)"/i);
      row.push(url ? url[1] : '');
    }
    output.push(row);
    }
    return output
    }

    0

1
1
  • or this indirect (shorter) one with a custom formula: =URL("A1")

    function URL(input) {
    var range = SpreadsheetApp.getActiveSheet().getRange(input);
    var url = /"(.*?)"/.exec(range.getFormulaR1C1())[1];
    return url;
    }

    enter image description here

2
  • This solution does not work for me when referencing any of the hyperlinked cells in docs.google.com/spreadsheets/d/…
    – adaaaam
    Mar 28 '21 at 5:52
  • @adaaaam This can be done for links auto created by pasting them in or with the link button going to Tools -> Script editor and creating the following script: function GETLINK(input){ return spreadsheetApp.getActiveSheet().getRange(input).getRichTextValue().getLinkUrl(); } If cell A1 has the link you will need to reference it as =GETLINK("A1") That's not going to update dynamically if you have a lot of links so use =GETLINK(cell("Address",A1)) to get around that.
    – MrG
    Apr 22 '21 at 5:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.