How To Get Email Google Sheet Is Updated

Coding level: Beginner
Duration: 10 minutes
Projection type: Automation with a custom carte du jour

About this solution

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

mail merge example

How it works

Yous create a Gmail draft template with placeholders that represent 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 typhoon.

Apps Script services

This solution uses the post-obit services:

Gmail Service: Gets, reads, and sends the draft electronic mail you desire 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 e-mail.
Spreadsheet Service: Fills in the email placeholders with the personalized information for each of the recipients.

Before you begin

To employ this sample, y'all need the following prerequisites:

A Google Account (Google Workspace accounts might require administrator approving)
A web browser with access to the internet

Try it

Step 1: Fix upward the spreadsheet

Click the button beneath to make a copy of the Gmail/Sheets Mail service Merge sample spreadsheet.
Make a copy
Update the Recipients column with electronic mail addresses yous want to employ in the mail service merge.
(Optional) Add, edit, or remove columns to customize the information you desire to include in your email template.

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

Step 2: Create an email template

In your Gmail account, create an email draft. To include information from the spreadsheet in your email, use placeholders that represent to column names surrounded past curly braces, such as {{First proper name}}.
Copy the subject line of your email draft.

Pace 3: Send emails

In the spreadsheet, click Post Merge > Transport Emails. Y'all might need to refresh the page for this custom carte du jour to appear.
When prompted, qualify the script.
Click Postal service Merge > Transport Emails again.
Paste the email template's discipline line and click OK.

Open up the Apps Script projection

The sample script'southward 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 lot can edit the post merge automation as much as yous'd like to fit your needs. Beneath are a few optional changes y'all can make to the source lawmaking.

Add Bcc, Cc, ReplyTo, or From email parameters

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

Activate the parameters y'all want to add together 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 e-mail parameters:

GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.bailiwick, msgObj.text, {          htmlBody: msgObj.html,          bcc: 'bcc@example.com',          cc: 'cc@example.com',          from: 'from.alias@instance.com',          name: 'name of the sender',          replyTo: 'respond@example.com',         // noReply: truthful, // if the email should be sent from a generic no-reply electronic mail address (not available to gmail.com users)

In the above sample, the noReply parameter is withal commented out considering the replyTo parameter is set.

Include unicode characters in your emails

If y'all want to include unicode characters, such as emojis, in your emails, you must update the code to utilise Post 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 post-obit code:

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

Contributors

The sample was created past Martin Hawksey, Learning Pattern and Engineering science Lead at Edinburgh Futures Establish, blogger, and Google Developer Expert.