CODEWITHSUNDEEP
javascriptgoogle-sheets
Black Pepperz
Black Pepperz

Reputation: 45

How to pass 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.

Upvotes: 0

Views: 5044

Answers (2)

Exodus 4D
Exodus 4D

Reputation: 2822

You could use a "named function" with a range reference (e.g. = BGCOLORS(G125:H126)) that calls your "custom function" with the "dimension" of rage (e.g. =BACKGROUNDS(row,col,rows,cols)

  1. Create a new "named function": "Data" ➜ "Named functions"

    enter image description here

  2. Define a "function name" and "formula definition" for it.

    • Function name: BGCOLOR

    • Formula definition: =BACKGROUNDS(ROW(range), COLUMN(range), ROWS(range), COLUMNS(range))

    enter image description here

  3. Change signature of your "custom function".

    function BACKGROUNDS(...args) {
  const range = SpreadsheetApp.getActiveSheet().getRange(...args);
  return range.getBackgrounds();
}

  4. Use the "named function" in the Sheet. E.g. = BGCOLORS(G125:H126)

The combination of "named function" + "custom function" is very powerful. You can use native Sheets functions + custom Apps Script code bundled in a single function.

Upvotes: 1

Kristkun
Kristkun

Reputation: 5953

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

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

enter image description here

Upvotes: 1

Related Questions