CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Benson Batty
Benson Batty

Reputation: 15

Trying to Speed up my Script Compare 2 arrays (not a for loop)

I'm trying to compare two columns in my sheet 1 column is a list of all email addresses that have ever made a purchase with us which is in excess of 50000 and the other column which is a list of emails that have made a purchase with us in the past month. I'm trying to just count the new email addresses by comparing it from master list.

I've got something to works with a smaller list but it keeps timing out when I index my list of 50000+. Is there anyway to speed my script up?

function compareList(){
var s= SpreadsheetApp.openById('My sheet ID');
var ss = s.getSheetByName("Compare");
var oldsubs = ss.getRange(5,1,ss.getLastRow(),1).getValues();
var newsubs = ss.getRange(5,2,ss.getLastRow(),1).getValues();


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

Any help is welcome.

Upvotes: 0

Views: 61

Answers (1)

Cooper
Cooper

Reputation: 64062

Try this:

function newnotinold() {
  var s = SpreadsheetApp.getActive()
  var ss = s.getSheetByName("Sheet1");
  var o = ss.getRange(2, 1, ss.getLastRow(), 1).getValues().flat();
  var n = ss.getRange(2, 2, ss.getLastRow(), 1).getValues().flat();
  let newnotinold = n.filter(x => !~o.indexOf(x));
  Logger.log(newnotinold);
}

Upvotes: 3

Related Questions