Pass Range to Google Spreadsheet Function without Quotes

4
votes

I have a custom google spreadsheet function which requires a range of cells to be passed as a parameter. For example, a call to my function might look like: =myFunction("D3:Y23").

I'd like to call my function using a range that isn't surrounded by quotes, like this: =myFunction(D3:Y23). I know this isn't a huge change, but it'd be nice to reference cell ranges without using quotes, similar to all default google spreadsheet functions. Is it possible to make my function work with an unquoted range as input like this?

2
google-apps-scriptgoogle-sheets

2 Answers

0
votes

I have been scouring this site and Google searches for the answer to this question and I have finally figured it out. The links Max Makhrov are very helpful but for those who don't want to have to go to other links for the solution to this problem, here's the answer:

When you pass in a range of cells like this: =myFunction(A4:A10), in the script, A4:A10 becomes an array containing the values in the cells you pass in. So in your function, you need to treat the range like an array, rather than a reference to a certain range. You also do not have to include the code that retrieves the range .getRange(a1notation).

I'll include an example with a (stupidly) simple function I've been trying to optimize for some time now. For context, this is a function that removes outliers from a standard deviation calculation that I've been using on often-noisy data that is read in from an oscilloscope.

function TRIMSTDEV(cellrange, mean, excludePercentage) {

  var workingRange = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(cellrange);
  
  var sum = 0;
  var trimmedStDev = 0;
  var numCells = 0;
  var numCells = workingCells.length;

  for(i = 0; i < workingCells.length; i++)
  {
    if (workingCells[i][0] > ((1 + excludePercentage) * mean))
    {
      numCells--;
    }
    else if (workingCells[i][0] < ((1 - excludePercentage) * mean))
    {
      numCells--;
    }
    else
    {
       sum += ((workingCells[i][0] - mean) * (workingCells[i] - mean));
    }
  }

  trimmedStDev = Math.sqrt(sum/numCells);
  return trimmedStDev;
}

Note how I have to use SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(cellrange) in order to retrieve the data from the range. This kind of notation requires that the passed in range be surrounded in quotations like this: =TRIMSTDEV("A1:A10",myAverage,somePercentage). This does the job but since I wanted my function to be "smarter" (and more usable for anyone else in this semi-specific situation), I wanted to not have to include the quotes. So I had to change my function to this:

function TRIMSTDEV(cellrange, mean, excludePercentage) {
  
  var sum = 0;
  var trimmedStDev = 0;
  var numCells = 0;
  var numCells = cellrange.length;
  
  for(i = 0; i < cellrange.length; i++)
  {
    if (cellrange[i] > ((1 + excludePercentage) * mean))
    {
      numCells--;
    }
    else if (cellrange[i] < ((1 - excludePercentage) * mean))
    {
      numCells--;
    }
    else
    {
       sum += ((cellrange[i] - mean) * (cellrange[i] - mean));
    }
  }
  trimmedStDev = Math.sqrt(sum/numCells);
  return trimmedStDev;
}

Now instead of having to get the data from the range from the string, I can work with the data in the range directly. So now I can use my function like this: =TRIMSTDEV(A4:A10,myAverage,somePercentage)

This is four years late so I hope you have solved your problem (haha) but for anyone else looking for a way to make a custom function that passes in a range from Google Sheets using Apps Script, here you go.