Looping through sheets in Google Spreadsheet and using getSheetName()

Question

I'm writing a script to loop through each sheet in one spreadsheet and copy data from specific cells into a corresponding sheet on another spreadsheet. I am getting an error on line 18 of the below code, however, stating that it can't call the getLastRow method of null. I used a couple of Logger.log lines to check my variables and see that targetSheet is coming back as null. Any advice on what I've got wrong?

//Export each sheet's daily data to another sheet *Test Version*
function exportReports() {
  var sourceSS = SpreadsheetApp.getActiveSpreadsheet();
  //Open Back Production Record *Test Version*
  var targetSS = SpreadsheetApp.openById("1ZJKZi-UXvqyGXW9V7KVx8whxulZmx0HXt7rmgIJpUY4");
  var allSourceSheets = sourceSS.getSheets();

  //For-Loop to loop through hourly production sheets, running the move data for-loop on each
  for(var s in allSourceSheets){

    var loopSheet = allSourceSheets[s];
    var loopSheetName = loopSheet.getSheetName();
    var targetSheet = targetSS.getSheetByName(loopSheetName);
    Logger.log(s);
    Logger.log(loopSheet);
    Logger.log(targetSheet);
    Logger.log(loopSheetName);
    var targetRow = targetSheet.getLastRow()+1;
    var currentDate = Utilities.formatDate(new Date(), "GMT-5", "MM/dd/yy");

    targetSheet.getRange(targetRow, 1).setValue(currentDate);
    //For-Loop to move data from source to target
    for(var i=6;i<=10;i++){
      var sourceRange = sourceSheet.getRange(i, 2);
      var targetRange = targetSheet.getRange(targetRow, i-4);
      var holder = sourceRange.getValue();

      targetRange.setValue(holder);
    }
  }    
}

If you replace the not-recommended for-in loop with the traditional index loop, do you still have problems? stackoverflow.com/questions/500504/… — tehhowch
Thank you; this was helpful for this issue and improving my knowledge in general. Much appreciated. — Derek Glissman

tehhowch tehhowch · Accepted Answer · 2018-03-14T17:05:38

Per the documentation on getSheetByName, if the target sheet name does not exist, then you get null as a return value.

getSheetByName(name)
Returns a sheet with the given name.

If multiple sheets have the same name, the leftmost one is returned. Returns null if there is no sheet with the given name.

So, the desired sheet with name specified by loopSheetName does not exist in the target workbook. Perhaps someone has created a new sheet, or renamed an existing sheet in the source workbook.

You haven't asked about it, but you can improve the performance of your copy code as well, by reading the inputs as a multi-row range array, creating a row array to hold the results, and writing that once:

var sourceData = sourceSheet.getRange(6, 2, 5, 1).getValues(); // (6,2) through (10, 2)
var output = [];
// Transpose row array to column array (could use other functions, but this is easier to understand)
for(var i = 0; i < sourceData.length; ++i) { output.push(sourceData[i][0]); }
targetSheet.getRange(targetRow, 2, 1, output.length).setValues([output]); // i0 = 6 -> 6 - 4 = 2

Looping through sheets in Google Spreadsheet and using getSheetName()

1 Answers