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.

[Andrew Apell]

The correct methods are either SpreadsheetApp.getActiveSheet().getSheetId() OR SpreadsheetApp.getActiveSpreadsheet
().getSheetId() not  SpreadsheetApp.getSheetId()

I encourage you to look at the documentation in order to learn how to use either method correctly.

[Damian Burgos]

Hi Andrew,

I did look at the documentation & it does not help at all..This is the error I get now.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/c5a9f35c-489b-496e-a25d-ebb007020b16n%40googlegroups.com.

[Andrew Apell]

I have just realised that we missed an important step.

After getting the sheet ID, you are supposed to read data from it using this method:

SpreadsheetApp.openById(<unique sheet ID>).getRange(<some custom range>).getValues();

You can play around with this method depending on what you would like to do.


I think this will fix your issue.

[Damian Burgos]

Hi Everyone,

Just following up on this.

[Damian Burgos]

Hi Andrew,

This is the error I am getting, If you could take my script and put whatever code I need to accomplish what I need to do, that would be great. If I have to compensate you for your time, I will do that. I am not a programmer, I work at a car dealership and simply trying to get this going. 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/fc2477f4-4379-404f-acd4-70e93c407941n%40googlegroups.com.

[Andrew Apell]

In this method SpreadsheetApp.openById(<unique sheet ID>).getRange(<some custom range>).getValues(), you were supposed to replace <unique sheet ID> and <some custom range> with actual values from your spreadsheet.

Those words were merely placeholders.

[Damian Burgos]

I am a bit confused on "custom range" ? If you look at my original script there is no "specific" range so I am not sure what would be placed here.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/fe905513-8ee2-45ad-8276-ef986396ed3bn%40googlegroups.com.

[Andrew Apell]

Your code has this variable declaration  sheet=SpreadsheetApp.getActiveSheet() but, because you are running a time-based trigger, the getActiveSheet() method cannot really work.

This means that, in order to read that data, you are going to specify a range from which the function sendEmails will always read data from whenever the trigger runs.

Using the function I sent earlier, "custom range" would be something like "A1:B200" and, of course, the "unique sheet ID" would be the value you got from running the  getSheetId() method.

By doing this, you ensure that, every time the trigger runs, it reads data from the range you specified and then works on it as specified in your code.

If you modify your spreadsheet data and, somehow, the data in "A1:B200" is not what the function sendEmails expects to find, you will get an error.

[Damian Burgos]

Hi Andrew,

I am not sure what I am doing wrong.. The reason why I choose Q:Q is because that column is where the "SENT DATE" get's written when the script has been completed.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/617a42f9-a15a-4fcc-bb5b-d61c389afe0fn%40googlegroups.com.

[Andrew Apell]

Your screenshot is showing errors in sections of your code because you did not remove the <> symbols; they were part of the placeholders. Moreover, the ID you used would still cause another error since that is not an actual spreadsheet ID.

Here are highlights of what you need to do:

1. SpreadsheetApp.getActiveSpreadsheet().getId(): This code gets the unique ID of the spreadsheet. As we have already noted, when a trigger is running, there is no active sheet for it to access. However, at the point of creating the trigger, there is an active sheet, and that is the spreadsheet the user is accessing at that point. This means you could place this code in a button that creates this trigger on the client side so that, when a user clicks that button, the trigger is created. You should, at the same time, store that ID in the Properties Service for use in subsequent steps.
2. SpreadsheetApp.openById(PropertiesService.getUserProperties().getProperty("yourSheetId")).getDataRange(): In this step, I have decided to use the getDataRange() method since it was in your original example. So, having stored the sheet ID in properties service, we can read it into this method in order to access the specific spreadsheet that is holding your data. The rest of the code remains the same as what you posted in the beginning.

Be sure to read about the Properties Service in the official documentation in case you do not know how to use it.

PS: There was an error in the ID retrieval code I posted earlier. This post has the correct method.

[Damian Burgos]

Sheet ID zero is what I am getting.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a0abb0b7-39f9-4bb0-9d27-e50187b48e77n%40googlegroups.com.

[Andrew Apell]

I'm not sure what you are doing here but there is no need to see the ID yourself. As you saw in my code, I simply store and retrieve the ID but at no time do I ever need to see it.

By the way, is your script bound to a spreadsheet or standalone? Does it have a client-side interface?

[Damian Burgos]

You told me the Sheet ID is not zero so I am showing a screenshot that it is zero and here is the URL showing zero as well and the script is attached to the sheet.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/66e2e845-ba2a-4cb6-93f2-5554f56e0252n%40googlegroups.com.

[Andrew Apell]

This is actually helpful:

When you run the getId() method, it targets the portion that you have highlighted red NOT the one you have highlighted yellow.

The yellow part is not important for the purpose of your script. Once you have the red part, you can access that spreadsheet data freely.

The purpose of writing it the way I suggested is so that you can access that ID dynamically.