I have 2 sheets in a spreadsheet, I want to check if in Sheet 1 ('QualityCheck') in column D are same Unique IDs as in Sheet 2 ('Dubs') column A3:A, delete the rows from Sheet 1 ('QualityCheck').

Here's what I have, but is not working.

Any help will be appreciated.

Thank you!

function deleteDubs() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s1 = ss.getSheetByName('QualityCheck');
  var s2 = ss.getSheetByName('Dubs'); 
  var ui=SpreadsheetApp.getUi();

  var values1 = s1.getDataRange().getValues();
  var values2 = s2.getDataRange("A3:A").getValues();
  
  var delData = [];
  
  for(var i in values1){
   var keep = true
    for(var n in values2){
      if( values1[i][3] == values2[n][0]){
        keep = false;
        break;
      }
    }
   if(keep){
      delData.push(values1[i]);
    }
  }
  s1.clear()
  s1.getRange(1,1,delData.length,delData[0].length).setValues(delData);

ui.alert("Dups Deleted!");
}

CodePudding user response：

getDataRange has no arguments. I think that an error occurs by this. From your question, var values2 = s2.getRange("A3:A" s2.getLastRow()).getValues(); might be useful. When this is reflected in your script, it becomes as follows.

From:

var values2 = s2.getDataRange("A3:A").getValues();

To:

var values2 = s2.getRange("A3:A"   s2.getLastRow()).getValues();

Note:

• In your script, when filter and an object are used, the process cost might be able to be reduced a little. The modified script is as follows. By the way, when SpreadsheetApp.flush(); is used before ui.alert("Dups Deleted!");, you can see the result situation.

    function deleteDubs() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var s1 = ss.getSheetByName('QualityCheck');
      var s2 = ss.getSheetByName('Dubs');
      var ui = SpreadsheetApp.getUi();
  
      var values1 = s1.getDataRange().getValues();
      var values2 = s2.getRange("A3:A"   s2.getLastRow()).getValues().reduce((o, [a]) => (o[a] = true, o), {});
      var delData = values1.filter(r => !values2[r[3]]);
      s1.clear()
      s1.getRange(1, 1, delData.length, delData[0].length).setValues(delData);
      SpreadsheetApp.flush();
      ui.alert("Dups Deleted!");
    }
  

References:

• getDataRange()
• filter()