CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
allemandi
allemandi

Reputation: 79

How to skip duplicate rows values between separate spreadsheets

I have two spreadsheets with different types of data. The only similarity is a list of names spread out across the spreadsheets.

I want to create a script that lets me copy specific rows from Spreadsheet 2 to Spreadsheet 1 with two conditions:

  1. Only rows marked Ready in Spreadsheet 2 should be copied over to Spreadsheet 1
  2. No rows containing names already in Spreadsheet 1 should be copied over from Spreadsheet 2

I'm probably missing something simple, but I can't get Point 2 to work.

    function CopyClick() {
      var sheet = SpreadsheetApp.getActiveSheet();
      var originalnames = sheet.getRange("A1:A").getValues();
      
      var otherss = SpreadsheetApp.openById("Spreadsheet 2 ID HERE");
      var copysheet = otherss.getSheetByName("Spreadsheet 2 Sheet Name");  
      var copynames = copysheet.getRange("C1:C").getValues();
      var statusdata = copysheet.getRange("D1:D").getValues();  //First condition has to be "Ready"
      
      var copylastrow = copysheet.getLastRow();
      
      
      for(var i=0; i<copylastrow; i++) {
        
        if(statusdata[i][0].toString().match(/Ready/ig) && (copynames[i][0].indexOf(originalnames) === -1)){  //Don't want duplicates, but indexOf isn't working   
          var lastRow = sheet.getLastRow();
          sheet.getRange(lastRow+1, 1).setValue(copynames[i][0]); //back to the original spreadsheet
          
        }
      } 
    }

As you can see, I wasn't sure of how to keep the second condition, ending up with a placeholder indexOf.

How could I best solve this?

Upvotes: 1

Views: 165

Answers (1)

Marios
Marios

Reputation: 27380

Explanation:

You can take advantage of the includes() function. It returns true if there is a match. However, you need to flat() the originalnames array first. I also changed the range of originalnames to avoid taking empty cells:

var originalnames = sheet.getRange("A1:A"+sheet.getLastRow()).getValues().flat(1);

and inside the if condition use:

!originalnames.includes(copynames[i][0])

this will evaluate to true if copynames[i][0] is not included in originalnames array. I also replaced statusdata[i][0].toString().match(/Ready/ig) to statusdata[i][0] == 'Ready' but feel free to use your own version if this does not work.

Solution:

  function CopyClick() {
      var sheet = SpreadsheetApp.getActiveSheet();
      var originalnames = sheet.getRange("A1:A"+sheet.getLastRow()).getValues().flat(1);
      
      var otherss = SpreadsheetApp.openById("Spreadsheet 2 ID HERE");
      var copysheet = otherss.getSheetByName("Spreadsheet 2 Sheet Name");  
      var copynames = copysheet.getRange("C1:C").getValues();
      var statusdata = copysheet.getRange("D1:D").getValues();  //First condition has to be "Ready"
      
      var copylastrow = copysheet.getLastRow();
      
      
      for(var i=0; i<copylastrow; i++) {
        
        if( statusdata[i][0] == 'Ready' && !originalnames.includes(copynames[i][0]) ){     
          var lastRow = sheet.getLastRow();
          sheet.getRange(lastRow+1, 1).setValue(copynames[i][0]); //back to the original spreadsheet
          
        }
      } 
    }

Upvotes: 4

Related Questions