Mail Merge Time-Based Trigger not working

[Damian Burgos]

Hello Everyone,

I have a mail script that works fine when using the dropdown menu to execute, but I am trying to setup an hourly trigger, which I have but when it runs it says "completed" but nothing really happens... Here is my code. 

 

/**

 * @OnlyCurrentDoc

*/

 

/**

 * Change these to match the column names you are using for email

 * recipient addresses and email sent column.

*/

const RECIPIENT_COL  = "Recipient";

const EMAIL_SENT_COL = "UCC DATE";

 

/**

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

 */

 

/**

 * Sends emails from sheet data.

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

 * @param {Sheet} sheet to read data from

*/

function sendEmails(subjectLine, sheet=SpreadsheetApp.getActiveSheet()) {

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

  if (!subjectLine){

    subjectLine = "Works Check - {{STOCK #}}"                    

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

    // If no subject line, finishes up

    return;

    }

  }

 

  // 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();

  // Assumes row 1 contains our column headings

  const heads = data.shift();

 

  // Gets the index of the column named 'Email Status' (Assumes header names are unique)

  // @see http://ramblings.mcpher.com/Home/excelquirks/gooscript/arrayfunctions

  const emailSentColIdx = heads.indexOf(EMAIL_SENT_COL);

 

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

  const obj = data.map(r => (heads.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){

    // Only sends emails if email_sent cell is blank and not hidden by a 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: 'a....@email.com',

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

          //from: 'sen...@gmail.com',

           name: 'PreOwned Department',

           replyTo: 'PreO...@dealership.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 {

      out.push([row[EMAIL_SENT_COL]]);

    }

  });

 

  // Updates the sheet with new data

  sheet.getRange(2, emailSentColIdx+1, out.length).setValues(out);

 

  /**

   * 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");

    }

    /**

     * 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');

  };

}

[Andrew Apell]

I noticed that you are using "getActiveSheet()" method. As someone on this forum (in another thread once said) when you run the code manually, this method would make sense.

However, when you set the trigger to run, "active sheet" no longer makes sense to the script because there is, in fact, no sheet open and active at that point.

You, therefore, need to find another way to identify the sheet and the range you are targeting.

My current solution for such a scenario is to save the unique id of the sheet inside the Properties Service using the "getSheetId()" method at the point of creating the trigger, then reference it later on as and when I need it.

Try that and let us know whether or not it works.

[Damian Burgos]

Hi Andrew,

That is a great point, I got my 9-digit sheet ID, am I putting it right below in the blue highlighted text?

function sendEmails(subjectLine, sheet=SpreadsheetApp.getActiveSheet(123456789)) {

[Andrew Apell]

No, that is not how you use it.

You use it like this: SpreadsheetApp.getActiveSheet().getSheetId()

Then you use it to read and write from that specific spreadsheet.

You are basically simply telling Apps Script to look for a spreadsheet with a specific ID and then do the other stuff in your code to it.

This is a replacement for using SpreadsheetApp.getActiveSheet().getActiveSheet() which reads and writes from the spreadsheet that is active at that point i.e. the one the user is using.

[Damian Burgos]

This is what I see as an error.

--
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/8595869c-2aae-4020-b65c-409cfeb9d210n%40googlegroups.com.