Skip to content
Gallery
Syncing movie data from a constantly refreshing Google Sheet of TV shows and movies
Syncing movie data from a constantly refreshing Google Sheet of TV shows and movies
Titles by stage
Search titles
Data
More
Share
Explore
Syncing movie data from a constantly refreshing Google Sheet of TV shows and movies

icon picker
Google Apps Script

// Sync from a constantly updating Google Sheet to Coda
// Last Updated: April 9th, 2021
// 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_CODA_API_KEY')

// Coda settings
TARGET_DOC_ID = 'YOUR_CODA_DOC_ID'
TARGET_TABLE_ID = 'YOUR_CODA_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_GOOGLE_SHEET_ID'
SOURCE_WORKSHEET_NAME = 'YOUR_GOOGLE_SHEET_NAME'
SOURCE_SHEET_SOURCE_ROW_COLUMN = 'YOUR_GOOGLE_SHEET_UNIQUE_COLUMN_ID'

// 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);
for (var i = 0; i < codaHeaderRow.length; i++) { if (codaHeaderRow[i]['name'] == SOURCE_SHEET_SOURCE_ROW_COLUMN) { var codaRowURLIndex = i; }} // Get index of lookup column in Coda header

// 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 sheet IDs 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'][codaRowURLIndex]['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'][codaRowURLIndex]['value']) != -1) {
diffRow.push(targetRow['cells'].slice(-2)[0]['value'])
//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'], body);
})
Logger.log('::::: %s values changed in Google 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'], codaRowURLIndex);
var deletedRows = findDeletedRows(allRows['sourceRows'], allRows['targetRows']);
}
else {
targetRowURLs = [];
deletedRows = [];
}
var sourceRows = findNewRows(allRows['sourceRows'], targetRowURLs);
}
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.