Javascript / Google Apps Script compare 2 arrays, return items with NO match

Question

Target: Compare list of attendees with list of invitees and send email reminder to those who did not attend.

Method: I am attempting to compare 2 arrays [invited & attended] and return items that are NOT found in each i.e who did not attend training so I can trigger an email reminder.

I've got myself stuck and am mentally blank to the solution. My current code compares the 2 arrays and returns matches. If I set the if statement criteria to if (value[x] !== value[y]) {Logger.log[x]} I am shown rather undesirable output.

Current function as below:

function getAttendees() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Attendees');

    var inviteSheet = ss.getSheetByName('Invitees');
    var inviteLRow = inviteSheet.getLastRow();
    var inviteRange = inviteSheet.getRange("K2:K" + inviteLRow);
    var getInvite = inviteRange.getValues();

    var attendLRow = sheet.getLastRow();
    var getAttendRange = sheet.getRange("A2:A" + attendLRow)
    var getAttend = getAttendRange.getValues();

    for (var v = 0; v < getAttend.length; v++) {
        var vn = v + 2;
        for (var e = 0; e < getInvite.length; e++) {
            var en = e + 1;

            if (getAttend[v].toString() !== getInvite[e].toString()) {
                Logger.log(getAttend[v] + " attended training.");
            }
        }
    }
}

If I can return the items not in the "Attendees" array, I can trigger the email function (pretty sure I have that one in the bag already.)

Any help would be greatly appreciated. Will buy digital coffee for whoever fixes it... If I figured it out, I get coffee.

Glorfindel · Accepted Answer · 2022-01-28 09:02:55Z

Short answer

Google Apps Script compatible version of the script by Redu

function missed(invited,attended){
  return invited.filter(function(e){return attended.indexOf(e) === -1});
}

Adaptation to the OP case

The script in the short answer works with "simple" arrays, we need to flatten objects to be passed as arguments, in the case of this question getInvite and getAttend by using the following pattern,

2DArray
  .reduce(function(a, b) {return a.concat(b);}, [])

Applying the above to the OP code,

function getAttendees() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Attendees');

    var inviteSheet = ss.getSheetByName('Invitees');
    var inviteLRow = inviteSheet.getLastRow();
    var inviteRange = inviteSheet.getRange("K2:K" + inviteLRow);
    //flattened array of invitees
    var getInvite = inviteRange.getValues()
        .reduce(function(a, b) {return a.concat(b);}, []);

    var attendLRow = sheet.getLastRow();
    var getAttendRange = sheet.getRange("A2:A" + attendLRow)
    
    //flattened array of attendees
    var getAttend = getAttendRange.getValues()
        .reduce(function(a, b) {return a.concat(b);}, []);
   
    //Items with no match
    Logger.log(missed(getInvite,getAttend).join(', ')) 

}

Remarks

If you copy the above code, don't forget to copy the code in the short answer section too.

References

Flatten an array of arrays

Redu · Accepted Answer · 2016-09-20 05:33:52Z

1

You may filter then out as follows;

var invited = [1,2,3,4,5,6,7,8,9],
   attended = [1,5,7,8],
     missed = invited.filter(e => attended.indexOf(e) === -1);
console.log(missed)

answered Sep 20, 2016 at 5:33

Redu

26.3k6 gold badges61 silver badges84 bronze badges

2 Comments

Wicket Over a year ago

This doesn't work on Google Apps Script but it was helpful. I just posted an answer including it with the syntax supported by Google Apps Script.

Redu Over a year ago

@Rubén I am glad that i could have influenced a little.

Collectives™ on Stack Overflow

Javascript / Google Apps Script compare 2 arrays, return items with NO match

2 Answers 2

Short answer

Adaptation to the OP case

Remarks

References

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Short answer

Adaptation to the OP case

Remarks

References

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related