4

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?

Rubén
  • 34,714
  • 9
  • 70
  • 166
user3347814
  • 1,138
  • 9
  • 28
  • 50

3 Answers3

5

Why not using the spreadsheet built in function SUM() ? (doc here)

enter image description 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;
}
Serge insas
  • 45,904
  • 7
  • 105
  • 131
  • You can refer to https://stackoverflow.com/questions/12036726/how-do-i-add-formulas-to-google-spreadsheet-using-google-apps-script – Arià Jul 20 '17 at 21:42
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;
}
Just-J
  • 49
  • 4
1

First. Always a range is a 2 dimensional array: range[row][column]. So if you are working with only one column, you must work with range[i][0] and iterate on i. Second thing, when the function finds an empty cell, it changes the type to what it should return. Anyway an empty cell counts as zero as a number. So to solve this just add

if (range[i][j] != 0) sum = sum + range[i][j];

and it will avoid working with empty cells. Hope this helps you.

user18881793
  • 11
  • 1