Can't copy more than one value when comparing two columns in two sheets for differences in Google Apps Script

Question

I am creating a script in Google Apps Script that would compare two sheets in a Spreadsheet row-by-row in a specific column containing unique IDs. If A[i] in Sheet(1) is different from A[i] in Sheet(2), I want the entire row with that cell to be copied to a new sheet. The sheets are really large, several thousand rows.

So far I have gotten to a point when the script copies a value once and then continues with the loop with no further results. Also, it takes ages and I suspect this might be very sub optimal. It's super difficult for me to debug in Google Apps Script and I'm at wits end. Here's what I have so far and my comments.

function compareSheetDrop(input) {

    var ss = SpreadsheetApp.getActiveSpreadsheet();

    //create a new blank sheet where the output will be stored
    ss.insertSheet(5);

    /*create references to sheets used by the script. 
    dropSheet is the sheet used as a basis, 
    compareSheet is a sheet possibly containing new values, 
    resultSheet is the blank sheet that will store new values
     */
    var dropSheet = ss.getSheetByName("Drop (2)");
    var compareSheet = ss.getSheets()[4];
    var resultSheet = ss.getSheets()[5];

    /*loop over the entire dropSheet and look for differences. 
The sheets have header rows*/
    for (var i = 2; i <= dropSheet.getLastRow(); i++) {

        /*variable j will represent the row number 
        where the difference will be pasted to 
        resultsSheet.A[j] etc.*/
        var j = 1;

        /*establish references to the cells 
        that will be used for comparison 
        (i.e. A[i] in dropSheet, A[i] in compareRange)*/
        var dropRange = dropSheet.getRange(i, 1);
        var compareRange = compareSheet.getRange(i, 1);

        /*establish references to the row 
        that will be copied from compareSheet 
        and to the row that 
        will be the destination in resultsSheet*/
        var resultRange = resultSheet.getRange(j, 1);
        var entireRow = compareSheet.getRange(i, 1);

        /*check if the unique value of 
        cell dropSheet.A[i] is different from 
        compareSheet.A[i] and whether they are not blanks. 
        If they are different, 
        copy entire row from compareSheet to the resultsSheet*/
        if (dropRange.getValues()[0] !== compareRange.getValues()[0] && dropSheet.getRange(i, 1).isBlank() == false && compareSheet.getRange(i, 1).isBlank() == false) {
            j++;
            entireRow.copyTo(resultRange, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
        }
    }
}

I expect the resultSheets to be full of rows from compareSheet that are not present in dropSheet, but instead of a row I get one cell A1 populated and then nothing happens while the script is still running. I suspect the problem might be in the loop itself and in the copyTo, but I can't find the cause.

Are the sheets sorted? Where do you want the information to be added in sheet 2? — J. G.

Cooper Cooper · Accepted Answer · 2019-06-10T16:40:10

Try this:

function copyRowWhenAsDontMatch() {
  var ss=SpreadsheetApp.getActive();
  var sh1=ss.getSheetByName('Sheet1');
  var rg1=sh1.getRange(2,1,sh1.getLastRow()-1,sh1.getLastColumn());
  var vA1=rg1.getValues();
  var sh2=ss.getSheetByName('Sheet2');
  var rg2=sh2.getRange(2,1,sh2.getLastRow()-1,sh2.getLastColumn());
  var vA2=rg2.getValues();
  var sh3=ss.getSheetByName('Sheet3');//rows that dont match in sheet1
  sh3.clear();
  var sh4=ss.getSheetByName('Sheet4');//rows that dont match in sheet2
  sh4.clear();
  for(var i=0;i<vA1.length;i++) {
    if(vA1[i][0]!=vA2[i][0]) {
      sh3.appendRow(vA1[i]);
      sh4.appendRow(vA2[i]);
    }
  }
}

Can't copy more than one value when comparing two columns in two sheets for differences in Google Apps Script

2 Answers