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 https://console.developers.google.com 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:
- the name of the file containing the data
- 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:
# Parse argument
parser = argparse.ArgumentParser(description='Process some integers.')
parser.add_argument('data_file', metavar='F', type=str,
help='path to the data file')
parser.add_argument('n_invoice', metavar='N', type=int,
help='invoice to print')
args = parser.parse_args()
# Load the inboice and select the one to process
invoices = load_invoices(args.data_file)
if not str(args.n_invoice) in invoices:
print("Unknown invoice %i. Known invoices: %s" % (args.n_invoice, invoices.keys()))
return
invoice = invoices[str(args.n_invoice)]
invoice['number'] = args.n_invoiceAn example of data file:
{
"11" : {
"date": {
"day":11,
"month":"April",
"year":2016
},
"noVAT": true,
"client": {
"name": "Pinco",
"address": "Via Foo",
"vatID": "FOO123",
"contact": "Mr. Pallo"},
"lines":[
{"description": "Stuff done", "amount":128.34, "vatRate":20.0},
{"description": "Other Stuff", "amount":80.0, "vatRate":20.0},
{"description": "Third line", "amount":85.0, "vatRate":20.0}
]
}
}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:
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:
# find the 'DriveInvoicing' directory and look for the file 'Template' inside it
credentials = get_credentials()
http = credentials.authorize(httplib2.Http())
drive_service = discovery.build('drive', 'v3', http=http)
folder_id = get_folder(drive_service, 'DriveInvoicing')['id']
template_id = get_content(drive_service, 'Template', folder_id)['id']
...
...
def get_content(service, file_name, folder_id):
page_token = None
res = []
while True:
response = service.files().list(q=" '%s' in parents and name = '%s'" % (folder_id, file_name),
spaces='drive',
fields='nextPageToken, files(id, name, properties)',
pageToken=page_token).execute()
for file in response.get('files', []):
res.append(file)
page_token = response.get('nextPageToken', None)
if page_token is None:
break;
if len(res) != 1:
raise Exception("File not found %s (res=%s)" % (file_name, res))
return res[0]
def get_folder(service, folder_name):
page_token = None
res = []
while True:
response = service.files().list(q="mimeType = 'application/vnd.google-apps.folder' and name = '%s'" % folder_name,
spaces='drive',
fields='nextPageToken, files(id, name, properties)',
pageToken=page_token).execute()
for file in response.get('files', []):
res.append(file)
page_token = response.get('nextPageToken', None)
if page_token is None:
break;
if len(res) != 1:
raise Exception("Folder not found %s (res=%s)" % (folder_name, res))
return res[0]Copying the template and filling it
First of all we create a copy of the template:
# Copy the template
invoice_doc_id = copy_file(drive_service, template_id, 'Invoice_%i' % invoice['number'], folder_id)['id']Then we execute a Google Script on it:
# Run the script to fill the template
script_service = discovery.build('script', 'v1', http=http)
request = {"function": "insertData", "devMode": True, "parameters": [
invoice_doc_id, invoice['number'], invoice['date'], invoice['noVAT'], invoice['client'], invoice['lines']]}
response = script_service.scripts().run(body=request, scriptId=SCRIPT_ID).execute()
print("Execution response: %s" % str(response))Finally we download the document as a PDF:
# Download the PDF file
download_file_as_pdf(drive_service, invoice_doc_id, 'Invoice_%i.pdf' % invoice['number'])
...
...
...
def download_file_as_pdf(service, file_id, file_name):
download_file_as(service, file_id, 'application/pdf', file_name)
def download_file_as(service, file_id, media_type, file_name):
request = service.files().export_media(fileId=file_id, mimeType=media_type)
fh = io.FileIO(file_name, mode='wb')
downloader = MediaIoBaseDownload(fh, request)
done = False
while done is False:
status, done = downloader.next_chunk()
print("Download %d%%." % int(status.progress() * 100))The script which fill the data is this:
function findContainingTable(element) {
if (element.getType() == 'TABLE') {
return element;
}
var parent = element.getParent()
if (parent) {
Logger.log(parent);
return findContainingTable(parent);
}
}
function findContainingTableRow(element) {
if (element.getType() == 'TABLE_ROW') {
return element;
}
var parent = element.getParent()
if (parent) {
Logger.log(parent.getType());
return findContainingTableRow(parent);
}
}
function findPlaceholder(element, placeholder) {
if (element.getNumChildren !== undefined) {
for (var i=0;i<element.getNumChildren();i++) {
var child = element.getChild(i);
//Logger.log(child.getType());
if (child.getType() == 'PARAGRAPH') {
//Logger.log(child.getText());
if (child.getText().indexOf(placeholder) > -1) {
return child;
}
}
var res = findPlaceholder(child, placeholder);
if (res) {
return res;
}
}
}
return null;
}
function to2decimal(num) {
return Math.round(num * 100) / 100;
}
function num2str(value) {
var decimals = 2;
return value.toFixed(decimals);
}
function insertData(documentId, invoiceNumber, date, noVat, client, lines, currency, paymentDays) {
var body = DocumentApp.openById(documentId).getBody();
body.replaceText('#{N}', invoiceNumber);
body.replaceText('#{DD}', date.day);
body.replaceText('#{MM}', date.month);
body.replaceText('#{YY}', date.year);
body.replaceText('#{clientName}', client.name);
body.replaceText('#{clientAddress}', client.address);
body.replaceText('#{clientVatID}', client.vatID);
body.replaceText('#{clientContact}', client.contact);
Logger.log("START lines="+lines);
var placeholder = findPlaceholder(body, '#{lineDescription}');
Logger.log("res="+placeholder);
var table = findContainingTable(placeholder);
Logger.log("table="+table);
var totalAmount = 0.0;
var totalVAT = 0.0;
var totalTotal = 0.0;
for (var i=lines.length;i>0;i--) {
var tableRow = findContainingTableRow(placeholder);
if (i!=1) {
Logger.log("inserting at "+(lines.length-i+1));
tableRow = table.insertTableRow(lines.length-i+1, tableRow.copy());
}
var line = lines[lines.length - i];
tableRow.replaceText('#{lineDescription}', line.description);
tableRow.replaceText('#{lineAmount}', num2str(line.amount));
var vat = to2decimal(line.amount * (line.vatRate/100.0));
tableRow.replaceText('#{lineVAT}', num2str(vat));
tableRow.replaceText('#{lineTotal}', num2str(line.amount + vat));
totalAmount += line.amount;
totalVAT += vat;
totalTotal += line.amount + vat;
}
body.replaceText('#{totalAmount}', num2str(to2decimal(totalAmount)));
body.replaceText('#{totalVAT}', num2str(to2decimal(totalVAT)));
body.replaceText('#{totalTotal}', num2str(to2decimal(totalTotal)));
body.replaceText('#{currency}', currency || 'Euro');
body.replaceText('#{paymentDays}', paymentDays || '15');
if (!noVat) {
var par = findPlaceholder(body, 'Value added tax levied');
par.clear();
}
}This is created in the online editor for Google Scripts:
What we got
This is the final result:
ENOUGH, GIMME THE CODE!
Code is available on GitHub: https://github.com/ftomassetti/DriveInvoicing
| Reference: | A template system for Google Docs: Google Drive automation and PDF generation with Google Execution API from our WCG partner Federico Tomassetti at the Federico Tomassetti blog. |
