Skip to content

Instantly share code, notes, and snippets.

@al-codaio
Last active February 18, 2020 00:09
Show Gist options
  • Save al-codaio/310d7d5579f0d07fc33df44c8a5a3f78 to your computer and use it in GitHub Desktop.
Save al-codaio/310d7d5579f0d07fc33df44c8a5a3f78 to your computer and use it in GitHub Desktop.
One-way data sync between Coda tables
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;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment