In this article we are going to outline the way to perform two-way synchronization with Google Calendar.
From TeamDesk to Google Calendar
At bare minimum you should have a table, say Calendar with two fields: Title of text type and Date of date type – for simplicity let’s assume we’ll be handling all-day events only.
Let’s take a look at Google Calendar API. First thing you notice, you’ll need a calendar ID in all of the methods. If you are going to update user’s primary calendar, the ID is “primary”. If you are going to manage additional calendar, you can find its id on calendar’s property page. To simplify administration, let’s add “Google Calendar ID” Formula – Text column and put calendar’s Id as a string in formula code.
Next, in order to update event you’ll need its ID generated by Google Calendar. Let’s create a storage for that: text column named, say Google Event ID. Increase it’s length to 256 (event ids are quite long) and mark it unique ids are unique by nature.
Last, it might be useful to have a link from TeamDesk to an Google Calendar event – let’s add another column for the link: Google URL of URL type – set it’s display type as “View event” or something similar.
Create record in Google Calendar
To do that, we’ll need a trigger that fires when record is added, or modified and it’s “Google Event ID is <blank>” – latter means we have record in TeamDesk that is not yet synchronized with calendar and we need to create new event for it. Then let’s add an Call URL action to the trigger with the following settings:
| Authorization | |
|---|---|
| Type | Google* |
| Scope | Calendar |
| Request | |
| Method | POST |
| URL | https://www.googleapis.com/calendar/v3/calendars/<%[Google Calendar ID]%>/events |
| Body | JSON { “summary”: <%=[Title]%>, “start”: { “date”: <%=Format([Date], “yyyy-MM-dd”)%> }, “end”: { “date”: <%=Format([Date], “yyyy-MM-dd”)%> } } |
| Assignments | |
| Response(“$.id”) | Google Event ID |
| Response(“$.htmlLink”) | Google URL |
With this we are calling Calendar’s insert method, sending Title as event summary and Date column as event’s start and end date – it’s a one-day-full-day event. When API responds, we store event id into Google Event ID column, and link to the event into Google URL column.
* If you have dbFLEX database, you need to register an application with Google, select Generic OAuth v2 authorization type and provide Authentication URL, Token URL, Client ID and Client Secret as outlined here.
Update record in Google Calendar
Now, we need to add another trigger to handle the case when record is modified and already exists in Google Calendar – this record will have Google Event ID column filled in. The trigger should fire when record is modified and either Title or Date columns are changed (we are not going to call Google when we modify columns not related to event, right?) and filter by “Google Event ID is not <blank>”.
Let’s add another Call URL action to the trigger with following settings:
| Authorization | |
|---|---|
| Type | |
| Scope | Calendar |
| Request | |
| Method | PATCH |
| URL | https://www.googleapis.com/calendar/v3/calendars/<%[Google Calendar ID]%>/events/<%[Google Event ID]%> |
| Body | JSON { “summary”: <%=[Title]%>, “start”: { “date”: <%=Format([Date], “yyyy-MM-dd”)%> }, “end”: { “date”: <%=Format([Date], “yyyy-MM-dd”)%> } } |
With this we are calling Google Calendar’s patch method sending new Title and Date values. We are not interested in response since neither id nor link change as a result of this API call.
Now when record is create or updated in TeamDesk, change are propagated instantly to Google’s calendar.
From Google Calendar to TeamDesk
UPDATE 06/06/2022: Using webhooks are more efficient way to get the changes from Google Calendar. There is a new article and sample database outlining the use of webhooks.
This part is a little tricky since neither Google Calendar can send the data in a format compatible with TeamDesk, nor TeamDesk can accept Google Calendar’s data. You’ll need third-party tool that is able to transcode the data and call TeamDesk REST API. So, first need to setup up REST API token for use in your application with rights to create new and update existing records in Calendars table.
But wait… REST API calls triggers and here we are at risk to enter the loop. Suppose event is modified in Google and sent to TeamDesk via REST API. This will call modification trigger that will send modification request to Google that will send another API call that will trigger… To avoid this situation we have to flag the record.
Let’s create Google Synchronized checkbox column with the following Default/Calculate formula:
If(IsNull([Date]) and IsNull([Title]), false, false)
The formula looks weird, but it will effectively reset the flag when either column listed in the formula is modified via TeamDesk. Now modify update trigger and add “Google Synchronized is not checked” to the filter. Also, include the assignment of true into Google Synchronized in both triggers. Ok, we are ready for next step.
To send the data to TeamDesk you’ll need a tool that is able to receive event creation/modification notifications from Google, transform the data and send the following HTTP call to our REST API (double curly braces indicate event’s data fields):
POST https://www.teamdesk.net/secure/api/v2/<database-id>/Calendar/upsert.json?match=Google%20Event%20ID
Authorization: Bearer <rest-api-token>
Content-Type: application/json
[{
"Title": {{summary}}
"Date": {{start.date}},
"Google Event ID": {{id}},
"Google URL": {{htmlLink}},
"Google Synchronized": true
}]
Last assignment will block the trigger from sending modifications back to Google.
Using Zapier
With Zapier, we have TeamDesk connector (currently in beta stage, works by invite) that can greatly simplify setup process. All you need is simple trigger+action zap:
Trigger
- select Google Calendar,
- select New or Updated Event (it’s under show less common options)
- select Account
- select Calendar
- Test, save.
Action
- Select TeamDesk (beta)
- Select Create or Update action
- Set up Account – prepare your database ID and API token.
- Select Calendar table, Google Event ID as Match column and assign data from step 1: Summary into Title, Start Date into Date, ID into Google Event ID, HTML Link into Google URL, and finally “yes” to Google Synchronized.
Since zaps run periodically, prepare to 5 to 15 minutes delay before the data gets into TeamDesk.
