Email

Google Sheets and Email: Send Email or Mail Merge contents of Google Sheets with Email Template using Sheet Director.

What is Sheet Director?

Sheet Director is a G Suite 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 G Suite Marketplace and Chrome Web Store. Please check 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 Service - Send contents from a Google Sheet as PDF and Inline table to client via Email.

> Mail Merge Service - 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
  • Switch on Run Automatically if the queries needs to be executed in regular intervals.

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.

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)



  • Setup mapping of the mail merge fields and sheet fields
  • Execute the service
  • 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

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.