How to write (SetValues) to a Google Sheet using a filtered forEach loop?

I’ve been trying for hours to make the following Google Apps Script work. What it needs to do, is send emails (from an html-template) to anyone that:

  • has a complete Event Schedule (which is completed if they have been
    assigned to at least 4 events, which is counted in column Q);
  • has NOT been sent an email earlier (which is kept track of in column

The script keeps track of errors in column S, i.e. if there’s no email address provided.

It appears it only works:

  • if I comment out

    data = data.filter(function(r){ return r[17] == true & r[16] > 3});

  • or if I comment out

    ws.getRange("S3:S" + ws.getLastRow()).setValues(errors);
    ws.getRange("R3:R" + ws.getLastRow()).setValues(mailSucces);

How can I get this script to work properly?
A copy of the Google Sheet I’m referring to is this one:

This is my code so far:

function SendEmail(){

  var voornaam = 3;
  var achternaam = 4;
  var email = 5;
  var event1 = 9;
  var event2 = 10;
  var event3 = 11;
  var event4 = 12;
  var event5 = 13;
  var event6 = 14;
  var event7 = 15;
  var emailTemp = HtmlService.createTemplateFromFile("email");
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Events Day 1");
  var datum = ws.getRange(1,3).getValue();
  var spreker = ws.getRange(1,6).getValue();

  var data = ws.getRange("A3:R" + ws.getLastRow()).getValues();

  data = data.filter(function(r){ return r[17] == false & r[16] > 3}); //Either this needs to be commented out...

  let errors = [];
  let mailSucces = [];
      emailTemp.voornaam = row[voornaam]; = row[email];
      emailTemp.datum = datum;
      emailTemp.spreker = spreker;
      emailTemp.event1 = row[event1];
      emailTemp.event2 = row[event2];
      emailTemp.event3 = row[event3];
      emailTemp.event4 = row[event4];
      emailTemp.event5 = row[event5];
      emailTemp.event6 = row[event6];
      emailTemp.event7 = row[event7];
      var htmlMessage = emailTemp.evaluate().getContent();
        "Here you go! Your personal schedule for the event of " + datum, 
        "Your emailprogramm doesn't support html.",
          name: "Event Organisation Team", htmlBody: htmlMessage, replyTo: ""
      errors.push(["Error: no message sent."]);
  }); //close forEach
  ws.getRange("S3:S" + ws.getLastRow()).setValues(errors);  //or this and the next line need to be commented out.
  ws.getRange("R3:R" + ws.getLastRow()).setValues(mailSucces);

69 thoughts on “How to write (SetValues) to a Google Sheet using a filtered forEach loop?”

  1. Hey I know this is off topic but I was wondering if you knew of any
    widgets I could add to my blog that automatically tweet my newest twitter updates.
    I’ve been looking for a plug-in like this for quite some time and was hoping maybe you would have some experience with something like this.
    Please let me know if you run into anything. I truly
    enjoy reading your blog and I look forward to your new updates.


Leave a Comment