Mail google form submissions as PDFs

How to design and implement a service for creating a Google Form, using the submitted data for filling in a spreadsheet template, generating a PDF from it and emailing the result to the Google Form submitter. It is assumed throughout this post that you are familiar with the basic Google products.

The challenge

Germany, the country I was born and live in, is a quite bureaucratic one. There are forms for everything. It doesn’t matter if you want to get the medicine the doctor recommended, apply for tax correction, signup for a bus ticket subscription or get your travel expenses reimbursed at work — there is a form for it, and you have to use it. No exceptions.

But it doesn’t end there. These forms can take up to 40 pages. The form design language and the field label language are highly cryptic and not user-centric. Sometimes it even makes a significant difference if you put a seemingly harmless zero-value in a form field instead of leaving it empty. Filling this out can be a tedious process. And there is always some mistake you made, that you only find out about when you try to submit it. Apparently is multiple degrees harder when you’re not a native speaker or not familiar with the topic of the form.

The strategy for a solution

In 2008 Google, which already had released Google Docs and Spreadsheets, released a new tool called Google Forms. Forms allows you to create a form on the web, where multiple users can fill it out, and the results are saved in a spreadsheet. There is also some analytics available for it. This form is a web form, so how can it be connected to the real life forms in a way that is more user centric?

Let’s create an abstract user story:

As a person who needs to fill out a form I want a filled out form based on questions I can answer so that I can submit the proper filled out form.

But nobody cares about the form itself. So let’s take a more concrete example:

As an employee of my company I want my travel expenses form filled out properly so I can get reimbursed for my business lunch.

The process for creating a user centric process to solve this problem is clearly target driven, as the expenses sheet that needs to be submitted is a nonnegotiable document format constraint by laws and regulations. The technical challenge is, to hide as much as possible of this process from the user. Whenever I am challenged with minimising a process for a user, I imagine how this process would look like if a very important person had to do it and some helper gets very short time to get the crucial information from this VIP. For most processes, the solution would be for the helper to ask a prepared list of questions to get a set of minimum viable data, then the helper prepares everything on his own time in a way that the VIP only has to approve it or give more feedback for another adjustment round. Once it is approved, it is signed by the VIP.

For a user centric solution for the travel expenses form it means, we need a minimal set of questions to ask and from that create a printable document that can be presented for approval via signature. Or in a more technical language:

We want to build a web form that asks for the travel expenses data, create a PDF based on a travel expense form template filled out based on the data we got, and email this PDF to the person who filled out the web form.

The user centric service design

To build this process, we start at the end and go mostly step by step backwards. So the first step is, to obtain a copy of the spreadsheet with the travel expenses form as it needs to be printed. It probably looks something similar to this:

In the next step we need to find out, which fields have to be filled out with which data, so that the form is properly filled out for submission. We go and talk to person who knows best about it, probably someone in the finance department. From that interview we create a list of questions to ask, which get us all the answers that allow us to fill out the form. Then we analyse this list and kick out all questions that give us data that can be obtained otherwise, via calculations or calendars, for example something like Day of submission — we know the date of when the web form is filled out, so we can assume that this will also be the day of submission. The fewer questions the better.

With this list of questions, we want to create the web form. So we open the spreadsheet with the travel expense template, and go the menu bar to the menu item ‘Tools’ and select the first item: ‘Create a form’. This instantly creates a scaffolding for the new web form. A new sheet is added to the spreadsheet, where all the submitted data will be stored. And a new page is opened for editing the new form. With the new Google Forms it looks something like this:

With this user interface we can easily add more questions. We could add date-pickers and put constraints on the answers that are allowed. All the details on how to do things like that to build an optimal form with low probability of misunderstandings and errors can be found on Google Help. When you’re finished and try submitting the form with the first test data, it ends up in the new sheet like this:

So how do we get this data into the template, create a PDF and send it via email? Going back to the VIP example, the helper would do the following steps manually:

1. Copy the data into the proper fields on the template
2. Generate a PDF for the filled form
3. Send the PDF to the email address of the VIP
4. Clean the template again

So this is what we want to automate, as you don’t need much brain to do that, once you know how it’s done.

The automation with a script

Google has a tool for that kind of automation: Google Script — it allows you to write Javascript with Google specific extensions to do more than the regular stuff in Google spreadsheet. To add a script to our sheet we go to ‘Tools’ and this time choose ‘Script editor...’ — the window that opens looks like this:

This is a new Google Script file with extension .gs that is accessible in your spreadsheet. We need to replace the function with the one that we want to build: sendFilledFormByEmail(). It might look like this:

This is an ugly hack. But good enough for a proof of concept. Let’s have a closer look at each part.

function sendFormByEmail(e) { 
   // Initialize   
   var ss = SpreadsheetApp.getActiveSpreadsheet();   
   var sheets  = ss.getSheets();   
   var sheet   = SpreadsheetApp.getActiveSheet();   
   var line    = sheet.getLastRow();

In this part, we’re creating the local domain language for the objects we want to deal with: The spreadsheet, the whole range of sheets within the spreadsheet, the sheet that is currently active which at script-runtime is always the sheet with the form submission data, and the line of the latest form submission, which is the data we currently want to use for building the PDF. Here is the second section:

// Fill in form template   
sheets[1].getRange("C5").setValue(
   sheet.getRange(line, 3).getValue()
);

This is just an example for how to copy data to the right field. With sheet.getRange(line, 3) we get the data in the third column of the latest form submission, and with sheets[1].getRange(“C5”).setValue() we designate we want to enter it on the sheet with the template in the cell C5. There could also be more complexity added, like validations, algorithms, lookup-tables, etc. but for our example copying is sufficient.

The next step is to create a PDF with the filled in template:

This is an adapted form of a script example from Amit Agarwal. It builds a URL for receiving the proper PDF file, authenticates as the script and then fetches the PDF with the proper authentication. The key is, to specify sheets[1] where 1 is the id of the sheet with the template, so we don’t have all the other sheets or even the other form responses in the PDF that we sent out via email.

Mostly taken from the same script example, we finally create the email to be sent out with proper attachment, subject, message and email-address. When the form is used within a company gmail account, the email-address can be obtained by activating automatic collection of the submitter by enabling that feature in the configuration of the Google Form. Otherwise, the form needs an extra field asking for the email address the PDF should be sent to. Alternatively, putting a static email address will result in this email always being sent to the same recipient. And then it is sent in the last line.

But wait, we forgot to clean up the template. That’s why we need to do this somewhere after generating the PDF:

// Clean template   
sheets[1].getRange("C5").clearContent();

Essentially, we want to reset all the fields that we touched while filling out the template. If data was copied into an empty field, .clearContent() for that field is enough.

So that’s our script.

The workflow

By now we have a template, a form, a response sheet and a script. But even though Google knows they are somewhat connected, not all of them are really connected. Submitting the form enters the data in the response sheet, but nothing else happens. What we want is, that whenever the form is submitted, the script should run once.

For this we need to look at the menu bar in our Script editor, go to ‘Resources’ and select ‘Current project triggers...’ — this will open a dialog like this:

We click to add one and we get:

We can translate our goal to this UI language like this:

In the ‘notifications’ section, we can also add an email address of a person to be notified about the script encountering some problem — either immediately or on a scheduled basis. So, let’s save and submit the form once again.

There we go. Within seconds I get the email to the gmail account I was logged on while filling out the form, it has the subject and message from the script, and attached is the PDF file with the sheet of the filled out template.

The iteration

Are we done yet? No. This is a proof of concept. It works in the most basic way it can work. This is the beginning, not the end of it. Now begins the time of evaluating all the assumptions, testing all the different environments, tweaking all the wordings, individualising for different use cases, hardening against multiple concurrent users, smartening up the script, making it reliable, extendable, reusable, secure, and most important of all: more user friendly.

But the first step is done:

As an employee of my company I can fill out my travel expenses form properly so I can get reimbursed for my business lunch.

Surely, we could have that in a much easier way by just asking somebody for help doing it manually. But then we would face the same problem again and again. And so would others. By attacking the root cause of the problem and putting automation on it where possible, we made the process simpler and faster and less error prone. It made this process both more effective and more efficient. The cost we have to pay is the construction time and the maintenance time. The gain can be calculated by estimating how many people do it how often and how much time does it cost every single time. So if it is worth doing this depends on the situation — just like in this xkcd:

via https://xkcd.com/399

The conclusion

In a perfect world, we would not face unindividualised templated print forms anymore at all. But we, as a society, are not quite there yet. Even if technology already would allow us to do much better than what we are doing right now. So let us put more patches on things that are not ideal and help each other to get more free time to tackle the root problems we have. And also let us learn how to ‘hack’ the world, so that technology and bureaucracy are serving us, and not the other way around. Because at the end of the day, all we want to have dealt with are the important questions of our time. And not the trivial annoying ones that no one will ever remember.