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.
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.
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.
Table of Contents
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:
Each PDF file contains a list of student IDs that I need to extract and put into a Google Sheet.
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:
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.
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.
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:
Better Than Average
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)? Weakwould 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.
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: