Google Sheets Script - passing a range as a function parameter

Question

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.

Answer 1

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:

• You can also specify which sheet you want to read the range as shown in the example output