Skip to content
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 = [];
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.