如何使用公式从Google表格中提取链接中的URL？

Question

如何使用公式从Google表格中提取链接中的URL？

71

我从一个网站复制了一系列超链接，并将它们粘贴到 Google Sheet 中。这些值显示为链接文本，而不是超链接公式，并且仍然正确地链接。对于每一行，我想仅提取URL（而不是友好的文本），并将其插入相邻的列中。如何使用公式来完成这个操作？

例如:

=SOMEFUNCTION(cellThatHoldsLink, returnedURLOnly)

这种情况及类似情况并不适用，因为粘贴的数据不是公式。我可以接受脚本（GAS）或其他任何解决方案，但如果可以使用公式解决会更好。我已经找到了许多超链接操作脚本，但没有针对这种特定情况的，甚至找不到如何访问保存该网址的属性。谢谢。

- iamtoc

1

提供一些单元格中内容的真实示例。这样别人就可以测试输出了。 - Alan Wells

1

Sandy，这是一个例子：https://docs.google.com/spreadsheets/d/1KksyZwP4CBfmX8YN7BK0jcNY3iFvXYZTUZQnA6TfT0g/edit#gid=0 - iamtoc

你最开始是在那个位置粘贴了一些链接或超链接，之后删除并重新粘贴的吗？ - Aurielle Perlmann

2

是的 - 我每天都使用Linkclump - 它是一个很棒的Chrome插件 - 或者如果您分享了获取链接的源URL，我会向您展示如何在您的表格上使用一个酷炫的技巧。 - Aurielle Perlmann

linkclump 能够节省很多时间。 - macasas

显示剩余3条评论

16个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Azriel Blumberg · Answer 1

我能够以非常简单的方式解决Jotform的这个问题。

我想在查询中包含编辑链接，但它只会给我文字“编辑提交”。

然而，我注意到我在R列中有表格ID。然后，我能够复制JotForm链接并将其与单元格引用“https://www.jotform.com/edit/”和R2组合使用。

- Euviao FreeSoul · Answer 2

我有一种相当间接的方法来提取所有URL。导出HTML文件中嵌入的URL标题列。然后在线URL提取器就可以完成任务。

- Natso · Answer 3

Ryan Tarpine的示例帮了很大的忙。谢谢！

使用下面的代码，您可以在所选范围内将所有嵌入链接替换为标准的HYPERLINK公式。请注意，必须激活高级表格服务。

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 + '")');
        }
      }
    }
  }
}

如果您想处理整个工作表，请使用以下代码替换第4-6行：

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

- Faiz Noerdiyan Cesara · Answer 4

我使用这个宏（扩展 > 宏）来自动从每个单元格中提取链接，范围为指定范围（仅限1列）

function extractHyperlinks() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveRange();
  var values = range.getRichTextValues();

  var extractedLinks = [];
  var extractedText = [];

  values.forEach(function(row) {
    row.forEach(function(cell) {
      var hyperlink = cell.getLinkUrl();
      var text = cell.getText();
      
      if (hyperlink != null) {
        extractedLinks.push([text, hyperlink]);
        extractedText.push([text]);
      }
    });
  });
  
  if (extractedLinks.length > 0) {
    var outputSheet = createOutputSheet();
    outputSheet.getRange(1, 1, extractedLinks.length, 2).setValues(extractedLinks);
    SpreadsheetApp.getActiveSpreadsheet().setActiveSheet(outputSheet);
  } else {
    Browser.msgBox("No hyperlinks found in the selected range.");
  }
}

function createOutputSheet() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = "Extracted Links";
  
  // Check if a sheet with the same name already exists
  var existingSheet = spreadsheet.getSheetByName(sheetName);
  if (existingSheet != null) {
    // Generate a unique name for the new sheet
    var timestamp = new Date().getTime();
    sheetName = "Extracted Links (" + timestamp + ")";
  }
  
  // Create a new sheet
  var outputSheet = spreadsheet.insertSheet(sheetName);
  outputSheet.getRange(1, 1).setValue("Text");
  outputSheet.getRange(1, 2).setValue("Link");
  
  return outputSheet;
}

保存宏，定义要提取的范围，并使用宏。它将自动创建一个带有原始文本和超链接的新单元格。

- Richard · Answer 5

最简单的粘贴链接方式：

=getlink(cell("Address",A2))

使用以下函数：

function GETLINK(input){

return SpreadsheetApp.getActiveSheet().getRange(input).getRichTextValue().getLinkUrl();

}

- Jayesh Mayekar · Answer 6

如果单元格行数有限，可以快速完成以下操作：

右键单击单元格，然后点击编辑链接
删除文本并点击应用
对于每个后续的单元格，使用F4键

如果您还有其他方法或者可以通过选择单元格来完成，请告诉我。