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