Enhanced Google Sheets integration for Docassemble
by Andrew McMaster | Sep 16, 2022
Are you a Docassemble developer in need of a robust integration with Google Sheets? You've come to the right place!
The problem
DocuMoose had a client who needed to collect detailed monthly reports from dozens of branches of their organization. Historically, they used an online form that collated data into a very wide spreadsheet. Then they manually manipulated the spreadsheet to create summary reports. The process required several days each month.
Our solution
We proposed a Docassemble interview that would collect the data, write to Google Sheets, and then present summary reports in Google Data Studio.
Docassemble‘s documentation includes a basic code sample for writing data to Google Sheets. It was a great starting point, but the data we needed to collect was too complex and too extensive for a basic integration. So we wrote a custom Python module, documoose-google-sheets, which we've made available on Github.
Features
In addition to writing Docassemble data to Google Sheets, documoose-google-sheets has the following features:
Refer to spreadsheet by id. Rename your spreadsheet at any time!
Post to different tabs by tab id. Add new tabs, move them around, rename them!
Post row updates in batches. We use the low-level batch_update method to make many edits at once, reducing API calls and greatly increasing overall speed. For our application, it was a 20X performance gain.
Write to correct cell even if column is moved (column name lookup). Reorder your columns at any time!
Accept objects and explode to multiple columns. Each object property becomes a column name in the format objectName[propertyName].
Set datetimes. Datetimes are written properly to Google Sheets as datetimes, not text.
Try out a demo to see an example of documoose-google-sheets in action.
Code
Here's the code. Head over to the documoose-google-sheets repo on Github for information on installation, usage, and an example for you to try.
Results
Our client has now been using Docassemble-powered data collection for six months. Dozens of branches complete their monthly reports, the data feeds to Google Sheets, and then beautiful summary charts appear in real time in Google Data Studio. Most importantly, it's saving our client several days each month!
Big thanks to Jonathan Pyle, the Docassemble creator who provided the original Google Sheets code sample and who continues to support the excellent Docassemble community on Slack.