This Mail Merge Script is from the Google Workspace Apps Script website, and was reviewed and tested by IT in July 2024.
This method is an alternative to using the backoffice mail merge method. We strongly recommend you test this first with colleagues, before creating an entire mail merge.
- Log into your NBER Google Apps account.
- Click on this link to make a copy of the Google Sheets template. This copy is hosted at NBER, and is one where we have reviewed the code for security reasons. The link on the external website may be updated at any time.
- Update the Recipients column with email addresses you would like to use in the mail merge.
- Create a draft message in your Gmail account using markers such as {{First name}}, which correspond to column names in the Google Sheet, to indicate text you’d like to be replaced with data from the copied spreadsheet.
- Copy the subject line text of the email, so you can paste it into the script later in the process.
- In the Google Sheet, click on custom menu item Mail Merge > Send Emails.
- The first time you do this, a dialog box will appear and tell you that the script requires authorization. Read the authorization notice, allow it to access your Gmail, and continue. Note these permissions are necessary for the script to run, but it only affects the sending of these messages.
- When prompted, paste the subject line used in your draft Gmail message and click OK.
- The Email Sent column will update with the message status.
If you want to reuse the same Google Sheet for another mailing, you will need to clear the Timestamp cells, as the script will skip any rows that have the timestamp field filled in.