Google Sheet not copying rows to correct sheet

Question

I am splitting a sheet of data into multiple sheets, using an email address as a unique identifier.

I'm using a variant of some script I've found and edited, but using copyTo rather than appendRow.

The problem is that it seems to copy the last row of a particular data set to the subsequent sheet.

I thought this is perhaps as I'm using getActiveSheet rather than explicitly targeting the correct sheet, so I have tried doing so, using something like: getSheetByName(emailAddress[i][0]).

However, that keeps coming up with null. When I log the values of emailaddress, look in the logger, the values appear correctly, so not quite sure why I'm getting null.

The following is the script that "works", but copies the last row to the subsequent sheet.

  function myFunction() {
  
  var theWorkbook = SpreadsheetApp.getActiveSpreadsheet();
  var theSheet = theWorkbook.getSheetByName("Copied");
  
  // This var will contain all the values from column B -> The email addresses
  var theEmails = theSheet.getRange("B:B").getValues();

  // This var will contain all the rows
  var rows = theSheet.getDataRange().getValues();
  
  //Set the first row as the header
  var header = rows[0];

  //Store the Sheets already created
  var completedSheets = []

  //The last created Sheet
  var last = theEmails[1][0]


  for (var i = 1; i < theEmails.length; i++) {    

    //Check if the Sheet is already done, if not create the sheet
    if(!completedSheets.includes(theEmails[i][0])) {

      //Set the Sheet name = email address (except if there is no name, then = Blank)
      if (theEmails[i][0] === "") {
        var currentSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet("Blank");
      } else {
        var currentSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(theEmails[i][0]);
      }


      //append the header
      currentSheet.appendRow(header);
      var rowtocopy = theSheet.getRange(i,1,1,8);
      rowtocopy.copyTo(currentSheet.getRange(currentSheet.getLastRow()+1, 1));
      completedSheets.push(theEmails[i][0])
      last = theEmails[i][0]
    } else if (last == theEmails[i][0]) {      
      
    // If the sheet is created append the row to the sheet
      var currentSheet = SpreadsheetApp.getActiveSpreadsheet();
      var activespreadsheet = SpreadsheetApp.getActiveSheet();
      var rowtocopy = theSheet.getRange(i,1,1,8);
      rowtocopy.copyTo(activespreadsheet.getRange(activespreadsheet.getLastRow()+1, 1));  
      
    }

  }
 

}

And the snippett of code where I'm trying to name the sheet (and many variants of this), although this returns a null error.

else if (last == theEmails[i][0]) {      
  
// If the sheet is created append the row to the sheet
  var currentSheet = SpreadsheetApp.getActiveSpreadsheet();
   var activespreadsheet =SpreadsheetApp.getActive().getSheetByName(last);
   var rowtocopy = theSheet.getRange(i,1,1,8);
  rowtocopy.copyTo(activespreadsheet.getRange(activespreadsheet.getLastRow()+1, 1));

What am I doing wrong?

dwmorrin dwmorrin · Accepted Answer · 2020-07-12T19:46:49

It's the classic Sheets gotcha of the values array being index zero based and the API's "rows" and "columns" being index one based.

In your for loop, you are using the same index i for both the values array and the API "row" parameter in your getRange(row, ...) calls. Anyone working with Sheets has done this to themselves, probably many times.

Update where you have

var rowtocopy = theSheet.getRange(i,1,1,8);

to

var rowtocopy = theSheet.getRange(i + 1,1,1,8);

and it will work.

Google Sheet not copying rows to correct sheet

1 Answers