Getting Started Guide: Coda API

Explore

Guides

Syncing a Google Calendar
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

My Calendar

Day

Title

Start Time

End Time

Status

Location

Description

All Day?

Guest Names

Guest Emails

Date Created

Last Updated

Creator

Color

Owned By Me?

Recurring?

Placeholder event

⁠

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 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 (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 (

⁠

) 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.

4. Do more with your data in Coda

Now that your calendar data is in a Coda table, you have the ability to do more with your data. Here are some of our favorites ways:

Analyze where your time is going

Annotate each of your meetings with meaningful metadata to maximize meeting efficacy

Goals

Notes per meeting

Sentiment indicator

Follow up Action items (this might be a great template to combine with

Automated reminders⁠

Track meeting trends over time

☑️ Mark the "Syncing a Google Calendar" guide as complete!

No results from filter

⁠

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.

Syncing a Google CalendarSyncing a Google Calendar