I have a function in Google sheets
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);
}
and I want to copy this cell
=countlinks("B150:BK150", B150:BK150)
to other cells, but the range in quotes are just copied and not adjusted automatically
=countlinks("B150:BK150", B151:BK151)
=countlinks("B150:BK150", B152:BK152)
How can I copy (if possible?) that cell that the part in quotation marks is also copied like this:
=countlinks("B151:BK151", B151:BK151)
=countlinks("B152:BK152", B152:BK152)
CodePudding user response:
try:
=countlinks("B"&ROW(A150)&":BK150"&ROW(A150), B151:BK151)
or:
=countlinks(INDIRECT("B"&ROW(A150)&":BK150"&ROW(A150)), B151:BK151)
also if you are not aware there is ISURL
:
=INDEX(SUM(1*ISURL(A2:A4)))