如何计算新的Google表格链接 [英] How to count new Google sheet links

问题描述

我很久以前问过这个问题，并且得到了一个很好的答案:"Google表格:对HYPERLINKS的数量进行计数，其中值> 0" Google表格:计算HYPERLINKS的数量，其中值> 0

I have asked long time ago and got a great answer to the this question: "Google Sheets: count numbers of HYPERLINKS where value > 0" Google Sheets: count numbers of HYPERLINKS where value > 0

现在google已更改其超链接以在单元格中插入多个超链接-很好...但是他们也不再以这种方式插入超链接:

Now google has changed their hyperlinks to insert multiple hyperlinks in a cell - nice... But they also do NOT insert it in this way anymore:

=HYPERLINK("http:/www.test.com", 100") 

所以现在我的老问题又来了:如何使用HYPERLINKS来计算值> 0的单元格数

So now my old question comes up again: How to count number of cells with HYPERLINKS where value > 0

推荐答案

我相信您的目标如下.

• 您要从Google Spreadsheet中工作表的某个范围中检索超链接的数量.

• 您想通过修改以下Google Apps脚本来实现. 参考

function countLinks(rangeNotation) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formulas = sheet.getRange(rangeNotation).getFormulas()[0];
  var values = sheet.getRange(rangeNotation).getValues()[0];
  return formulas.reduce(function(acc, formula, i) {
    return acc += (/^=HYPERLINK/i.test(formula) && values[i] > 0 ? 1 : 0);
  }, 0);
}

为此，这个答案如何?

在2020年5月，似乎已更改了在Google Spreadsheet中使用超链接的规范.不幸的是，这样，上面的脚本现在不能使用.但是，在当前阶段，可以使用Class RichTextValue检索超链接.因此，在当前阶段，为了实现您的目标，需要对上述脚本进行如下修改.

At May, 2020, it seems that the specification for using the hyperlinks in Google Spreadsheet was changed. By this, unfortunately, above script cannot be used now. But, in the current stage, the hyperlinks can be retrieved using Class RichTextValue. So, in the current stage, in order to achieve your goal, it is required to modify above script as follows.

function countLinks(rangeNotation) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var richTextValues = sheet.getRange(rangeNotation).getRichTextValues();
  return richTextValues.reduce((c, row) => {
    row.forEach(col => {
      col.getRuns().forEach(r => {
        if (r.getLinkUrl()) c++;
      });
    });
    return c;
  }, 0);
}

• 当将此脚本用作自定义函数时，请将以下公式添加到单元格中.在这种情况下，将返回单元格"A1:A10"中的超链接数量.

  • When you use this script as the custom function, please put the following formula to a cell. In this case, the number of hyperlinks in the cells "A1:A10" are returned.

    =countlinks("A1:C10")
    

  • 在此修改后的脚本中，可以检索使用和不使用HYPERLINK的超链接的数量.
  • 请将此脚本与V8配合使用.
  • In this modified script, the number of hyperlinks both with and without using HYPERLINK can be retrieved.
  • Please use this script with V8.
  • 既然链接未显示为HYPERLINK，如何从单元格中提取链接?
  • RichTextValue类
    • 不幸的是，在当前阶段，官方文件似乎仍然没有更新.因此找不到getLinkUrl()的文档.但是可以使用getLinkUrl().
    • How to extract the link from a cell now that links are not reflected as HYPERLINK?
    • Class RichTextValue
      • Unfortunately, in the current stage, it seems that the official document is still not updated. So the document of getLinkUrl() cannot be found. But getLinkUrl() can be used.

      当我看到您的示例电子表格时，我可以理解您出现问题的原因.出现问题的原因是因为该数字值用于超链接.在当前阶段，似乎getRichTextValues无法检索数字值.这样，仅检索HYPERLINK的数量.我认为这可能是一个错误.因此，我已经将此问题发布到了问题跟踪器. 参考解决此问题后，我认为上述示例脚本可能会起作用.

      When I saw your sample Spreadsheet, I could understand about the reason of your issue. The reason of your issue is due to that the number value is used for the hyperlink. In the current stage, it seems that getRichTextValues cannot retrieve the number value. By this, only number of HYPERLINK is retrieved. I think that this might be a bug. So I have already posted this issue to the issue tracker. Ref When this issue was resolved, I think that above sample script might work.

      顺便说一句，无法检索具有超链接而没有URL的单元格.请注意这一点.

      By the way, the cells which have the hyperlinks without the URL cannot be retrieved. Please be careful this.

      例如，作为当前解决方法，当单元格格式从数字更改为文本时，可以看到计数.但是在这种情况下，无法检索具有超链接但没有URL的单元格.请注意这一点.

      As the current workaround, for example, when the cell format is changed from the number to the text, the count can be seen. But in this case, the cells which have the hyperlinks without the URL cannot be retrieved. Please be careful this.

      这篇关于如何计算新的Google表格链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！