Get two arrays, compare and remove duplicates in Google Apps Script

Question

I have got two lists on a google spreadsheet: 'Existing Companies' and 'New Companies'. I would like to compare the two and find out which unique entries in 'New Companies' do not exist in 'Existing Companies', get those entries and eliminate from 'New Companies' all other entries.

I have made the following script to do it:

function grabNewCompanies() {

  // grab existing companies list from sheet into an array
  var sh = SpreadsheetApp.openById("sheetID").getSheetByName("sheetName")
  var row = sh.getDataRange().getLastRow()
  var existingCompanies = sh.getRange(2,1,row - 1,1).getValues()
  Logger.log(existingCompanies)

//grab new companies added
  var sh = SpreadsheetApp.openById("sheetID").getSheetByName("sheetName")
  var row = sh.getDataRange().getLastRow()
  var newCompanies = sh.getRange(2,4,row - 1, 1).getValues()
  Logger.log(newCompanies)

  var array = [];  
  for(i=0; i<newCompanies.length; i++) {
    for(j=0; j<existingCompanies.length; j++) {
      if(newCompanies[i][0] !== existingCompanies[j][0]) {
      array.push([newCompanies[i][0]]);
}

Logger.log(array)

    }

I have ran this script but it has failed. The two arrays (existingCompanies and newCompanies) are returned correctly.

However, the comparison between the two does not seem to be working: it always returns the first element of the newCompanies array, regardless of whether it exists in existingCompanies.

Also, I am unsure about how to ensure that the values pushed into array are not duplicated if newCompanies contains more than one entry which does not exist in existingCompanies.

Thank you.

Tanaike · Accepted Answer · 2018-09-07 06:44:20Z

3

You want to retrieve the difference elements between existingCompanies and newCompanies. If my understand for your question is correct, how about this modification? I think that there are several solutions for your situation. So please think of this as one of them.

Modification points:

In the case that your script is modified, it picks up an element from newCompanies and it checks whether that is included in existingCompanies.
If that picked element is not included in existingCompanies, the element is pushed to array. In this modification, I used true and false for checking this.

This flow is repeated until all elements in newCompanies are checked.

Modified script 1:

When your script is modified, how about this?

From:

var array = [];  
for(i=0; i<newCompanies.length; i++) {
  for(j=0; j<existingCompanies.length; j++) {
    if(newCompanies[i][0] !== existingCompanies[j][0]) {
      array.push([newCompanies[i][0]]);
    }
  }
}

To:

var array = [];
for(i=0; i<newCompanies.length; i++) {
  var temp = false; // Added
  for(j=0; j<existingCompanies.length; j++) {
    if(newCompanies[i][0] === existingCompanies[j][0]) { // Modified
      temp = true; // Added
      break; // Added
    }
  }
  if (!temp) array.push([newCompanies[i][0]]); // Modified
}
Logger.log(array)

Modified script 2:

As other patterns, how about the following 2 samples? The process cost of these scripts are lower than that of the script using for loop.

var array = newCompanies.filter(function(e) {return existingCompanies.filter(function(f) {return f[0] == e[0]}).length == 0});
Logger.log(array)

or

var array = newCompanies.filter(function(e) {return !existingCompanies.some(function(f) {return f[0] == e[0]})});
Logger.log(array)

If I misunderstand your question, please tell me. I would like to modify it.

edited Sep 7, 2018 at 6:44

answered Sep 7, 2018 at 6:33

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

franciscofcosta Over a year ago

Thank you very much! This was exactly what I needed. I ended up using one of the less costly solutions you suggested. Thank you

Tanaike Over a year ago

@franciscofcosta I'm glad your issue was solved. Thank you, too.

Collectives™ on Stack Overflow

Get two arrays, compare and remove duplicates in Google Apps Script

1 Answer 1

Modification points:

Modified script 1:

Modified script 2:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points:

Modified script 1:

Modified script 2:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related