Using Google Docs text in Apps Script

In the previous post, we looked at how to scan a Google Drive folder in order to collect the names of its files, and use them to populate a dropdown in Google Sheet. We will now see how to access the text inside these Google Docs in order to use them as email templates in our mail-merge solution.

Currently, the Google Docs are empty and only have unique names for the dropdown. We need to add the texts for the email subject line and body in each document. Here's what my "Follow-up" template looks like:

The document contains three areas: one for the subject line, the next for an HTML body, and the third for plain-text body. We will pass both versions of the body to "GmailApp.senEmail()", so that the recipient's email system can render the right version for them. All three areas include our placeholders wrapped in double curly brackets, as we saw in the first post. We will still personalize each message by replacing these placeholders with the recipient's data.

One thing to be careful about: Google Docs tends to replace a simple quotes character with a fancier one, which isn't part of a valid HTML. This may result in broken CSS, so pay attention to that.

I've decided to place the texts inside three tables each made of a single column and a single row. This gives me a nice border for the area and, as we will see below, this will make fetching the text in Apps Script super easy. But you can arrange your three areas however you want.

We need to change our "sendEmail()" function to take advantage of the Google Docs. Here's the revised code:

function sendEmails() {
  // Inform user that automation is starting
  notifyUser_('Preparing emails...');
  // Get the name of the Google Docs file from the dropdown cell
  const templateName = g.ss
    .getSheetByName(g.adminSheetName)
    .getRange(g.dropdownRange)
    .getValue();
  // Check that the name isn't empty  
  if (templateName.toString().trim().length === 0) {
    notifyUser_('Select an email template and click SEND EMAILS');
    return;
  }
  // Construct a query string that looks for a Google Docs file 
  // with the given name inside the templates folder
  const query = `"${g.templatesFolderId}" in parents and 
    title = "${templateName}"`;
  // Get the template file corresponding with the given name
  const file = DriveApp.searchFiles(query).next();
  // Open the document using the file's ID
  const doc = DocumentApp.openById(file.getId());
  // Get the document's body
  const body = doc.getBody();
  // Get the list of tables inside the body
  const tables = body.getTables();
  // Assign text from each table to the global variable's keys
  [g.subject, g.htmlBody, g.body] = tables.map((table) => table.getText());
  // Get the recipients' data
  const sh = g.ss.getSheetByName(g.dataSheetName);
  let [headers, ...data] = sh.getDataRange().getValues();
  data = data.filter((row) => row[0].toString().trim().length > 0);
  // Create and send a personalized message to each recipient
  try {
    data.forEach((row) => {
      // Copy the subject, HTML body and text body
      let subject = g.subject;
      let htmlBody = g.htmlBody;
      let body = g.body;
      let email;
      headers.forEach((header, i) => {
        // Personalize the message components
        // using the recipient's data
        const re = new RegExp(`{{${header}}}`, 'g');
        subject = subject.replace(re, row[i]);
        body = body.replace(re, row[i]);
        htmlBody = htmlBody.replace(re, row[i]);
        if (header === 'email') {
          email = row[i];
        }
      });
      // HTML body needs to be passed inside an options
      // object when using GmailApp.sendEmail
      let options = {};
      if (htmlBody) {
        options.htmlBody = htmlBody;
      }
      GmailApp.sendEmail(email, subject, body, options);
    });
    notifyUser_('Mail merge completed');
  } catch (err) {
    notifyUser_(err.message);
    return;
  }
}

We can see how easy it is to extract text from Google Docs: once the user makes a template selection using the dropdown, the template name is stored in the cell. We simply use that name to look for a file with such name inside our templates folder. We open the file as a Google Doc and then access the tables inside its body. We extract the text from each table and assign it to the keys inside the global object. You will notice that the order of the keys "[g.subject, g.htmlBody, g.body]" must match the order of the tables in the Google Docs.

Once we have the template texts, we iterate over the list of recipients, duplicating the texts, and populate the placeholders with the recipient's data.

Notice how we've revised the "GmailApp.sendEmail()" to accept an options object that contains an "htmlBody" data.

Our mail-merge solution now supports multiple templates. We can store texts of different campaigns in different files and easily access them via the dropdown menu when sending out the emails.