How To Coda with Paperform
Using the Coda PaperForm Pack from the Coda Guy
Once you use external form builders to get feedback you can choose between various providers like Jotform, Typeform and the one we deal with in this blog: Paperform.
I previously used Paperform for some projects and appreciated the tool a lot. The great Coda Maker Scott (on Twitter The Coda Guy) created a pack to handle the responses. The set up is very well done. You need an API key and if you click on the the link in Coda that asks for the key you are directed to the right place directly inside PaperForm. Good job. From here you give a name and copy the key into the Coda Pack (window in your doc).
The result is an active pack in your doc
you ask for responses and first you see the loading, Coda connects to Paperform:
And a bit later all the forms got in. When you have only 10–30 forms this is okay and if you need an overview because you got lost in your forms, you simply click on the Forms tab and this generates a table with all your forms. This overview is a handy tool to find what you are looking for.
The responses arrive as columns and for logical reasons each question got a column. They become part of one table. This tables is often a mix of contact details (name, first name, email, gender) and all sort of responses.
Below how the data is pushed into each column, the columns are not plain text, they are formula driven so you cannot manually alter the outcome.
In short Scott did a wonderful job with this pack. It is simple, goes fast and takes away the need for any integration like Zapier or Make.com (which will save you both time, money and fake alerts…). Nothing to complain so far!
But be careful
In this blog I wrote about how a spreadsheet logic can fool us.
The structure of your PaperForm table looks a lot like ”spreadsheet logic” and is maybe not the best way to move forward.
I created a test form designed to evaluate teas and used the pack to integrate the results. Below the imported Paperform open in Coda.
This is what you see
It is very well possible that you — like me — end up with this, see below:
Do you see what is happening? We end up with the wrong data structure. Why? Have a look below, this is how products live in a Coda table. It is row based, not column based.
Next steps
How to move forward from here? Two suggestions to make it work.
- First you apply a transpose logic inside Coda using a button OR
- Second you export the PaperForm logic into a spreadsheet and you transpose before you paste it into Coda…
Option 1 — Transposing in Coda
This is actually not that easy and requires a combination of insights and skills. I am not going to work out the solution in detail, just the headlines. Most important are two aspects. You create a list of results (manual work) in the source table using the function List() to group all results in one column — in the below image this is ListOfResults. Next you make sure that your target table has the same amount of rows as the list count and that the order corresponds with the column order in your source table (Evaluate Be Your Tea in the example). Once you have this manuel work behind you, it is time to define the function in the button. This function modifies each currentvalue, thus each row via the part after FormulaMap and since the rows in the target table are equal to the amount of evaluations, we use the same CurrentValue (a number generated via Sequence(). The last part in the function makes sure that we keep what we have and add each time the values of a new row.
You cannot via code access the refresh button of the pack, so I suggest that you create a button on the canvas the presses the button only for those rows that have not yet been pressed. You can use a simple trick. Add a check box field to the source table and set it as true via an extra ModifyRows() function (don’t forget about the RunActions() and disable the button for checked boxes. Well, here you are.
We have a working transpose solution in Coda!
There is an alternative to this complexity, but is it a good alternative? Have a look!
Transposing in Google Sheets:
This is quick and dirty. Quick because it takes 30 seconds, dirty you have to do it every time you have new data and it is error prone. Good for a one time exercise, not something to repeat, unless you don’t understand what I shared previously.
How it works? Click on the function : Transpose( — data range — ) and that is it…. pff, so simple and fast, amazing actually.
Next steps
I’ll ask Scott and all the other pack builders to create a tool that does this transpose work for you, thus generate an additional table that contains the column values as rows. This will make the transpose logic as explained above redundant and that might be a good thing for 95% of the Coda makers. I would not be surprised to see an update in the nearby future on this part. Anyway, keep up the good work Scott.
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.
Not to forget: the Coda Community provides great insights for free once you add a sample doc.
