Dec 5, 2022
How to Coda with JSON — Canvas
Counting the checked boxes
In my last article related to JSON I showed how to bring in data via webhook using JSON. When I noticed a question in the community on a related matter I decided to fresh up my JSON knowledge. Below the steps I took to make it work and a document you can check to see the code in action.
I splitted the solution into a steps. First we turn the canvas column into a JSON object. Second we look for the checkboxes and we count them, then we look for the checked checkboxes and we count them and last we compare both outcomes. This is all rather straightforward when you see the code in in action.
The truth is that — although this is not a higher type of knowledge — JSON is a different syntax than the Coda Formula Language — the CFL. It has all kinds of specific elements you have to get used to. I am very much in favour of learning how to Coda properly and this implies trying to solve as many problems with the CFL as possible. For this reason you won’t see me diving into Regex logics often. Not because it won’t work (it works very very well!), but it is again a different syntax you have to get used to and often we can get the job done without it. This is not so much the case with data you bring in via a webhook or API or when you look into a data object inside Coda. In these contexts, you need JSON. This is why I consider JSON a good additional toolkit besides the CFL. I also believe that we often should use webhooks and not packs, but that is a different story.
Step 00 — turn the canvas column into a JSON Object
This is something to you have to know, not something Coda will teach you:
Step 01 — find the list of check boxes
The next step is to find the checkboxes and for this we use the double dot in combination with the dollar symbol and we reference the term we found in the code snippet. This is JSON syntax you have to get used to in order to work with it.
Note: after I shared my solution, Freerk gave me the feedback that he had to add a filter to only get the checkboxes in case you have multiple style elements in your canvas like a paragraph or a H2 or H3. I did not notice this first since I only worked with checkboxes.
The adapted line of code to deal with multiple style elements is.
thisRow.Notes._Merge().ToText().ParseJSON("$..style").ListCombine().Filter(CurrentValue.Contains("CheckboxList")).Count()Step 02 — find the checked boxes
We repeat more or less the previous step, but using a different term, because we look for checked boxes.
Step 03 — count these items
this is the easiest part and a classical CFL approach and differs from the JSON and JS logic. We use Count() to count and not Length to get the count in a list.
Step 04 — compare the outcome
comparing the counted outcomes is done with a simple function that outputs true or false
All In One
What if you want to do all of this in one function? That is a bit different but not complicated with the previous developed knowledge at our fingertips. We integrated — a few days after I first wrote the blog — the insight that we have to take care of additional elements as well and this results in what you see below.
What about sub lists?
That is a different trick. Although Coda presents new functions (Separate as JSON) to deal with JSON in a table, what you first need is understanding the syntax and for this we use a website.
We observe that we have line levels we can reference. In our first example it was not important, we only had one level, but here it is a bit different. It depends on what you want exacatly, but below something that could be a first step. I added the numberedlist() logic to show that in case of a true, it is mentioned, but in case of a false, nothing
and to show how levels work (they start at zero) with a false, see below at number 16. Number 17 is again the CheckboxList and not a false.
Up to you do apply this logic. In my understanding this is one bridge too far in most cases. You better set up a different schema for tasks etc to follow up on checked boxes.
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.
If you enjoyed this read and would like to get more Coda related content, please consider a Medium membership. It is it only $5 a month, and you’ll have access to every article ever published on Medium. If you sign up using my referral link, I’ll earn a small commission.
