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

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_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
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
):

Stage
Detail
1
Not started
No movement yet
2
Acquiring Rights
Working with owner of property to acquire rights.
3
Processing
Internal processing to get show/movie ready for streaming.
4
Ready for Marketing
Show is ready to be merchandised on the platform.
5
Live
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 (
CtrlP
) instead.