A template system for Google Docs: Google Drive automation and PDF generation with Google Execution API

My consulting business is getting more steam and I am starting to be annoyed by the administrative steps. Basically when I need to prepare a new invoice I have to:

  • copy I template I have in my Google drive
  • fill the data
  • download a PDF
  • archive the PDF on Dropbox
  • send the PDF to the customer
  • if the customer is in the European Union (outside France) I need to fill a declaration for the “Douane”. This is what is called an “intrastat” declaration in some places

Now, this is not the most exciting and creative part of the job so I automated some of the process.

Right now I have a script that can create a copy of the template fill it, generate the PDF and download it. I still need to automate the part in which I upload the PDF to Dropbox, but for now I could just copy the PDF in my Dropbox local checkout.

Google Developers Console

Now, this is the boring part and it is easy to miss something. I will try to recollect from memory what I did and wish you good luck. After all I do not want to make things too easy. That would be boring, wouldn’t it?

First, visit and create a project.

Then add permissions to that project, selecting the Google Drive API and the Google Apps Script Execution API.

Finally go to credentials and generate the “OAuth client ID” credentials. You should get a file to download. It is a JSON file containing the credentials for your project.

Good, enough with the boring bits, let’s start to program.

Loading the data

For now the data for the invoices is kept in a simple JSON file. Later I could store it in a Google Spreadsheet document. At that point I could trigger the invoice generation when I add some data to that file.

Right now instead my script take 2 parameters:

  1. the name of the file containing the data
  2. the number of the invoice I want to generate

So the usage scenario is this: first of all I open the data file and add the data for the new invoice. Typically I copy the data from a previous invoice for the same customer and I adapt it. Then I close the file and run the script specifying the number of the new invoice to generate. If I need it I could also regenerate an old invoice just by running the script with the number of that invoice, without the need to touch the data file.

This is the code which parses the arguments and load the data:

An example of data file:


Finding the template and cloning it

In my Google Drive I have a directory named DriveInvoicing which contains a Google Doc named Template. Here it is the first page:

Screenshot from 2016-04-12 21-47-21

The second page contains uninteresting legalese in both French and English: French because I am supposed to write my invoices in French, given that I am located in France. English because most of my clients do not speak any French.

The code to locate the template file is this:

Copying the template and filling it

First of all we create a copy of the template:

Then we execute a Google Script on it:

Finally we download the document as a PDF:

The script which fill the data is this:

This is created in the online editor for Google Scripts:

Screenshot from 2016-04-12 22-02-58

What we got

This is the final result:

Screenshot from 2016-04-12 21-49-22


Code is available on GitHub: