How to Coda with JSON — Extra
Bringing data in and distributing it over rows
In my last blog about JSON I described a method that permits for isolating data in a JSON string. The developed logic shown by Eric is based on what look like a kind of Regex for JSON. It is elegant and fast and prepares us for the next step: the distribution over the rows. Eric was also so friendly to share this tool and I advice you to use it as well to test your JSONPath set up.
We need to understand the JSONpath a bit more before we dive into solutions. Besides comprehending the JSON basics I wrote about, you need to see that this is a separate language besides the Coda Formula Language. Indeed there are some similarities like the dot operator, but they are used in a different way and inside JSON path it makes sense to have 2 in a row.
- The single dot → get values is related to a named level — . —
- The double dot → get all the values regardless of level — . . — and the logic behind is that you first relate to something and by repeating this something you say basically give me everything. This logic you see again below in the screenshot. A formal statement is: “recursive descent.”
The double dot needs to be told where to start, where to look for values:
- The dollar sign $ → says start at the beginning, thus at the root.
- The astrerix * → start at any of the top level items, the wildcard
- The squared brackets [] → we need these to indicate which value we need for which column.
I noticed that book stores are a very much wanted type of example when it comes to explaining JSONPath, below an overview of the most common patterns based on the previous functions.
Besides you have the @ symbol that references to a value inside your string you see logical operators like equals , greater than and smaller than and there is some specific JavaScript logic when you observe [@ . length-1] . In the beginning you can skip a lot of this info and bit by bit you fill your tool kit while building.
The focus of this blog is making you aware of some key differences and logical constructs that may help you in setting up your web hook logic in Coda. We therefore leave the further and more detailed explanation of JSON Path to sites that focus specifically on it like:
https://www.toolsqa.com/rest-assured/jsonpath-and-query-json-using-jsonpath/
Distribution over rows
Once we see that we can ask for all values in a Key Value pair, we can also find a way to isolate value per value since values live in Arrays. Maybe you recall that an Array is like a list in Coda and is defined by the square brackets [ ] . We need this logic in our function. Below you see how we get the values in the columns of our choosing via the web hook automation.
What happens is that first I convert the outcome of step 01 to text to get the RawJSON and this long string I print in the column RawJson. This enables me later on to make use of elements I don’t define today, but maybe necessary later on like the time stamp. Next we define the columns and per column we add the function. You may notice that we start at zero. Max wrote me about this:
JSON is a subset of javascript and javascript evolved from java and c. Those languages always index arrays from zero. this is because the index is added to the base address of the array — so adding zero gives you the first item etc.
Maybe the function as written below would make more sense in this specific case since the top level is “Data” and I use this instead of $ . The dollar sign is simply pointing to the root and in this case this the ‘data’, while I could also replace the $ with an * because the root and the top level are the same in this example. In this rather simple case of nested JSON such an approach will work well. It gets harder when you have multiple nestings.
Advantage 01.
The result of this JSON manipulation is a table that via a web hook directly gets filled out with the data as specified per column. There is no delay and based on the new row, you can automate further like sending emails, notifications launching calculations etc. This is one of the main differences with a pack that needs extra tricks to get the ‘new row’ action.
Advantage 02.
There is an other advantage that is a bit harder to grasp when you stay in the realm of personal data. This is an advantage related to processing numbers as outcome of an evaluation. I created a test form and the data arrived into Coda via the pack and every tea — a column value — got a proper evaluation. But this is not how product related information lives in Coda. Products are items in a row. More about this issue in this blog:
Using a web hook, you can grab the data as you wish and yes you need to develop your JSONPath skill set. Once done, you can bring in data as required, thus the user as item, the tea as item and the evaluation as item. Maybe I’ll write a separate blog about how to do this later on. This requires a bit more trickery on the side of JSONPath (thus extracting data while keeping items stable like the name of the user).
Advantage 03
With a web hook you link only to a specific form, with a pack to the account and from there you select the form you need. This means that all your clients have access to all your forms in Paperform since there is no split on folder level (the Paperform API key is valid for all forms unlike the Coda web hook API key that is only valid for one web hook). The web hook is the safer option.
Long story short, the Paperform Pack is so easy to use, it creates for you a table aligned with questions, no manual work. Is almost a no-brainer. Once you need a bit more, the web hook is the way to go.
How to bring JSON into Coda via a Web Hook?
We wrote for quite a bit how to manipulate data in Coda, but first of all is the question: “how to get the data into Coda”? Well here I hesitated a bit because I bypassed the Coda security settings. Coda requires a bearer authentication, this means a way to enter your web hook API key in the settings of the software you want to ‘talk to’. In my case Paperform only offered an open Web Hook, meaning this field was not present. However you can bypass this by adding a bit of code to the URL generated by Coda, it goes like below.
You add ?access_token= to the and + the API key. That is it. This will bypass the security settings, but may be not the best way to proceed. Coda’s advice in this case is to use a third party integration like Zapier or Make.
Once you have done this, you enable the web hook and you click test. It will work. And your step one result is ready. In step two you add the function ToText() to get the JSON string. That is it.
The hardest part was first knowing this trick (how to bypass) and second understanding the JSONPath logic. This exercise gave me many insights I will apply over time to create powerful docs. I became a great believer in the opportunities Web Hooks create.
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter
My name is Christiaan and I support SMB with calculations (budgets and planning) and I prefer using Coda to get the job done.
Coda comes with a set of building blocksーlike pages for infinite depth, tables that talk to each other, and buttons that take action inside or outside your docーso anyone can make a doc as powerful as an app (source).
Not to forget: the Coda Community provides great insights for free once you add a sample doc.
