Advanced Email Template Interaction with Google Apps Script

by

Creating dynamic and interactive email templates with Google Apps Script can significantly enhance your customer interaction and streamline your workflow. In this blog post, we’ll walk you through how to create email templates with buttons that interact with your Google Sheet or send customized responses directly to your customers.

Step 1: Set Up Your Google Apps Script Project

Create a new Google Apps Script project:

  1. Open Google Drive.
  2. Click + New > More > Google Apps Script.
  3. Rename your project to something meaningful, like “Customer Interaction System”.

Step 2: Create the HTML Email Template

In the script editor, create a new HTML file:

  1. Click on the “+” icon next to Files and select HTML.
  2. Name it EmailTemplate.html.

Add the following HTML code to EmailTemplate.html:

<!DOCTYPE html>
<html>
<body>
  <p>Dear #USER,</p>
  <p>Thank you for filling out our form. Please select one of the following options:</p>
  <p>
    <a href="https://script.google.com/macros/s/YOUR_SCRIPT_ID/exec?action=late&email=#EMAIL" target="_blank">Running 30 minutes late.</a><br>
    <a href="https://script.google.com/macros/s/YOUR_SCRIPT_ID/exec?action=ontime&email=#EMAIL" target="_blank">Will be there on Time.</a><br>
    <a href="https://script.google.com/macros/s/YOUR_SCRIPT_ID/exec?action=canceled&email=#EMAIL" target="_blank">Service Canceled, call me directly to reschedule.</a>
  </p>
  <p>Best regards,<br>#MYNAME</p>
</body>
</html>

Step 3: Write the Apps Script Code

Modify the sendHTMLtemp function to send an email using the HTML template:

function sendHTMLtemp() {
  const id = '1_CzyhBlVdw3rUWyFubnm9zzfIGsniMmDpcT6c4T8OPk'; // Replace with your Google Doc ID
  const url = 'https://docs.google.com/document/d/' + id + '/export?format=html';
  const param = {
    method: "get",
    headers: {
      "Authorization": "Bearer " + ScriptApp.getOAuthToken()
    },
    muteHttpExceptions: true
  };
  let html = UrlFetchApp.fetch(url, param).getContentText();
  html = html.replace(/#USER/g, 'Laurence');
  html = html.replace(/#EMAIL/g, 'laurence@example.com'); // Replace with dynamic email if needed
  html = html.replace(/#MYNAME/g, 'Svekis Teacher');
  const email = Session.getActiveUser().getEmail();
  const subject = 'HTML from DOC ' + id;
  const prop = {
    htmlBody: html
  };
  Logger.log(html);
  GmailApp.sendEmail(email, subject, '', prop);
}

Step 4: Create a Web App to Handle Button Clicks

Add a new script file for handling the button clicks:

function doGet(e) {
  const action = e.parameter.action;
  const email = e.parameter.email;
  
  switch (action) {
    case 'late':
      updateSheet('Running 30 minutes late.', email);
      sendResponseEmail(email, 'Running 30 minutes late.');
      break;
    case 'ontime':
      updateSheet('Will be there on Time.', email);
      sendResponseEmail(email, 'Will be there on Time.');
      break;
    case 'canceled':
      updateSheet('Service Canceled, call me directly to reschedule.', email);
      sendResponseEmail(email, 'Service Canceled, call me directly to reschedule.');
      break;
  }
  
  return HtmlService.createHtmlOutput("Your response has been recorded.");
}

function updateSheet(response, email) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Responses'); // Replace 'Responses' with your sheet name
  sheet.appendRow([new Date(), email, response]);
}

function sendResponseEmail(email, response) {
  const subject = 'Response Received';
  const body = `Dear Customer,\n\nWe have received your response: "${response}".\n\nThank you!`;
  GmailApp.sendEmail(email, subject, body);
}

Step 5: Deploy the Web App

  1. In the Apps Script editor, click on Deploy > New deployment.
  2. Choose Web app.
  3. Set the Execute as option to Me.
  4. Set the Who has access option to Anyone.
  5. Click Deploy and note the Web App URL. Replace YOUR_SCRIPT_ID in the EmailTemplate.html with your actual script ID from the URL.

Step 6: Test the System

  1. Run the sendHTMLtemp function to send an email to yourself.
  2. Click the links in the email to test the interaction with the Google Sheet and to send a response email.

Conclusion

By following these steps, you can create a powerful and interactive email notification system using Google Apps Script. This system allows users to respond via email, record their responses in a Google Sheet, or trigger custom email responses, making it ideal for managing customer interactions efficiently.

About the Author:

Laurence is an experienced developer and author specializing in Google Apps Script and educational content creation. He has written several books, including “Google Apps Script Sheets Custom Functions: Over 150 Apps Script Code Examples for Sheets” and “FrontEnd Code Playground: HTML, CSS, and JavaScript Exercises to Develop Your Skills”.

Follow for more tech tips and tutorials!