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.