How do i set up automatic in google sheets?

Coding level: Beginner
Duration: 10 minutes
Project type: Automation with a custom menu

About this solution

Automatically populate an email template with data from Google Sheets. The emails are sent from your Gmail account so that you can respond to recipient replies.

How it works

You create a Gmail draft template with placeholders that correspond to data in a Google Sheets spreadsheet. Each column header in a sheet represents a placeholder tag. The script sends the information for each placeholder from the spreadsheet to the location of the corresponding placeholder tag in your email draft.

Apps Script services

This solution uses the following services:

• Gmail Service: Gets, reads, and sends the draft email you want to send to your recipients.
  • If your email includes unicode characters like emojis, use the Mail service instead. Learn how to update the code to include unicode characters in your email.
• Spreadsheet Service: Fills in the email placeholders with the personalized information for each of the recipients.

Before you begin

To use this sample, you need the following prerequisites:

• A Google Account [Google Workspace accounts might require administrator approval]
• A web browser with access to the internet

Try it

Step 1: Set up the spreadsheet

1. Click the button below to make a copy of the Gmail/Sheets Mail Merge sample spreadsheet.
   Make a copy
2. Update the Recipients column with email addresses you want to use in the mail merge.
3. [Optional] Add, edit, or remove columns to customize the data you want to include in your email template.

If you change the name of the Recipient or Email Sent columns, you need to update the corresponding code in the Apps Script project. You can open the Apps Script project from the spreadsheet by clicking Extensions > Apps Script.

Step 2: Create an email template

1. In your Gmail account, create an email draft. To include data from the spreadsheet in your email, use placeholders that correspond to column names surrounded by curly braces, such as {{First name}}.
   • If you format the text in the email, ensure you also format the placeholder brackets.
   • Placeholders are case sensitive and must exactly match the column headers.
2. Copy the subject line of your email draft.

Step 3: Send emails

1. In the spreadsheet, click Mail Merge > Send Emails. You might need to refresh the page for this custom menu to appear.

2. When prompted, authorize the script. If the OAuth consent screen displays the warning, This app isn't verified, continue by selecting Advanced > Go to {Project Name} [unsafe].

3. Click Mail Merge > Send Emails again.

4. Paste the email template's subject line and click OK.

If you applied a filter to the sheet, the script still emails the filtered participants, but it won't add the timestamp.

Open the Apps Script project

The sample script's code includes in-line comments to help you understand how it works. To access the code, in the spreadsheet, click Extensions > Apps Script.

Modifications

You can edit the mail merge automation as much as you'd like to fit your needs. Below are a few optional changes you can make to the source code.

Add Bcc, Cc, ReplyTo, or From email parameters

The sample code includes a number of additional parameters, currently commented out, that let you control the name of the account the email is sent from, reply to email addresses, as well as Bcc and Cc email addresses.

Activate the parameters you want to add by removing the forward slashes // in front of each one.

The following sample shows an excerpt from the sendEmails function that activates most of the email parameters:

GmailApp.sendEmail[row[RECIPIENT_COL], msgObj.subject, msgObj.text, {
         htmlBody: msgObj.html,
         bcc: '',
         cc: '',
         from: '',
         name: 'name of the sender',
         replyTo: '',
        // noReply: true, // if the email should be sent from a generic no-reply email address [not available to gmail.com users]

In the above sample, the noReply parameter is still commented out because the replyTo parameter is set.

Include unicode characters in your emails

If you want to include unicode characters, such as emojis, in your emails, you must update the code to use Mail service instead of Gmail service.

In the sample code, update the following line:

GmailApp.sendEmail[row[RECIPIENT_COL], msgObj.subject, msgObj.text, {

Replace the line with the following code:

MailApp.sendEmail[row[RECIPIENT_COL], msgObj.subject, msgObj.text, {

Contributors

The sample was created by Martin Hawksey, Learning Design and Technology Lead at Edinburgh Futures Institute, blogger, and Google Developer Expert.

• Find Martin on Twitter @mhawksey.
• Read Martin's Google Apps Script related blog posts.
• Watch Martin's YouTube show, Totally Unscripted.

This sample is maintained by Google with the help of Google Developer Experts.