App Script to Find New or Unique Data Between Two Google Spreadsheets (how to append)

Question

I've modified the script from the Removing Duplicate Rows in a Spreadsheet Google Apps tutorial so that it's comparing data in a given range between two sheets (SRC and DEST). The unique data are then written to the DEST sheet. It seems to work, however the new (unique) data found on the SRC is overwriting the current data on DEST sheet (starting at the first row) and I need it to be appended at the end. What would be the best way to accomplish this?

Here's the Spreadsheet

And here's the script:

function appendNEW() {
  // source spreadsheets
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = ss.getSheetByName("SRC");  
  //destination spreadsheet
  var destSheet = ss.getSheetByName("DEST");

  var srcData = srcSheet.getRange('A2:B').getValues();
  var destdData = destSheet.getRange('A2:B').getValues();

  var newData = new Array();


  for(i in srcData){
    var row = srcData[i];
    var duplicate = false;

    for(j in srcData){

        if(row[0] == destdData[j][0] && row[1] == destdData[j][1]){
  duplicate = true;

      }
    }
    if(!duplicate){
      newData.push(row);
    }
  }
  destSheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);

}

Any help would be greatly appreciated! Thank You!

Rubén Rubén · Accepted Answer · 2018-05-06T23:34:03

Instead of

destSheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);

try

destSheet.getRange(destSheet.getLastRow()+1, 1, newData.length, newData[0].length).setValues(newData);

The above because destSheet.getLastRow()+1 returns the row number of the first blank row.

App Script to Find New or Unique Data Between Two Google Spreadsheets (how to append)

1 Answers