In my Google Sheets script, I am looking to introduce a 2-minute delay within the forEach loop for sending emails. Unfortunately, I have not had success

Question

In my Google Sheets script, I am looking to introduce a 2-minute delay within the forEach loop for sending emails. Unfortunately, I have not had success

I am looking to introduce a 2-minute delay after each iteration of the loop. Specifically, I want to add a delay in the process of sending emails. You can find the complete code at this link.

   obj.forEach(function(row, rowIdx){
   sleep(1200000);
    // Emails will only be sent if the email_sent cell is empty and not hidden by a filter
    if (row[EMAIL_SENT_COL] == ''){
      try {
        const msgObj = fillInTemplateFromObject_(emailTemplate.message, row);

    // For more information on sending emails with unicode/emoji characters using GmailApp or MailApp, visit: https://developers.google.com/apps-script/reference/gmail/gmail-app#sendEmail(String,String,String,Object)
    GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {
      htmlBody: msgObj.html,
      attachments: emailTemplate.attachments,
      inlineImages: emailTemplate.inlineImages
    });
    
    out.push([new Date()]); // Record the date the email was sent
  } catch(e) {
    out.push([e.message]); // Record any errors encountered
  }
} else {
  out.push([row[EMAIL_SENT_COL]]); // Record that the email has already been sent
}
});

javascript google-apps-script mailmerge delayed-execution

Answer 1

Answer №1

Solution:

To automate the task of sending emails periodically, you can create an Installable Trigger in Google Apps Script. This trigger will execute the function sendEmails() every minute once it is set up by following these steps:

NOTE: As a workaround for not being able to use everyMinutes(2), a method is suggested where the function runs every minute and checks a certain column to determine if an email needs to be sent.


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

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

In order to optimize the process, replace the forEach() loop with a simple for loop that breaks out once the first email is marked for sending or has been sent successfully.


// Iterate through the rows of data and break when one email is sent
for (i = 0; i < obj.length; i++) {
    var row = obj[i];
    // Mark emails as "To Send" if email_sent cell is blank. Only send emails if email_sent cell is "To Send" and visible.
    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);

            GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {
                htmlBody: msgObj.html,
                attachments: emailTemplate.attachments,
                inlineImages: emailTemplate.inlineImages
            });

            out.push([new Date()]); // Record email sent date
        } catch(e) {
            out.push([e.message]); // Record error message
        }
        break;
    } else {
        out.push([row[EMAIL_SENT_COL]]);
    }
}

Once all rows are processed, delete the trigger to complete the task:


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

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

Answer 2

Solution:

To automate the task of sending emails periodically, you can create an Installable Trigger in Google Apps Script. This trigger will execute the function sendEmails() every minute once it is set up by following these steps:

NOTE: As a workaround for not being able to use everyMinutes(2), a method is suggested where the function runs every minute and checks a certain column to determine if an email needs to be sent.


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

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

In order to optimize the process, replace the forEach() loop with a simple for loop that breaks out once the first email is marked for sending or has been sent successfully.


// Iterate through the rows of data and break when one email is sent
for (i = 0; i < obj.length; i++) {
    var row = obj[i];
    // Mark emails as "To Send" if email_sent cell is blank. Only send emails if email_sent cell is "To Send" and visible.
    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);

            GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {
                htmlBody: msgObj.html,
                attachments: emailTemplate.attachments,
                inlineImages: emailTemplate.inlineImages
            });

            out.push([new Date()]); // Record email sent date
        } catch(e) {
            out.push([e.message]); // Record error message
        }
        break;
    } else {
        out.push([row[EMAIL_SENT_COL]]);
    }
}

Once all rows are processed, delete the trigger to complete the task:


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

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

In my Google Sheets script, I am looking to introduce a 2-minute delay within the forEach loop for sending emails. Unfortunately, I have not had success

Answer №1

Solution:

Similar questions

How can I dynamically display Material-UI's <MenuItem/> within a <DropDownMenu/> using ReactJS?

Transmitting a Custom JS Object via Vue Router

Selenium throws an error message stating 'NoSuchAlertError: no alert is currently opened'

`Creating the perfect bar``

Bidirectional data connection in a Meteor application

Is it possible to repeat this action by swiping to the left?

Using sinon.js version 1.10, jQuery version 2.1, and making synchronous requests

Selecting meteor.js user logout functionality

Troubleshooting: How can I ensure my custom scrollbar updates when jQuery niceselect expands?

When the page is scrolled, trigger a click event on the next button in

InvalidSelectorError: The specified selector is not valid //button[contains(., 'buttonText')] while running JavaScript code

fade in and out twice (for beginners)

Tips on displaying a selected choice | Utilizing Material UI Autocomplete

The Angular bootstrap popover vanishes as soon as the mouse hovers over it

Guide to incorporating a dynamic object within another object in JavaScript

Utilizing Props in Vue.js to Access Data for v-model

What is the best way to compare two strings without considering their capitalization?

When trying to inject HTML into AngularJS elements, the data binding may not function

Is there a way to retrieve the response body in Express framework?

Improve performance by debouncing computed properties and getters in Vue