Dear Analyst #31: Writing Google Apps Scripts to sync data from Coda to Google Sheets

Podcast: Play in new window | Download

Subscribe: Spotify | TuneIn | RSS

I worked on a “small” side project recently to sync data between Google Sheets and tables in Coda. The full blog post tutorial is here, and the GitHub repository is here. I started using Google Apps Script last year and it’s a super powerful way to connect different apps you use in the G Suite ecosystem. The impetus for creating these two scripts was seeing a few people in the Coda community talk about syncing data between their Google Sheets and Coda. The big caveat is that these are only one-way syncs, but there are several use cases where doing this could be useful in business workflows and making your team more productive.

Writing a script in Google Apps Script

Some Google Apps scripts can be super simple to set up. See this pretty simple workflow below of sending email automatically when there is data in your Google Sheet:

Most of the “work” with writing these scripts was transforming data so that the model in Google Sheets matches the model in Coda as per Coda’s API. Once that data munging is done, the rest of the script was relatively easy in terms of giving users the ability to add, delete, and modify data. I would highly recommend taking a look at Google Apps Script especially if you use a lot of Google Sheets. You’ll be able to connect your Google Sheet with other applications in G Suite and other 3rd-party apps you use for work.

Use cases for syncing data between Coda and Google Sheets

This comes straight from the blog post, but thought it was worth repeating again:

Data synced from your Google Sheet

  • HR & recruiting – All your candidates are stored in a Google Sheet but you want to be able to move candidates through different stages in the interviewing pipeline and Google Sheets isn’t sufficient for your needs. Having all your candidates in a table in Coda means you can use templates like this one to manage candidates more effectively.
  • E-commerce and ERP – Orders, customers, and POs may all be different tabs in a Google Sheet that gets updated through Shopify or some other e-commerce platform. In order to manage your e-commerce business, you may want to see charts, calendar of shipments, and reports that Google Sheets cannot provide easily. Syncing the data from Google Sheets to Coda means you can do ERP properly (see this template as an example).
  • Customer Feedback – You may have a ticketing system like Zendesk or Intercom and all feedback lands in a Google Sheet somewhere. You can do some basic analytics in the Google Sheet but to reply to the feedback means you have to go into Gmail and start replying to customers. If your customer feedback is all in a Coda doc, you can run analytics and send emails using the Gmail Pack (see this template).

Data synced to your Google Sheet

  • 3rd-party vendor reporting – Your vendors may not be using Coda yet, but you have all your vendor data in Coda and need to send them the data in a format they prefer. While you could publish your Coda doc, the vendor still wants the data in a Google Sheet you have edit access to.
  • Data “backup” – Your team may create thousands of rows of data every quarter in a Coda doc and want to start each quarter “fresh.” Coda docs grow with your teams and they may get slow as you add in more functionality, so having a backup of your data in Google Sheets is another reason to sync data from your Coda doc to Google Sheets.
  • Finance & Accounting – Most internal finance and accounting functions still use Excel and spreadsheets for month-end reporting, taxes, and other business-critical activities. As your data grows in Coda, you can keep your finance counterparts in the loop by having your data synced to a Google Sheet which your finance team can use for their reporting and forecasting purposes.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting: