What is JSON?

JSON is the data transfer format used by Google Sheets, and many other services. This is the format that allows your Voiceflow skill to transfer data to your Google Sheet.

JSON looks like this:

It is separated by semi-colons and commas. You can think of JSON structure like a library with many layers of information. You can ask for the specific page of a specific chapter of a specific book in a specific section of the library - or - you can just ask for all the books. When data is transferred using JSON, it is grouped into these sorts of 'shells', and you can choose how many layers you want to go deep. 

Next, we'll go over how to 'query' (select) data from a JSON 'snippet'.

How to write a JSON query

Here is the JSON query we used in our Google sheets/API block tutorial:

response.feed.entry[1].title.$t

Like the library example in the beginning of that article - you can 'unwrap' layers of JSON by using a period/dot. In the query above (response.feed.entry[1].title.$t), we are 'unwrapping' the JSON by specifying the layer we want to unwrap, followed by a period/dot. You can think of this in the library example using a query like this:

library.fantasy.harrypotter[2].page[548]

Above, we are searching for a page in a particular book in a library. We specify the library (for JSON you would say 'response'), then we use a dot to specify to genre, then another dot to specify the series. The [2] on the harrypotter specifier is saying which book in that series (the second), and then the page is breaking it down by page, and uses the [548] to specify the page.

In the example used in the actual API x Google Sheet tutorial, the JSON snippet when shrunk down looks like this:

This is what's called a JSON Response - so the very first thing we want to do is put response.feed. This means we are unwrapping the 'response' (you will always have this when dealing with APIs), and we want to open the layer beneath that called "feed'. So, we do that by creating the query response.feed.

When we unwrap response.feed our available layers look like this:

After doing a quick check, all the information we want within the Google sheet is in the layer 'entry' so we want to add ".entry" to our query to unwrap that layer. 

Now, we can choose which entry we want in the list by using the bracket number [1]. For example, say we have these items in a JSON list:

  • USA
  • Canada
  • Brazil

To select Canada, we would put response.countries[2]. Meaning, we are selecting the second item in the countries category.

Next, we unwrap the [1] item in entry which is the USA, then unwrap title, and lastly $t to get the data we want "USA". If we were to put a different item number, such as [2], we would get a different country - but the query would still work as it follows the same format.

Hopefully this helped you better understand JSON queries! You can always message our team if you have additional questions.

Remember that this particular format is specific to Google Sheets, but may not extend to all JSON queries :) Query wisely!

Still have questions?

Did this answer your question?