Skip to content
Syncing movie data from a constantly refreshing Google Sheet of TV shows and movies

icon picker
Syncing movie data from a constantly refreshing Google Sheet of TV shows and movies

How to keep your Coda doc up-to-date with a Google Apps Script-refreshed Google Sheet.
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 show_id:
Image 2021-04-09 at 2.58.33 PM.jpg
And every night the cells in this Google Sheet get deleted and overwritten with brand new data (including the show_id 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:
Screen Recording 2021-04-09 at 03.25.21 PM.gif
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:
Image 2021-04-09 at 3.20.35 PM.jpg
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 constant. As I mentioned earlier, our Google Sheet gets wiped out every night including the unique id (show_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 SOURCE_SHEET_SOURCE_ROW_COLUMN needs to be the unique ID in your Google Sheet. In our case, it’s the show_id column.
// Coda settings
TARGET_ROW_ID_COLUMN = '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 ):
Not started
No movement yet
Acquiring Rights
Working with owner of property to acquire rights.
Internal processing to get show/movie ready for streaming.
Ready for Marketing
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 Project Owner, Project Stage, and Est. Cost are the columns that matter for our team and our workflow. These columns don’t show up anywhere in the database:
Image 2021-04-09 at 3.38.41 PM.jpg
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 Project Stage.
- Search all the TV shows/Movies in our main database.
👉 Start with:

Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
) instead.