Copy-Paste rows from one sheet to another comparing cell values to avoid duplicates using AppScript

Question

function copyRows()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = ss.getSheetByName("Form Responses 1");
  var tarSheet = ss.getSheetByName("MasterDB");
  var lastRow  = srcSheet.getLastRow();
  var Lr       = tarSheet.getLastRow();
  for (var i = 2; i <= lastRow; i++) 
  {
    var cell = srcSheet.getRange("A" + i);
    var val = cell.getValue();
    Logger.log(lastRow)
    
        for(var j=2; j<= Lr; j++)
    
      {
    
        var findstring = tarSheet.getRange("A" + j);
        var find =findstring.getValue();
       // Logger.log(find)
       
      if (val == find) 
       {
        continue;
        }
var srcRange = srcSheet.getRange("A" + i + ":T" + i);
            var tarRow = tarSheet.getLastRow();
            tarSheet.insertRowAfter(tarRow);
            var tarRange = tarSheet.getRange("A" + (tarRow+1) + ":T" + (tarRow+1));
            srcRange.copyTo(tarRange);
 }
}
}

Hello ,actually I'm trying copy & paste rows from one sheet to another within the spreadsheet ,but to avoid duplicates ,i'm comparing cell value from column A in both the sheets.
- column A is having Date & Time Stamp ,which i'm comparing ,if value matches ,then don't copy &if not copy the particular range & paste on 'last row' of Master DB.
- But with above code its just keep on copying rows multiple times ,i'm not getting where exactly the error is.
- Please help me with this ,any help will be appreciated ,Thanks in Advance.
- Sorry ,if i have posted this in a wrong format ,actually i'm new here.

Take one of the date columns flatten it with map take everyone of those values convert it to a getTime or valueOf then you can do an indexOf with valUeOf the other column and if it’s not -1 then that’s a duplicate. — Cooper
Can you share the sheet to reproduce it? Do the values have different formats? — Kessy
Hello Cooper ,thank you for your response ,actually just i tried with adding 'getTime' to the variable 'Val' & 'Find' but getting this error - "TypeError: cell.getTime is not a function (line 111, file "test11")" ,can you please help me with this. — Akshay Jadhav
hello Kessy ,here is the link for the sheet - docs.google.com/spreadsheets/d/… — Akshay Jadhav

Cooper Cooper · Accepted Answer · 2020-06-03T15:26:42

function copyRows() {
  var ss=SpreadsheetApp.getActive();
  var ssh=ss.getSheetByName("Form Responses 1");
  var tsh=ss.getSheetByName("MasterDB");
  var srg=ssh.getRange(2,1,ssh.getLastRow()-1,ssh.getLastColumn());//assume 1 header row
  var svs=srg.getValues();
  var trg=tsh.getRange(2,1,tsh.getLastRow()-1,1);//assume 1 header row
  var tvs=trg.getValues().map(function(r){return new Date(r[0]).valueOf();});
  svs.forEach(function(r,i){
    //if it is not in the target then append it to the target
    if(tvs.indexOf(new Date(r[0]).valueOf())==-1) {
      tsh.appendRow(r);
    }
  });
}

Copy-Paste rows from one sheet to another comparing cell values to avoid duplicates using AppScript

1 Answers