Email

Google Sheets + Email Integration

Send Email or Mail Merge contents of Google Sheets with Email Template.

What is Sheet Director?

Sheet Director is a Google Workspace add-on that helps to manage and control bi-directional data flow between Google Sheets and several other applications like Google Calendar, Tasks, Contacts, Sheets, QuickBooks, FreshBooks, Zoho etc.

It is available in the Google Workspace Marketplace. Please check the Installation instructions to install the add-on.

What is Email?

Email is a method of exchanging messages between people using electronic devices.

Use Case(s)

> Send Email - Send contents from a Google Sheet as PDF and Inline table to client via Email.

> Mail Merge - Send mail from a Google Sheet row by row by merging values into email template

Google Sheets

Sheet Director

Email

Demo Video: Send Google Sheets Contents as Email or Mail Merge from Google Sheets

Send Email - Service

Choosing App and Service

Select Email from the list of applications
Select service as Send Email

Email Configuration:

Select the sheet that needs to be send via Email.
Select Full Sheet or Range

Full Sheet:

Switch on Attach PDF to attach the contents as PDF via Email.
Mention the required name of the PDF, if PDF name is not mentioned a default PDF name will be created.
Select the required folder in which the PDF needs to be saved.

Range:

Mention the range of the values that is required.
Switch on Embed as Inline Table if the contents need to be send as inline table.
If the empty rows need to be ignored click the check box.
Switch on Attach PDF if PDF needs to be attached in Email.
Mention the required name of the PDF, if PDF name is not mentioned a default PDF name will be created.
Select the required folder in which the PDF needs to be saved.

Email Template:

Click on Email Template to customize your Email.
${Sheet Data} will replace as your sheets fields
Execute the service.

Scheduler Settings:

Switch on Run automatically to execute services automatically in background.

Sample Output

Below image shows the output Email that is received.

Mail Merge Service

Sends emails from the Google Sheets rows - one email per row with dynamic fields replaced with corresponding values

Open Mail App Service
Select Mail Merge as service
You can Apply Template or Setup Manually
Apply Template will insert new tab in your Google Sheet and Maps automatically

Source Sheet

Choose the sheet that contains records.
Enter the Start at cell value

(the default value is A1, if you change the value you need to refresh the header by clicking on refresh icon on side)

Select the Status column (where status of mail merge execution to be updated)
If status column is not there, simply click the + button to add the status header on your sheet (A status column will be created in Spreadsheet)
Select the Result column (where result/info of mail merge execution to be updated)
If result column is not there, simply click the + button to add the result header on your sheet (A result column will be created in Spreadsheet)

Mapping

It will ask for intelligent mapping
If you need to do intelligent mapping click on yes
Intelligent mapping will map the matching fields automatically
You can do manual mapping by selecting Column drop down
You can delete the field by click on delete icon on top of each filed map
You can't delete mandatory* fields

Click save to save the service
Click on execute will ask for confirmation

It will show number of records ready to post
Click post now to post the records on HubSpot

Note: Post Now will send the mail by row by row

Sample Output

It will show the count of number of records posted successfully and failed to post.

Mail Merge - Example Scenario - Send event invitation to list of members

Let assume a scenario. You are a event organiser.

You have a situation, where you are having member data in Google Sheet.

You want to send out pre-filled Google Form link individual members to verify their data and confirm their attendance

For that you need to do the following steps

Step 1 - Create Google Form and get Pre-Filled Link

Create a form with the required fields.

Create the required fields
Goto more -> Click on Get pre-filled link
The new form page will open

Set values for every fields
Click on get link
Clink on copy link
Now the link will be copied to clipboard

Here is a sample of copied link coming with values

https://docs.google.com/forms/d/e/1FAIpQLScyo-aId87TC32Y2QapqfNRuCjHCt-xjjY0USaeJ8Mfq_7FcQ/viewform?usp=pp_url&entry.807772626=Name&entry.1603152525=Event+Location&entry.1493073077=2020-03-11&entry.1217069727=Yes

Step 2 - Setup Google Sheet and prepare Pre-Filled Link Template

Here is the sample Google Sheet having the member records

Note : Date column should formatted as yyyy-mm-dd (2020-10-01) with apostrophe(') in start (as text format)

Google Form Pre-Filled Link Template

To form template, replace the values with ${header fields} of the Google Sheet

https://docs.google.com/forms/d/e/1FAIpQLScyo-aId87TC32Y2QapqfNRuCjHCt-xjjY0USaeJ8Mfq_7FcQ/viewform?usp=pp_url&entry.807772626=${Name}&entry.1603152525=${Location}&entry.1493073077=${Date}&entry.1217069727=${Attending}

Note: ${field} will be replaced with value while executing Mail Merge service

Place the Pre-filled Link template in Message field of Google Sheet

Drag and fill all Message column records

Step 3 - Execute Mail Merge Service using Sheet Director

Source Sheet

Open Sheet Director add-on
Choose Email app and select Mail Merge service
Select the Status column (where status of mail merge execution to be updated)
If status column is not there, simply click the + button to add the status header on your sheet (A status column will be created in Spreadsheet)
Select the Result column (where result/info of mail merge execution to be updated)
If result column is not there, simply click the + button to add the result header on your sheet (A result column will be created in Spreadsheet)