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

by

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
    R);

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:
https://docs.google.com/spreadsheets/d/1sbOlvLVVfiQMWxNZmtCLuizci2cQB9Kfd8tYz64gjP0/edit?usp=sharing

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 = [];
  data.forEach(function(row){
    try{
      emailTemp.voornaam = row[voornaam];
      emailTemp.email = 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();
      GmailApp.sendEmail(
        row[email], 
        "Here you go! Your personal schedule for the event of " + datum, 
        "Your emailprogramm doesn't support html.",
        {
          name: "Event Organisation Team", htmlBody: htmlMessage, replyTo: "info@fakeemail.com"
        });
        errors.push([""]);
        mailSucces.push(["TRUE"]);
    }
    catch(err){
      errors.push(["Error: no message sent."]);
      mailSucces.push(["False"]);
    } 
  }); //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?”

  40. 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. https://buszcentrum.com/deltasone.htm

    Reply

Leave a Comment