How to extract URL from Link in Google Sheets usin

问题:

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.

回答1:

You can use importxml to pull in the whole data table and it's contents with this:

=IMPORTXML(A1,"//tr")

next to it we pulled in the url strings in the tags

=IMPORTXML(A1,"//tr/td[1]/a/@href")

and finally concatenated the strings with the original domain to create your hyperlinks

=ARRAYFORMULA("http://www.bnilouisiana.com/"&INDIRECT("A2:A"&COUNTA(A2:A)))

回答2:

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.

回答3:

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. :)

回答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.

回答5:

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