How to use for to sum a range in google app script?
Solution 1
Why not using the spreadsheet built in function SUM()
? (doc here)
edit following comment.
I don't like custom functions but here is a way to achieve what you want : use only 1 parameter = number of days, it will count from the first column.
function somaDias(days) {
var sum = 0;
var sh = SpreadsheetApp.getActiveSheet();
var values = sh.getRange(sh.getActiveCell().getRowIndex(),1,1,days).getValues();
for(var i in values[0]){
sum += values[0][i];
}
return sum;
}
Solution 2
If you loop through the values you can use the function below. You can specify the object and what column index needs to be summed.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getRange("A1:F30");
var values = range.getValues();
function sum( obj, colindex ) {
var sum = 0;
for( var el in obj ) {
sum += parseFloat( obj[el][colindex] );
}
return sum;
}
user3347814
Updated on December 23, 2020Comments
-
user3347814 over 3 years
I want create a simple custom sum formula in google Spreadsheets using google app Script.
function somaDias(range, days) { var sum = 0; for(i=0; i<days; i++){ sum = sum + range[i]; } return soma; }
I will use this function in a report that has a column with monetary values for each day of the month. The idea is to select the whole column of values, pass it as a range and them sum this values up to a day of the month. I know there is something wrong with
sum = sum + range[i];
because, when I test it with a column that has only 1... the return is "0111111"
=somaDias(C9:C15;6) // should return 6, but it returns 0111111
I don´t want to use =sum(), because the range changes according to the day. I want to pass as the range the 30 days of the month and then pass the number of days I want to sum. If I used sum, I would have to reselect the range everyday....
What I´m doing wrong?
-
Arià almost 7 yearsYou can refer to stackoverflow.com/questions/12036726/…