SELECT Image returns incorrect path when created from Form. Using a shared Sheets file.

AppSheet AppSheet Q&A
,
1

I have just encountered very confusing behavior from a SELECT expression to populate an Image column, that was working correctly prior to ~2024/05/01, but is now failing.

I am using a shared Sheets file as a table source, which may be compounding this problem, and I don’t know how common it is to use this setup.

I already have workarounds to solve this problem (VCs), but I’m hoping someone can shed some light on what has changed.

Sorry about the length, I’m going to leave all the details in case someone has similar issues in the future.

Preface

  1. This appears to only be an issue because I am referencing an image via SELECT(Lookup_Table[item_image],[item_key]=x) in a static column.
  2. I get item_key from a SORT from another table, for UI reasons, but the error occurs if you hardcode item_key, so it is unrelated to my SELECT statement that returns item_key. So I’ve written the expressions as [item_key] = x.
  3. I am assuming this is specific to the particular setup I am using: the affected images are not stored locally, their root folder is the same root as the shared google sheets file that AppSheets has access to (via sharing permissions on the sheets file set to “anyone can view”).
  4. I use the shared sheets file because it lets me update a single source, and users can fork my app (to change it to suit their needs) but still access the canonical shared sheets file.
  5. I also assume it is related to the recent change in how Google permits access to Drive files (which occurred around the same time), although since my method still technically works, this error may be an unintended consequence of that change. But maybe it’s a coincidence.

The problem

I had an expression which has been working for 6 months, but the results have changed since ~2024/05/01

NB:
“Lookup_Table” is a table from the shared google sheets file.

Added *** to obscure details that aren’t relevant

All expressions are populating Session[image], a static column, type Image. It is in a separate sheets file, which is local to the app.

The previously-working method:
  1. “Lookup_Table::”&ANY(SELECT(Lookup_Table[image],[id]= x))
  2. Expression result (row created from form):
    Lookup_Table::images/correct_image.png
  3. This points correctly to: (https://lh3.googleusercontent.com/_qrvm***)
Different result since ~2024/05/01:
  1. “Lookup_Table::”&ANY(SELECT(Lookup_Table[image],[id]= x))
  2. Expression result (row created from form):
    Lookup_Table::https://www.appsheet.com/image/getimageurl?appName=appname&tableName=Lookup_Table&fileName=images%2Fcorrect_image.png&appVersion=version&signature=signature
  3. As expected, this fails, BUT:
    In table view, after creating the row with a form, the image DOES appear for ~2 seconds, and is then replaced by a warning triangle.
    Appsheet was apparently able to resolve the path sufficiently to display a (cached?) version, but then replaced it, with no user input, on an otherwise static page.
    I don’t know how to capture what the image path was for the correct-but-temporarily-displayed image.
  4. Furthermore, when a bot causes update to the row (by changing another column), expression result changes to:
    Lookup_Table::Lookup_Table::images/correct_image.png
    (which also fails, as expected, but shows that the path resolving is not completely different than before)

Attempted Solution #1:

  1. Remove “Lookup_Table::”
  2. Expression ANY(SELECT(Lookup_Table[image],[id]= x))
  3. Expression result (row created from form):
    images/correct_image.png (this path does not exist, so the image does not load)
  4. Use bot to trigger update to row, expression result changes to:
    Lookup_Table::images/correct_image.png
    Images resolves correctly (https://lh3.googleusercontent.com/_qrvm***)

Attempted Solution #2:

  1. Use a virtual column Session[VC image]
  2. Expression ANY(SELECT(Lookup_Table[image],[id]= x))
  3. Image resolves correctly (https://lh3.googleusercontent.com/_qrvm***)
  4. BUT test expression fails (this is just part of the return message):
    'images/correct_image.png' not found
    Probable cause: N/A
  5. AND I set a static column to the result of the Session[VC image], and got yet a different result:
    images/correct_image.png (this path does not exist)

I’m using Session[VC image] because it appears to work fine. I’m mostly curious about how/why the image path resolving appears to have changed recently.

The range of outputs I got, and the fact that the expression resolves differently when created from a form, or re-calculated by a bot/row update, is a bit baffling to me.

I don’t love patching over a problem that I don’t understand, in case there are wider-ranging effects that I’m not aware of.

2

The second parameter for SELECT should be an expression that returns true or false, so it should be for example [item_key] = “something”, or [item_key] = [_ThisRow].[someColumn].

The Result of SELECT is a list, so you cannot compare it with a single value like [id]. At least you’d need to encapsulate your SELECT() within ANY().

Also, I’m not sure why would you need two SELECT statements instead of writing the conditions directly into a single one.

3

I was just using short-hand for the expression, I don’t need assistance with the expression, I tried to clarify that in my preface.

I’ll re-write the expressions in my post, but that’s not related to the issue.

Again, just short-hand. The expression is not the issue, the question is about image paths and how they resolve, and why things have changed.

I tried to clarify in my preface that the issue occurs regardless of the second SELECT.

The second select is used because I am sorting the results of rows from a related child table. If I can do that in a single statement I would like to that, but it would not address the issue that this post is about.

/edit I’ve amended the expressions in my post to be more general.

1 Like