Google apps script throwing error sometime in loop - help debug?

Question

I am new to Google apps scripts, so I apologize if this is something very basic.

I have a workbook in which multiple employees track their hours on different projects. Each employee has their own sheet in the workbook, titled with their last name. Each sheet has columns for the person's name, their projects, and their hours. The sheets contain only the hours for that week, so the number of rows that are completed varies by person and week to week.

I want a script that loops over each person's sheet, copies their data, and pastes it in to a master sheet that tracks everybody over time.

I have taken a stab at this (see code below). It does exactly what I want; however, it never makes it through all the sheets - at some point, it always throws an error on the line "var source_range = source_sheet.getRange('A3:E'+(ct)); ". I think this is because the ct variable is staying at zero for some reason. However, all the sheets have text in column E in at least rows 1-3. Also, if I move the sheets around (re-order them), the sheet on which the error is thrown changes. It helped to alphabetize the sheets (it got through more of them before the error occurred), but I am still getting an error once it reaches the first sheet named with something that comes after R (something about coming after one of the sheets I want it to skip? - but it skips the one titled "Dashboard" with no problems).

I'm thinking this is something obvious and due to my inexperience - any help would be greatly appreciated!!

    function MoveData() {
  // First find the correct data 

  var ss = SpreadsheetApp.getActive(); 
  var target_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('RawData');
  var allsheets = ss.getSheets();

  for(var s in allsheets){
    var source_sheet = allsheets[s];

    // Exclude sheets I don't want to copy data from 
    if(
       (source_sheet.getName() == "RawData") || 
       (source_sheet.getName() == "ProjectDB") || 
       (source_sheet.getName() == "Dashboard")  
      ) continue;

    // Find first empty row 
    var column = source_sheet.getRange('E:E');
    // NOTE that you need to put some text in to E1 or the ct variable below will stay at zero
    var values = column.getValues(); 
    var ct = 0;

    while ( values[ct] && values[ct][0] != "" ) {
      ct++;
    }  

    var source_range = source_sheet.getRange('A3:E'+(ct));  

    // Now find the right place to put it


    var last_row = target_sheet.getLastRow();
    target_sheet.insertRowAfter(last_row);
    var target_range = target_sheet.getRange("A"+(last_row+1)+":E"+(last_row+1));

    source_range.copyTo(target_range);

  }  
};

Answer 1

function MoveData() {
  var ss=SpreadsheetApp.getActive(); 
  var tsh=ss.getSheetByName('RawData');
  var shts=ss.getSheets();
  var exclA=["RawData","ProjectDB","Dashboard"];
  for(var i=0;i<shts.length;i++){
    var ssh=shts[i];
    if(exclA.indexOf(ssh.getName())!=-1) continue;
    // Find first empty row 
    var ch=getColumnHeight(5,ssh,ss);
    var rg=ssh.getRange(1,5,ch,1);
    var vA=rg.getValues();
    tsh.insertRowAfter(ch);
    tsh.getRange(ch+1,1);
    rg.copyTo(target_range);
  }  
}

function getColumnHeight(col,sh,ss){
  var ss=ss || SpreadsheetApp.getActive();
  var sh=sh || ss.getActiveSheet();
  var col=col || sh.getActiveCell().getColumn();
  var rg=sh.getRange(1,col,sh.getLastRow(),1);
  var vA=rg.getValues();
  while(vA[vA.length-1][0].length==0){
    vA.splice(vA.length-1,1);
  }
  return vA.length;
}