How to extract URL from Link in Google Sheets using a formula?

Question

I have copied from a website a series of hyperlinks and pasted them in a google sheet. The values show up as linked text, not hyperlink formulas, and are still linked correctly. For each row, I'm trying to extract the URL ONLY (not the friendly text) and insert it into the adjacent column. How could this be accomplished using a formula?

For example:

=SOMEFUNCTION(cellThatHoldsLink, returnedURLOnly)

This and similar scenarios do not apply because the pasted data are not formulas. I will accept a script (GAS) solution, or any solution for that matter, but would prefer if it could be done using a formula. I have found dozens of HYPERLINK manipulation scripts, but nothing on this particular scenario, or even how to access the property that is holding that url. Thanks.

Answer 1

After some update in 2020 all codes I have found on the Internet were broken, so here is my contribution:

/** 
 * Returns the URL of a hyperlinked cell, if it's entered with control + k. 
 * Author: @Frederico Schardong based on https://support.google.com/docs/thread/28558721?hl=en&msgid=28927581 and https://github.com/andrebradshaw/utilities/blob/master/google_apps/convertHiddenLinks.gs 
 * Supports ranges
 */
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 row = [];
    for (var j = 0; j < formulas[0].length; j++) {
      row.push(formulas[i][j].getLinkUrl());
    }
    output.push(row);
  }
  return output
}

Answer 2

If your hyperlink is specified in another cell as a formula—for example let's suppose that cell A1 contains the formula =HYPERLINK("https://www.wikipedia.org/","Wikipedia"), you can extract the Link text using a regular expression. All you need to do is:

=REGEXEXTRACT(FORMULATEXT(A1),"""(.+)"",")

This formula will yield the result:

https://www.wikipedia.org/

No custom functions required.

Answer 3

This can be done for links auto created by pasting them in or with the button by 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.

Credit to morrisjr1989 on reddit.

Answer 4

The built-in SpreadsheetApp service doesn't seem to support pulling such URLs out, but the “Advanced” Sheets service does.

Enable the Advanced Sheets service according to Google's instructions, and then try this code:

function onOpen() {
  var menu = SpreadsheetApp.getUi().createMenu("Extract URLs");
  menu.addItem("Process =EXTRACT_URL(A1) formulas", "processFormulas");
  menu.addToUi();
}

function EXTRACT_URL() {
  return SpreadsheetApp.getActiveRange().getFormula();
}

function processFormulas() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var rows = sheet.getDataRange().getFormulas();
  for (var r = 0; r < rows.length; r++) {
    var row = rows[r];
    for (var c = 0; c < row.length; c++) {
      var formula = row[c];
      if (formula) {
        var matched = formula.match(/^=EXTRACT_URL\((.*)\)$/i);
        if (matched) {
          var targetRange = matched[1];
          if (targetRange.indexOf("!") < 0) {
            targetRange = sheet.getName() + "!" + targetRange;
          }
          var result = Sheets.Spreadsheets.get(spreadsheet.getId(), {
            ranges: targetRange,
            fields: 'sheets.data.rowData.values.hyperlink'
          });
          try {
            var value = result.sheets[0].data[0].rowData[0].values[0].hyperlink;
            sheet.getRange(r + 1, c + 1).setValue(value);
          } catch (e) {
            // no hyperlink; just ignore
          }
        }
      }
    }
  }
}

This creates a custom function called EXTRACT_URL, which you should call with a reference to the cell that contains the link; for example, =EXTRACT_URL(B3).

Unfortunately it doesn't work immediately, because the Advanced Sheets service can't be used directly by custom functions. So this script adds a menu called “Extract URLs” to the spreadsheet menu bar, with one menu item labeled “Process =EXTRACT_URL(A1) formulas”. When you click it, it will replace all uses of the EXTRACT_URL function with the URL itself.

Answer 5

If you happy to use Google Apps Script then use below function to get the hyperlink from a text. When you pass the cell, you should send with double quote. Eg: =GETURL("A4") to get the A4 hyperlink.

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

Refer here for example.

Edit: Ignore this answer. This will only work if url is linked in cell.

Answer 6

So, with a bit of research, I can see that there are 2 types of links in google sheets

1. hyperlinks (formula like =HYPERLINK=("www.google.com"))
2. "embedded" links (where you can't see or edit the formula)

For the first kind you can use @frederico-schardong 's linkURL directly.

For the 2nd kind you can first use the extract URLs add-on and then linkURL.

Answer 7

Found an answer that works on Google Groups (by Troy):

1. Publish your spreadsheet (copy the url)
2. Create a new spreadsheet and use IMPORTXML function (replace the url with the url you get in step 1)
3. You will get the data - you can then copy and paste the values where you need them
4. Unpublish the spreadsheet (if you do not want it public)

Could be done by Script, but I have no time now. :)

Answer 8

I was able to solve this for Jotform in a very simple way.

I was looking to include the Edit link in a query, but it would only give me the text "Edit Submission."

However, I noticed that I had the form ID in Column R. I was then able copy the JotForm link and combine it with the cell reference "https://www.jotform.com/edit/"&R2

Answer 9

Ryan Tarpine's Example helped a lot. Thanks!

With the code below, you can replace all embedded links by standard HYPERLINK formulas within a selected Range. Please note, that the Advanced Sheets Service must be activated.

function embeddedURLsToHyperlink() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var rows = sheet.getActiveRange().getValues();
  var z = sheet.getActiveRange().getRowIndex();
  var s = sheet.getActiveRange().getColumn();

  for (var r = 0; r < rows.length; r++) {
    var row = rows[r];
    for (var c = 0; c < row.length; c++) {
      var val = row[c];
      if (val) {
        var targetRange = sheet.getRange(r+z, c+s).getA1Notation();
        var result = Sheets.Spreadsheets.get(spreadsheet.getId(), {
          ranges: sheet.getName() + "!" + targetRange,
          fields: 'sheets.data.rowData.values'
        });
        if (result.sheets[0].data[0].rowData[0].values[0].hyperlink) {
          var url = result.sheets[0].data[0].rowData[0].values[0].hyperlink;
          var text = result.sheets[0].data[0].rowData[0].values[0].effectiveValue.stringValue;
          sheet.getRange(r + z, c + s).setFormula('=HYPERLINK("' + url + '","' + text + '")');
        }
      }
    }
  }
}

I you want to process the full sheet, replace lines 4-6 by the following code:

var rows = sheet.getDataRange().getValues();
var z = 1;
var s = 1;

Answer 10

You can create a macro "ExtractURLs", then edit it with the following code which parses consistent text style blocks in the active cell, tries to retrieve URLs, and pastes them into a neighbor cell.

    function ExtractURLs() {
      
      var spreadsheet = SpreadsheetApp.getActive();
    
      var richTextCell = SpreadsheetApp.getActiveRange().getRichTextValue();
    
      var richTextStrings = richTextCell.getRuns();
      
      var linksStr = "";
      var linkCell = spreadsheet.getCurrentCell()
      var link = "";
      var richTextString = "";
    
      for (var x=0; x < richTextStrings.length; x++)
      {
        richTextString = richTextStrings[x].getText();
        
        Logger.log(richTextString);
    
        link = richTextStrings[x].getLinkUrl();
        Logger.log(link);
    
        if (link != null)
        {
         linksStr += link;
         linksStr += ', '; 
        }  
      }  
    
      var targetCell = linkCell.offset(0, 1).activate();
      targetCell.setValue(linksStr.substr(0,linksStr.length-2));
    };

Answer 11

Updated for 2022:

1. Record a dummy macro, save it as "ExtractLinks".
2. Then edit the macro to get to the script editor.
3. Then paste the following and save:

function ExtractLinks() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var rows = sheet.getActiveRange().getValues();
  var z = sheet.getActiveRange().getRowIndex();
  var s = sheet.getActiveRange().getColumn();

  for (var r = 0; r < rows.length; r++) {
    var row = rows[r];
    for (var c = 0; c < row.length; c++) {
      var val = row[c];
      if (val) {
        var targetRange = sheet.getRange(r+z, c+s).getA1Notation();
        var result = Sheets.Spreadsheets.get(spreadsheet.getId(), {
          ranges: sheet.getName() + "!" + targetRange,
          fields: 'sheets.data.rowData.values'
        });
        if (result.sheets[0].data[0].rowData[0].values[0].hyperlink) {
          var url = result.sheets[0].data[0].rowData[0].values[0].hyperlink;
          var text = result.sheets[0].data[0].rowData[0].values[0].effectiveValue.stringValue;
          sheet.getRange(r + z, c + s).setValue(''+url);
        }
      }
    }
  }
};

4. You can run the macro from Extensions > Macros > ExtractLinks

This is an adaptation of Natso's code, and will work for a range.