In a google sheet, I have a custom function in a cell =doSomething(myrange)
.
When using myrange
directly in my script, it will return the values of the range as an array. In case I need not the content of the range but the range itself (e.g. to get cell colors) there is an option to parse the first parameter as a string and produce the range on the fly.
Example:
function doSomething(myRange) {
let activeRg = SpreadsheetApp.getActiveRange();
let activeformula = activeRg.getFormula();
let countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
...
Is there a more elegant way to retrieve the range and not the content of the range as an array? This would make the code much more readable.
You can pass your range as a string in your custom function and use Sheet.getRange(a1Notation)
Instead of =doSomething(A2:B3)
use =doSomething("A2:B3")
Sample Code:
function doSomething(myRange){
var sheet = SpreadsheetApp.getActiveSheet();
var bgColors = sheet.getRange(myRange).getBackgrounds()
for (var i in bgColors) {
for (var j in bgColors[i]) {
Logger.log(bgColors[i][j]);
}
}
return bgColors;
}
Output: