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. 

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

Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet

Google Apps Script: DriveApp, Advanced Drive Service, SpreadsheetApp,  DocumentApp Javascript: spread operator, map, regex

Ah, bureaucracy at its finest.  Have you ever asked for a list from a client, another department or agency and instead of saving into a file that might even be considered marginally useful, they give it to you as a pdf. A… p…d…f.

Gee, thanks.

After some prostrations to the great Google Apps Script gods, I had a though.

“Hey, can’t we convert a PDF to a Google Doc with just a click of the button? Surely the great Google Apps Script devs have made it so we can do it programmatically too.”

And you know what? They bloody well did. The big legends.

The Scenario

I’ve just received a bunch of PDFs. The PFDs are all labelled by the class number. Take a look at the files in my Google Drive:

PDFs of sections in Google Drive

Each PDF file contains a list of student IDs that I need to extract and put into a Google Sheet.

PDF file with student IDs

The aim is to have a list of student IDs in column A and their corresponding sections in column B.

As you can see, we have some pretty standard text in the PDF that should be easy for Google to recognise so that we can extract the IDs.

The list of names in the demo sheets were randomly generated by AI!

NOTE! As always, I have tried to create this tutorial for varying levels. Feel free to follow along, or just grab what you need and get stuck into your own project.

If you are playing along, you can find a copy of the PDF files below. Simply add them to your own Drive before you get started:

PDF files by Section.

Continue reading “Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet”

Google Apps Script Course – Part 4: 2D Array Data Transformation of Multiple Question Multiple Group Items Data to Total Count of Rating Choices in Google Sheets

Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays

<<Part 3                                         <<Intro>>

In Part 3 of our 2D array data transformation course in Google Apps Script, we worked out how to get the count of each choice of each question item from the survey results in a Google Sheet.

This time we are going to add a final element to our mix. Let’s say we have multiple questions and multiple groups. We want to find out the count for each choice for each question for each group.

Continue reading “Google Apps Script Course – Part 4: 2D Array Data Transformation of Multiple Question Multiple Group Items Data to Total Count of Rating Choices in Google Sheets”

Google Apps Script Course – Part 3: 2D Array Data Transformation of Multiple Question Items Data to Total Count of Rating Choices in Google Sheets

Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays

<<Part 2                                         <<Intro>>

In our last tutorial, we grabbed the Google Sheets count data from a single rating survey question. We then displayed it two ways; horizontally with the question item at the top, or vertically with the question item to the side.

Google Sheete count data from rating survey

In part 3 of our Google Sheets data transformation with Google Apps Script course we are going to expand on our current work and add two more rating question items from our survey. Each survey will have the same 1. Weak to 5. Strong survey items.

Our new list of question items will include:

  • Rate your goat’s athleticism.
  • Rate your goat’s agility.
  • Rate you goat’s headbutt

All the information any self-respecting human companion for goats would know.

We’ll be updating our code to be able to take any number of item questions so long as they have the same choices.

Continue reading “Google Apps Script Course – Part 3: 2D Array Data Transformation of Multiple Question Items Data to Total Count of Rating Choices in Google Sheets”

Google Apps Script Course – Part 2: 2D Array Data Transformation of Single Question Item Data to Total Count of Rating Choices in Google Sheets

Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays

<<Part 1                                         <<Intro>>

In our previous tutorial, we created a 2d array of count values for each item chosen in a survey form in Google Apps Script. In our survey, we asked users to submit what type of goat they are. We didn’t know what species of goat they identified as so we just needed to count for any goat species that was submitted.

In part 2 of our course, our Google Sheets survey data is a little different. This time around we are asking the human companion of their coding goat to:

Rate your goat’s athleticism.

Respondents then rate their goat’s athleticism on a 5-point scale:

  1. Weak
  2. Below Average
  3. Average
  4. Better Than Average
  5. Strong

But, Yagi! Can’t we simply use the script in part one?

Sure, you could. However, you might come across a bit of a problem. In Part 1 we generated our choices for our count based on their appearance in the survey. What happens if none of the respondents rated their goat as Weak (This is right and just)? Weak would not be recorded in our 2d count array when we ran our Google Apps Script code.

Further, if the first user in our Google Sheet response data rates their goat as Strong, then the first choice in our 2d count array will be Strong.

That would just look weird for a summary count of a rating survey. We really need to display our count in order from 1.Weak through to 5.Strong.

Continue reading “Google Apps Script Course – Part 2: 2D Array Data Transformation of Single Question Item Data to Total Count of Rating Choices in Google Sheets”

Google Apps Script Course – Part 1: 2D Array Data Transformation of Single Question Item Data to Total Count of Choices in Google Sheets

Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays

<< back to Introduction                                                                            Part 2  >>

Hey, you made it! It’s time to do some coding. You know, the fun stuff.

In this part of the 2-dimensional data transformation course, we are going to get the total count of results of some form data on a Google sheet. The form data contains a single question item with a selection of different unordered choices.

To get the most accurate data we could we surveyed goats online and asked them what type of goat they are. Here is a sample of the results:

what type of goat are you - Google Sheets Survey

Continue reading “Google Apps Script Course – Part 1: 2D Array Data Transformation of Single Question Item Data to Total Count of Choices in Google Sheets”