MailMerge - getGmailTemplateFromDrafts fails when called on trigger

[Josh Goldstein]

Hi apps script community,

I'm using a copy of the "Mail Merge" script by Martin Hawksey to automate sending emails to recipients in a spreadsheet. 

When I run the following script manually in the code editor, it works fine, and it's able to find the Gmail draft I specify.

However, when this is called on a daily trigger, I get an email that the execution failed with the "Oops - can't find Gmail draft" error seen in the try/catch. 

I don't understand why the draft is found when run manually, but not when called via trigger. Any thoughts? I've verified that the subject_line parameter it's looking for is valid, and matches the subject line of an existing draft in the Gmail account (which is why it runs without an error when executed manually).

Thanks in advance for any ideas.

  /**

   * Get a Gmail draft message by matching the subject line.

   * @param {string} subject_line to search for draft message

   * @return {object} containing the subject, plain and html message body and attachments

  **/

  function getGmailTemplateFromDrafts_(subject_line){

    try {

      // get drafts

      const drafts = GmailApp.getDrafts();

      // filter the drafts that match subject line

      const draft = drafts.filter(subjectFilter_(subject_line))[0];

      // get the message object

      const msg = draft.getMessage();

      // Handles inline images and attachments so they can be included in the merge

      // Based on https://stackoverflow.com/a/65813881/1027723

      // Gets all attachments and inline image attachments

      const allInlineImages = draft.getMessage().getAttachments({includeInlineImages: true,includeAttachments:false});

      const attachments = draft.getMessage().getAttachments({includeInlineImages: false});

      const htmlBody = msg.getBody(); 

      // Creates an inline image object with the image name as key 

      // (can't rely on image index as array based on insert order)

      const img_obj = allInlineImages.reduce((obj, i) => (obj[i.getName()] = i, obj) ,{});

      //Regexp searches for all img string positions with cid

      const imgexp = RegExp('<img.*?src="cid:(.*?)".*?alt="(.*?)"[^\>]+>', 'g');

      const matches = [...htmlBody.matchAll(imgexp)];

      //Initiates the allInlineImages object

      const inlineImagesObj = {};

      // built an inlineImagesObj from inline image matches

      matches.forEach(match => inlineImagesObj[match[1]] = img_obj[match[2]]);

      return {message: {subject: subject_line, text: msg.getPlainBody(), html:htmlBody}, 

              attachments: attachments, inlineImages: inlineImagesObj };

    } catch(e) {

      throw new Error("Oops - can't find Gmail draft");

    }

[Martin Hawksey]

Hi there,

When run manually there is a user dialog to enter the draft subject line. It is possible to use this mail merge solution on a trigger with a couple of lines of extra code in the script editor:  

function sendScheduledEmails() { const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet2'); // <-- Insert sheet name to use data from const subjectLine = 'Your personal discount code {{Discount amount}}'; // <-- Insert email draft subject line sendEmails(subjectLine, sheet); }

With this added you can then setup your required trigger running the sendScheduledEmails() function.

If you have any questions about this feel free to get back to me

Best

Martin

[Josh Goldstein]

Thanks, Martin.

Nothing seems too different between that code than what was already in the sample (which I have renamed).

I did find another forum post where the OP noted that getActive() might have been getting the wrong spreadsheet, so I replaced that with openById(), and it still works when running manually (and still fails by trigger):

----------

const EMAIL_SUBJECT_LINE = "It's Time to Renew Your Membership!";

const SPREADSHEET_ID = '13lo8rinFI4d0G-vQ5VYZBYQ1O87OpkLrTvUENPDl75w';

function sendMembershipRenewalEmails(subjectLine=EMAIL_SUBJECT_LINE, sheet=SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME)) {

  // option to skip browser prompt if you want to use this code in other projects

  if (!subjectLine){

    subjectLine = Browser.inputBox("Mail Merge", 

                                      "Type or copy/paste the subject line of the Gmail " +

                                      "draft message you would like to mail merge with:",

                                      Browser.Buttons.OK_CANCEL);

                                      

    if (subjectLine === "cancel" || subjectLine == ""){ 

    // If no subject line, finishes up

    return;

    }

  }

/* skipping a bunch of code... */

  

  // Gets the draft Gmail message to use as a template

  const emailTemplate = getGmailTemplateFromDrafts_(subjectLine);

}

----------

It's still failing in the getGmailTemplateFromDrafts function, with a message that it failed to find the draft (only when invoked via trigger).

Can you point out what's different conceptually between your code and mine?

Josh

[cbmserv...@gmail.com]

Josh,

 

Perhaps it may be good for you to publish here all the code that you are using so that we can see what is going on.

 

Just from the code snippet below, the code would not run in an installed trigger because you are using:

 

Browser.inputBox("Mail Merge", 

 

This would stop the trigger from running just because the code is there.

 

Paste in your full code and we will be able to provide you guidance.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/e5713d08-a617-434e-96c6-5832afec8e18n%40googlegroups.com.

[Martin Hawksey]

Hi Josh,

As George had noted the Browser.input() will cause a trigger to fail. Did you try adding the snippet I provided adding your sheet name and draft subject line?

function sendScheduledEmails() { const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet2'); // <-- Insert sheet name to use data from const subjectLine = 'Your personal discount code {{Discount amount}}'; // <-- Insert email draft subject line sendEmails(subjectLine, sheet); }

If it doesn't work let us know what any error messages you get

Thanks

Martin

[Josh Goldstein]

Thank you both. 

I removed Browser.inputBox() because I'll never need that - this will always run from a trigger.

But this is still failing when run from the trigger with the error: "Oops - can't find Gmail draft."

Here's my full code:

----------

/**

 * Define the relevant sheet, columns, and other data required to run the automatic renewal email script

**/

const SPREADSHEET_ID = '13lo8rinFI4d0G-vQ5VYZBYQ1O87OpkLrTvUENPDl75w';

const SHEET_NAME = "TestSheet"; /** the sheet from which all data is read - use "TestSheet" for debugging */

const NUMBER_OF_HEADER_ROWS = 2; /** specify how many rows from the top of the spreadsheet consist of header data (for column searching) */

const RENEWAL_DATE_COL = 'NEXT RENEWAL DATE'; /** the column name from which the renewal date is read and compared to the current date */

const RECIPIENT_COL  = 'EMAIL'; /** the column name from which the email address will be read from */

const EMAIL_SENT_COL = 'AUTOMATIC RENEWAL EMAIL SENT'; /** the column name in which to record the successful email dispatch date */

const EMAIL_SUBJECT_LINE = "Historic Englewood - It's Time to Renew Your Membership!"; /** only use an email draft with this subject line */

const DAYS_BEFORE_RENEWAL = 30; /** the number of days before the renewal date that an email should be sent */

 

/** 

 * Creates the menu item "Mail Merge" for user to run scripts on drop-down.

 * Note: This script is tied to a trigger, so running manually is not necessary except for debugging

 **/

function onOpen() {

  const ui = SpreadsheetApp.getUi();

  ui.createMenu('Mail Merge')

      .addItem('Send Emails', 'sendEmails')

      .addToUi();

}

/* get the column ID from a header name, searching through the given row numbers when headers exist in multiple rows */

function getColumnHeaderIndexByName(data /* from sheet.getDataRange() */, columnName, numberOfRowsToSearch) {

  let index = -1;

  for (var i = 0; i < numberOfRowsToSearch; i++)

  {

    let headers = data[i];

    index = headers.indexOf(columnName);

    // break out of the loop if we found the index

    if (index != -1)

    {

      break;

    }

  }

  return index;

}

 

/**

 * Sends renewal reminder emails from Sheets data

 * @param {string} subjectLine (optional) for the email draft message

 * @param {Sheet} sheet to read data from

**/

function sendMembershipRenewalEmails(subjectLine=EMAIL_SUBJECT_LINE, sheet=SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME)) {

  // option to skip browser prompt if you want to use this code in other projects

                                  

    if (subjectLine === "cancel" || subjectLine == ""){ 

    // If no subject line, finishes up

    return;

  }

  /* convert dates to milliseconds format for arithmetic operations */

  // today's date in milliseconds since 1970

  const todayInMilliseconds = new Date().getTime();

  // one day in milliseconds

  const oneDayInMilliseconds = 24 * 60 * 60 * 1000;

  // one year in milliseconds

  const oneYearInMilliseconds = oneDayInMilliseconds * 365;

  // number of days before renewal in milliseconds

  const daysBeforeRenewalInMilliseconds = DAYS_BEFORE_RENEWAL * oneDayInMilliseconds;

  

  // Gets the draft Gmail message to use as a template

  const emailTemplate = getGmailTemplateFromDrafts_(subjectLine);

  

  // Gets the data from the passed sheet

  const dataRange = sheet.getDataRange();

  // Fetches displayed values for each row in the Range HT Andrew Roberts 

  // https://mashe.hawksey.info/2020/04/a-bulk-email-mail-merge-with-gmail-and-google-sheets-solution-evolution-using-v8/#comment-187490

  // @see https://developers.google.com/apps-script/reference/spreadsheet/range#getdisplayvalues

  const data = dataRange.getDisplayValues();

  /* identify the column header index numbers using the given names, searching through the number of header rows */

  const RECIPIENT_COL_INDEX = getColumnHeaderIndexByName(data, RECIPIENT_COL, NUMBER_OF_HEADER_ROWS);

  const RENEWAL_DATE_COL_INDEX = getColumnHeaderIndexByName(data, RENEWAL_DATE_COL, NUMBER_OF_HEADER_ROWS);

  const EMAIL_SENT_COL_INDEX = getColumnHeaderIndexByName(data, EMAIL_SENT_COL, NUMBER_OF_HEADER_ROWS);

  // the original script uses only the first row of headers here

  row1Headers = data[0];

  // remove the rows of headers

  data.splice(0, NUMBER_OF_HEADER_ROWS);

  

  // Converts 2d array into an object array

  // See https://stackoverflow.com/a/22917499/1027723

  // For a pretty version, see https://mashe.hawksey.info/?p=17869/#comment-184945

  // Note: Since this script now supports multiple column headers, the object is no longer used - instead, the column is found via new getColumnHeaderIndexByName function

  const obj = data.map(r => (row1Headers.reduce((o, k, i) => (o[k] = r[i] || '', o), {})));

  // Creates an array to record sent emails

  const out = [];

  // Loops through all the rows of data

  obj.forEach(function(row, rowIdx) {

    // the renewal date of this cell in milliseconds

    const thisRenewalDateInMilliseconds = new Date(data[rowIdx][RENEWAL_DATE_COL_INDEX]).getTime();

    // only proceed if the renewal date is valid

    if (isNaN(thisRenewalDateInMilliseconds)) {

      out.push([data[rowIdx][EMAIL_SENT_COL]]);

      console.log("Error: The renewal date was not valid.");

      return;

    }

    // Only send an email if we're less than the specified number of days before the renewal date and an email hasn't been sent already

    let bIsRenewalDateWithinRange = thisRenewalDateInMilliseconds - todayInMilliseconds < daysBeforeRenewalInMilliseconds;

    let lastRenewalDateInMilliseconds = new Date(data[rowIdx][EMAIL_SENT_COL_INDEX]).getTime();

    let bIsLastRenewalEmailDateValid = !isNaN(lastRenewalDateInMilliseconds);

    let bHasRenewalEmailBeenSentInCurrentYear = bIsLastRenewalEmailDateValid && todayInMilliseconds - lastRenewalDateInMilliseconds <= oneYearInMilliseconds;

    let bSendRenewalEmail = bIsRenewalDateWithinRange && !bHasRenewalEmailBeenSentInCurrentYear;

    // Only sends emails if we're the specified number of days before the renewal date and an email hasn't been sent already

    if (bSendRenewalEmail){

      console.log("Attempting to send membership renewal email to " + data[rowIdx][RECIPIENT_COL_INDEX] + " for membership renewal date of " + data[rowIdx][RENEWAL_DATE_COL_INDEX] + " which is less than " + DAYS_BEFORE_RENEWAL + " days in the future, AND an email has not yet been dispatched this year.");

      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(data[rowIdx][RECIPIENT_COL_INDEX], msgObj.subject, msgObj.text, {

          htmlBody: msgObj.html,

          // bcc: 'a....@email.com',

          // cc: 'a....@email.com',

          // from: 'an.a...@email.com',

          name: 'Historic Englewood',

          // replyTo: 'a.r...@email.com',

          // 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

        });

        // Edits cell to record email sent date

        out.push([new Date()]);

      } catch(e) {

        // modify cell to record error

        out.push([e.message]);

      }

    } else {

      console.log("No need to email " + data[rowIdx][RECIPIENT_COL_INDEX] + " since membership renewal date of " + data[rowIdx][RENEWAL_DATE_COL_INDEX] + " is more than " + DAYS_BEFORE_RENEWAL + " days in the future, OR an email has already been sent this year.");

      out.push([data[rowIdx][EMAIL_SENT_COL_INDEX]]);

    }

  });

  

  // Updates the sheet with new data

  sheet.getRange(NUMBER_OF_HEADER_ROWS + 1, EMAIL_SENT_COL_INDEX + 1, out.length).setValues(out);

    /**

     * Filter draft objects with the matching subject linemessage by matching the subject line.

     * @param {string} subject_line to search for draft message

     * @return {object} GmailDraft object

    **/

    function subjectFilter_(subject_line){

      return function(element) {

        if (element.getMessage().getSubject() === subject_line) {

          return element;

        }

      }

    }

  }

  

  /**

   * Fill template string with data object

   * @see https://stackoverflow.com/a/378000/1027723

   * @param {string} template string containing {{}} markers which are replaced with data

   * @param {object} data object used to replace {{}} markers

   * @return {object} message replaced with data

  */

  function fillInTemplateFromObject_(template, data) {

    // We have two templates one for plain text and the html body

    // Stringifing the object means we can do a global replace

    let template_string = JSON.stringify(template);

    // Token replacement

    template_string = template_string.replace(/{{[^{}]+}}/g, key => {

      return escapeData_(data[key.replace(/[{}]+/g, "")] || "");

    });

    return  JSON.parse(template_string);

  }

  /**

   * Escape cell data to make JSON safe

   * @see https://stackoverflow.com/a/9204218/1027723

   * @param {string} str to escape JSON special characters from

   * @return {string} escaped string

  */

  function escapeData_(str) {

    return str

      .replace(/[\\]/g, '\\\\')

      .replace(/[\"]/g, '\\\"')

      .replace(/[\/]/g, '\\/')

      .replace(/[\b]/g, '\\b')

      .replace(/[\f]/g, '\\f')

      .replace(/[\n]/g, '\\n')

      .replace(/[\r]/g, '\\r')

      .replace(/[\t]/g, '\\t');

  };

}

----------

I'm not sure what would be special about Martin's code - I am specifying the sheet name and subject line already, and those are valid and defined. I am getting the spreadsheet by ID, but this was failing also with getActive().

Appreciate any further help in advance.

[cbmserv...@gmail.com]

Josh,

 

That error is from your function getGmailTemplateFromDrafts. If it fails for any reason, it gives that error. Not sure what the failure is because it depends on whether you have drafts defined in your gmail account and with the required subject_line that you seek.

 

If you want, remove the try/catch from that function and this way when it fails, it will give you the actual line of code that failed so you can better determine how to correct.

 

Here is the function without the try/catch sequence. Try it out and let us know. This really depends on your environment (what drafts you have defined or not) so we can not debug it without the specific errors.

 

 

 function getGmailTemplateFromDrafts_(subject_line){

 

      // get drafts

      const drafts = GmailApp.getDrafts();

      // filter the drafts that match subject line

      const draft = drafts.filter(subjectFilter_(subject_line))[0];

      // get the message object

      const msg = draft.getMessage();

 

      // Handles inline images and attachments so they can be included in the merge

      // Based on https://stackoverflow.com/a/65813881/1027723

      // Gets all attachments and inline image attachments

      const allInlineImages = draft.getMessage().getAttachments({includeInlineImages: true,includeAttachments:false});

      const attachments = draft.getMessage().getAttachments({includeInlineImages: false});

      const htmlBody = msg.getBody(); 

 

      // Creates an inline image object with the image name as key 

      // (can't rely on image index as array based on insert order)

      const img_obj = allInlineImages.reduce((obj, i) => (obj[i.getName()] = i, obj) ,{});

 

      //Regexp searches for all img string positions with cid

      const imgexp = RegExp('<img.*?src=cid:(.*?).*?alt="(.*?)"[^\>]+>', 'g');

      const matches = [...htmlBody.matchAll(imgexp)];

 

      //Initiates the allInlineImages object

      const inlineImagesObj = {};

      // built an inlineImagesObj from inline image matches

      matches.forEach(match => inlineImagesObj[match[1]] = img_obj[match[2]]);

 

      return {message: {subject: subject_line, text: msg.getPlainBody(), html:htmlBody}, 

              attachments: attachments, inlineImages: inlineImagesObj };

 

    /**

     * Filter draft objects with the matching subject linemessage by matching the subject line.

     * @param {string} subject_line to search for draft message

     * @return {object} GmailDraft object

    **/

    function subjectFilter_(subject_line){

      return function(element) {

        if (element.getMessage().getSubject() === subject_line) {

          return element;

        }

      }

    }

  }

 

 

 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/9f14192e-41b2-4d67-bce4-28d4609bfd41n%40googlegroups.com.

[Josh Goldstein]

Thanks.

I removed the try/catch, and the error is now:

TypeError: Cannot read property 'getMessage' of undefined

It doesn't tell me what line this error occurs on, but I suspect it's the first instance of getMessage(), which would indicate that indeed, the draft cannot be found:

      // get drafts

      const drafts = GmailApp.getDrafts();

      // filter the drafts that match subject line

      const draft = drafts.filter(subjectFilter_(subject_line))[0];

      // get the message object

      const msg = draft.getMessage();

I know that subject_line is valid, but I don't know what subjectFilter_ does.

It's so strange that this works when run manually but not via the trigger.

[cbmserv...@gmail.com]

So the error is what I thought it was, you have no drafts defined that match your filter criteria.

 

      const drafts = GmailApp.getDrafts();

      // filter the drafts that match subject line

      const draft = drafts.filter(subjectFilter_(subject_line))[0];

      // get the message object

      const msg = draft.getMessage();

 

 

Define some mail drafts in your gmail and the code should work. The subject line it is looking for in the filter is this:

 

"Historic Englewood - It's Time to Renew Your Membership!"

 

So your drafts need to have that subject line in it.

 

George

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a33bf4b9-f95b-4a06-96c5-0039a16fa514n%40googlegroups.com.

[Josh Goldstein]

But I do have a draft with that exact subject line. As I've said multiple times, this works as expected when run manually from the script editor, but only fails when run via trigger.

There is already, absolutely, a draft with that subject line in the Gmail account. If there wasn't a draft, it wouldn't work when I run the script manually.

The issue is that the draft seems to not be found when run via trigger and that's the part I'm trying to figure out.

[CBMServices Web]

Hi Josh,

Looked a little further at this error for you and it seems to be caused by the use of the Array methods. (Such as Filter, Index Of, etcc) Most are not supported during triggers. The code would have to be redone not to use them for it to work properly.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/f170e6b0-e793-488f-bbae-49abf7b15e20n%40googlegroups.com.

[Martin Hawksey]

Hi Josh,

Personally I've not encountered issues with array methods used in triggers but happy to be proved wrong. Perhaps it might be worth taking a step back and rather than modifying the getGmailTemplateFromDrafts function to test with a setup I've tested and not encountered any issues with? The way I've scheduled mail merge emails is:

1. Make a copy of the mail merge solution at https://developers.google.com/apps-script/samples/automations/mail-merge
2. Update the sheet with a test recipient email address 
3. In your copied version of the mail merge open the Script Editor (Extensions > Apps Script) and add the following lines of code (I inserted this at line 30 in my test):
   
   function sendScheduledEmails() {

       const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1'); // <-- Update with sheet name to use data from

       const subjectLine = 'Historic Englewood - It's Time to Renew Your Membership!'; // <-- Update with email draft subject line (to avoid errors copy/paste from the draft subject line)

       sendEmails(subjectLine, sheet);

   }
4. In the Script Editor from the left hand menu select 'Triggers' and click 'Add Trigger' selecting the 'sendScheduledEmails' function and setting a specific date/time in the future (useful to set the 'failure notification settings' to notify me immediately')
   
   
5. Click 'Save' which if running for the first time should prompt authorization for the script. Important: If running this from a @gmail.com account at the 'Google hasn’t verified this app' you need to click Advance and then click the 'Go to Gmail/Sheets mail merge (unsafe)' 
6. Click 'Save' on the trigger dialog 

A general note is when using this solution is everything is case sensitive (subject lines, column headings, replacement tokens all need to match exactly).

The modified copy of the Mail Merge example I tested with is here https://docs.google.com/spreadsheets/d/17thg6_FA8WGvr5bJobpJgZDEgezW29dVetikAreeE04/copy

A copy of the Gmail text I copied/pasted into Gmail is here https://docs.google.com/document/d/15p7YVZ78VF-B5ywF1rV5AHBIzIS6XvIdX1NVs4EpWf4/edit?usp=sharing

Let us know how you get on

Best

Martin 

[Josh Goldstein]

Hi Martin,

Thanks for your detailed instructions.

I ran your test, and indeed, there are no errors.

Any idea what could be different about my code that is causing the draft to not be found?

Josh

[Martin Hawksey]

Hi Josh,

Glad you got that version working.

Not entirely sure why your version wasn't working - trigger executions can be harder to debug and may have been a typo somewhere

Best

Martin

[cbmserv...@gmail.com]

Josh,

 

Your code was failing because of the array method used.

 

This code that you had was not succeeding as filter was not working as expected.

 

      const draft = drafts.filter(subjectFilter_(subject_line))[0];

      // get the message object

      const msg = draft.getMessage();

 

 

If you want to bypass that, try this code to replace it.:

 

      var msg = null;

      const drafts = GmailApp.getDrafts();

      // filter the drafts that match subject line

      for (var i = 0;i<drafts.length;i++)

      {

        msg = drafts[i].getMessage();

        if(msg.getSubject() == subject_line) 

        { 

          drafts = drafts[i]; 

          break;

        }

      }

      msg = draft.getMessage();

 

Code does the same thing but used a for loop to look for correct draft rather than a filter method.

 

From: google-apps-sc...@googlegroups.com <google-apps-sc...@googlegroups.com> On Behalf Of Josh Goldstein
Sent: March 29, 2022 9:25 PM
To: Google Apps Script Community <google-apps-sc...@googlegroups.com>
Subject: Re: [Apps-Script] Re: MailMerge - getGmailTemplateFromDrafts fails when called on trigger

 

Hi Martin,

 

Thanks for your detailed instructions.

 

I ran your test, and indeed, there are no errors.

 

Any idea what could be different about my code that is causing the draft to not be found?

 

Josh

On Thursday, March 24, 2022 at 4:16:59 AM UTC-6 m.ha...@gmail.com wrote:

Hi Josh,

 

Personally I've not encountered issues with array methods used in triggers but happy to be proved wrong. Perhaps it might be worth taking a step back and rather than modifying the getGmailTemplateFromDrafts function to test with a setup I've tested and not encountered any issues with? The way I've scheduled mail merge emails is:

1. Make a copy of the mail merge solution at https://developers.google.com/apps-script/samples/automations/mail-merge
2. Update the sheet with a test recipient email address 
3. In your copied version of the mail merge open the Script Editor (Extensions > Apps Script) and add the following lines of code (I inserted this at line 30 in my test):

function sendScheduledEmails() {

    const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1'); // <-- Update with sheet name to use data from

    const subjectLine = 'Historic Englewood - It's Time to Renew Your Membership!'; // <-- Update with email draft subject line (to avoid errors copy/paste from the draft subject line)

    sendEmails(subjectLine, sheet);

}

4. In the Script Editor from the left hand menu select 'Triggers' and click 'Add Trigger' selecting the 'sendScheduledEmails' function and setting a specific date/time in the future (useful to set the 'failure notification settings' to notify me immediately')
   

4. 

4. Click 'Save' which if running for the first time should prompt authorization for the script. Important: If running this from a @gmail.com account at the 'Google hasn’t verified this app' you need to click Advance and then click the 'Go to Gmail/Sheets mail merge (unsafe)' 
5. Click 'Save' on the trigger dialog 

A general note is when using this solution is everything is case sensitive (subject lines, column headings, replacement tokens all need to match exactly).

 

The modified copy of the Mail Merge example I tested with is here https://docs.google.com/spreadsheets/d/17thg6_FA8WGvr5bJobpJgZDEgezW29dVetikAreeE04/copy

 

A copy of the Gmail text I copied/pasted into Gmail is here https://docs.google.com/document/d/15p7YVZ78VF-B5ywF1rV5AHBIzIS6XvIdX1NVs4EpWf4/edit?usp=sharing

 

Let us know how you get on

 

Best

Martin 

On Thursday, 24 March 2022 at 02:56:40 UTC George wrote:

Hi Josh,

 

Looked a little further at this error for you and it seems to be caused by the use of the Array methods. (Such as Filter, Index Of, etcc) Most are not supported during triggers. The code would have to be redone not to use them for it to work properly.

 

On Tue., Mar. 22, 2022, 8:35 a.m. Josh Goldstein, <jdeango...@gmail.com> wrote:

But I do have a draft with that exact subject line. As I've said multiple times, this works as expected when run manually from the script editor, but only fails when run via trigger.

 

There is already, absolutely, a draft with that subject line in the Gmail account. If there wasn't a draft, it wouldn't work when I run the script manually.

 

The issue is that the draft seems to not be found when run via trigger and that's the part I'm trying to figure out.

 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a9215931-f740-4f49-8f4e-cc6f4e5c3685n%40googlegroups.com.

[Josh Goldstein]

Thanks, George! 

One thing that doesn't quite add up here is, Martin's original code also has the array.filter() method, and when I ran his example, I didn't get any errors. But I'm going to try your suggestion because I have no other leads.

I noticed a small typo in your new code, for anyone who might reference this:

drafts = drafts[i]; 

should be:

draft = drafts[i];

Anyway, I've got this working when running manually with the new code (no array.filter() method), and will see what happens tomorrow morning when the trigger runs!

[cbmserv...@gmail.com]

Thanks for catching the typo.

 

I do not understand why the array methods do not work while under trigger execution, yet they run fine during manual invocation. I ran across this several times in the past, but definitely inconsistenly. It may be a quirk of the Google Run-time system.

 

If someone opens an issue on this it would be great. But I suspect it would be a low priority compared to other problems they may be facing.

 

If someone else has an explanation for this, I would love to hear it. Besides the Filter method, I have seen this with the IndexOf method as well.

 

Thanks,

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/962987f0-1d3a-4208-9b74-081f7d1caf85n%40googlegroups.com.

[Josh Goldstein]

Unfortunately, the script continues to fail with the same error. It can't find the draft. But it finds it when I run the script manually.

How frustrating!

[cbmserv...@gmail.com]

I went ahead and swapped out another Array Method.. Tested it and it worked for me. Let me know what you see.

 

George

 

Here is the resulting code:

 

/**

 * Define the relevant sheet, columns, and other data required to run the automatic renewal email script

**/

const SPREADSHEET_ID = '18JKQMoMiqN6BgMeg24RdJUnTWHPu0teEXllb_b33fWQ';

const SHEET_NAME = "Testsheet"; /** the sheet from which all data is read - use "TestSheet" for debugging */

const NUMBER_OF_HEADER_ROWS = 2; /** specify how many rows from the top of the spreadsheet consist of header data (for column searching) */

const RENEWAL_DATE_COL = 'NEXT RENEWAL DATE'; /** the column name from which the renewal date is read and compared to the current date */

const RECIPIENT_COL  = 'EMAIL'; /** the column name from which the email address will be read from */

const EMAIL_SENT_COL = 'AUTOMATIC RENEWAL EMAIL SENT'; /** the column name in which to record the successful email dispatch date */

const EMAIL_SUBJECT_LINE = "Historic Englewood - It's Time to Renew Your Membership!"; /** only use an email draft with this subject line */

const DAYS_BEFORE_RENEWAL = 30; /** the number of days before the renewal date that an email should be sent */

 

/** 

 * Creates the menu item "Mail Merge" for user to run scripts on drop-down.

 * Note: This script is tied to a trigger, so running manually is not necessary except for debugging

 **/

function onOpen() {

  const ui = SpreadsheetApp.getUi();

  ui.createMenu('Mail Merge')

      .addItem('Send Emails', 'sendMembershipRenewalEmails')

      var msg = null;

      const drafts = GmailApp.getDrafts();

      // filter the drafts that match subject line

      for (var i = 0;i<drafts.length;i++)

      {

        msg = drafts[i].getMessage();

        if(msg.getSubject() == subject_line) { break;}

      }

      

      // Handles inline images and attachments so they can be included in the merge

      // Based on https://stackoverflow.com/a/65813881/1027723

      // Gets all attachments and inline image attachments

      const allInlineImages = msg.getAttachments({includeInlineImages: true,includeAttachments:false});

      const attachments = msg.getAttachments({includeInlineImages: false});

      const htmlBody = msg.getBody(); 

 

      // Creates an inline image object with the image name as key 

      // (can't rely on image index as array based on insert order)

      const img_obj = allInlineImages.reduce((obj, i) => (obj[i.getName()] = i, obj) ,{});

 

      //Regexp searches for all img string positions with cid

      const imgexp = RegExp('<img.*?src=cid:(.*?).*?alt="(.*?)"[^\>]+>', 'g');

      const matches = [...htmlBody.matchAll(imgexp)];

 

      //Initiates the allInlineImages object

      const inlineImagesObj = {};

      // built an inlineImagesObj from inline image matches

      matches.forEach(match => inlineImagesObj[match[1]] = img_obj[match[2]]);

 

      return {message: {subject: subject_line, text: msg.getPlainBody(), html:htmlBody}, 

              attachments: attachments, inlineImages: inlineImagesObj };

    } catch(e) {

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/0ed70951-a354-4011-99fe-489df7e072bdn%40googlegroups.com.

[Josh Goldstein]

Thanks, George.

Any chance you can point me to what array method you replaced, and where?

Josh

[cbmserv...@gmail.com]

Have you tried it and did it work for you?

 

I had initially removed the use of the function subjectFilter_ and then removed the complete use of the .filter method with a replacement to do a For loop and check draft title name instead and that seemed to have done the trick.

 

In some of my other triggers that I use, the usage of indexOf gave me trouble and I had to replace it with a specific loop to do the check instead. But it does not seem to cause problems in this application. Go figure the inconsistency..

 

Let me know what you got.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/f954952d-6667-4725-8f29-defe63f48d1an%40googlegroups.com.

[Josh Goldstein]

I didn't want to copy and paste everything and replace my code because my code has been slightly modified over the course of this discussion, so I was hoping to see exactly where you made a change.

I did a search in my code and I don't have any further usage of array.filter, and in fact, that subjectFilter_() function you mention doesn't appear to be used anywhere (this was copied from the original sample by Martin).

[cbmserv...@gmail.com]

You should be able to get all the changes just by swapping out function getGmailTemplateFromDrafts_ of what you have versus the code I put in place.

 

Let me know if you have any other difficulties. Feel free to send me a private email.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/48b2f43d-a914-4370-a2fd-dd93474bc720n%40googlegroups.com.

[Josh Goldstein]

Brilliant, thank you!

I replaced that function, and adjusted the trigger so I could test it quickly, and I see my error rate has gone down from 100% and I've verified the script is now being invoked by the trigger without failing. 

Amazing!

Since this code was copied from Martin's example, I still don't understand why his vanilla example can be run via trigger but my modified code can't. Oh well.

Thank you so much for your help!

Josh

[CBMServices Web]

Excellent. Glad its resolved.