javascriptgoogle-sheets

Google Sheets Script - passing a range as a function parameter


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.


Solution

  • 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:

    enter image description here

    enter image description here

    enter image description here