Google Sheets: count numbers of HYPERLINKS where value > 0

1
votes

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

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

1
google-apps-scriptgoogle-sheetshyperlinkgoogle-appsgoogle-sheets-formula
In order to correctly understand your question, can you provide a sample Spreadsheet including the input and output you want? Of course, please remove your personal information. And if you will use Google Apps Script, please add the tag. – Tanaike
This is a test sheet:docs.google.com/spreadsheets/d/… The cell BL should show the total number of cells with a link and bigger than 0 - that's the code I need... – bob
Thank you for replying and providing the sample Spreadsheet. I apologize for my poor English skill. Is this Spreadsheet the current situation which is the input value? Where can I see your goal which is the output you want? If the Spreadsheet doesn't include your goal, can you add it to your shared Spreadsheet? By this, I would like to think of about your solution. By the way, if you will use Google Apps Script, please add the tag. – Tanaike
The cell BL should show the total number of cells with a link and bigger than 0 – bob
it's the blue cell – bob

1 Answers

1
votes
  • 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.
  • As a sample, in your shared Spreadsheet, you want the number of 2.

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.

Your current script:

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.
  • In your case, the formulas are retrieved from one row. So one reduce() can be removed.
  • range is not used.

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

Modified script:

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

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.

enter image description here

References:

Updated: June 8, 2020:

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.

Modified script:

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

References: