Getting Started Guide: Coda API

Explore

Guides

Building a one-way sync

In this guide, we'll show you how to build a basic one-way sync to push updates from a table in one Coda doc to a table in another Coda doc.

This specific example is centered around a team lead who wants to push updates from an individual project doc to a lead's doc that is tracking a collection of projects. However you can imagine many other uses for a one-way sync either between two Coda docs or a Coda doc and external service. Here are a few:

Push updates from team project docs to an exec dashboard

Fan out milestones from a main table into sub docs

Push a filtered set of updates from an internal doc to a client facing doc

Sync a table in Coda with a table outside of Coda

What you'll learn

How to push additions, deletions, and edits in a table in one doc to another doc on an ongoing basis

Ways to work with special types of columns, including formulaic and lookup columns

⁠

Instructions

1. Setup

For this example, we'll need two tables in two separate docs (a source table and a target table). Let's use "Source Table" table in this document as our source table.

Source Table

Source Table

Task

Complete

Time Estimate

Negotiate 100/day supply of Part X with our vendor

4 hrs

Find 3 candidates for initial testing

3 days

Address quality issues with current chamfer mill

2 days

There are no rows in this table

Count

⁠

For our target table, create a

Target Doc⁠

doc, and copy in this table:

Target Table

Target Table

Task

Complete

Org Priority

Source Row URL

Link all subprojects to this tracker.

There are no rows in this table

Count

⁠

The goal here is to sync all the rows in the source table to the target tables. Columns that are in both tables will get synced, while unique columns in either table won't be affected.

We want to sync the Task and Complete columns from the Source Table into the Target Table (in a second doc). The Time Estimate column is only relevant for the project owner, so we won't be syncing it to the target table.

In the target table though, we'll have an Org Priority column that can be used to set the priority of each of these tasks, without exposing them to the subproject doc.

You may also notice another column at the end: Source Row URL. This column is used as an upsert key to let the sync adapter determine whether a row from the source table is new or existing. You can also click on it to get taken directly to the row in the source doc.

2. Writing the sync adapter

Let's write a

Google Apps Script script⁠

(

v8 runtime⁠

) to sync data from all subtables to the main table. To do this, the script will have to:

Insert rows from the source table that don't have a matching entry in the main table (based on the Source Row column) into the main table

Update any rows that were modified (in the source table) in the main table

Luckily, we can do both of these things with the API's

upsert⁠

endpoint. Without further ado, here's the code:

CodaAPI.authenticate('abcd1234-efgh-5678-ijkl-1234mnop5678'); // Replace with your token.

SOURCE_TABLES = [

{

doc: 'TO UPDATE',

table: 'Source Table',

// Add more as needed.

];

TARGET_TABLE = {

doc: 'TO UPDATE',

table: 'Target Table',

};

TARGET_TABLE_SOURCE_ROW_COLUMN = 'Source Row URL';

/** Run me! */

function oneWaySync() {

for (var source of SOURCE_TABLES) {

syncSpecificTable(source, TARGET_TABLE);

}

// TODO: handle pagination for syncing source tables with >500 items.

function syncSpecificTable(source, target) {

// Get info on the source and target tables.

var sourceTable = CodaAPI.getTable(source.doc, source.table);

var targetTable = CodaAPI.getTable(target.doc, target.table);

Logger.log('::::: Syncing "%s" => "%s"...', sourceTable.name, targetTable.name);

// Find which columns we have to sync.

var sourceColumns = CodaAPI.listColumns(source.doc, source.table).items.map(function(item) { return item.name; });

var targetColumns = CodaAPI.listColumns(target.doc, target.table).items.map(function(item) { return item.name; });

var commonColumns = intersection(sourceColumns, targetColumns);

Logger.log('Syncing columns: %s', commonColumns.join(', '));

// Pull down all the rows in the source table.

var sourceRows = CodaAPI.listRows(source.doc, source.table, {limit: 500, useColumnNames: true}).items;

Logger.log('Source table has %s rows', sourceRows.length);

// Upsert all rows in the source table into the target table.

var upsertBodyRows = sourceRows.map(function(row) {

var cells = commonColumns.map(function(colName) {

return {

column: colName,

value: row.values[colName],

};

});

// Add a URL to the source row in the target, table, which will also be used as the upsert key.

cells.push({column: TARGET_TABLE_SOURCE_ROW_COLUMN, value: row.browserLink})

return {cells: cells};

});

CodaAPI.upsertRows(target.doc, target.table, {rows: upsertBodyRows, keyColumns: [TARGET_TABLE_SOURCE_ROW_COLUMN]});

Logger.log('Updated %s!', targetTable.name);

}

function intersection(a, b) {

var result = [];

for (var x of in a) {

if (b.indexOf(x) !== -1) {

result.push(x);

}

return result;

}

3. Getting it working

Once you've configured the script above with your doc IDs (see

Start Here⁠

if you need a refresher) and table names, run (▶) your oneWaySync() function. If all goes well, you'll see data flow into your table:

⁠

Try making some changes in the source table (you'll have to wait a few seconds for the API to catch up with your changes), and then running it again - you should see your changes sync in shortly.

3.1 Setting up a schedule

Here's the best part! Hit the triggers (

⁠

) button in the toolbar, and you'll see a dialog like the one below. Here you can set your script to run periodically, depending on how often you need it. If any errors ever happen, you'll get an email from Google with the details, and you can review previous script executions via the menus in Google Apps Script.

⁠

☑️ Mark the "Building a one-way Sync" guide as complete!

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.