Custom Range-A1 Functions in Google Sheets
This article describes how to use range objects in a custom function. Read it or jump to Sample Spreadsheet and see.
Suppose, you make a script for a custom function:
/**
* @customfunction
*/
function F(range) {
return range;
}This will return the range values. The default behavior for Google Sheets. Those who started to code in Excel were used to another logic for custom functions. Excel’s range was an actual range. To get the same for Google Sheets, you’ll have to use this construction first:
/**
* @customfunction
*/
function F(range) {
return SpreadsheetApp.getActiveRange().getFormula();
}↑ if this is used as a function from the sheet:
=F(A1:B15)↑ will return the formula text “=F(A1:B15)”. And since this is a text, we can parse it. This job may appear not easy as we may have nested functions:
=COUNTA(F(A1:B15)) + sum(A1:B)↑ where “F” is our custom function.
There are lots of ways to parse, here’s mine:
/** getCustomFunctionRanges_
*
* gets array of entered ranges
*
* @param {Array} indexes - `indexes` if set,
* will get ranges by 0-based index(es)
*
Use:
```
ranges = getCustomFunctionRanges_();
ranges = getCustomFunctionRanges_([0]);
range = getCustomFunctionRanges_(0);
```
*
*/
function getCustomFunctionRanges_(indexes) {
var enteredFormula = SpreadsheetApp.getActiveRange().getFormula();
var cleanedFormula = enteredFormula
.replace(/[\r\n]/g, ' ') // new lines
.replace(/\s+/g, '') // spaces
/** cut current formula and it's arguments */
var functionsStack = stack2lines_((new Error()).stack);
var parentFunctionName = Object.keys(functionsStack[1])[0];
// console.log(parentFunctionName); // ~ColorBackgrounds
var position1 = cleanedFormula.indexOf(parentFunctionName) +
parentFunctionName.length;
var position2 = findClosingBracket_(cleanedFormula, position1);
var cuttedFormula = cleanedFormula.substring(position1+1, position2)
// console.log({position1, position2, cuttedFormula})
var parts = cuttedFormula
.split(/[,;]/) // split by formula delimiters
var sheet = SpreadsheetApp.getActiveSheet();
var result = []; // ranges
var pushRangeByA1_ = function(rangeA1) {
if (rangeA1 === '') {
return;
}
var range;
try {
range = rangeA1.indexOf('!') === -1 ?
sheet.getRange(rangeA1) :
sheet.getParent().getRange(rangeA1);
} catch (err) {
throw '(' + rangeA1 + ') ' + err;
}
result.push(range);
}
var part; // ranges A1
if (indexes !== undefined) {
// console.log(indexes);
if (Array.isArray(indexes)) {
for (var i = 0; i < indexes.length; i++) {
part = parts[indexes[i]];
pushRangeByA1_(part);
}
} else {
part = parts[indexes];
pushRangeByA1_(part);
return result[0];
}
} else {
for (var i = 0; i < parts.length; i++) {
part = parts[i];
pushRangeByA1_(part);
}
}
return result;
}
/** findClosingBracket_
*
* https://codereview.stackexchange.com/a/179523/268840
* finds the corresponding closing parenthesis
*/
// function test_findClosingBracket() {
// console.log(findClosingBracket_('a (bc)', 2)); // 5
// console.log(findClosingBracket_('a (b ())', 2)); // 7
// console.log(findClosingBracket_('a (b ())', 5)); // 6
// console.log(findClosingBracket_('(a (b ()))', 0)); //9
// console.log(findClosingBracket_('(a (b ()))'));
// console.log(findClosingBracket_('(a (b ()))', 50));
// }
function findClosingBracket_(str, pos) {
if (pos == undefined) {
pos = 0; }
const rExp = /\(|\)/g;
rExp.lastIndex = pos + 1;
var deep = 1;
while ((pos = rExp.exec(str))) {
if (!(deep += str[pos.index] === "(" ? 1 : -1 )) { return pos.index }
}
}
// function test_stack2lines() {
// console.log(f_(100));
// }
// function f_(n) {
// console.log(stack2lines_((new Error()).stack))
// return n + 1;
// }
/** stack2lines_
*
* https://stackoverflow.com/a/72053260/5372400
*
* get function names and lines from
* (new Error()).stack)
*/
function stack2lines_(stack) {
var re1 = new RegExp('at ([^ ]+) \\(Code\\:(\\d+).*', 'gm');
var re2 = new RegExp('\\{.*\\}', 'gm');
var result = stack
.replace(re1, '{"$1": $2}')
.match(re2)
.map(function(elt) {
return JSON.parse(elt)
});
// console.log(typeof result[1])
// object
// console.log(result[1]);
// { test_stack2lines: 156 }
return result;
}My code is old-fashioned and wordy, but it does what’s needed: creates ranges from formula text for you. And bonus: you can also use your custom function in nested functions.
Sample Scripts using this approach:
/** ColorBackgrounds
*
* Get
* Background Colors
* of Range
*
* @param {A2:B10} rangeA1
* @return HEX-Colors
* @customfunction
*/
function ColorBackgrounds(rangeA1) {
var range = getCustomFunctionRanges_(0);
return range.getBackgrounds();
}
/** Notes
*
* Get
* Notes
* of Range
*
* @param {A2:B10} rangeA1
* @return notes text
* @customfunction
*/
function Notes(rangeA1) {
var range = getCustomFunctionRanges_(0);
return range.getNotes();
}
/** FontWeights
*
* Get
* FontWeights
* of Range
*
* @param {A2:B10} rangeA1
* @return FontWeights text
* @customfunction
*/
function FontWeights(rangeA1) {
var range = getCustomFunctionRanges_(0);
return range.getFontWeights();
}Now you see the final functions are super simple and return some information about the range. For more things to get, please look here:
Use Cases
Here’s a link to copy my Spreadsheet with samples.
What’s inside ↓
Sum, count, join by Color
Get background colors of your range with data:
And in column A you see the background color codes used in the data.
Next step => use native Sheets functions to get reports by colors.
Join by color groups by data columns:
Sum:
Or do it with the named function:
It will act like a native function with tips:
And you can pass any function as a parameter: SUM, AVG, COUNTA, JOIN. Cover it with LAMBDA.
Get notes and links to cells with them
Get notes of a selected range:
Use native functions to get links to those notes:
Count, join, sum bolded texts
Use the custom function to get font weights:
Use the named function to create a report:
Color Report
Finally, create a report of all colors in the selected range:
For now, the range parser cannot recognize the range passed with a lambda, so this will not work:
=lambda(data, ColorBackgrounds(data)) (C2:G6)↑ My parser is “stupid” in this situation, it thinks that the range A1 address is “data”, but here “data” is a placeholder, and the real range address is C2:G6.
But still, we can create a beautiful formula pack:
=COLOR_REPORT(C2:G6, LAMBDA(a,sum(a)), ColorBackgrounds(C2:G6))It uses an extra lambda parameter to set up the formula to handle results.
