Automate Email Sending with Google Sheets and Apps Script StepbyStep Tutorial

Streamlining Email Campaigns with Google Apps Script and Google Sheets

The need for efficient, personalized communication is more prevalent than ever. In the digital age, automation tools are key to streamlining workflows, particularly in email marketing and mass communication. This blog post explores how to utilize Google Apps Script alongside Google Sheets to automate the process of sending customized emails.

Body:

• Overview of the Script: Briefly describe what the sendCustomEmails function does and its components, including createHTMLEmail.
• Setting Up Your Spreadsheet: Instructions on preparing your Google Sheet with email addresses, recipient names, and tracking email status.
• Creating the HTML Email Template: Guide on crafting a personalized email template using HTML.
• Running the Script: Steps to execute the script and automate email sending, including granting necessary permissions.
• Benefits: Discuss how this automation saves time, allows for personalization at scale, and improves email campaign efficiency.

Conclusion: Emphasize the transformative power of automation in communication strategies. Encourage readers to experiment with the script for their needs.

Hashtags and Call to Action: Don’t forget to share your thoughts and experiences with email automation in the comments below! #TechTips #EmailAutomation #GoogleAppsScript #GoogleSheets #DigitalMarketing #ProductivityTools #CodingForBeginners #EmailCampaigns

Objective: Create a script that fetches contacts from a Google Sheets document, creates personalized emails based on a template, and sends them at scheduled intervals. Include functionality to track which contacts have already been emailed and prevent duplicate emails.

Skills Practiced:

• Reading and writing data with Google Sheets API
• Sending emails using GmailApp
• Using HTML templates for email content
• Implementing triggers for automation

Email Address	Name	Email Sent Status
gappsco@gmail.com	John Doe
gappscours@gmail.com	Jane Doe
gappscourse@gmail.com	Alex Smith	Email Sent

1. Custom Email Campaign Manager

Key Steps:

1. Read Contacts from Google Sheets: Use SpreadsheetApp to access your sheet and read the contacts.
2. Send Emails: Loop through each contact, generate a personalized email using a template, and send it using GmailApp.
3. Track Sent Emails: Mark contacts as emailed in your sheet to avoid duplicates.

Step 1: Prepare Your Google Spreadsheet

Create or open an existing Google Spreadsheet.

Name your sheet exer1 or adjust the script to match the name of your sheet.

Prepare your data in the following format starting from the second row (assuming the first row contains headers):

Column A: Email addresses of the recipients.

Column B: Names of the recipients or any data you want to include in the email.

Column C: This column will be used to mark “Email Sent” once the email is successfully sent to avoid resending.

Step 2: Open Google Apps Script

From your spreadsheet, click on Extensions > Apps Script.

Delete any code in the script editor if it’s a new project.

Paste the provided code into the script editor.

Step 3: Create an HTML Template

In the Apps Script editor, click on File > New > Html file.

Name the file emailTemplate.

Enter your HTML content. Use <?= name ?> where you want the recipient’s name (or any variable data) to appear. For example:

<p>Dear <?= name ?>,</p>

<p>This is your custom email message.</p>

Save the HTML file.

Step 4: Understanding the Code

The sendCustomEmails Function:

Accesses the active spreadsheet and selects the sheet named exer1.

Calculates the number of rows with data to process, starting from the second row.

Fetches the data from the first three columns for each row.

Iterates over each row of data:

• Checks if the email has already been sent (Column C not marked as “Email Sent”).
• If not, it calls createHTMLEmail, passing the recipient’s name (or other data from Column B) to generate the email content.
• Sends an email with a custom subject and the generated HTML content.
• Marks the row in Column C as “Email Sent” and forces the spreadsheet to update with SpreadsheetApp.flush().

The createHTMLEmail Function:

Uses Google Apps Script’s HtmlService to create an HTML template from the file named emailTemplate.

Inserts the recipient’s name into the template.

Returns the processed HTML content to be used as the email body.

Step 5: Grant Permissions and Run

Click on the play button (▶️) next to the sendCustomEmails function in the Apps Script editor to run the script.

The first time you run it, Google will prompt you to authorize the script. Follow the on-screen instructions to grant the necessary permissions.

Once authorized, the script will send emails to each recipient not already marked as “Email Sent” in your sheet.

Step 6: Check Your Spreadsheet

After running, Column C in your spreadsheet will be updated with “Email Sent” for each row processed, indicating that the email was successfully sent.

This setup allows you to automate the process of sending personalized emails using a custom HTML template, directly from a Google Spreadsheet, with the ability to track which emails have been sent.

<!DOCTYPE html>

<html>

  <head>

    <base target="_top">

  </head>

  <body>

    <h1>Hello</h1>

    <p>Dear <?=name?>,</p>

    <p>My Message</p>

    <p>Laurence</p>

  </body>

</html>

function sendCustomEmails(){

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const sheet = ss.getSheetByName('exer1');

  const startRow = 2;

  const numRows = sheet.getLastRow()-1;

  const dataRange = sheet.getRange(startRow,1,numRows,3);

  const data = dataRange.getValues();

  data.forEach((row,index)=>{

    const emailAddress = row[0];

    const emailSent = row[2];

    const rowValue = index+2;

    if(emailSent !== 'Email Sent'){

      const message = createHTMLEmail(row[1]);

      const subject = 'Your custom email';

      const options = {to:emailAddress,subject:subject,htmlBody:message};

      MailApp.sendEmail(options);

      sheet.getRange(rowValue,3).setValue('Email Sent');

      SpreadsheetApp.flush();

    }

  })

}

function createHTMLEmail(name){

  const temp = HtmlService.createTemplateFromFile('emailTemplate');

  temp.name = name;

  return temp.evaluate().getContent();

}