I am looking to create a pack intended to enrich data with Pappers, with french company information (name, EBITDA, number of employees, …) thanks to these APIs.
The recovered data does not need to be updated.
I created a formula to retrieve the data via the API. Easy Thanks Coda for the work.
This formula returns an object with a specific schema.
Access to the API is paid for by the number of requests. So I try to optimize the number of requests.
I came up with several ideas but none that worked.
Do you have any ideas ?
My basic idea was to have a table with leads. A button to request data enrichment by putting the information in several dedicated columns.
I know how to do it in 2 steps:
I use the formula and I fill a cell with my object
I create the columns with formulas to retrieve the name, EBITDA, … from the obtained object.
However with this solution, Coda will call the API each time the page is loaded while the data is always the same but above all by consuming the API credits.
Hi @Ronald_Diquelou - Sounds like a great idea for a Pack! When it comes to optimizing requests, there are a few options:
Fetcher-level caching: Tell the fetcher to cache the API responses for longer than normal. If you Pack makes the same request again within the cache window it will load the cached response.
Formula-level caching: Same as above, but for the entire formula. As long as the formula parameters are the same, it will load the previous result from the cache.
Default formula recalculation: Even if you don’t enable any caching, Coda normally doesn’t re-run your formula on every page load. It should only run it when the inputs change, or occasionally for maintenance.
Make it an action: Unlike regular formulas, action formula are only run when a user presses a button (or an automation is triggered). This ensures that the requests are only made when explicitly triggered.
So if I understand correctly for action buttons, if I click on the button, Coda will make an API call and the result (an object) will be saved in a specific column. This column will never be recalculated. That’s right ?
On the other hand when I have my object in this column. How can I do to use this properties of this object?
I saw that you can create a column format but I can’t manage to use it.
Yes, when you save the button results to a column that is a one-time action, and nothing will be recalculated. As you’ve indicated, that pattern doesn’t work well however when returning rich data (an Object) as a result. While the object can be written to the cell, if the column doesn’t know the schema of the object you can’t “dot” into its properties.
A column format can be used to associate a schema with a column, but it currently requires the data to be loaded fresh, which defeats the purpose in your case. This is something we’ve talked about changing, but isn’t available yet.
One solution could be have your formula output strings instead of objects, using a format you can later extract the data from in a formula. For example, it could output JSON which you later use the ParseJSON formula to extract information from.
Indeed I understand better now some errors with the format of the column.
I used your method. My formula action returns JSON. My result column is therefore not very readable because it is JSON. On the other hand I can extract the data via a ParseJSON(thisRow.[Data pappers],"$.siret")
It’s not very clean. I look forward to future improvements
Use coda AI to read the json column and summarize only the information that you want for such column (ie: summarize [column name] ebtida]. It will need some trials to get exactly what you want for each column, but you will get there eventually.
I use that trick, to read a pdf with coda pdf pack, and extract only the info that i want from a pdf read column. Then I hide the pdf read column that is getting the whole info, as it results on a large data within a column. This makes it easier to visualize/navigate on your table later on…
Thanks Coda for the work.