Your team may have a traditional internal database, and you may want to add your own attributes to the rows of data in this database. The issue is that your data team won’t just let anyone arbitrarily add new columns to the database, so you ask the team to do a daily dump of the data into a Google Sheet. But what do you do with that data?
At this point, my team wanted to sync the data to a Coda doc and more importantly, keep the data in Coda synced with what’s in the Google Sheet. Here’s how we keep our data synced and add our own columns in Coda
to see our data the way we want to see it
The Problem: A constantly updating Google Sheet
Every night, we have a script that dumps a bunch of movie and TV show data into a Google Sheet. Take a look at
as an example. Every TV show or move has a unique id called
And every night the cells in this Google Sheet get deleted and overwritten with brand new data (including the
column) so that we have the most updated data in the Google Sheet—even when the genre or description of the title changed in the database. To give you a visual, this is what happens every night to our Google Sheet:
Even if only one cell changes, the Google Sheet gets completely erased and the data gets written again. How do we ensure that our data in Coda stays up to date with this ever-changing Google Sheet? This is where Google Apps Script game into play as a potential solution.
The Solution: A daily script that syncs data over to Coda
To give you a recap of our workflow:
We originally wanted to use this
to sync data from our Google Sheet to Coda, but that script assumes that the unique ID in your Google Sheet stays
. As I mentioned earlier, our Google Sheet gets wiped out every night including the unique id (
So we altered the Google Apps Script to allow for a constantly updating Google Sheet where the unique ID gets overwritten every day. Check out the script in
You can follow the instructions
on setting up the script, but there is one customization you need to make to the script depending on the unique ID in your Google Sheet. The Coda and Sheets settings are all custom for you, but the
needs to be the
in your Google Sheet. In our case, it’s the
// Coda settings
'Coda Row ID'
// You cannot have a column in your Coda table with this name
// Sheets Settings
Why this solution works for us: Custom columns map to IDs in our database
I want to make sure our team is always operating on a single source of truth (the data from our internal database) and also have the flexibility to tag and add additional attributes to the data that make sense for us.
The Google Sheet syncs data over to the
table in this doc. This template is all about managing each TV show or movie as a “project” that moves through different stages (you can see the stages in
Working with owner of property to acquire rights.
Internal processing to get show/movie ready for streaming.
Show is ready to be merchandised on the platform.
Live for customers to stream.
There are no rows in this table
Each of the filtered views in the
simply filters the entire list of TV shows and movies to the “stage” the title is in. In the screenshot below, you’ll see that the
are the columns that
matter for our team and our workflow
. These columns don’t show up anywhere in the database:
With this script, the custom columns we added in our Coda table will
always map to a unique ID in our Google Sheet
(and therefore our database), which means we can add as many custom columns as we want to our Coda table knowing that:
Our Coda table will always have the most up-to-date data from our Google Sheet
Our teams can continue to edit the custom columns of data I added to do their work
What’s in this template
- The main table of data we keep up to date from our Google Sheet using the script in
- View the TV Shows/Movies data filtered by the
- Search all the TV shows/Movies in our main database.