Trying to figure out behavior of Google Sheets using cell entries as array params using a custom function?

1
votes

I would like to add some JS functionality to Google Sheets. Just as a test, I have written a simple summation function: /** This function is just to try out some JS in Google Sheets. @customFunction */enter image description here

function addUp(values) {
  var total = 0;
  for (var i = 0; i < values.length; i++) {
    total = total + parseInt(values[i]);
  }
  return total;
}

See my screenshot of the sheet.. when entering range B2..D3 it only reads the values[0]. When entering C5..C7, it works as expected.

Is anybody familiar with this behavior?

1
javascriptgoogle-sheets

1 Answers

0
votes

Treat values as a 2D array as such:

function addUp(values) {
  var total = 0;
  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++)
      total = total + parseInt(values[i][j]);
  }
  return total;
}

From the Google Apps Script docs:

If you call your function with a reference to a range of cells as an argument (like =DOUBLE(A1:B10)), the argument will be a two-dimensional array of the cells' values. For example, in the screenshot below, the arguments in =DOUBLE(A1:B2) are interpreted by Apps Script as double([[1,3],[2,4]])

https://developers.google.com/apps-script/guides/sheets/functions