Saturday, July 1, 2017

Google Sheet Script to extract URL from a Cell containing function HYPERLINK

In Google Sheet, if you have a cell or a range containing functions HYPERLINK and if you want to extract URL(s) from it then you can do it with the help of following script:


function extractURL() {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula();
  if(formula=="") return "";
  var rangeA1Notation = formula.match(/\((.*)\)/).pop();
  var range = activeSheet.getRange(rangeA1Notation);
  var f = range.getFormulas();
  var v = range.getValues();
  for(var i=0;i<f.length;i++) {
    for(var j=0;j<f[0].length;j++) {
      if(f[i][j]=="") v[i][j]="";
      var matchURL = f[i][j].match(/\"(.*?)\"/);
      if(matchURL==null) v[i][j]="";
      else v[i][j]=matchURL.pop();
  return v;


copy the above script code in the script editor of your spreadsheet and then use custom function "=extractURL(A2:A10)" to extract the URLs from the range "A2:A10"


No comments:

Post a Comment