Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Takes all your Google Docs in a Google Drive folder, takes the first table in each Google Doc, and syncs that data to a table in Coda.
// Google Apps Script for one-way data sync from a table in a Google Doc to Coda table from multiple Google Docs in a Google Drive folder
// Author: Al Chen (al@coda.io)
// Last Updated: August 6th, 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_CODA_DOC_ID'
TARGET_TABLE_ID = 'YOUR_CODA_TABLE_ID'
GOOGLE_DOC_COL_NAME = 'Google Doc Link'
// Google Drive folder settings
GOOGLE_DRIVE_FOLDER_ID = 'YOUR_GOOGLE_DRIVE_FOLDER_ID'
////////////////////////////////////////////////////////////////////////////
function runSync() {
var fileCounter = 0
var currentGoogleDocIds = currentFileIds()
var files = getDriveFiles().map(function(file) {
if (currentGoogleDocIds.indexOf(file['id']) == -1) {
fileCounter += 1
var row = getRows(file)
addRowToCoda(row)
Logger.log(file['name'] + " has been added to Coda.")
}
})
Logger.log(fileCounter + " Google Docs have been synced.")
}
// Get existing Google Doc IDs from Coda table
function currentFileIds() {
var fileIds = CodaAPI.listRows(TARGET_DOC_ID, TARGET_TABLE_ID, {useColumnNames: true}).items.map(function(row) {
return row['values'][GOOGLE_DOC_COL_NAME].split("https://docs.google.com/document/d/")[1]
})
return fileIds
}
// Get all Google Docs from a Google Drive folder specified in GOOGLE_DRIVE_FOLDER_ID
function getDriveFiles() {
var filesArray = []
var folder = DriveApp.getFolderById(GOOGLE_DRIVE_FOLDER_ID)
var files = folder.getFiles()
while (files.hasNext()) {
var file = files.next()
var fileId = file.getTargetId() ? file.getTargetId() : file.getId()
filesArray.push({id: fileId, name: file.getName()})
}
return filesArray
}
// Get table from individual Google Doc
function getRows(file) {
var doc = DocumentApp.openById(file['id']);
var body = doc.getBody()
var table = body.getTables()[0]
var numRows = table.getNumRows()
var cellsArray = []
for (var i = 0; i < numRows; i++) {
row = table.getRow(i)
cellsArray.push({column: row.getCell(0).getText(), value: row.getCell(1).getText()})
}
cellsArray.push({column: GOOGLE_DOC_COL_NAME, value: 'https://docs.google.com/document/d/' + file['id']})
return cellsArray
}
// Add one table of data from a Google Doc to Coda table
function addRowToCoda(rowFromFile) {
var sortedRow = []
var targetColumns = CodaAPI.listColumns(TARGET_DOC_ID, TARGET_TABLE_ID).items.map(function(item) {
rowFromFile.map(function(cell) {
if (cell['column'] == item.name) {
sortedRow.push(cell)
}
})
});
CodaAPI.upsertRows(TARGET_DOC_ID, TARGET_TABLE_ID, {rows: [{'cells': sortedRow}]})
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.