Google表格:计算HYPERLINKS的数量，其中值> 0 [英] Google Sheets: count numbers of HYPERLINKS where value > 0

问题描述

我在Google表格中有许多单元格 这些单元格中有些包含值(数字)，有些带有，而有些没有HYPERLINKS 如果值> 0

I have a range of cells in Google Sheets Some of these cells contain values (numbers) some with and others without HYPERLINKS I need to count the number of hyperlinks for this range of cells if the value > 0

此功能可以正常使用，但不包含任何单元格值 在Google表格公式中计算超链接

This function works fine, but does not include any cell-values Counting hyperlinks on Google Sheets formula

推荐答案

• 您要计算具有=HYPERLINK()公式且值大于0的像元数.
  • 这将运行一行，并且还有一些没有超链接的单元格.
  • 您要使用自定义功能来实现.
  • You want to calculate the number of cells which have the formulas of =HYPERLINK() and the value more than 0.
    • This is run for one row and also there are the cells which have no hyperlink.
    • You want to achieve this using the custom function.

    我可以像上面那样了解您的目标.如果我的理解是正确的，那么该修改如何?请认为这只是几个答案之一.

    I could understand about your goal like above. If my understanding is correct, how about this modification? Please think of this as just one of several answers.

    在共享的电子表格中，我确认您当前的脚本如下.

    From the shared Spreadsheet, I confirmed your current script is as follows.

    function countLinks(rangeNotation, range) {
      var formulas = SpreadsheetApp.getActiveSheet().getRange(rangeNotation).getFormulas();
      return formulas.reduce(function(acc, row) {
        return acc + row.reduce(function(acc, formula) {
          return acc + (/^=HYPERLINK/i.test(formula) ? 1 : 0);
        }, 0);
      }, 0);
    }
    

    修改点:

    • 在当前脚本中，未检索到值.
      • 在此修改中，公式和值均从单元格中检索.

      Modification points:

      • In your current script, the values are not retrieved.
        • In this modification, both the formulas and values are retrieved from the cells.

        当以上几点反映到您当前的脚本中时，它如下所示.

        When above points are reflected to your current script, it becomes as follows.

        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);
        }
        

        用法:

        例如，当您将其用作自定义函数时，请将以下自定义函数放在共享电子表格的"AO3"单元格中.

        Usage:

        When you use this as the custom function, for example, please put the following custom function to the cell "AO3" in your shared Spreadsheet.

        =countlinks("A3:AN3")
        

        结果:

        将上述脚本用于共享电子表格时，将获得以下结果.

        Result:

        When above script is used for your shared Spreadsheet, the following result is obtained.

        • getFormulas()
        • getValues()
        • reduce()
        • getFormulas()
        • getValues()
        • reduce()

        在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, 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);
        }
        

        参考文献:

        • 如何计算新的Google表格链接
        • 如何在链接未反映为HYPERLINK的情况下如何从单元格中提取链接?

        References:

        • How to count new Google sheet links
        • How to extract the link from a cell now that links are not reflected as HYPERLINK?

        这篇关于Google表格:计算HYPERLINKS的数量，其中值> 0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！