Range input to function in Google apps script

Question

In a cell in my google sheet, I call my function like this: myFunction(A1:A3), where A1 = 5, A2 = 7 and A3 = 3. I now want to loop over the input (A1, A2, and A3) and (for example) sum them.

function myFunction(input) {

if (!input.map) {     
return -1; 
}

var sum=0
for(var i=0; i<input.length; i++){
  sum = sum + parseInt(input[i]);
}

return sum

}

But it only returns the value in A1 (5) because input.length returns 1. If I remove parseInt(input[i]), it returns "05,7,3" What am i doing wrong?

Your code works for me...is there a reason to use the custom function over =SUM(A1:A3)? — random-parts
– random-parts, Commented Nov 22, 2017 at 14:08

Wicket · Accepted Answer · 2017-11-22 15:14:53Z

4

Custom function arguments are converted to JavaScript object types. If the parameter is a multicell range, it's converted to an array object which members are arrays, in other words, as a 2D array.

In order to get each cell value, instead of input[i] use something like input[i][j].

Example:

/**
 * @customfunction
 */
function mySum(input) {
  if (!input.map) return -1; 
  var sum = 0;
  for(var i = 0; i < input.length; i++){
    for(var j = 0; j < input[0].length; j++){
      sum = sum + parseInt(input[i][j]);
    }
  }
  return sum
}

Note: The above function could be improved by adding some rules / input data validation, like replace blanks by 0.

References

Custom Functions in Google Sheets

answered Nov 22, 2017 at 15:14

Wicket

39.4k9 gold badges81 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user6637260 · Accepted Answer · 2017-11-22 14:20:27Z

1

there's built functions for do that but if you want to learn how to script and code this is great ! the code works for just vertical ranges e.g (A1:A5) but in horizontal/square ranges wont, just do another loop inside the first one to do sum of 2D array

for(var i=0; i<input.length; i++){
  for(var j=0; j<input[0].length; j++){
      sum = sum + parseInt(input[i][j]);
  }
}

answered Nov 22, 2017 at 14:20

user6637260

Collectives™ on Stack Overflow

Range input to function in Google apps script

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related