Google-sheets – How to extract hovered links from a cell in Google Sheets

formulasgoogle sheetsgoogle-apps-scriptgoogle-sheets-custom-function

Can someone please help me extract the URL?

Best Answer

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
}

Code

function tournament(range) {
  // create output array
  var output = [];

  // add header names
  output.push(["Name", "Number"]);

  // create table
  for(var i=2, iLen=range.length; i<iLen; i++) {
    for(var j=0, jLen=range[0].length; j<jLen; j++) {
      // push letter, ring number * 100 + tournament into array
      output.push([range[i][j], (range[0][j] * 100) + (i-1)]);
    }
  }

  // return table
  return output;
}

Screenshot

data
enter image description here

result
enter image description here

Example

I've created an example file for you: Tournament

Google Sheets – How to Extract Image Link from Spreadsheet Cell

Using REGEX It is possible. For example,the

image link specifically inside the src tag

can be extracted by

=REGEXEXTRACT(A1,"(https://\S+)"&CHAR(34))

Best Answer

Related Solutions

Google-sheets – How to calculate a value based on the row/col number of another cell value

Code

Screenshot

Example

Google Sheets – How to Extract Image Link from Spreadsheet Cell

Related Topic