My Code Tricks

Mail Merge using Google Sheet and Google Docs

Google Sheets and Google Docs are powerful tools for organizing and presenting data.

However, when it comes to performing mass mailing, it can be quite challenging to personalize each email for every recipient.
This is where mail merge comes into play. 

With the help of Google Apps Script, you can easily automate the mail merge process and send personalized emails to all the recipients.

In this blog, we will discuss how to perform mail merge in Google Sheets and Google Docs using Google Apps Script.

Step 1: Create a Google Sheet with Data 

The first step in performing a mail merge is to create a Google Sheet with the data you want to merge. 

The sheet should contain columns with information such as recipient name, email address, and any other data you want to include in the email.

Step 2: Create a Google Docs Template

Next, create a Google Docs template with the content you want to include in your email. 

You can use placeholders such as <<Recipient Name>> and <<Email Address>> in the template to personalize the email for each recipient.

Step 3: Create a Google Apps Script 

Once you have created the Google Sheet and the Google Docs template, it's time to create a Google Apps Script. To create a new script, open your Google Sheet, click on Tools -> Script Editor.

In the script editor, create a new function and give it a name. This function will be used to perform the mail merge.

Step 4: Write the Google Apps Script Code

The following code can be used to perform mail merge in Google Sheets and Google Docs using Google Apps Script:

function sendEmails() { var sheet = SpreadsheetApp.getActiveSheet(); var startRow = 2; // First row of data to process var numRows = 10; // Number of rows to process var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn()) var data = dataRange.getValues(); var templateFile = DriveApp.getFileById('TEMPLATE_DOC_ID'); // replace with your template file ID var templateDoc = DocumentApp.openById(templateFile.getId()); for (var i = 0; i < data.length; ++i) { var row = data[i]; var recipientName = row[0]; var recipientEmail = row[1]; var copyDoc = templateDoc.makeCopy(); var copyId = copyDoc.getId(); var copyDocFile = DriveApp.getFileById(copyId); var copyDocFolder = DriveApp.getFolderById('FOLDER_ID'); // replace with your folder ID copyDocFolder.addFile(copyDocFile); var copyDocUrl = copyDocFile.getUrl(); var subject = 'Your Subject Here'; var body = 'Your email body text here'; var emailOptions = { htmlBody: body, attachments: [copyDoc.getAs(MimeType.PDF)], name: 'Automatic Emailer Script' } MailApp.sendEmail(recipientEmail, subject, body, emailOptions); copyDocFile.setTrashed(true); } }

The above code uses the SpreadsheetApp and DocumentApp services to read data from the Google Sheet and create a copy of the Google Docs template for each recipient.

It then replaces the placeholders in the copied document with actual data from the sheet, converts the document to a PDF, and sends an email to the recipient with the PDF as an attachment.

Make sure to replace 'TEMPLATE_DOC_ID' and 'FOLDER_ID' with the actual ID of your Google Docs template and folder, respectively. Also, customize the subject and body of the email as per your requirements.

Step 5: Run the Script