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 and identify undelivered messages.

Important: This mail merge sample is subject to email limits described in Quotas for Google Services

How it works

This sample is based on the solution described here. The “Recipient” field has been changed to “EmailAddress” and the behaviour relating to the “Email Sent” field has been changed to work with the GAR “PreferEmail” field.

Set up the spreadsheet

• Click the following button to make a copy of the GAR Gmail/Sheets Mail Merge sample spreadsheet.

• Download a notifications csv file from GAR
• In your copied spreadsheet import the notifications file you downloaded from GAR. Choose “Replace current sheet”.
• The mail merge script respects the “PreferEmail” column, i.e. emails will only be sent where the value in this column is “TRUE”. However, you will need to split the data into multiple sheets so that you can choose a different template / draft for donors with additional sales. Use the spreadsheet tools to sort the data before duplicating the sheet and deleting rows appropriately.
• You may also need to reduce the number of rows in each sheet to avoid exceeding Google service quotas.

Create an email template

• 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 {{LastName}}.
• The below text is provided as an example. Copy and paste it into your draft email. Note: Remove the text in italics to create a template specific to donors without additional sales.

Dear {{Title}} {{LastName}},

Thank you for bringing your goods to CHARITY NAME for sale. You agreed that we would contact you at the end of the tax year, which runs from 6 April to 5 April the following year, and let you know how much we raised from selling your goods.

I am pleased to tell you that proceeds from selling your goods have raised £{{TotalNetSales}} (net of commission and VAT) which will allow the Charity to reclaim Gift Aid of £{{TotalGiftAid}} in the tax year to 5 April {{Year}}.

Since the total proceeds are in excess of £100, we agreed that we would write to you to confirm that you wish to donate the additional sum of £{{AdditionalSales}} over and above the amounts in excess of £100 that you may have been notified of during the tax year and already agreed to donate to the charity.

To donate this amount to our charity you need do nothing further. If we do not hear from you within 21 days of the date of this letter, we will assume that you wish to donate the money raised to our charity and, once again we thank you for your continued support.

We appreciate that your status as a taxpayer making Gift Aid donations can change from one tax year to the next.

Please also contact us within 21 days if:

a)    you have paid less than £{{TotalGiftAid}} in UK Income and / or Capital Gains Tax in this tax year to meet the Gift Aid amount claimed back by the charity or

b)    your personal details, such as name and address, have changed or if you wish to cancel your Gift Aid declaration.

If you have paid insufficient tax to meet the Gift Aid claimed by the charity, HMRC may seek to recover this sum from you directly, as it is your responsibility to pay any difference.

Yours sincerely,

YOUR SIGNATURE

YOUR POSITION

• Copy the subject line of your email draft.

Run the script

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 subject line and click OK.

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