Skip to content
Gallery
Getting Started Guide: Coda API
Coda API
Guides
Config
Share
Explore
Guides

icon picker
Syncing a Google Calendar

⁠
image.png
⁠
⁠
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
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
⁠
(
v8 runtime
⁠
) 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 [email protected] 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 = '[email protected]';
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 (var event of events) {
var status = event.getMyStatus();
var guestEmails = [];
var guestNames = [];
for (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 (var rowsChunk of 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 (var existingRow of 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,
};
var response = CodaAPI.listRows(CALENDAR_DOC_ID, CALENDAR_TABLE_NAME, getSyncedRowsOptions);

var items = response.items;
Logger.log('Fetched %s rows. Next page token: %s', items.length, response.nextPageToken);
if (response.nextPageToken) {
items = items.concat(fetchAllCodaEventRows(response.nextPageToken));
}

return items;
}

/** Returns calendar events from Google Calendar within the specified range. */
function getCalendarData(calendarName, syncRange) {
Logger.log('Fetching events from Google Calendar from %s to %s...', syncRange.start, syncRange.end);
var calendar = CalendarApp.getCalendarById(calendarName);
var events = calendar.getEvents(syncRange.start, syncRange.end);
Logger.log('Done! Fetched %s rows', events.length);
return events;
}
/** Returns a date range from the given basis date given start and end offsets. */
function getDateRange(date, startOffset, endOffset) {
var startDate = addDays(date, -startOffset);
startDate.setHours(0, 0, 0, 0);

var endDate = addDays(date, endOffset + 1);
endDate.setHours(0, 0, 0, 0);

return {start: startDate, end: endDate};
}

/** Returns a date that is offset `numberOfDays` from `startDate`. */
function addDays(startDate, numberOfDays) {
return new Date(startDate.getTime() + numberOfDays * 24 * 60 * 60 * 1000);
}

/** Breaks up the array into smaller arrays of at most `chunkSize` length. */
function chunkArray(array, chunkSize) {
var output = [];
for (var i = 0; i < array.length; i += chunkSize) {
output.push(array.slice(i, i + chunkSize));
}
return output;
}

3. Get it working

With the script set up, run (▶) your syncGoogleCalendarEvents() function.
Since you're syncing a month's worth of data, the script may take a minute to run. If you'd like to see its progress, go to the script executions list using View→Execution Transcript. After a while, you should see your events trickle in.
You can now set up a trigger (
image.png
⁠
) to run syncGoogleCalendarEvents() periodically to sync your calendar events (see
Building a one-way sync
⁠
if you need a refresher).
You can also change the deletion behavior and the date range that you sync if you'd like. See the code for the relevant sections.
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.
OSZAR »