Counting hyperlinks on Google Sheets formula

Question

How do you count hyperlinked numbers within a simple Google Sheet formula? Currently the =SUM(F6:CS6) (for example) does not count cells with hyperlinks.

What you you mean by "hyperlnked numbers within a simple Google Sheet formula? — Rubén
This is for annual leave tracker for staff. The spreadsheet I have has a hyperlinked holiday form to the number of days taken at that time. For example '5' is in the cell and has a holiday form hyperlinked to it. The formula adding all leave across the year, however, does not recognise the 5 as it is hyperlinked. — Ashley Burd

Unknown Unknown · Accepted Answer · 2018-01-12T22:16:41

This requires a custom function, because built-in functions cannot detect what formulas are entered in other cells, they can only access values. The discussion at Google Product Forum confirms this. There is already a custom function posted there, but I wrote another one, a bit shorter and not case-sensitive:

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

Usage example: =countlinks("A2:E10", A2:E10). Range notation has to be passed in as a string, because the function needs the range, not the values. But this also means it needs the second parameter to be recalculated in case something changes in the referenced range.

Warning: this only counts the hyperlinks created with hyperlink formula. It will not detect the links created by pasting rich text into a cell. Those links (which really should never be created) are not detectable with Google Apps Script at present.

Counting hyperlinks on Google Sheets formula

2 Answers