Skip to content
How to extract data from tables in multiple Google Docs and combine into one table in Coda
How to extract data from tables in multiple Google Docs and combine into one table in Coda
Google Doc -> Coda Script
More
Share
Explore

icon picker
Google Doc -> Coda Script

// 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}]})
}
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.