0

I have set up this demo spreadsheet.

Can someone please help me extract the URL?

enter image description here

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
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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