General Google Sheet weirdness: ranges, functions, and javascript peculiarities eg Math.trunc missing

Question

There seem to be a bunch of undocumented javascript issues in google sheets.

ranges in arguments are some represented by idiosyncratic object. For example, let's say I have

function myfunction(range){  // A1:A3, contents [1, 2, 3]
  range.length == 1;  // true
  range.toString();   // "1,2,3"
  range.toString().split(","); // ["1","2","3"], .length 3
}

the documentation says it's returned as an array, but it's clearly not a standard JavaScript array.

The second annoying issue is that Math.trunc() is not implemented, but the code sheet seems to recognize it (doesn't cause a load error). In a function, however, it's an identity: Math.trunc(91.9080) returns 91.9080.

The range one really seems like I must have some basic misunderstanding — surely the documentation wouldn't be that far wrong in that peculiar a way. But I couldn't find any documentation; I had to work it out by experimentation, which turns a one-hour favor for a friend into a weekend-long project.

The other one is less annoying, possibly because after working out the range thing I was pre-suspicious about weird behaviors.

So, at last, the real question: is this documented? Was there (with ranges) some incantation I was missing?

Update

Okay, the answer is that what is passed is not an array but an object which contains an array. The documentation does not make that clear, to say the least. But thanks for pointing out the Range object which eliminates the need for my arg.toString().split(',') hack.

Answer 1

Range objects

Range isn't an Array. It's one of many classes implemented to provide a programmatic interface to Google Sheets.

However, the Range.getValues() method returns a two-dimensional array containing the values of the cells represented by a Range instance.

To illustrate, given a sheet that looks like this:

+---+
| 1 |
+---+
| 2 |
+---+
| 3 |
+---+

And the following function:

function testRange() {
  // Get a range object representing cells A1:A3.
  var range = SpreadsheetApp.getActiveSheet().getRange("A1:A3");

  // The range object does not have a length property.
  Logger.log("range.length: " + range.length);

  // Obtain a two dimensional array of values from the Range object.
  var values = range.getValues();

  // Demonstrate the layout of the two-dimensional array. The first two 
  // results should be true, representing the outer and one of the inner arrays.
  // The final result should be false, as it contains the value of cell A1.
  Logger.log("Array.isArray(values): " + Array.isArray(values));
  Logger.log("Array.isArray(values[0]): " + Array.isArray(values[0]));
  Logger.log("Array.isArray(values[0][0]): " + Array.isArray(values[0][0]));

  // Traverse the two-dimensional array and log it's contents.
  var numRows = values.length;
  var numColumns = values[0].length;

  for (var i = 0; i < numRows; i++) {
    for (var j = 0; j < numColumns; j++) {
      Logger.log("Row " + (i+1) + ", Column " + (j+1) + ": " + values[i][j]);
    }
  }
}

You get the following logging output from executing the function:

[19-12-16 11:44:53:146 PST] range.length: undefined
[19-12-16 11:44:53:254 PST] Array.isArray(values): true
[19-12-16 11:44:53:254 PST] Array.isArray(values[0]): true
[19-12-16 11:44:53:255 PST] Array.isArray(values[0][0]): false
[19-12-16 11:44:53:255 PST] Row 1, Column 1: 1
[19-12-16 11:44:53:256 PST] Row 2, Column 1: 2
[19-12-16 11:44:53:256 PST] Row 3, Column 1: 3

---

Math built-in

Regarding Math.trunc(), when I attempt to execute this example function:

function testMathTrunc() {
  Logger.log(Math.trunc(91.9080));
}

If fails with the following error message:

TypeError: Cannot find function trunc in object [object Math].

So in this case, more context about your code is necessary to reproduce what you're seeing.

It is also worth noting that the Apps Script run time is based on an older version of Javascript:

Apps Script is based on JavaScript 1.6, plus a few features from 1.7 and 1.8.

This explains the lack of a Math.trunc() implementation and is something to generally keep in mind, especially if you're used to using ES6+ features.

---

Custom functions

In the context of custom functions, for the sake of convenience, an A1 notation argument is automatically converted to either a value (if a single a cell is specified) or a 2-dimensional array of values (if a cell range is specified).

In order to clearly see this, it's better to utilize JSON.stringify() instead of Array.toString(). Given the following custom function implementation:

function TEST_CUSTOM_FUNCTION(values) {
    return JSON.stringify(values);
}

Using that custom function in the above sheet like so:

=TEST_CUSTOM_FUNCTION(A1:A3)

Results in the cell being populated with:

[[1],[2],[3]]

The key here is that using JSON accurately depicts the data structure, while Array.toString() does not.

These are normal Javascript Array objects and the way the range of values is structured matches my example above (i.e. when using Range.getValues()).

It makes sense to always return a two-dimensional array for a range of cells, as the layout of values within the data structure allows you to reason about the layout of the spreadsheet cells.

This is pertinent when a custom function returns a two-dimensional array (which then overflows the returned values into adjacent cells). If the input isn't provided as a two-dimensional array for single dimensional ranges, you'd be unable to differentiate between whether you're working with a column or a row of cells. See this example for a concrete case where this is relevant (i.e. beyond the ambiguity a 1-dimensional array would introduce, the function would have to be more complicated if 1-dimensional arrays had to be specially accounted for).

At the end of the day, it's simpler to represent a range of cells in a consistent way, particularly in the context of a general purpose API.

Answer 2

Sheet values passed to custom functions called using the in-sheet =functionname() syntax are either a single value (for a single cell), or a two-dimension array (for a range of cells). This behaves as documented. Your "myfunction()" code behaves as expected when receiving range values of 1 row with three columns.

The confusion around the length when converting to string and back is about the behaviour of array.tostring(), not an issue with Apps Script.

Math.trunc() is not implemented, and raises an error when called.

function testmyfunction(){
  var values = [[1,2,3]];
  myfunction(values);
}

function myfunction(range){  
  Logger.log(range);  //[[1,2,3]]

  Logger.log((range.length == 1));  // true
  Logger.log(range.toString());   // "1,2,3"
  Logger.log(range.toString().split(",").length); // ["1","2","3"], .length 3
  Logger.log(Math.trunc(range[0][0]));  //raises exception
}