Hey folks! I have a research repository powered by Coda. People can enter search terms in a text control box, and then the table filters to show them relevant results.
Because the Coda API is pretty limited with how it grabs rows and sets filters, and because I am limited in that I know no code, I have set up an automation using webhooks to Coda endpoints that works like this:
Someone does a slash command in Slack
Send search term to Coda via API, and force it into the text control in the doc
Table automatically filters on its own (if you have the doc open you can see it happening live!)
I grab results from the table via API
I format the answer in Slack.
Itâs pretty handy because it means people donât have to go to Coda to use the repository, and can just use a Slack command. However itâs not great in that I am actually changing the dimensions of the table.
Iâve also noticed that this doesnât appear to work when I either do not have the doc open, or have currently selected one row from the table being filtered. The latter isnât too much of a concern, the first issue is. Instead of filtered values, it returns rows by index. This is less preferable than it returning no rows at all, because this makes it seem as if itâs returning ârealâ search results.
Hey @Zen_Ren, sounds like a neat integration! It seems like youâre basically using Coda here to help search and narrow down results, but you might have better luck with just getting all the rows and then filtering them in your automation directly, or alternatively using the query query parameter in listRows if youâre looking for exact matches only. Otherwise if multiple people are using your current tool simultaneously then one of them may get the otherâs search results.
To explain the behavior here, the reason youâre likely seeing all the rows is that youâre querying the table directly using the API, which returns all rows, even filtered out ones (if we didnât do that, youâd have no way of getting all the ârawâ data in a table using the API). What youâll want to do instead is to query the view thatâs actually being filtered down.
Note that due to a limitation of the API, youâll currently need to create a new view in Coda, as we currently donât let you access the âviewâ version of the base table (which includes all the applied filters).
Thanks @oleg! Iâve actually already added in a contingency step in case multiple people use it simultaneously, so thankfully that is not an issue. It basically blocks a query if time since last query < 15 seconds.
Thereâs a few reasons why listRows on a view and a query param is insufficient for my use case:
I needed to access hidden columns, but not hidden rows. This is because the hidden columns contained the relevancy score that users of the repo donât need to see, but filtered out rows that completely werenât relevant.
visibleOnly=false never worked properly either on a view, and never grabbed all my columns, only just a few more hidden ones. Hence querying the parent table. I was told by someone on your team that querying the view could be inconsistent in this way, but I think this would be incredibly valuable if you could update your API to treat views like tables. All the gold of what people want is operated on views. Even all your templates in Coda operate with an âadmin tableâ but the real function is in the views. Your API should match your user baseâs behavior and even your stated norms
my query param is relatively complicated - itâs not just filtering the table on the specific word being searched, itâs also searching for matches on similar forms of the word pulled from another table (e.g. imagine this is a recipe table, a search for aubergine would match on aubergines, eggplant, and eggplants).
I donât know a single bit of code which is why Iâm using Coda and straightforward http requests. This is my own issue. I can definitely see how if I were able to replicate both the relevancy score and the matching functions outside of Coda, this would be infinitely faster and more stable.
The way Iâve hacked my solution is by adding my filter and relevancy score column to the parent table as well. Not ideal but it gets me there.
âNote that due to a limitation of the API, youâll currently need to create a new view in Coda, as we currently donât let you access the âviewâ version of the base table (which includes all the applied filters).â - I am not 100% sure what you mean here as this seems to be a contradiction. The API doesnât allow views, but I am supposed to create a new view? Do you mind explaining more what you mean here? Also the API does allow you to access the view version of the parent table including all the base filters, itâs just for some reason, visibleOnly doesnât work properly.
Iâm also thinking now after some more testing the operant issue isnât necessarily ânot having the doc openâ but rather sometimes thereâs inconsistent delays. I noticed when setting things up using Postman that most often Iâd have to wait for 5 seconds for a change on the table (done manually) to be picked up by a query. However, with my automation for some reason Iâm having to wait 20 seconds, and even then it doesnât pick up the change around 10% of the time. This is despite having the table open and actually visually seeing the change in it, then a delay in 20 seconds, and only then does my automation query the table. A delay of 10 seconds resulted in pulling the default table instead of a filtered view around 20% of the time. Iâm very curious why a manual change only took 5 seconds to be properly read but one done through the API takes much longer.
@Johg_Ananda I set it up on Zapier! I can make a howto post if it turns out the Coda API works for this.
which is why Iâm using Coda and straightforward http requests. This is my own issue. I can definitely see how if I were able to replicate both the relevancy score and the matching functions outside of Coda, this would be infinitely faster and more stable.