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

Explore

Syncing movie data from a constantly refreshing Google Sheet of TV shows and movies
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.

How to keep your Coda doc up-to-date with a Google Apps Script-refreshed Google Sheet.

Camille Nussbaum

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

this one⁠

as an example. Every TV show or move has a unique id called show_id:

⁠

⁠

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:

⁠

⁠

We originally wanted to use this

Google Apps Script⁠

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

Google Apps Script⁠

You can follow the instructions

here⁠

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_DOC_ID = 'YOUR_CODA_DOC_ID'

TARGET_TABLE_ID = 'YOUR_CODA_TABLE_ID'

TARGET_ROW_ID_COLUMN = 'Coda Row ID' // You cannot have a column in your Coda table with this name

// Sheets Settings

SOURCE_SHEET_ID = 'YOUR_GOOGLE_SHEET_ID'

SOURCE_WORKSHEET_NAME = 'YOUR_GOOGLE_SHEET_NAME'

SOURCE_SHEET_SOURCE_ROW_COLUMN = 'YOUR_GOOGLE_SHEET_UNIQUE_COLUMN_ID'

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

All Netflix shows/movies⁠

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

Stages⁠

Stage

Detail

Not started

No movement yet

Acquiring Rights

Working with owner of property to acquire rights.

Processing

Internal processing to get show/movie ready for streaming.

Ready for Marketing

Show is ready to be merchandised on the platform.

Live

Live for customers to stream.

There are no rows in this table

⁠

Each of the filtered views in the

Titles by stage⁠

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:

⁠

⁠

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