Skip to content
How to extract data from tables in multiple Google Docs and combine into one table in Coda
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 settingsTARGET_DOC_ID = 'YOUR_CODA_DOC_ID'TARGET_TABLE_ID = 'YOUR_CODA_TABLE_ID'GOOGLE_DOC_COL_NAME = 'Google Doc Link'
// Google Drive folder settingsGOOGLE_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 tablefunction 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_IDfunction 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 Docfunction 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 tablefunction 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.