AppSheet API Find with Properties.Selector returns no rows for strings of this pattern: #a#a#### / #A#A####

AppSheet AppSheet Q&A
1

I have identified a specific string (3a3a5769) generated by UNIQUEID() that the AppSheet API Find Selector will not return. I explored this over several variations and reduced the issue to the following pattern: #a#a#### / #A#A#### / #a#A#### / #A#a####.

For example, this fails:
{
“Action”: “Find”,
“Properties”: {
“Locale”: “en-US”,
“Selector”: “FILTER(“User Image”, [User Image ID] = “1a1a1111”)”
}
}

Note: that same selector will retrieve the row if the pattern identified above is altered. For example, a row with [User Image ID] = “1a1b1111” will be returned as expected.

___
this does not fail:
{
“Action”: “Find”,
“Properties”: { “Locale”: “en-US” },
“Rows”: [{ “User Image ID”: “1a1a1111” }]
}

These work (Selector returns the expected row)

  • 3a3b5769

  • 3b3a5769

  • 3b3b5769

  • 1b1b1111

  • 7A1A77c7

  • "1a" & "1a" & "1111"

  • "1" & "a" & "1a" & "1111"

These do not (Selector returns [] even though row exists / is retrievable via Rows)

  • 3a3a5769

  • 3a2a5769

  • 3a9a5769

  • 2a9a5769

  • 2a9a1234

  • 1a1a1111

  • 7a1A7777

  • "1a1" & "a" & "1111"

  • "1a1a" & "1111"

  • "1a" & "1a1111"

  • "1" & "a" & "1a1111"

Summary

In the AppSheet REST API v2, Action: "Find" behaves inconsistently depending on whether results are requested via:

  • direct key lookup using Rows: [{ <col>: <value> }] (works), versus

  • expression evaluation using Properties.Selector (fails for specific patterns / expression forms)

This is reproducible across multiple AppSheet apps and multiple Google Apps Script projects.

Selector-based Find returns an empty result set ([]) for certain ID strings and for certain string-construction expressions, even though the target row exists and can be returned via the Rows method.

Endpoint

POST https://api.appsheet.com/api/v2/apps/{appId}/tables/{tableName}/Action

EDIT:
This is my actual selector in the code, the version above i copied from my logging output, that’s why the quotation marks do not look correct. This is not an issue of using quotations incorrectly.

const selector = `

FILTER(

“User Image”,

[User Image ID] = (“1a1a1111”)

)

`;
2

Instead of:

[User Image ID] = "1a1a1111"

try:

[User Image ID] = '1a1a1111'
2 Likes
3

Sorry, yes, I tried that as well. It does not appear to change the outcome.

2 Likes
4

Whoops, I missed the other quotes:

"Selector": "FILTER("User Image", [User Image ID] = "1a1a1111")"

should be:

"Selector": "FILTER('User Image', [User Image ID] = '1a1a1111')"
3 Likes
5

Thanks for the suggestion, but I tried that. Nothing like that seems to help. My current selector works as is in every case except the shape I indicated.

3 Likes
6

Attn @Adam-google

2 Likes
7

Strange.

I tried it myself. I can confirm the issue is present on my end too.

Further, I tried using both STARTSWITH and ENDSWITH within the selector filter. It appears the 3rd character is the culprit.

STARTSWITH( [id] , ‘1a’ ) ==> returns the correct row

STARTSWITH( [id] , ‘1a1’ ) ==> returns []

ENDSWITH( [id] , ‘a1111’ ) ==> returns the correct row

ENDSWITH( [id] , ‘1a1111’ ) ==> returns []

Reminds me of the scientific notation issues, where if the id value was some number following by an e, then more numbers, sheets converted it to a number. But I double checked that everything was firmly set to text.

2 Likes
8

I pasted the OP into Google (Search AI Mode) and asked some questions. Ultimately, it gave me:

The “Time Literal” Hypothesis:
The behavior you isolated (#a#a fails, but #a#b works) strongly suggests the evaluator is misinterpreting the value as a Time/Duration literal rather than a Hex value.

  • The Pattern: In many loose parsing formats, 3a is shorthand for 3 AM.
  • Why it fails: The parser likely sees 3a3a... and tries to interpret it as a time-based syntax (e.g., 3 AM followed by 3 AM…), gets confused by the repetition or the following digits, and fails silently (returning []).
  • Why 1a1b... works: The character b has no meaning in time formats (it doesn’t mean AM or PM). The presence of b breaks the “Time” pattern immediately, forcing the parser to fall back to treating the whole value as a simple Text String, which is what you want.

Summary of Fixes

Since the bug lies in the ambiguity of the Selector expression evaluation:

  • Force Text Type: Ensure the column [User Image ID] is explicitly defined as Text in the AppSheet definition (not “Ref” or “Auto-Compute” if possible).
  • Wrap in Quotes (Again): Try wrapping the value in double quotes inside the string to force a literal interpretation, though this is tricky with JSON escaping (e.g., "Selector": "FILTER('Table', [ID] = '\"1a1a1111\"')" ).
  • Use Rows Method: As you noted, the Rows lookup works because it bypasses the expression parser and performs a direct index seek.
2 Likes
9

If correct, a universal fix would be ("_" & [User Image ID]) = "_1a1a1111".

5 Likes
10

Yep, that works.

2 Likes
11

This is a thoughtful analysis and solution @Steve :+1:

1 Like
12

Thank you! That’s a great solution.

1 Like
13

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.