Skip to content
Gallery
How to sync data from Coda to Google Sheets (and vice versa) with Google Apps Script tutorial
How to sync data from Coda to Google Sheets (and vice versa) with Google Apps Script tutorial
Coda -> Google Sheets
Google Sheets -> Coda
More
Share
Explore

icon picker
Google Sheets -> Coda

One-way data sync from a Google Sheets worksheet to a table in a Coda doc
// One-way data sync from Google Sheets to Coda using Google Apps Script
// Author: Al Chen (al@coda.io)
// Last Updated: July 16, 2020
// Notes: Assumes you are using the V8 runtime (https://developers.google.com/apps-script/guides/v8-runtime)
// Coda's library for Google Apps Script: 15IQuWOk8MqT50FDWomh57UqWGH23gjsWVWYFms3ton6L-UHmefYHS9Vl

//////////////// Setup and global variables ////////////////////////////////

CodaAPI.authenticate('YOUR_API_KEY')

// Coda settings
TARGET_DOC_ID = 'YOUR_TARGET_DOC_ID'
TARGET_TABLE_ID = 'YOUR_TARGET_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_SOURCE_GOOGLE_SHEETS_ID'
SOURCE_WORKSHEET_NAME = 'YOUR_SOURCE_GOOGLE_SHEETS_WORKSHEET_NAME'
SOURCE_SHEET_SOURCE_ROW_COLUMN = 'YOUR_SOURCE_ROW_URL_COLUMN_NAME' // Column name in Google Sheets to store source row URLs from Coda

// Optional Settings
DO_NOT_DELETE_COLUMN = 'Do not delete' // If you want to add rows directly to Coda table without rows getting deleted on sync. Must be a checkbox column in your Coda table and cannot exist in Google Sheets.
REWRITE_CODA_TABLE = false // Set as true if you want the sync to completely overwrite the Coda table each sync
////////////////////////////////////////////////////////////////////////////

fromSpreadsheet = SpreadsheetApp.openById(SOURCE_SHEET_ID);
fromWorksheet = fromSpreadsheet.getSheetByName(SOURCE_WORKSHEET_NAME);
sheetsHeaderRow = fromWorksheet.getDataRange().offset(0, 0, 1).getValues()[0];
codaHeaderRow = CodaAPI.listColumns(TARGET_DOC_ID, TARGET_TABLE_ID).items;
codaTableName = CodaAPI.getTable(TARGET_DOC_ID, TARGET_TABLE_ID).name;
rowURLIndex = sheetsHeaderRow.indexOf(SOURCE_SHEET_SOURCE_ROW_COLUMN);

// Run main sync functions
function runSync() {
sheetsPermissions();
addDeleteToCoda();
if (!REWRITE_CODA_TABLE) {
updateCoda();
}
}

// Determine if you have edit or view access to to the Google Sheet
function sheetsPermissions() {
try {
fromSpreadsheet.addEditor(Session.getActiveUser());
}
catch (e) {
REWRITE_CODA_TABLE = true; // If no access automatically rewrite Coda tables each sync
}
}

// Updates existing rows in Coda if any changes in Sheets
function updateCoda() {
var matchingRows = [];
var diffRowURLs = [];
var diffRows = [];
var cleanedSourceRows = [];
var allRows = prepRows();
var sortedSourceRows = allRows['sourceRows'].sort(sortArray);
var targetRows = allRows['targetRows'];

// Remove rows with empty source URLs in sortedSourceRows
for (var i = 0; i < sortedSourceRows.length; i++) {
if (sortedSourceRows[i][rowURLIndex].length != 0) {
cleanedSourceRows.push(sortedSourceRows[i]);
}
}
// Get relevant target rows with columns that match Sheets columns
var cleanedTargetRows = targetRows.map(function(row) {
var cells = row['cells'].map(function(cell) {
if (sheetsHeaderRow.indexOf(cell['column']) != -1) {
return {
column: cell['column'],
value: cell['value'],
}
}
})
return { //filter out undefined cells
cells: cells.filter(function(x) {
return x !== undefined;
})
}
})
// Find rows in Sheets only if it exists in Coda
cleanedSourceRows.map(function(row) {
var rowURL = row[rowURLIndex];
cleanedTargetRows.map(function(targetRow) {
if (targetRow['cells'].slice(-1)[0]['value'] == rowURL) {
matchingRows.push(targetRow)
}
})
})
var convertedMatchingRows = convertValuesForSheets(matchingRows);
// Create array of rows that need to be updated in Coda
for (var i = 0; i < cleanedSourceRows.length; i++) {
sourceRowURL = cleanedSourceRows[i][rowURLIndex];
for (var j = 0; j < cleanedSourceRows[0].length; j++) {
if (cleanedSourceRows[i][j].length != convertedMatchingRows[i][j].length) {
if(diffRowURLs.indexOf(sourceRowURL) == -1) { diffRowURLs.push(sourceRowURL); }
}
else if (cleanedSourceRows[i][j] != convertedMatchingRows[i][j]) {
if(diffRowURLs.indexOf(sourceRowURL) == -1) { diffRowURLs.push(sourceRowURL); }
}
}
}
// Get full rows from source that have changes
cleanedSourceRows.map(function(row) {
if (diffRowURLs.indexOf(row[rowURLIndex]) != -1) {
diffRows.push(row);
}
})
// Get row IDs from target and splice into diffRows
targetRows.map(function(targetRow) {
diffRows.map(function(diffRow) {
if (diffRow[rowURLIndex].indexOf(targetRow['cells'].slice(-1)[0]['value']) != -1) {
diffRow.splice(-1, 0, targetRow['cells'].slice(-2)[0]['value'])
}
})
})
// Convert diffRows into format for Coda
var updateTargetRows = sortSheetsTableCols(diffRows, true);
// Update row in Coda
updateTargetRows.map(function(row) {
var body = {
row: { cells: row['cells'] }
}
CodaAPI.updateRow(TARGET_DOC_ID, TARGET_TABLE_ID, row['rowID'][0], body);
})
Logger.log('::::: %s values changed in Gogole Sheets => Updating "%s" in Coda...', updateTargetRows.length, codaTableName);
}

// Append new data from Sheets to a Coda table and delete any rows from the Coda table if in Google Sheets
function addDeleteToCoda() {
var allRows = prepRows();
if (REWRITE_CODA_TABLE) {
deleteAllTargetRows(allRows['targetRows']);
var sourceRows = allRows['sourceRows'];
}
else {
if (allRows['targetRows'].length > 0) {
var targetRowURLs = getTargetRowValues(allRows['targetRows'], -1);
var deletedRows = findDeletedRows(allRows['sourceRows'], allRows['targetRows']);
}
else {
targetRowURLs = [];
deletedRows = [];
}
var sourceRows = findNewRows(allRows['sourceRows'], targetRowURLs);
}
// Add rows to Coda only if new rows exist
if (sourceRows.length != 0) {
Logger.log('::::: Adding %s new rows from Google Sheets => "%s" in Coda...', sourceRows.length, codaTableName);
var sortedSourceRows = sortSheetsTableCols(sourceRows)
var timer = 0;
for (var i = 0; i < sortedSourceRows.length; i += 500) {
CodaAPI.upsertRows(TARGET_DOC_ID, TARGET_TABLE_ID, {rows: sortedSourceRows.slice(i, i + 500)});
}
if (!REWRITE_CODA_TABLE) {
var currentCodaRows = retrieveRows();
// Time delay to wait for Coda API to propagate rows into Coda. Times out after 30 seconds.
while(currentCodaRows.length < allRows['targetRows'].length + sourceRows.length) {
timer += 2;
if (timer == 60) { break; }
Utilities.sleep(2000);
currentCodaRows = retrieveRows();
}
//Write new Source Row URLs to Google Sheets
var rowURLs = [];
currentCodaRows.slice(0, sourceRows.length).map(function(row) { // Only look at the number of new rows in the Sheet
rowURLs.push(row['cells'].slice(-1)[0]['value']);
})
fromWorksheet.getRange(allRows['sourceRows'].length - sourceRows.length + 2, rowURLIndex + 1, rowURLs.length, 1).setValues(convertValues(rowURLs));
}
}
if (!REWRITE_CODA_TABLE) {
// Remove deleted rows
if (deletedRows.length != 0) {
Logger.log('::::: %s deleted rows in Sheets => Deleting these row in "%s" in Coda...', deletedRows.length, codaTableName);
var body = {
'rowIds': deletedRows,
};
CodaAPI.deleteRows(TARGET_DOC_ID, TARGET_TABLE_ID, body);
}
}
}

// Delete all rows in target
function deleteAllTargetRows(targetRows) {
var rowIDs = [];
targetRows.map(function(row){
rowIDs.push(row['cells'].slice(-2)[0]['value']);
})
var body = { 'rowIds': rowIDs, };
CodaAPI.deleteRows(TARGET_DOC_ID, TARGET_TABLE_ID, body);
}

// Pre-processing step for retrieving/cleaning rows from source and target
function prepRows() {
var targetRows = retrieveRows();
var sourceRows = getSheetValues();
sourceRows.shift(); // Remove header row from Sheets range
return {targetRows: targetRows, sourceRows: sourceRows}
}

function findDeletedRows(sourceRows, targetRows) {
var deletedRowIDs = [];
var sourceRowURLs = [];
var deleteColNum;
sourceRows.map(function(row) {
sourceRowURLs.push(row[rowURLIndex]);
});
for (var i = 0; i < codaHeaderRow.length; i++) {
if (codaHeaderRow[i]['name'] == DO_NOT_DELETE_COLUMN) {
deleteColNum = i;
}
}
targetRows.map(function(row) {
if (deleteColNum == null) {
if (sourceRowURLs.indexOf(row['cells'].slice(-1)[0]['value']) == -1) {
deletedRowIDs.push(row['cells'].slice(-2)[0]['value'])
}
}
else {
if (sourceRowURLs.indexOf(row['cells'].slice(-1)[0]['value']) == -1 && row['cells'][deleteColNum]['value'] != true) {
deletedRowIDs.push(row['cells'].slice(-2)[0]['value'])
}
}
})
return deletedRowIDs;
}

// Finds new rows in Sheets table to sync
function findNewRows(sourceRows, targetRowURLs) {
var newRows = [];
sourceRows.map(function(row) {
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.