CODEWITHSUNDEEP
google-apps-script
Rishi Thariani
Rishi Thariani

Reputation: 27

How to check for duplicates and only append unique values in google-app-scripts?

I have the below code which helps me take data from sheet1 and pastes it in Sheet2

function HubArchive() 
{
  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var sourceData = sourceSheet.getDataRange().getValues();
  sourceData.splice(1,0);  // Remove header
  var rows = sourceSheet.getDataRange();
  var data = rows.getValues();
  var targetData = [];

  for (var i in sourceData) {
  if (sourceData[i][3] !== null && sourceData[i][3] !== 'test') {
    targetData.push(sourceData[i]);
  }
}
  var targetSS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
  var targetRangeTop = targetSS.getLastRow(); // Get # rows currently in target
     targetSS.getRange(targetRangeTop+1,1,targetData.length,targetData[0].length).setValues(targetData);

}

However, I'm looking for a way for the code to check existing values in Sheet2 and only append any new ones instead of copying in any duplicates again. Any suggestions on what I could add in to make it check for duplicates and append only unique rows?

Upvotes: 1

Views: 1634

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

You can do it with the help of indexOf, array.push() and join.

Sample:

function HubArchive() {
  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var sourceData = sourceSheet.getDataRange().getValues();
  sourceData.splice(1,0);  // Remove header
  var rows = sourceSheet.getDataRange();
  var data = rows.getValues();
  var targetSS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");    
  var targetData =targetSS.getDataRange().getValues();
  var targetRowArray=[];
  for(var i=0;i<targetData.length;i++){
    targetRowArray.push(targetData[i].join(","));
   }
  targetData=[];
  for (var i in sourceData) {
    if (sourceData[i][3] !== null && sourceData[i][3] !== 'test'&&targetRowArray.indexOf(sourceData[i].join(","))==-1) {
      targetData.push(sourceData[i]);
    }
  }
if(targetData.length>0){ 
 var targetRangeTop = targetSS.getLastRow(); // Get # rows currently in target 
 targetSS.getRange(targetRangeTop+1,1,targetData.length,targetData[0].length).setValues(targetData);
 }
}

If you only want to compare the first column you can replace [i].join(",") by [i][0] :

function HubArchive() {
  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var sourceData = sourceSheet.getDataRange().getValues();
  sourceData.splice(1,0);  // Remove header
  var rows = sourceSheet.getDataRange();
  var data = rows.getValues();
  var targetSS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");    
  var targetData =targetSS.getDataRange().getValues();
  var targetRowArray=[];
  for(var i=0;i<targetData.length;i++){
    targetRowArray.push(targetData[i][0]);
   }
  targetData=[];
  for (var i in sourceData) {
    if (sourceData[i][3] !== null && sourceData[i][3] !== 'test'&&targetRowArray.indexOf(sourceData[i][0])==-1) {
      targetData.push(sourceData[i]);
    }
  }
  if(targetData.length>0){
  var targetRangeTop = targetSS.getLastRow(); // Get # rows currently in target
  targetSS.getRange(targetRangeTop+1,1,targetData.length,targetData[0].length).setValues(targetData);
  }
}

Upvotes: 2

Related Questions