Earlier, we have published an article describing synchronization with Google Calendar, where the Google Calendar Event ID plays the role of the match condition. Recently, some of our customers expressed a need to use another match condition to synchronize their database with Google Calendar account. Therefore, we decided to provide a detailed example of the setup you can replicate in such cases.
Say, you manage a consulting company providing pre-purchase building inspections. Company’s employees should register inspection records comprising unique IDs of inspected buildings. When the inspection record is created, a manager assigns a consultant, who is going to do the inspection. Also, a date and time of the inspection should be agreed and registered in the “Inspection” record. At the same time, a corresponding Inspection event should be created (or updated) in a Google Calendar account of the company. Moreover, the opposite synchronization (from Google Calendar to TeamDesk database) needs to be configured.
Thus, the synchronization consists of two parts:
• From TeamDesk to Google Calendar
• From Google Calendar to TeamDesk.
Let’s begin from a match column we need for the synchronization:
1. Match column
A match column is a column keeping unique values. It will allow to find corresponding records in Google Calendar and vice-versa.
In our example we use the “Building ID” text column as the match column. It keeps the default/calculate formula displayed on the screenshot below. Also, the Unique and Required options are activated in the column settings.
Moreover, we recommend to mark the “Required” checkbox in all columns participating in the default/calculate formula, namely in the “Type”, “State” and “Registered #” columns.
2. Google Calendar ID and formula-text column
According to the Google Calendar API documentation, to achieve the synchronization, you would need a google calendar ID. In this example, we use firstname.lastname@example.org google calendar ID.
To simplify the usage of this ID in the synchronization, in the “Inspections” table we added the “Google Calendar ID” column of the Formula-Text type. It keeps the calendar Id in quotes in the formula field.
3. “Google Synchronized” checkbox
Since this synchronization must work in both directions (From TeamDesk to Google Calendar and vice -versa), we need to avoid looping. Therefore, we should flag an Inspection record using the “Google Synchronized” checkbox column with the following Default/Calculate formula:
At first glance, the formula looks strange, but it will effectively drop the flag when any column listed in the formula is changed in any record of the “Inspections” table.
4. “Find Event Trigger is Done” checkbox
Now we added the “Find Event Trigger is Done” checkbox column to the “Inspections” table. This column is an auxiliary column letting know that the search process with match criterion is complete. Furthermore, when this column is checked, the system triggers either the Create Record action or the Update Record action due to the cascading execution of record change triggers.
5. “Google Event ID” text column
When a matching Event is found in the Google Calendar, TeamDesk should save its Google Event ID value and use it for updating. For this purpose, we created the “Google Event ID” text column in the “Inspections” table. In addition, we restricted the column width to 300 characters because a Google Event ID value can be quite long.
6. “Event Begins Timestamp (from Google)” column.
The last auxiliary column we need to create is the column of the Timestamp type. Let’s name this column “Event Begins Timestamp (from Google)”. It is going to be used for the synchronization from Google Calendar to TeamDesk. The column stores ‘Event Begins Timestamp’ values of events created or updated directly in the Google Calendar app.
7. Record Change Triggers (From TeamDesk to Google)
To make the synchronization form TeamDesk to Google we created three record change triggers.
Please note, that the order of the triggers is important. Besides, we apply the cascading execution for record change triggers.
The first “Find Event” trigger locates a corresponding event by searching for “Building ID” text value in Google calendar events. In case the matching Building ID value is found, the “Google Event ID” column gets filled in and the second trigger “Update record in Google Calendar” gets activated. If no results are found, the third trigger “Create record in Google Calendar” fires and a new even gets created in Google Calendar.
Let’s review each trigger settings in details.
8. “Find Event” Trigger
As we mentioned above, this trigger searches for a “Building ID” text value in the Google Calendar events.
Please note, that according to the Google API documentation, the search process can be performed in all the fields belonging to any event stored in Google Calendar. It means that it’s not possible to restrict the search by any single field, such as event’s “Summary” field, for example, where Building ID is stored. That’s why, while creating events in Google Calendar, please make sure that Building ID unique value is specified in one event only.
The trigger settings are displayed on the screenshot below, please look at the “Matching” section.
Also, please take a look at the “Find Event ID” call URL action added to the trigger. The action settings are provided on the following screenshot:
As you can see, the URL field comprises the “Building ID” column. A value stored in this column is queried in the related Google Calendar.
Moreover, the “Execute Triggers” option comprises the “Yes” value. Therefore, the action allows cascading execution of two other record change triggers.
Please notice, that the action includes two assignments. The first one inserts a found google event id value into the “Google Event ID” column, the second activates the “Find Event Trigger is Done” checkbox.
9. “Update record in Google Calendar” trigger
When the Event comprising the matching “Building ID” value is found in Google Calendar and the Google Event ID value is added to the “Google Event ID” field of the “Inspections” record in TeamDesk, the “Update record in Google Calendar” trigger is activated automatically. It happens due to the “Execute Triggers” option, that is checked in the “Find Event ID” call URL action described in the item #8.
The trigger settings are shown on the following screenshot:
The settings of the “Call URL (Update Event in Google Calendar)” action are displayed on the screenshot below:
Also, please pay your attention to the assignments added to the action.
10. “Create record in Google Calendar” trigger
In case an Event comprising the corresponding “Building ID” value cannot be found in Google Calendar, the “Google Event ID” column value is left blank in the Inspection record and the “Create record in Google Calendar” trigger fires automatically because of the “Execute Triggers” option is activated in the “Find Event ID” call URL action, that is described in the item #8.
The trigger settings are shown on the screenshot:
The trigger includes the “Call URL (Create Event in Google Calendar)” action that generates a new Event in the Google Calendar.
The assignments of the action are needed to prevent the possible loop that could occur during the synchronization process.
11. Zapier connection tool (From Google Calendar to TeamDesk)
To make the opposite synchronization (from Google Calendar to TeamDesk) you need an additional connection tool, since neither Google Calendar can send the data in a format compatible with TeamDesk, nor TeamDesk can accept Google Calendar’s data. Such a third-party tool should be able to transcode the data and call TeamDesk REST API. Therefore, you need to setup up REST API token for use in your application with the rights to create new and update existing records in the “Inspections” table.
We chose the “New or Update Event” trigger.
On the next step, the Google Calendar account and the calendar should be selected.
After that we may fill in the second (TeamDesk) part of the Zap.
More details about Zapier to Teamdesk intergation may be found here >>>
When TeamDesk account is selected in the Zap, the Customize Record fields should be filled in.
The match column is especially important. In our example we use the “Building ID” column as the match column. Moreover, you need to update a “Building ID” column value by a “Summary” column value, when an event is added or updated. Therefore, the “Summary” must be specified in the “Building ID” field of the created zap.
Also, select the following options in the following fields:
When it’s done, turn on the Zap.
12. Time Zone checking
To avoid time shifts, your TeamDesk user account and the Google Calendar time zones settings should coincide.
13. Default/Calculate Formulas in the Date and Time columns
Whereas the Event Begins timestamp value is received from the Google Calendar to TeamDesk, we recommend using the Default/Calculate formulas in the “Inspection Date” and “Inspection Time” columns. These formulas allow extracting date or time value from a timestamp. As the result, the columns will be filled in automatically after getting data from Google Calendar.
For example, the “Inspection Date” column with Default/Calculate formula:
The “Inspection Time” column with Default/Calculate formula:
This concludes TeamDesk to Google Calendar synchronization by match column setup process.