AppSheet API Find Call Issue

[bradlegassick]

Hi Community
I am retrieving records via Apps Script using the AppSheet API. I am having relative success with the API. However I have encountered strange behaviour when using the Selector property & the record ID is all integers. The record ID was created by AppSheet using the uniqueid() initial value expression. The ID causing problems is 35137629. The other IDs are for example: 

'19f35ba1, c17b7170, c8dd1369, c05ac13f, 95ad0e18, edfedafb, ccec567a, caf126bd, b8336f7b, 825ce45d, 1890c0f2, 3583d0f5, 5d4ab131, d93704b5, 5a571e20, c5eacb43, 31698c86, d14f86f3, 5c578752, 35bc61fb, 78ec95a4, f384e3ce, 35137629, d34a0972, 385f5b5d, 672112f5, a390faef, 80357987, 0dc23380, ee9bdb70, dc6aa7c9, 42ece0be, 34a7efb3'
The issue is this expression fails when including the ID 35137629, however when I temporarily edit the ID to be 35137629X & update my Selector property to include 35137629X instead of 35137629, I am returned the records. However the returned records is an empty array if the record ID is 35137629 & the Selector property is 35137629.
This is my Selector property:

// API call properties

const recipeDetailsRowProperties = {

'Locale': 'en-GB',

'RunAsUserEmail': userEmail,

'Selector': 'Filter(Recipe_Details, IN([Recipe_Ingredient_ID], {385f5b5d, 672112f5, a390faef, 0dc23380, ee9bdb70, dc6aa7c9, 42ece0be, 34a7efb3, 35137629}))',

// 'Selector': 'Filter(Recipe_Details, CONTAINS({' + testrecords + '}, [Recipe_Ingredient_ID]))',

};

This does work when there is no all integer record ID, however when I include the record ID with an X appended it all works.
I have checked my record ID's & the ID 35137629 is unique in the table, the column format in Google Sheets is "Text", there is even an ' before the integers 35137629 denoting a text value.
Any assistance resolving this is appreciated.
Thanks
Brad

[Marc_Dillon · Accepted solution]

Put all of those key values between quotes.

I also recommend never using {} notation for Lists, I know it doesn't work properly in some cases, use LIST() instead.

[Marc_Dillon · Accepted solution]

Put all of those key values between quotes.

I also recommend never using {} notation for Lists, I know it doesn't work properly in some cases, use LIST() instead.

[bradlegassick]

Thx @Marc_Dillon I tested this:

'Selector': 'Filter(Recipe_Details, IN([Recipe_Ingredient_ID], LIST("385f5b5d", "672112f5", "a390faef", "0dc23380", "ee9bdb70", "dc6aa7c9", "42ece0be", "34a7efb3", "35137629")))'& success. Appreciate the prompt & helpful advice. Cheers

[bradlegassick]

@Marc_Dillon thought I had this call & filtering sorted. It has worked almost flawlessly for a couple of months. Today I am getting some empty arrays returned. The data is there, some other rows are returned if I test manually & use those rows filter value. My call log looks like this:
Sending Payload: {"Action":"Find","Properties":{"Locale":"en-AU","RunAsUserEmail":"(PII Removed by Staff)","Selector":"Filter(Invoices, [InvoiceID] = 995a780c)"},"Rows":[]}
I am returned an empty array, when there should be 6 rows returned. If I change the filter value to 4865ac2a..
Sending Payload: {"Action":"Find","Properties":{"Locale":"en-AU","RunAsUserEmail":"(PII Removed by Staff)","Selector":"Filter(Invoices, [InvoiceID] = 4865ac2a)"},"Rows":[]}
I am returned all the relevant data. I am at a loss at to what is causing this. Maybe you can see an issue in the call structure that can cause this behaviour?
I followed your advice & double quoted & have pretty robust logging in the actual API fetch call. The columns in the Google Sheet are formatted as text. However it returns a 200 response, just empty! Any help is appreciated. Cheers Brad

[Marc_Dillon · Accepted solution]

Looks like you've regressed back to the original issue of not putting the key values between quotes.

[bradlegassick]

Hi Marc, thanks for the observation, I did several tests & perhaps have messed up my call structure. I'll go over this aspect now. Cheers for the fresh eyes!

[bradlegassick]

I've tried sending the call with the "" log here - Sending Payload: {"Action":"Find","Properties":{"Locale":"en-AU","RunAsUserEmail":"(PII Removed by Staff)","Selector":"Filter(Invoice_Detail, [link_Invoice] = \"ad389abf\")"},"Rows":[]}
Actual call here:

const invRowProperties = {

'Locale': 'en-AU',

'RunAsUserEmail': '(PII Removed by Staff)',

'Selector': 'Filter(Invoice_Detail, [link_Invoice] = "'+ invoiceId +'")'

};

If you can see the error in the actual properties thanks, otherwise I will open a ticket. Cheers Marc.

[bradlegassick]

@Marc_Dillon I did a bit of reading Troubleshooting API calls & it seems my security filters were causing an issue. I've just tested now & the records are all found & returned. So I will need to review my security filters. Turns out the document being processed & not returned via the API was dated 18 Sept, tomorrow & the security filters don't allow documents after todays date! So there we go, I gotta be more careful. PS I've added the "" back into the calls. Thanks for the help. Cheers