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
Coda -> Google Sheets

One-way data sync from a Coda table to Google Sheets worksheet
// One-way data sync from Coda to Google Sheets using Google Apps Script
// Author: Al Chen (al@coda.io)
// Last Updated: March 6th, 2024
// 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')
SOURCE_DOC_ID = 'YOUR_SOURCE_DOC_ID'
SOURCE_TABLE_ID = 'YOUR_SOURCE_TABLE_ID'
TARGET_SHEET_ID = 'YOUR_GOOGLE_SHEETS_ID'
TARGET_WORKSHEET_NAME = 'YOUR_GOOGLE_SHEETS_WORKSHEET_NAME'
TARGET_SHEET_SOURCE_ROW_COLUMN = 'YOUR_SOURCE_ROW_URL_COLUMN_NAME'

////////////////////////////////////////////////////////////////////////////

toSpreadsheet = SpreadsheetApp.openById(TARGET_SHEET_ID);
toWorksheet = toSpreadsheet.getSheetByName(TARGET_WORKSHEET_NAME);
headerRow = toWorksheet.getDataRange().offset(0, 0, 1).getValues()[0];
rowURLIndex = headerRow.indexOf(TARGET_SHEET_SOURCE_ROW_COLUMN);

// Run main sync functions
function runSync() {
addDeleteToSheet();
updateSheet();
}

// Updates existing rows in Sheet if any changes in Coda table
function updateSheet() {
var matchingRows = [];
var diffRowURLs = [];
var diffRows = [];
var allRows = prepRows();
var sourceRows = allRows['sourceRows'];
var targetRows = allRows['targetRows'];
var sortedTargetRows = targetRows.sort(sortArray);
var targetRowURLs = toWorksheet.getRange(2, rowURLIndex + 1, targetRows.length).getValues().flat();
var editCount = 0;

// Find rows in Coda table only if it exists in Sheets
sourceRows.map(function(row) {
var rowURL = row['cells'].slice(-1)[0]['value'];
if (targetRowURLs.indexOf(rowURL) != -1) {
matchingRows.push(row)
}
})
var sortedMatchingRows = convertValues(sortCodaTableCols(matchingRows)).sort(sortArray)
var numCols = sortedMatchingRows[0].length;

// Create array of rows that need to be updated in Sheets
for (var i = 0; i < sortedMatchingRows.length; i++) {
for (var j = 0; j < numCols - 1; j++) {
if (sortedMatchingRows[i][j] == null) {
continue;
}
else if (sortedMatchingRows[i][j].length != sortedTargetRows[i][j].length) {
if (diffRowURLs.indexOf(sortedMatchingRows[i][rowURLIndex]) == -1) { diffRowURLs.push(sortedMatchingRows[i][rowURLIndex]); }
}
else if (sortedMatchingRows[i][j] != sortedTargetRows[i][j]) {
if (diffRowURLs.indexOf(sortedMatchingRows[i][rowURLIndex]) == -1) { diffRowURLs.push(sortedMatchingRows[i][rowURLIndex]); }
}
}

// Get the full row from source Coda table if one of the row URLs needs updating in the Sheets file
diffRowURLs.map(function(row) {
if (sortedMatchingRows[i][rowURLIndex] == row) {
diffRows.push(sortedMatchingRows[i]);
}
})
}

// Update row in Sheets
diffRows.map(function(row) {
diffRowIndex = targetRowURLs.indexOf(row[rowURLIndex]);
var sheetRow = toWorksheet.getRange(diffRowIndex + 2, 1, 1, numCols).getValues().flat();
for (var i = 0; i < numCols - 1; i++) {
if (row[i] == null) {
continue;
}
else if (row[i] != sheetRow[i]) {
editCount++;
toWorksheet.getRange(diffRowIndex + 2, i + 1, 1).setValue(row[i]);
}
}
})
Logger.log('::::: %s values changed in Coda => Updating "%s" in Google Sheets...', editCount, TARGET_WORKSHEET_NAME);
editCount = 0;
}

// Append new data from Coda table to Sheets and delete any rows from Sheets if in Coda table
function addDeleteToSheet() {
var allRows = prepRows();
if (allRows['targetRows'].length > 0) {
var targetRowURLs = toWorksheet.getRange(2, rowURLIndex + 1, allRows['targetRows'].length).getValues().flat();
var deletedRows = findDeletedRows(allRows['sourceRows'], targetRowURLs);
}
else {
targetRowURLs = [];
deletedRows = [];
}
var sourceRows = findNewRows(allRows['sourceRows'], targetRowURLs);

// Add rows to Sheets only if new rows exist
if (sourceRows.length != 0) {
Logger.log('::::: Adding %s new rows from Coda => "%s" in Google Sheets...', sourceRows.length, TARGET_WORKSHEET_NAME);
var sortedSourceRows = sortCodaTableCols(sourceRows)
var convertedSourceRows = convertValues(sortedSourceRows);
toWorksheet.getRange(toWorksheet.getLastRow() + 1, 1, convertedSourceRows.length, convertedSourceRows[0].length).setValues(convertedSourceRows)
}

// Remove deleted rows
if (deletedRows.length != 0) {
Logger.log('::::: %s deleted rows in Coda => Deleting these row in "%s" in Google Sheets...', deletedRows.length, TARGET_WORKSHEET_NAME);
deletedRows.map(function(row) {
toWorksheet.deleteRow(targetRowURLs.indexOf(row) + 2);
})
}
}

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

function findDeletedRows(sourceRows, targetRowURLs) {
var deletedRows = [];
var sourceRowURLs = sourceRows.map(function(row) {
return row['cells'].slice(-1)[0]['value'];
})
targetRowURLs.map(function(row) {
if (sourceRowURLs.indexOf(row) == -1) {
deletedRows.push(row)
}
})
return deletedRows;
}

// Finds new rows in Coda table to sync
function findNewRows(sourceRows, targetRowURLs) {
var newRows = [];
sourceRows.map(function(row) {
var rowURL = row['cells'].slice(-1)[0]['value'];
if (targetRowURLs.indexOf(rowURL) == -1) {
newRows.push(row)
}
})
return newRows;
}

// Converts Coda table rows to 2D array of values for Sheets
function convertValues(rows) {
var values = rows.map(function(row) {
var rowValues = []
row['cells'].map(function(rowValue) {
rowValues.push(rowValue['value'])
})
return rowValues;
})
return values;
}

// Sort's Coda's table columns by column order in Sheet
function sortCodaTableCols(sourceRows) {
var headerCodaTable = sourceRows[0]['cells'].map(function(row) { return row['column'] });
var sheetsColOrder = [];

headerRow.map(function(col) {
sheetsColOrder.push(headerCodaTable.indexOf(col))
})

var sortedSourceRows = sourceRows.map(function(row) {
var cells = sheetsColOrder.map(function(col) {
if (col == -1) {
return {
column: null,
value: null,
}
}
else {
return {
column: headerCodaTable[col],
value: row['cells'][col]['value'],
}
}
});
return {cells: cells}
})
return sortedSourceRows;
}

// Get values from Sheets
function getSheetValues() {
var values = toWorksheet.getDataRange().getValues();
return values;
}

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.