I want to add 2 mint delay in google sheet script forEach loop for sending emails but setTimeout and sleep is not working

Question

i want 2 mint delay after one alteration of the loop. Means I want to add some delay in sending emails. the complete code link is(https://github.com/googleworkspace/solutions/blob/master/mail-merge/src/Code.js)

   obj.forEach(function(row, rowIdx){
   sleep(1200000);
    // only send emails is email_sent cell is blank and not hidden by filter
    if (row[EMAIL_SENT_COL] == ''){
      try {
        const msgObj = fillInTemplateFromObject_(emailTemplate.message, row);

    // @see https://developers.google.com/apps-script/reference/gmail/gmail-app#sendEmail(String,String,String,Object)
    // if you need to send emails with unicode/emoji characters change GmailApp for MailApp
    // Uncomment advanced parameters as needed (see docs for limitations)
    GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {
      htmlBody: msgObj.html,
      // bcc: '[email protected]',
      // cc: '[email protected]',
      // from: '[email protected]',
      // name: 'name of the sender',
      // replyTo: '[email protected]',
      // noReply: true, // if the email should be sent from a generic no-reply email address (not available to gmail.com users)
      attachments: emailTemplate.attachments,
      inlineImages: emailTemplate.inlineImages
    });
    // modify cell to record email sent date
    out.push([new Date()]);
  } catch(e) {
    // modify cell to record error
    out.push([e.message]);
  }
} else {
  out.push([row[EMAIL_SENT_COL]]);
}
});

Answer 1

Solution:

You can use an Installable Trigger to run sendEmails() every one minute. This can be created when the menu item is selected:

EDIT: Since everyMinute(2) is not allowed, one workaround is to have the function execute every minute. And since there's a column that gets updated once the email is sent, on first execution it will mark the column as "To Send", and on the 2nd execution it will send the email.

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Mail Merge')
      .addItem('Send Emails', 'createTrigger')
      .addToUi();
}

function createTrigger() {
  ScriptApp.newTrigger("sendEmails")
    .timeBased()
    .everyMinutes(1)
    .create();
}

Then replace the forEach() with a simple for loop so it can break out of it once the first email is marked for sending or sent.

  // loop through the rows of data and break once one email is sent
  for (i = 0; i < obj.length; i++) {
    var row = obj[i];
    // Mark emails with "To Send" if email_sent cell is blank. Only send emails if email_sent cell is "To Send" and not hidden by filter
    if (row[EMAIL_SENT_COL] == ''){
      out.push(['To Send']);
      break;
    } else if (row[EMAIL_SENT_COL] == 'To Send'){
      try {
        const msgObj = fillInTemplateFromObject_(emailTemplate.message, row);

        // @see https://developers.google.com/apps-script/reference/gmail/gmail-app#sendEmail(String,String,String,Object)
        // if you need to send emails with unicode/emoji characters change GmailApp for MailApp
        // Uncomment advanced parameters as needed (see docs for limitations)
        GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {
          htmlBody: msgObj.html,
          // bcc: '[email protected]',
          // cc: '[email protected]',
          // from: '[email protected]',
          // name: 'name of the sender',
          // replyTo: '[email protected]',
          // noReply: true, // if the email should be sent from a generic no-reply email address (not available to gmail.com users)
          attachments: emailTemplate.attachments,
          inlineImages: emailTemplate.inlineImages
        });
        // modify cell to record email sent date
        out.push([new Date()]);
      } catch(e) {
        // modify cell to record error
        out.push([e.message]);
      }
      break;
    } else {
      out.push([row[EMAIL_SENT_COL]]);
    }
  }

And once all the rows are processed, delete the trigger:

  // updating the sheet with new data
  sheet.getRange(2, emailSentColIdx+1, out.length).setValues(out);

  if (out.length == obj.length) {
    var triggers = ScriptApp.getProjectTriggers();
    for (var j = 0; j < triggers.length; j++) {
    ScriptApp.deleteTrigger(triggers[j]);
    }
  }