API Error: Row having key '<key value>' not found

This is more of an FYI than a question. Posting this as a new topic since I only found old threads about this and I think it'll be helpful.

Problem

I was trying to send a web hook to the AppSheet API from a third party system to update rows on a table with security filters and kept getting a "Row having key '<key value>' not found" error. 

I knew security filters were my problem but couldn't figure out a way around it. The advice on the API troubleshooting page wasn't very helpful and messing with the system provided User Settings table was going to require really tedious changes to my security filters and views. The solution appears to be that the email the API action is executed as must explicitly have access to the rows you want to edit, in the table security filter. It wasn't good enough that the given email could access the table indirectly since its role is admin (see step 1 screenshot). Adding the email explicitly is redundant but apparently necessary.

Solution

1. I had to explicitly allow a given email to view all records in the security filter for the table I wanted to edit via API.

njordan_0-1742320956293.png

2. I then used the RunAsUserEmail property in my API call, referencing the same email from step 1.โ€ƒ

njordan_1-1742320956273.png

This allowed me to successfully edit, read, and delete rows.

Solved Solved
0 7 258
1 ACCEPTED SOLUTION

Solution in original post.

View solution in original post

7 REPLIES 7

In the bot settings for your app that has the api task there is a setting for 'Bypass Security Filters'.  I believe if you toggle this to on for your specific bot that there will be no missing rows...

scott192_0-1742374643896.png

 

So, that didn't work for me, here's why:

Background: My AppSheet app contains usage based billing logic for a subscription service, I'm integrating with Stripe to automatically generate and send invoices. This particular integration retrieves the corresponding stripe ID for new customers who are entered into the AppSheet app.

Data Flow: When a new customer is entered into AppSheet:

  1. AppSheet sends a POST webhook to zapier with customer_id and email.
  2. Zapier fires a GET request to Stripe to look up the customer with a matching email and returns the customer info, including stripe_id.
  3. Zapier sends a POST webhook to the AppSheet API to edit the customer row with the stripe_id.

The problem that my solution addresses was with step 3, sending a webhook to the AppSheet API from a third party service. Step 1 and 2 would work, then step 3 would fail because "no row with that customer id exists" even though a customer with that id literally initiated the automation. 

Enabling "Bypass Security Filters" on my AppSheet automation in step 1 would theoretically solve an issue where the automation failed to trigger in the first place for some owner rows but I'm not having that problem (thankfully). 

* Note

You might wonder why I've involved Zapier at all. Couldn't I just send a GET request from Appsheet right to Stripe to lookup the customer with a matching email? Well, yes, but the issue is that appsheet doesn't let me parse the returned json into a return value because it's in a complex nested array as described here. So Stripe gives me the info I need, I just can't use it to assign a value to my owner rows, or at least I couldn't figure out how. 

Ok...that sounds to me one of two things.

1) Not enough time has passed between the POST to Zapier with the newly created customer_id and email.  This means that Appsheet has not written the record that was created before the POST into the database and stored it for retrieval yet.  You could test this by adding a 5 minute wait as a step before the Zapier POST triggers.

2) You are not giving appsheet the correct key_column in the return journey from Zapier.  Which is it?  The email or the customer_id?  Is the customer_id a UNIQUEID()?

I made sure to account for those variables as well. Maybe there is a simpler solution than what I came up with but I tried to be pretty thorough. 

  1. I've actually been testing this both with new owners entered into the app, and as a backfill for existing customers without stripe_ids. Prior to the solution I found in the original post, step 3 failed regardless of whether the automation ran for new created customers, or I ran it manually for existing customers. After my security filter solution, the automation succeeded in both scenarios. This rules out the timing as a root cause.
  2. And I was definitely passing the AppSheet API the correct id for the owner row to be updated, with the json schema described in the Request Body section here. The key field for all my tables is an 'id' field of type UNIQUEID(). I validated this in Zapier logs along with testing dynamic and hard coded values and replicated all tests in Postman. Same as above, when I implemented my security filter change as the only variable, all tests worked.

LOL!  I have just realised that this problem was solved by yourself earlier and you were providing a tip for other users!  Please ignore me, I am having a senior moment ๐Ÿ˜‚

Amazing, haha thanks for your ideas man! I'll make that more clear at the top of the post. 

Solution in original post.

Top Labels in this Space