Google Apps Script: Create multiple versions of a document based on Google Sheet Data and a Google Doc Template (Mail Merge)

Google Apps Script: SpreasheetApp, DocumentApp, DriveApp; Google Sheets, Google Docs

If you have ever worked in LibreOffice or Microsoft Excel you will probably be familiar with the mail merge. Traditionally, mail merge is used to create multiple versions of a document and snail-mail them to someone.

These days, we don’t often use the snail mail approach, but it is a regular occurrence for us to need to produce multiple versions of reports based on a data set usually from a spreadsheet.

In this tutorial, we will create a document merger that will create new Google Documents based on a dataset from a Google Sheet using Google Apps Script.

If you want to quickly jump into your own project with our script, I’ll provide you with a quick-use guide.

Then, we will set up a template for our Google Doc and generate our Google Sheet data (don’t worry, I’ll share the document so you can follow along).

Finally, we will jump into the breakdown of the code for those legends who are learning how to create their own Google Apps Script.

Let’s get started:

Note: As always, take what you need and don’t worry about the rest. 

The Code

The code is separated into two *.gs files for easier accessibility. There are instructions documented in each file for you to update the code for your own purposes.

Map.gs

Code.gs

The Example

In our example, we have a letter regarding overdue books 🤓.  We also have a Google Sheet containing a list of all library members with overdue books. We want to create a Google Doc for each of our library members.

Let’s take a look at our Google Doc template:

… and let’s take a look at our Google Sheets Data (You can drag left and right to see all the data):

You can grab a copy of the files for this project below. Just go to File > Make a copy to work on your own version:

Overdue Books.doc

Overdue.sheet

Quick Use Guide

There are 4 main steps to set up your mail merge process:

1. Prepare your Google Doc template with keywords.
2. Setup your Google Sheet tab of rows that will be inserted into your template.
3. Update your file IDs.
4. Update your map between your Google Doc keywords and your Google Sheet Headers

Google Doc Setup

This mail merge – or document merger, more accurately – searches your Google Doc for keywords to replace after a copy of your template doc is created.

The Google Apps Script code searches the document for any text between double braces “{{keyword}}”.

To set up your document, add in your keywords where you want to replace them with your Google Sheet data. A good practice would be to make your keywords the same as your Google Sheets header.

You can use the same keyword for the same piece of data multiple times.

Here is what our example looks like:

 

Google Sheet Setup

To prepare your Google Sheets data, you should put your headers on row 1 and then your data below. Make sure you don’t have anything else below the rows of data you want to use to create your documents.

Best practices

Convert Everything to Text

A good approach to ensure that you get exactly what you see in your Google Sheet in your Google Doc is to ensure that you convert your data to text.

When you add formatting, dates or some formulas, you might find that the data that appears in the Document is not what you had in your sheet.

To fix this, create a new Google Sheets tab and then use the following formula:

=ARRAYFORMULA(TO_TEXT({Range}))

Your range will be where your original data is from.

Take a look at our example. Here is our Raw data (You can click on the image to expand it to get a better look.):

You can see in our raw data above, that we have some columns with formulas and also some dates. We’ve rounded up in our formula to 2 decimal places, but if you simply did this with the formatting button from the menu then the returned result in the Google Doc will have a long string of numbers. Also, we have some dates in there that will be returned as weird date-time stamps. We don’t want that.

First, we are going to create a new Google Sheets Tab, we’ll call it, Merge. We know that our raw data range goes from A1 to H9. In cell A1 of our Merge tab, we will insert the text transformation formula:

=ARRAYFORMULA(TO_TEXT({A1:H9}))

Update Variables in your Code

Mapping the Google Sheet to the Google Doc

The next task is for us to update our code. To correlate our Google Doc keywords with our Google Sheets headers we have an array of objects called mappedDocToSheet in our Map.gs file. We’ll need to update this.

This array consists of sets of objects. Each object set contains 3 key-value pairs:

• sheet: The Google Sheet header name of the column you are referencing. In double quotation marks.
• col: The location of the column. You will need to subtract one from the column number displayed. Google Apps Script starts counting from zero.
• doc: This is the keyword in your Google Doc that relates to this column. Remove the double curly braces and put it in double quotation marks.

Take a look at our first example. We have a sheet header called name, which is in column A. Column A is the zeroeth column, 0. Then we’ve added the keyword {{name}} minus the curly braces.

Update the file locations and sheet name

In the next step, you will need to go the Code.gs file. The runsies() function is just a place marker function for whatever else you want to put in your script. However, if your sole purpose is to run a document merge then using the function is fine.

You will need to update 5 variables:

1. TEMPLATE_ID: This is the id of your Google Doc template. It can be found in the URL.
2. SS_ID: This is the id of your Google Sheet with your data in it. It can be found in the URL.
3. SHEET_NAME: This is the name of the sheet tab you will use. In our example, this is Merge.
4. FILE_NAME: This is an array of headers that you can use to create your file name for each file. You can change the number of headers you use here.
5. ROW_LENGTH: (Optional) If you wish to define the row length of your Google Sheets Data.

Run The Document Merge

All done!

You are good to run your mail merge. In your Google Apps Script editor, go to Run > Run function > runsies. Accept the permissions and then the file will run and generate the new documents in the same folder as your Google Doc Template.

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

Code Breakdown

We’ve already covered everything we need to cover on the Map.gs file in the Quick Use Guide. The file is simply a container for mappedDocToSheet with all its relational data between the Google Doc template and the Google Sheet.

We’ll use this breakdown to focus on the main code.

Code.gs

runsies()

The runsies() function contains all the variables you need to run the main docMerge() function. It is essentially a placeholder for your own project. You can keep it as is if the code I provided is exactly what you are after or you can incorporate the variables and run docMerge() from your own custom function.

Let’s take a quick look at it:

TEMPLATE_ID is the ID of your Google Doc Template (Line  6). You can find it in the URL of the document. This is also the case for SS_ID, which is the Google Sheet ID of your data spreadsheet (Line 7).

Next, we have the SHEET_NAME. This is the sheet name tab or the sheet within the Google Sheet where your data can be found. You can find it down the bottom left of your spreadsheet. I usually just double click the tab and copy and paste in the value between the two double quotation marks. (Line 8)

On line 9 we have the MAPPED variable. This variable grabbed the mappedDocToSheet from the Map.gs. Note, that you don’t have to do any fancy importing from other .gs files in Google Sheet. These .gs files are essentially an aesthetic that helps you manage and organise your code.

Could I have simply called the mappedDocToSheet variable? Absolutely. However, I wanted to make it more explicit for anyone who is reading the code to see the process. Creating a variable from a variable is a pretty low-cost endeavour for the sake of clarity.

FILE_NAME is an array containing all the Headers we might want to use in creating a personalised file name for the new Google Doc we create. For our example, we have Library ID, name and Current Date. This will make it easy for me down the track to search for the files  I create or create a custom script to find and perhaps email these file as attachments. (Line 10)

docMerge() is our main function that makes a copy of our template and updates it with our Google Sheet data. We covered the arguments for this in our Quick Use Guide. Check them out if you missed. (Line 12)

docMerge()

The docMerge() is the main workhorse for this code. The file loops through each row of the data in the Google Sheet provided. As it does so, it creates a copy of the template file, names it with the appropriate headers we provided before replacing all the keywords in double curly braces with the current row of the sheet.

We first start off by calling our desired Google Sheet using the openById() method of the SpreadsheetApp class. This prepares the sheet to be worked on and sets is in the commonly used variable name ss for spreadsheet(Line 14).

To reference the sheet tab we wish to draw our data from, we use the getSheetByName() method. This method takes one argument, the name of the sheet you want to access. (Line 15)

Remember, we had an optional argument that allowed us to select the row length of our Google Sheet? On line 18, we check to see if the row length was set using a ternary operator.

It states that if the rowLen is set to “auto” – or in other words if no length argument was explicitly made, then run the getRowLen() function and subtract 1 from the returned value. Otherwise use the row length provided. (Line 18)

Once we have the row length we can now grab all the data in our desired range. To do this we first use the getRange method on our sheet variable. This method can take a number of arguments but for us, we will input the following:

1. The Start row as a number. This is the first one, so 1.
2. The Start column as a number. This is the first one, so 1.
3. The number of rows. This is our desired row length (rowLen).
4. The number of columns across. Here, we want to find this automatically. We do this by first calling our sheet variable. We then use the getDataRange() method for Apps Script to automatically find the range containing our data for us. Once we have the range we call, the getNumColumns() method to get the number of columns with data in them. This returns the total count of columns containing data.

Once we have the range, then we use getValues to grab the data in the range and store it in our matrix variable.

The next task is to get a list of the column numbers containing our filename data. The variable fileNameRows calls the getFileNameRows() function which iterates through the mapped data searching for the filename and returning the corresponding column number. (Line 25)

Once we have all our data set up, we can now loop through the rows. I’ve used a simple for loop here starting at 1. Remember in Javascript and Google Apps Script number starts at zero. We are skipping zero because it is the header row. It will iterate through each row for the desired rowLen. (Line 29)

Next, we set our row variable to contain the current array of data in the row.

Our first task is then to build the file name. buildFileName(row) takes the row data as an argument and then grabs all the relevant columns we identified in fileNameRows and then joins them together with an underscore (_)  between the items of data. It then returns this newly created file name into the fileName variable. (Line 32)

The next task is to create a copy of our template file. To do this we use the DriveApp class. First, we grab the template id with getFileById(). This method takes the ID string of our Google Doc template.

To make a copy, we simply then call the makeCopy() method. This method can take two optional arguments, the filename an the destination of the file in your drive. For this project, we just want to add our custom name. If we leave the destination blank, the new file will be located in the same directory as the origin file. (line 34)

The final task is to update all the double curly-braces keywords with our sheet. We do this with our updateFileData() function. This function takes two arguments, our current row of data and the file id of our newly created document. To get our new file ID we call the getId() method.

updateFileData()

This function is called in the Google Sheet row loop after a new file from the template Google Doc is created.

Here, we use a forEach loop on our mapped data we drew from Map.gs. It will loop through each element in the map array. Remember, each element of the array in an object of key-value pairs:

On line 13, we need to add our curly braces to our element.doc. So for example name will turn into {{name}}. We will store this value as textID.

Then, we need to access the text inside the document. We do this by using the DocumentApp class. We’ll grab the right file with our doc parameter which contains the file ID of our freshly copied Google Doc. (Line 15)

Then, get the body of the document with getBody(). Once we have that we can make use of the replaceText() method. This method takes two variables:(Lines 16 – 17)

1. The search pattern: This can be the exact text or a regular expression containing a set of parameters to look for. For us, this is the textID.
2. The replacement text: Here, we want to add the current row’s value based on the corresponding column number in our mapped object.

buildFileName()

This function is used to create the file name based on an array of column numbers. These numbers are generated from the getFileNameRows() function. As the code iterates through each column it grabs the selected file names based on the column numbers present in the fileNameRows parameter.

On line 9, map is used to iterate through the list of columns in the current row, rowArray, and select the data. It returns this new set of data. For example, if we had in our fileNameRows array [1 ,0 ,3] then on the first iteration it would return:

On line 11, we join this array together with an underscore to make our file name. Using our example above it would return:

It is in this function where you could modify your file names. Perhaps you wanted to add context to the file name by adding “overdue” to the front. You should do this here.

getFileNameRows()

In this function, we grab our list of file name headers and search for them in our mapped data from our Map.gs file.

First, we grab our file name data on line 9.

We then use the flatMap method. This handy method allows us to map through nested data and update it with our map instructions before returning a flattened array of results. (Line 10)

Within our flatMap we will search through our mapped array of key-value object and filter only those objects that contains the matching sheet name.(Line 11)

Then we will map this data again returning the elements column. (Line 12)

Once we have our list of columns, we return the array.

getRowLen()

This function automatically grabs the row length of the data in the sheet. Taking the sheet variable, it uses the getDataRange() method to grab all the rows and columns containing data. We use getNumRows() to count the number of rows containing data before returning to the main docMerge function.

Conclusion

Creating new documents based on a Google Doc template and Google Sheet data can save an incredible amount of time.

One limitation is that the process can be slow with a large number of rows or a lot of data to input. You may face times when the code runs over your runtime limit. So you may need to run the script in batches.

If you want to take this merging process further, you could look at merging data within one document or within Google Doc tables. Have a play around!

I really enjoy hearing how these tutorials are applied in your own project. Feel free to add a comment below and let me know what you are up to. And if you enjoyed this tutorial, hit the like button. You can even subscribe to the mailing list to get my latest posts (Top right).

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

Happy coding!

 

~Yagi