The merge functions, available in Gmail Mail Merge and Document Studio, let you easily produce multiple emails or documents by combining a template (like a Gmail draft or document) and a data source (Google Spreadsheet).

The spreadsheet contains multiple columns with unique headings, like First Name or Country, and the rows below contain personalized information that is merged to create a custom email or a document from the template.

google-document-merge.png

In the example above, the first line of the template contains a variable field (placeholder) called Name enclosed in double curly braces. Thus when the email goes out, the line will read Dear Angus McDonald for the first email, Dear Kiran Tawde for the second email and so on.

The Problem with Merge Fields

Now consider a scenario when the information in the Google Sheet in not complete and some of the data to be used in the merge process is missing. For instance, if the Name column is blank for a particular row, the outgoing email will read Dear since the {{Name}} value will be substituted will be a blank space.

This is a problem because the final email, or document, will not look proper when a line ends abruptly. It is better to say Greetings, or leave the line blank, than saying “Dear “.

Create Intelligent Merge Fields with Conditions

The solution is to use conditional merge fields that change based on the source data. These fields are “intelligenent” meaning they serve a different value if the source field is incompletete or blank. Let me illustrate with an example.

Say the column title is “Country” and the text in the merge template reads “I’ll be visiting {{Country}} later this year.”

Now if the value of Country is, say, India, the output will be “I’ll be visiting India later this year”. However, if the column is blank in the sheet, the merge output will be “I’ll be visiting ¬†later this year”.

conditional-mail-merge-field.png

To avoid this situation, we’ll create a new “intelligent” field in our Google Sheet that will internally use the IF formula. Let’s call this column “Country Visit” and set the value of the cell as a formula:

=IF(ISBLANK(B2), “your country”, B2)

You can either copy paste the formula in the other cells of that column or use an ARRAY FORMULA to populate all the cells in the column.

=ArrayFormula(IF(ISBLANK(B2:B4), “your country”, B2:B4))

Now that your data in the sheet is clean, we need to make a small change in the email template. Replace the variable field {{Country}} with {{Country Visit}} since the source column has changed.

This can also be used to have gender specific information in your email like:

=IF(G2===”Male”, “He”, “She”) (G2 contains the person’s gender)

Advanced Conditional Merge Fields

Now that you have an understanding of conditional merge fields, you can use them to not only clean the data but also pre-format the data before performing merge.

For instance, if a column contains the web urls in mixed formats, you can create a new column and set the formula as =ARRAYFORMULA(LOWER(B2:B)) to always have the links in lower case.

The conditional fields can also be used to replace text before the merge as shown below:

=SUBSTITUTE(B1, “Hello”, “Hola”)

Similarly, if a column contains the amount but formatted as a number, you can use the =TEXT() formula to format the number as tet with the currency symbol.

format-currency-number.png

The following example specifies that if the customer order quantity in column C is greater than or equal to 50 units, the text “Thanks” appears in the email else the text “The minimum order is 50 units” appears in the document.

=IF(C2>=100, “Thanks”, “The minimum order is 50 units”)

Mail Merge also support spreadsheet filters. This is useful if you want to send emails to say people located in a particular city or those with a particular postal code.

← Gmail Mail Merge