How to sync data between Coda docs (and Google Sheets) using Google Apps Script

Original Coda -> Coda Script

Explore

Original Coda -> Coda Script

Original script one-way sync script from https://coda.io/@oleg/getting-started-guide-coda-api

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 each (var source in SOURCE_TABLES) { //If you are using the V8 runtime delete "each" from this line

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 each (var x in a) { //If you are using the V8 runtime delete "each" from this line

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

result.push(x);

}

return result;

}

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.