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


Email Configuration:

Full Sheet:

Range:

Email Template:

Scheduler Settings:

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

Source Sheet

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




Mapping

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.

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



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

All my 3 records are posted successfully, that means 3 emails are sent

By clicking on result link we can see our sent items

The invite email in member's inbox

Lets see how the email appears on receiver side

When the invitee clicks on the link, the form opens with values corresponding to that individual.

FAQ

If PDF name is not entered , then how it will save the PDF file ?

If PDF name  is not entered, the PDF file will be created with a default name. The default name format is (Service Name_Date_Time).

If Range and Embed as Inline Table are selected , and if the range contains any empty rows, will it delete the empty rows ?

Yes it will remove all the empty rows . If "Embed as Inline Table" is selected it will remove all empty rows in that particular range.