You create a Gmail draft template with placeholders that correspond to data in a 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 draft.
This solution uses the following services:
Gmail service–Gets, reads, and sends the draft email you want to send to your recipients.
Spreadsheet service–Fills in the email placeholders with the personalized information for each of the recipients.
Click the following button to make a copy of the Gmail/Sheets Mail Merge sample spreadsheet.
Note: Once you have the file in your Drive you can click File > Make a Copy to create as many sheets you need for mail merge purposes.
In your copied spreadsheet, update the Recipients column with email addresses you want to use in the mail merge.
Add, edit, or remove columns to customize the data you want to include in your email template.
DO NOT change the name of the Recipient or Email Sent columns.
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 {{First name}}.
If you format the text in the email, you must also format the placeholder brackets.
Placeholders are case sensitive and must exactly match the column headers.
Copy the subject line of your email draft.
In the spreadsheet, click Mail Merge > Send Emails. You might need to refresh the page for this custom menu to appear.
When prompted, authorize the script. It may ask you to select your Google account.
Click Mail Merge > Send Emails again.
Paste the email template subject line and click OK.
Once these steps are complete you will see in the Email Sent column will indicate the date/time the emails were sent.
If you applied a filter to the sheet, the script still emails the filtered participants, but it won't add the timestamp