I think this is a client-side issue where it doesn’t pass the full context to the expression validation. I’ll get a bugfix released momentarily (likely by Friday).
Thanks for the report,
-Nico
1 Like
@nico Thank you for looking into it, could you give me a buzz here when you release a fix so that I test with my own environment? thank you again
Expression validation on [_THISROW_BEFORE] and [_THISROW_AFTER] should appear correctly now:
Please let me know if the issue persists.
1 Like
Hi @nico
Thank you for quickly working on this to fix.
I tested with my app and the error is gone and the expression is working as expected. Thanks again.
Thank you for the above @nico , unfortunately I do not see the option panel showing the different return types. Is there something I need to configure in AppSheet? I have an App Script that returns a JSON object, which I could transpose into an array if there are limitations. Are there further examples of the return value being the ‘object’ type and or ‘array’ type to be processed in automation?
This appears to be just what I was looking for. Any further examples would be of great help. Thank you.
1 Like
@Harriswe yup, you can send a JSON Object with the limitations I mentioned previously: no nested objects and only the first level can be arrays.
Here’s how to configure it in AppSheet: you’ll first need to select both an Apps Script project and an Apps Script function to run. Then, the option to toggle on a return value will appear, toggle that on and select “Object” and add each of the key names and values types:
In this example, I’ve configured the return value to accept an object of the form:
{
"ArrayKey": ["Text1", "Text2", "Text3"],
"NumberKey": 123,
"TextKey": "Some text",
"BooleanKey": true
}
Be sure to return an object from your Apps Script function rather than as a string otherwise it will not convert correctly (i.e. don’t return with JSON.stringify()). Hope that helps!
2 Likes
Hi @nico
But nested objects can be treated as first-level arrays (to be processed further), right?
If I understand correctly, you’re saying you can cleverly work around this limitation if you want to return an array of objects as an object of array instead? Yes, that’s true.
Like an array of object like [{a: 1, b: 2}, {a: 3, b: 4}] is not supported but you can instead return as {a: [1, 3], b: [2, 4]} and the latter is supported.
5 Likes
@nico Yes, you understood correctly, and thanks much for your answer!!
3 Likes
Many thanks @nico and @Joseph_Seddik I have the former requirement, where the object returned is an array (representing the records) of objects. For example
[{a: “12-Apr-23”, b: “UK”, c: “Some currency”} ],
[{a: “12-Jan-09”, b: “US”, c: “Digital currency”}], etc
Since this is unsupported it appears I’ll have to pass back individual records, one array element at a time or transpose it to something like…
{
a: [“12-Apr-23”], “12-Jan-09”, …],
b: [“US”, “UK”, …],
c: [“Some currency”, “Digital currency”,…]
}
Is that correct? Is that the only workaround? 
Actually thinking about it some more I do not think the transpose approach outlined above is feasible. It is basically deconstructing a record on the GAS side only to try an reassemble the record to be stored in the AppSheet side, which just appears messy. Better to have the GAS script call a webhook in AppSheet to store / update each record running through the array of nested objects the external API call returned.
Yes, if you want to use return values the easiest way is likely to transpose the data in that way and has the advantage of being easier to use in expressions afterwards.
Other possible workarounds:
-
Write the data back to a Sheet (assuming you’re using Sheets) using Sheet.appendRow in Apps Script
-
Use the AppSheet REST API: Add records to a table
I wish it were easier but until AppSheet has a JSON or dictionary type it’s difficult to do
Actually using MySql DB and not sheets. Transposing just creates it own headaches I think. Would be super cool if
[{a: “12-Apr-23”, b: “UK”, c: “Some currency”} ],
[{a: “12-Jan-09”, b: “US”, c: “Digital currency”}], etc
were supported. Are there any plans soon for a JSON dictionary type?
@Harriswe Relying exclusively on GAS will deny the benefit of getting immediate responses for your app, without the need of manually synching the app to read the result from the source.
The deconstructed records can perfectly be processed by AppSheet functions, and in case you need some extra processing steps by GAS that could complex to do or cannot be done by AppSheet alone, then I’d still call GAS functions repeatedly from AppSheet automation till the desired results are obtained instead of handling everything independently by GAS. This is the power of AppSheet’s GAS integration.
3 Likes
Adding a new AppSheet type is a large undertaking so I can’t speak to support for a new Dictionary type, but I’ve noted the request and understand that it’d be helpful in cases like this.
For now, I’d recommend either transposing the data or using the AppSheet REST API.
2 Likes
Hi @nico sure I can appreciate that it would not be straight forward. These days data transfer using the JSON format is a standard. Thank you also to @Joseph_Seddik for your comments. Does it remain the case that there are no loop constructs that can be used in Automation? In this example it would be to loop through the array elements reconstructing and saving the records. This is a missing component for AppSheet and the comments go way to back 2019-2020. The loop workarounds using a combination of Actions is not always feasible.
I ask because even if I transpose the data I am not clear how to proceed from within AppSheet from that point on. Which makes calling a web-hook from within GAS look more practical given the missing loop capabilities within AppSheet, unless that has changed.
1 Like
Do me a favor? Now the new feature was recently released on Webhook task (it does fit with API call as task name though). I have been testing it and found useful. It was good that we can reach out hands out to the nested field as illustrate in the document.
Spacial syntax of dot notation make it possible to access to the nested object. I also tested the same syntax with Google Apps Script return value step, however, I m seeing the error we could not reach to the nested field. Is this because Google Apps Script return value function is NOT able to access to the nested field like Webhook return value? In essense, the both is the same sort of functionalities, so I expected we could reach to the nested field on GAS task. Or am I missing something important point?
6 Likes
Currently the nested JSON syntax only works for Webhook return values, unfortunately. I (and my team) agree it makes sense to have the same support in Apps Script. I’ll work on this to add the support to Apps Script Tasks as well.
I don’t want to over-promise but it seems doable relatively quickly.
4 Likes
Thank you for your quick comment, all well noted.
Yes, both (GAS return, Webhook return) could be recognized the same from the user prospective, it is only a matter to call a API (or webhook) via AppSheet server or usng GAS, so the capability of dealing with object is ideally to be constant.
Looking foward to seeing this new feature soon. thank you again
1 Like
Hi @nico any update on this request? I ask because my team and I are building an Api to a 3rd party application using GAS to handle the nested JSON. It would simplify our development effort if knew some idea of timescales?
At present we are making multiple calls from GAS to the AppSheet API in order to handle the nested JSON returned by the 3rd party API call.
Many thanks. 
Soon - I’d estimate not much more than a week from now. Most of the code is written, just needs some code review and time to rollout to 100%.
3 Likes