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

Question

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: "[email protected]"
        });
        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);
}

Edit I have been trying and thinking en trying... but still haven't found out how to make it work. But I also got understanding of why it's not working; I just don't know how to get it fixed. Let me elaborate on the problem a bit more: The problem is, that within the forEach loop the range is a filtered variant of the data, pulled from the spreadsheet with getValues. Therefore, writing data back with ws.getRange("R3:R" + ws.getLastRow()).setValues(mailSucces); results in mismatched checkmarks in te spreadsheet. So, somehow I need to put the range of the previous used filter data = data.filter(function(r){ return r[17] == false & r[16] > 3}); in a variable...? I guess?

Furthermore, I don't think it's wise to use setValue within the loop, because (from what I understand from my searching on the topic) this results in a slow script, because every loop the script makes an API call to write in the spreadsheet. Hence the errors.push and mailSucces.push, and my attempt to do a setValue at the end, after the loop is finished.

Can someone help me to finish this problem?

question from:https://stackoverflow.com/questions/65937314/how-to-write-setvalues-to-a-google-sheet-using-a-filtered-foreach-loop

Answer 1

The problem is different size of the range you write to and data you are writing in.

Try replacing:

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

With:

ws.getRange(3, 19, errors.length, 1).setValues(errors);
ws.getRange(3, 18, mailSucces.length, 1).setValues(mailSucces);

You should use this variation of getRange

https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow,-column,-numrows,-numcolumns

Your data has non-fixed number of rows and fixed number of columns (1). In general case your data will be matrix of X rows and Y columns. For that purpose you can make it completely dynamic:

sheet.getRange(startRow, startColumn, data.length, data[0].length)

Just make sure data.length is > 0 before you do this, otherwise data[0].length will break.

---

Edit: I started writing a comment but it got too long. There are couple of things that may go wrong with sending emails. First thing I noticed is that you use & in filter, but in AppsScript/JavaScript/C-like-languages, you should use && for logical AND. Now the email: you only detect the code break with the catch block. At this point you don't know why the code breaks it could be anything. With GmailApp I recommend you to use createDraft while developing, then when all ok replace it with sendEmail for the final version, both functions have the exact same parameters, thank you Google devs ;-).

To find out the exact problem you should get the error message on break and display it. err.stack should tell you pretty much everything:

catch(err){
  Logger.log(err.stack); // Added
  errors.push(["Error: no message sent."]);
  mailSucces.push(["False"]);
}

Run the sendEmail function from the code editor and you should see the Log for each catch(err) pass.