Google Spreadsheet: Sum values in a set number of sheets

Question

I have a spreadsheet for soccer with an overview sheet (it's the first sheet) and 34 more sheets (for every matchday, called "x. ST") filled with values. In the overview sheet I want to get the sum of specific values from a set number of matchdays. I tried to realize this by an App Script. var start is the value that defines the first matchday, var end the last matchday. I want it as dynamic as possible so I can get values i.e. from Matchday 10 to 20 or 20 to 25 and so on.

Every sheet from start to end has a value in a given cell. This value can be "3" (for win), "1" (for draw) or "0" (for loss). Because this function should display the number of matchdays with wins, every "3" counts as 1, every "1" and "0" as 0. Logically the sum of all 1's is the number of the won matchdays. Here's the code:

function win(cell) { 
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheets = ss.getSheets();
   var sum = 0;
   var end = ss.getActiveSheet().getRange(21, 2).getValue() + 1;
   var start = ss.getActiveSheet().getRange(21, 3).getValue() + 1;


     for (var i = start; i < end ; i++ ) {
        var sheet = sheets[i];
        var val = sheet.getRange(cell).getValue();

        if (val == "3") {
            val = 1;
        } else {
            val = 0;
        }
        sum += val;
    }

    return sum;
}

Now, my problem is that the code does work, but the sum is always a 0 (zero), regardless of the number of sheets with "3" in the given cell. I really don't see where's the problem. Anyone with an idea?

EDIT: Here's the Spreadsheet. I'm not sure it will help, but whatever: https://docs.google.com/spreadsheets/d/1SoE4cJVAeeurmB7bvvGENEWvu2Mg1xvA3B3hTobBxW8/edit#gid=0

EDIT2: Ok guys, there were simple errors and the code works now. First I had to change cell in getRange(), and then I had to guarantee every value is an integer. There was also an error with start and end, causing the code searching in the wrong sheets. Because this sheets are currently without any values, there was always the sum of 0. Well, here's the current code:

function win(cell) {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheets = ss.getSheets();
   var sum = 0;
   var end = ss.getActiveSheet().getRange(21, 2).getValue() + 1;
   var start = ss.getActiveSheet().getRange(21, 3).getValue();

     for (var i = start; i < end ; i++ ) {
        var sheet = sheets[i];
        var val = sheet.getRange([cell]).getValue();

        parseInt(val) * 1;

        if (val == 3) {
            val = 1;
        } else {
            val = 0;
        }
        sum += val;
    }

    return sum;
}

TMD TMD · Accepted Answer · 2015-07-29T16:01:55

I cannot be sure without looking at your spreadsheet, but just from looking at your code, I am guessing that you have the "Start" and "end" cells mixed up. The way it is now, both cells are in the same row, but end comes before start, which is not what you seemed to describe above.

Google Spreadsheet: Sum values in a set number of sheets

1 Answers