google-sheets

Google Spreadsheet ||Typeerror : cannot read property '0'


I have a spreadsheet for project data with time-sheet for each month logged against each project ID

I want to iterate through each sheet and if there is matching project ID , I want to sum up the number of hours logged for each project.

I have written the following code but keep getting the

TypeError: Cannot read property "0" from undefined. (line 31).

This is my Code.

function TotalHours(TaskID) {

  var a = SpreadsheetApp.getActiveSpreadsheet().getSheets().length;
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var sum = 0;
  // var fcol = 0;


  for (var i = 1; i <= a; ++i) {

    // var sheetname = sheets[i].getName();
    //var cell = sheets[i].getActiveCell();

    //Set active cell to A1 on each sheet to start looking from there
    SpreadsheetApp.setActiveSheet(sheets[i])
      //var sheet = sh.getActiveSheet();
    var range = sheets[i].getRange("A1");
    //* sheets[i].setActiveRange(range);    

    var data = sheets[i].getDataRange().getValues();

    for (var row = 2; row <= data.length; ++row) {

      if (data[row][0] == TaskID) {

        for (var col = 2; col <= 31; ++col) {
          sum += sheets[i].getRange(row, col).getValue();
        }
      }
    }
  }

  return sum;

}

Can someone help me with what I am doing wrong.


Solution

  • I assume you want to exclude the sheet where the formula is going to be used ("Tracker" ?

    See if this works ?

    function TotalHours(TaskID) {
    var sum = 0,
        s = SpreadsheetApp.getActive(),
        active = s.getActiveSheet().getName(),
        sheets = s.getSheets();
    for (var i = 0, slen = sheets.length; i < slen; i++) {
        if(sheets[i].getName() != active) {
        var sheetVal = sheets[i].getDataRange()
            .getValues();
        for (var j = 0, vlen = sheetVal.length; j < vlen; j++) {
            if (sheetVal[j][0] == TaskID) {
                for (var k = 2, rlen = sheetVal[j].length; k < rlen; k++) {
                var c = sheetVal[j][k]
                    sum += c && !isNaN(parseFloat(c)) && isFinite(c)? c : 0; //check if cell holds a number
                }
            }
        }
        }
    }
    return sum;
    }