How to extract data from tables in multiple Google Docs and combine into one table in Coda

Explore

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.