The Project & Challenge

Recently, I was involved in an amazing project that used Tableau to build a booking system dashboard and Alteryx to automate processes such as sending out emails and editing Google Calendar invites by connecting to the Google API. You would first retrieve the calendar invites through the Google API and then send back a request to change them. The Google Calendar API documentation can tell you more about the different characteristics of the calendar event that you can change. In my case, I wanted to change the title and description and add attendees.

These changes have to be written in a single query string (i.e., a sequence of characters). I used a formula tool for this part. However, the query string has to be formatted in JSON (JavaScript Object Notation).

If you are not familiar with it, JSON is text-based, semi-structured data that make use of key-value pairs. It is semi-structured because it does not come in a tabular (think Excel) format but rather in a string. However, the string follows certain rules, which make it easy for applications or servers to read. It is often used in web development and in APIs (Application Programming Interfaces).

As someone without a JavaScript background, I struggled with this. It felt similar to coding, but with a coding language that I was not familiar with. I previously encountered JSON when downloading data from an API (as Robin Kennedy describes here), but all I had to do was break up the JSON format, find the values I needed, and parse them out. In this project, I had to write in the JSON format, which was a completely different experience. The query string uses double and single quotation marks, curly brackets, square brackets, parentheses, and commas, all of which have to be in the right order; otherwise, you would receive an error. In addition, I wanted it to be as dynamic as possible, which meant inserting fields without disrupting the logic of the string. There were certainly frustrating moments only to realize 45 minutes later that I accidentally inserted an additional comma.

However, throughout the project, I became more comfortable with the JSON format (at least for the part I used it for). It helped me to break down the string in separate elements and always keep the start and end of an element in mind.

Here is a slightly modified query string from the one I used:

The green and dark red colors indicate whether something is a string (green) or field (dark red). However, it is still difficult to keep track of parentheses, brackets, and quotation marks.

So, let me break down what the single elements mean, so that you can gain an understanding of what each part, quotation mark, and bracket does, which then hopefully saves you frustration and time in the future.

Breaking It Down

Step 1: Individual elements

First, let's break down the different sections within the query string. The first and last double quotation marks indicate that the string starts and ends. The first and last curly brackets indicate that a list with multiple elements starts and ends. A list stores multiple elements in an ordered way. The following picture shows you where the different elements sit. Each element has to end in a comma to indicate that the next element starts. The 'attendees' element also has a list of sub-elements which starts and ends with a square bracket that creates a so-called array. Arrays are very similar to lists but have a sequential order. In the below example, the client email address comes before the consultant email address.

Step 2: Fields to be changed

Second, let's break down the individual elements, as each element consists of a key-value pair (or field-values pair). The general syntax is 'field' : 'value". The first key term indicates the field you want to change (purple). In this case, what characteristic of the calendar invite do I want to change? For example, 'description', indicates that I want to add or edit a description in my calendar invite. The second part (orange) represents the value or what we want to change. In this case, I have a combination of text that also uses line breaks and fields from my dataset (see Step 3 for how to write that part). Every key-value pair needs to end in a comma.

Step 3: Correctly formatting values

Third, let's take a closer look at the formatting within these values. The single quotation marks around it indicate the changes you want to implement. If you only want to insert a static word, then you would only insert your string in the following format 'text'.

However, if you want to make your text dynamic, you may want to include some columns/fields from your dataset. In this case, I have the client name, company, problem, and some screenshots (as a URL link) that I want to include. I use \n to insert line breaks and write my normal string. Once I want to include a field, I have to "escape" the string though. I do this with double quotation marks. I then add my field with two plus signs around it. This basically tells Alteryx that you want the string plus a field plus another string. You can then continue writing your string until you want to insert another field where you would repeat the same process with " + [Field] + ".

Where it says summary (this is the Google API's name for the title of the Calendar event), you see an example where I don't include field names, but only a static string.

The Takeaway

If you are new to coding or it has been a while, chances are that editing the JSON format to send request to an API, can provide challenging. But with any language, coding and learning the syntax of JSON takes some time. Just like with any other language you may have to look up vocabulary (e.g., what different brackets or quotation marks do). You may forget the syntax you had previously learned or mix it up with a syntax from a different language (e.g., Alteryx formulas or Tableau calculated fields). You may also have to use a dictionary to look up individual words (e.g. this or similar blog articles) or you ask colleagues who are more proficient at the language.

I hope that these visuals will be helpful for your next endeavor with JSON formatting. Always happy to connect!

All screenshots created by Lisa Hitch in Keynote and Alteryx 2023.2

Feature Image created by Lisa Hitch on Canva