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.

High

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!

No results from filter

⁠

4. Appendix

4.1 Working with Formulaic Columns in the Source Table

Formulaic columns are special because you do not assign values to the column on a per-row basis. Instead, you define a formula that applies to all rows in the table.

When you are building a 1-way sync, you have a couple options on how to handle formulaic columns in a source table depending on your needs.

Option 1: Write the values to a non-formulaic destination column

This is a good option if your formulaic column is reliant on data that will not be synced to your destination doc.

Option 2: Do not write values from the formulaic column and instead, recreate the formulaic column in the destination table.

This can be a good option if all the information needed for the formulaic column will be available in the destination document (e.g. will be in columns that are going to be synced) and you want to compute the information formulaically. API writes to formulaic columns are not supported.

4.2 Working with Lookup Columns in the Source Table

Another thing to consider is how you want to handle look up columns within a source table you are syncing. Look up columns cannot reference tables in other docs.

Option 1: Sync the "looked up" table

One option is to also sync the table you are referencing in your lookup column. This is a great option if you are planning to reference the looked-up table and its content in many ways in the destination doc.

If you use the API to write a text value to a look up column, we'll use exact-match to identify the corresponding row in the table you're looking up values from. Here's how to make it work in a one way sync:

In your script, sync the "looked-up table" first and then sync the table with the look up reference.

Make sure that the look up column in the destination doc is configured to a "Lookup from table" and is pointing to the "looked up" table in the destination doc.

Option 2: Write the values to a non-lookup destination column

4.3 Handling deletions

To support deletions in each of the source tables, you can choose whether you want to delete the corresponding row in the target table, or have a new "Deleted" column that you'd mark as checked (and filtered out in your target document to hide these rows). The latter has the benefit that data in the original column will never be lost.

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.