CODEWITHSUNDEEP
google-apps-script
NotInTheCity
NotInTheCity

Reputation: 55

Compare two Google Sheets and copy only the non-duplicate rows

I am trying to compare two Google Sheets and only copy the non-duplicate values from one sheet to another, but it is not working.

A nested loop is used to check each individual row from ss1 against all individual rows of ss2 after they are pulled into arrays. If the row from ss1 is not in ss2, it should be copied to the first empty row in ss2 (it changes a boolean value). Currently, the script is not able to 'see' that the two values are the same. The log shows "no match" and all rows are copied to the 2nd sheet. (The first row in sheet 2 is a duplicate of the first row in sheet 1.) It is probably something simple, but I am missing it.

var ss1=SpreadsheetApp.openById('myID').getSheetByName('Sheet1');
var ss2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var ss1lr=ss1.getLastRow();
var ss2lr=ss2.getLastRow();
var arr1;
var arr2;
var ss2NewRow=ss2lr+1;

function getRows(){

  var range1=ss1.getRange("A2:U"+ss1lr).getValues();
  var range2=ss2.getRange("A2:U"+ss2lr).getValues();
  var blFound;

  for(var i=0;i<ss1lr-1;i++){
    arr1=range1[i];
      Logger.log("arr1[" + i + "]:  " + arr1);  
      //result:    arr1[0]:  Mon Jul 17 2017 15:35:23 GMT-0500 (CDT),Damaged,Bob Smith,123456-1,item123     
      cTime=range1[i][0];
      cName=range1[i][2];
      cOrder=range1[i][3];

      for(var j=0;j<ss2lr-1;j++){
        //arr2=range2[j];
        mTime= range2[j][0];
        mName= range2[j][2];
        mOrder=range2[j][3];

        Logger.log("cTime: " + cTime + "=====" + "mTime:  " + mTime);
          //result:  cTime: Mon Jul 17 2017 15:35:23 GMT-0500 (CDT)=====mTime:  Mon Jul 17 2017 15:35:23 GMT-0500 (CDT)
        Logger.log("cName: " + cName + "=====" + "mName:  " + mName); 
          //result: cName: Bob Smith=====mName:  Bob Smith
        Logger.log("cOrder: " + cOrder + "=====" + "mOrder:  " + mOrder); 
          //result: cOrder: 123456-1=====mOrder:  123456-1                

        if(cTime==mTime){
          Logger.log("Time matches");
            }

        if((cTime==mTime) && (cName==mName) && (cOrder==mOrder)){
          //they match - do not copy
          Logger.log("match");
          blFound=true;
          break;
          } else {
          Logger.log("no match"); //result:  no match
          blFound=false;
          } //end if-else
        }//end j loop

        if (blFound===false) {

          ss2.getRange("A" + (ss2NewRow)+":U"+(ss2NewRow)).setValues([arr1]);
            ss2NewRow=ss2NewRow+1;
           }
      }//end i loop

    } //end for loop

Upvotes: 1

Views: 961

Answers (1)

Cooper
Cooper

Reputation: 64102

Based upon your explanation I think this might work. But I don't want to have enter a bunch of data into your spreadsheet to test it so I didn't test it. This routine just identifies the rows but doesn't do anything with them. That's up to you.

function findNonDupes()
{
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sht1=ss.getSheetByName('Dupe1');
  var sht2=ss.getSheetByName('Dupe2');
  var rng1=sht1.getDataRange();
  var rng2=sht2.getDataRange();
  var rng1A=rng1.getValues();
  var rng2A=rng2.getValues();
  var dupeRows=[];
  var nonDupes=[];
  for(var i=0;i<rng1A.length;i++)
  {
    var isDupe=true;
    for(var j=0;j<rng1A[i].length;j++)
    {
      if(rng1A[i][j]!=rng2A[i][j])
      {
        isDupe=false;
        break;
      }
    }
    if(isDupe)
    {
      dupeRows.push(i+1);
    }
  }
  Logger.log(dupeRows);
  for(var i=0;i<rng1A.length;i++)
  {
    if(dupeRows.indexOf(i+1)==-1)
    {
      nonDupes.push(i+1);
    }
  }
  Logger.log(nonDupes);
  SpreadsheetApp.getUi().alert('Rows to Copy: ' + nonDupes.join(', '));
}

Upvotes: 2

Related Questions