Skip to content
Copy of Getting Started Guide: Coda API
Coda API
Guides 2
Untitled
Config
More
Share
Explore
Guides 2

icon picker
Syncing a Google Calendar

⁠
⁠
⁠
Your time is important. In this example we'll show you how you can build a Calendar to Coda sync so you can do the following:
Pull stats out of your calendar
How much time are you spending in meetings? What types of meetings?
Who you are spending time with?
Make the most of the time you and your team spend in meetings by adding meaningful metadata:
Goals
Notes per meeting
Sentiment indicator
Follow ups

What you'll learn

How to access Google Calendar events from Google Apps Script
How to write calendar data into rows in a Coda table
⁠

Instructions

1. Setup

Here's a table you can use to store your events. We'll be using column names instead of IDs for simplicity, so be careful not to rename any columns, or your script will start failing (and you'll get failure notifications from Google).
ⓘ If you copy this table outside of this doc, you'll have to set the formula on the Day column to [Start Time].DateTimeTruncate('day') .
My Calendar
0
Day
Title
Start Time
End Time
Status
Location
Description
All Day?
Guest Names
Guest Emails
Date Created
Last Updated
ID
Creator
Color
Owned By Me?
Recurring?
1
Placeholder event
​
​
​
​
​
​
​
​
0
0
⁠

2. Create your script

As before, go to
Google Apps Script
⁠
and create a new project. Paste in the following code, replacing the CALENDAR_DOC_ID with this doc's ID (hint: you can use the
Doc ID extractor
⁠
).
ⓘ You'll have to replace you@example.com with your desired calendar's ID. For your personal calendar, this is usually your email address. For other calendars, you can go to
calendar.google.com
⁠
, go into "Settings" via the hover menu for the calendar you're interested in within the left sidebar, and then use the "Calendar ID" that's in the "Integrate calendar" section.
CodaAPI.authenticate('abcd1234-efgh-5678-ijkl-1234mnop5678'); // Replace with your token.

// Configure as needed below.

GOOGLE_CALENDAR_NAME = 'you@example.com';
DAYS_TO_SYNC_BEFORE_TODAY = 14;
DAYS_TO_SYNC_AFTER_TODAY = 14;
CALENDAR_DOC_ID = 'TO UPDATE';
CALENDAR_TABLE_NAME = 'My Calendar';

/**
* How do you want deleted events and events outside the time range specified above handled?
*
* - 'remove_deleted' (default): keep events in Coda within the range specified. Cancelled events
* will be removed, while events that fall out out of the sync window will be kept.
*
* - 'keep_all': never delete any rows in Coda - as soon as an event is saved, it will never
* be deleted (but will still be updated).
*
* - 'keep_synced': keep events in Coda that are only within the range specified. Calendar events in Coda
* for cancelled events and any events outside of the sync window will be deleted.
*/
EVENT_DELETE_BEHAVIOR = 'keep_synced';

/** Run me! (Depending on how many events you have, this may take a couple minutes, so hang tight.) */
function syncGoogleCalendarEvents() {
// Get the date range we want to sync.
var syncRange = getDateRange(new Date(), DAYS_TO_SYNC_BEFORE_TODAY, DAYS_TO_SYNC_AFTER_TODAY);

// Get calendar data from Google Calendar.
var events = getCalendarData(GOOGLE_CALENDAR_NAME, syncRange);

// Construct rows to upsert into Coda.
var rows = [];
for each (var event in events) {
var status = event.getMyStatus();
var guestEmails = [];
var guestNames = [];
for each (var guest in event.getGuestList()) {
guestEmails.push(guest.getEmail());
guestNames.push(guest.getName() || guest.getEmail());
}

// This part can take some time depending on how many events
// you are pulling.
var row = {
cells: [
{column: 'ID', value: event.getId()},
{column: 'Start Time', value: event.getStartTime()},
{column: 'End Time', value: event.getEndTime()},
{column: 'Title', value: event.getTitle()},
{column: 'Status', value: status ? status.toString() : ''},
{column: 'Description', value: event.getDescription()},
{column: 'Location', value: event.getLocation()},
{column: 'Color', value: event.getColor()},
{column: 'Creator', value: event.getCreators()},

{column: 'Last Updated', value: event.getLastUpdated()},
{column: 'Date Created', value: event.getDateCreated()},

{column: 'Guest Emails', value: guestEmails},
{column: 'Guest Names', value: guestNames},

{column: 'All Day?', value: event.isAllDayEvent()},
{column: 'Recurring?', value: event.isRecurringEvent()},
{column: 'Owned By Me?', value: event.isOwnedByMe()},
],
};
rows.push(row);
}

Logger.log('Upserting %s events via chunks...', rows.length);

// Upsert rows into Coda. Send them in chunks of 100 events at most to avoid a "Request too large" error.
for each (var rowsChunk in chunkArray(rows, 100)) {
Logger.log('Upserting chunk of %s events...', rowsChunk.length)
CodaAPI.upsertRows(CALENDAR_DOC_ID, CALENDAR_TABLE_NAME, {rows: rowsChunk, keyColumns: ['ID']});
}
Logger.log('Done upserting');
// Delete events if needed.
if (EVENT_DELETE_BEHAVIOR === 'keep_all') {
Logger.log("EVENT_DELETE_BEHAVIOR is 'keep_all' - won't be deleting anything");
return;
}

var removeUnsyncedEvents = EVENT_DELETE_BEHAVIOR === 'keep_synced';
removeDeletedEvents(events, syncRange, removeUnsyncedEvents);
}

/** Removes cancelled/unsynced events from Coda. */
function removeDeletedEvents(events, syncRange, removeUnsyncedEvents) {
Logger.log('Removing cancelled events within %s - %s%s', syncRange.start, syncRange.end, removeUnsyncedEvents ? ' and unsynced events' : '');

// Fetch existing events from Coda.
var existingRows = fetchAllCodaEventRows();
Logger.log('Fetched %s events from Coda', existingRows.length);
// If not removing unsynced events, filter out those outside the time window.
if (!removeUnsyncedEvents) {
existingRows = existingRows.filter(function(row) {
var eventDate = new Date(row.values['Start Time']);
return eventDate >= syncRange.start && eventDate < syncRange.end;
});
Logger.log('Filtered out unsynced events; now have %s', existingRows.length);
}

// Delete Coda rows that don't exist in Google Calendar anymore.
var eventIds = events.map(function(event) { return event.getId(); });
for each (var existingRow in existingRows) {
var existingRowEventId = existingRow.values['ID'];
if (eventIds.indexOf(existingRowEventId) === -1) {
Logger.log('Deleting event %s from Coda: %s', existingRow.values['ID'], existingRow.values['Title']);
CodaAPI.deleteRow(CALENDAR_DOC_ID, CALENDAR_TABLE_NAME, existingRow.id);
}
}
Logger.log('Finished deleting events');
}

/** Fetches all event rows from Coda, handling paging. */
function fetchAllCodaEventRows(pageToken) {
Logger.log('Fetching Coda rows. Page token: %s', pageToken);
var getSyncedRowsOptions = {
useColumnNames: true,
limit: 500,
pageToken: pageToken,
};
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.