Boost Productivity: Automate Gmail to Multiple Recipients with Google Sheets and Apps Script

When sending emails to customers or contacts managed in a spreadsheet, do you find yourself sending similar content to each one individually?

For example, if you need to send emails to multiple recipients by only changing the meeting schedule in the email body, doing it manually can lead to mistakes and take a lot of time.

In such cases, it would be convenient to have a method to automatically send emails using Google Sheets.

In this article, we will introduce how to automate email sending using Google Sheets and Google Apps Script.

This allows you to focus on other important tasks without spending time on sending emails every time.

Completed example

Gmail Content

Here is a completed example of how to automatically send emails using the spreadsheet and Google Apps Script we will create this time.

Based on the information for each recipient entered in the Google Sheet, a portion of the email body is automatically customized, and then the email is sent via Gmail.

In this example, the changes to be inserted into the email body include the name, meeting date, and meeting time.

A pre-prepared email draft is duplicated, and these pieces of information are automatically replaced, allowing for personalized emails to be sent.

Google Sheet Input Fields

How to Use the Check Box in Column F

By using the checkboxes in Column F, you can easily select the recipients to whom the emails will be sent.

For example, if you want to send emails to Mr. Smith and Mr. Johnson but want to send one to Mr. Brown later, you would uncheck Mr. Brown.

Emails are sent only for the rows where the checkbox is checked, and the send time is recorded in Column G after the email is sent.

Google Apps Script Settings

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Email Sending List');  // Specify the tab name "Email Sending List"
  var range = sheet.getRange("A2:G");  // Get the data range from A2 to column G
  var data = range.getDisplayValues();  // Get the displayed data within the range
  var draftId = getDraftIdBySubject('Regarding Meeting Schedule');  // Email draft subject
  
  if (!draftId) {
    Logger.log("Error: Draft not found.");
    return;  // Exit if the draft is not found
  }

  data.forEach(function(row, index) {
    var sentTime = row[6];  // Column G: Sent Time
    if (row[5] === 'TRUE' && !sentTime) {  // Check if the checkbox in column F is checked
      var recipient = row[2];  // Column C: Email Address
      var subject = 'Regarding Meeting Schedule';  // Subject of the email to be sent
      var body = getDraftMessageBody(draftId);  // Get the draft message body
      var attachments = getDraftAttachments(draftId);  // Get the draft attachments

      // Replace placeholders with data from the spreadsheet
      body = body.replace(/{First Name}/g, row[0])       // Column A: First Name
                 .replace(/{Meeting Date}/g, row[3])     // Column D: Meeting Date
                 .replace(/{Meeting Time}/g, row[4]);    // Column E: Meeting Time
      
      // Send email using GmailApp.sendEmail (with HTML body)
      GmailApp.sendEmail(recipient, subject, '', {
        htmlBody: body,
        attachments: attachments  // Add attachments to the email
      });

      // Record the sent time in Column G
      var userTimezone = Session.getScriptTimeZone();  // Get the user's script timezone
      var localTime = Utilities.formatDate(new Date(), userTimezone, 'MM/dd/yyyy HH:mm:ss');
      sheet.getRange(index + 2, 7).setValue(localTime);  // Record the sent time in Column G
    }
  });
}

// Function to get draft attachments
function getDraftAttachments(draftId) {
  var draft = GmailApp.getDraft(draftId);
  var message = draft.getMessage();
  return message.getAttachments();  // Get attachments
}

// Function to get draft ID by specified subject
function getDraftIdBySubject(subject) {
  var drafts = GmailApp.getDrafts();
  for (var i = 0; i < drafts.length; i++) {
    if (drafts[i].getMessage().getSubject() === subject) {
      return drafts[i].getId();
    }
  }
  Logger.log("Error: No draft found with subject '" + subject + "'.");
  return null;  // Return null if no draft is found
}

// Function to get the draft message body
function getDraftMessageBody(draftId) {
  var draft = GmailApp.getDraft(draftId);
  var message = draft.getMessage();
  return message.getBody();  // Get the draft HTML body
}

his script automatically sends emails from Gmail based on the email addresses and meeting schedules listed in a spreadsheet.

The email body is created by customizing a pre-prepared draft email template.

Emails are sent individually based on the information in the spreadsheet, and the date and time of sending are also recorded.

Recording the sent time helps prevent duplicate emails from being sent.

Steps

STEP

Open the Apps Script Editor

Open Google Sheets, then click on “Extensions” in the menu and select “Apps Script” to open the editor.

STEP

Create the Script

Copy the code above and paste it into the editor.

STEP

Save the Script

Once you’ve written the code, name it and save it.

(For example: “Send Email”)

Create email draft

Next, open Gmail and create a draft email template.

In the draft email, use placeholders { } like below, so that data from the spreadsheet can be inserted.

Example draft

Subject: Regarding Meeting Schedule

Dear {First Name},

We are pleased to inform you of the scheduled meeting details as follows:

- Meeting Date: {Meeting Date}
- Time: {Meeting Time}

Thank you and best regards.

By including placeholders like {First Name} and Meeting Date in the email body, data from the spreadsheet will be automatically inserted.

Run the script to send emails

Once all settings are complete, run the script from the Apps Script editor screen.

STEP

Open the Apps Script Editor

Open Google Sheets, then click on “Extensions” in the menu and select “Apps Script” to open the editor.

STEP

Run the Script

Execute the script to send the emails.

If you’re running the script for the first time, you need to authorize it.

Therefore, press “Review Permissions.“

Detailed Authorization Steps

Press “Advanced.”

Press “Go to Untitled project (Unsafe).”

After that, press “Allow.”

This will automatically send emails to the people checked in the spreadsheet and record the sent date and time.

Recording sent date and time in the google sheet

Additionally, creating a button on the spreadsheet allows you to run the script more easily.

Gmail sending content

Summary

With this, the basic email sending automation using Google Sheets and Google Apps Script is complete.

Once set up, you can efficiently automate what used to be a labor-intensive email sending task.

Utilize this method whenever you need to change only parts of the email to send to different recipients.

Our company offers support for improving work efficiency through the use of Google Apps Script.
If you need assistance with Google Apps Script customization or error resolution, please feel free to contact us.
We are fully committed to supporting your business improvements.

Contact us here