Send bulk emails with personalised images in G Suite using Google Apps Script

Earlier in the year I managed to get a bulk emailing script published in the G Suite Developer Solutions Gallery. In this post I want to show you how you can extend this script so that you can send personalised images using Gmail, Sheets and Slides.

Before you get too excited about the potential of this script one important note is that this script is very quota heavy and to produce each card takes just over 8 seconds so if you are on a consumer account with a 6 minute runtime limit you’ll only be able to send out 40 emails in one execution.

Try it

  1. Create a copy of the modified Gmail/Sheets Mail Merge spreadsheet and also create a copy of the Google Slides card template.
  2. Update the Recipients column with email addresses you would like to use in the mail merge
  3. Copy/paste all the content from this Google Doc as a draft message in your Gmail account. When the script runs it will replace the first image with your customised Google Slide image and any markers like {{First name}}, which correspond to column names, will be replaced with data from the copied spreadsheet.
  4. In the copied spreadsheet, click on custom menu item Mail Merge > Send Emails.
  5. A dialog box will appear and tell you that the script requires authorization. Read the authorization notice and continue.
  6. When prompted enter or copy/paste the subject line used in your draft Gmail message and click OK.
  7. When prompted enter or copy/paste the url of the copied Google Slides card template and click OK.
  8. The Email Sent column will update with the message status.

Next steps

Additional columns can be added to the spreadsheet with other data you would like to use. Using the {{}} annotation and including your column name as part of your Gmail draft will allow you to include other data from your spreadsheet. If you change the name of the Recipient or Email Sent columns this will need to be updated by opening Tools > Script Editor. The same {{}} annotation is also used in the Google Slide Template allowing you to include additional data to your image before it is included in the email.

How it works

Like the original mail merge solution data from the Google Sheet is used to replace the {{}} holders in the Gmail draft and a similar technique is used to .replaceAllText() in Google Slides. When the text has been updated in the Google Slide the advanced Slides service is used to get an image of the modified slide which is then used as an inline image in Gmail. Let’s look at some of the code in more detail.

Replacing text in a slide and getting as an image

Initially I was encountering an error when trying to get the image after duplicating the slide and updating the text. The issue was that the new slide isn’t created in the deck until the app script finishes execution or .saveAndClose() is called (read about a slide lifecycle). Having open the slide deck each time takes about 7 seconds which when you are doing each row adds up. An alternative approach might be to create all the custom slides, store the objectId and then get the images. I’ll let you play with this one…

/**
 * Fill in slides and get the new slide as an image
 * @param {string} slides_id presentation id
 * @param {Object} row of data
 * @return {Blob} slide image
*/
function fillInSlideFromObject_(slides_id, row){
  var slides = SlidesApp.openById(slides_id);
  var templateSlide = slides.getSlides()[0]; // assume first slide is our template
  var newSlide = templateSlide.duplicate(); // copy the first slide
  // iterate across the data and replace any placeholder with our data
  for (var r in row) {
    newSlide.replaceAllText('{{'+r+'}}', row[r]);
  }
  // force changes to be saved
  // https://developers.google.com/apps-script/guides/slides/lifecycle#modifying_a_presentation
  slides.saveAndClose();
  // get slide image as a thumbnail (only PNG currently supported)
  var thumbnail = Slides.Presentations.Pages.getThumbnail(slides_id, newSlide.getObjectId(), {
    'thumbnailProperties.thumbnailSize': 'MEDIUM'
  });
  // from returned contentUrl fetch the image as a blob (contentUrl's only last 30 minutes)
  var blob = UrlFetchApp.fetch(thumbnail.contentUrl).getBlob();
  return blob;
}

Inline images in Gmail drafts

In Gmail drafts if images are used it appears that Google prepares these by hosting the images on their own servers (this was not previously the case requiring some extreme workarounds – referenced issue ticket). When updating the draft with our new image we need to inline the slide image data we’ve just created. In this script I’ve assumed the first image is the one we are replacing, but you can change this my changing the index number in imgTags[0].

/**
 Fills a Gmail draft with row data replacing first draft image with slide image
 Modified from: https://issuetracker.google.com/issues/36760925#comment8
 @param {Message} message object from GmailApp
 @param {Blob} slideImageBlob of replacement image
 @param {Object} row of data
 @return {Object} email message
 */
function fillInGmailDraftFromObject_(message, slideImageBlob, row) {
  var body = message.getBody();
  var plainBody = message.getPlainBody();
  var subjectLine = message.getSubject();
  var attachments = message.getAttachments(); //preserving attachments from draft
  var imgTags = body.match(/]+>/g) || []; // all image tags, embedded or by url
  // Assuming the first image is the one to replace
  var imgTagNew = imgTags[0].replace(/src="[^\"]+\"/, "src=\"cid:slideImage\""); // replace the long-source with just the cid
  body = body.replace(imgTags[0], imgTagNew); // update embedded image tag in message body
  // replacement of text placeholders
  for (var r in row) {
    var val = row[r];
    body = body.replace('{{' + r + '}}', val);
    plainBody = plainBody.replace('{{' + r + '}}', val);
    subjectLine = subjectLine.replace('{{' + r + '}}', val);
  }
  return {
    subject: subjectLine,
    text: plainBody,
    options: {
      htmlBody: body,
      inlineImages: {
        slideImage: slideImageBlob
      },
      attachments: attachments
    }
  }
}

Using emoji in placeholders

In the example data I wanted to include some presents under the tree for the number of posts (column D) from each of the AppsScriptPulse contributors. Using =REPT("🎁 ",D2) you can repeat the ‘wrapped gift’ unicode character which can also be displayed in a Google Slides text placeholder.

Wrapping up

Hopefully you can use this example code for your own jumping off point for creative things you can do with Google Slides, Sheets and Gmail. You can find this and other examples from the Google Apps Script community on pulse.appscript.info. Happy Scripting in 2020!

chevron_left
chevron_right
css.php