LinkedIn
Twitter
We're happy to announce an enhancement to Apps Script Tasks that will allow you to use the return value of a Google Apps Script function within your AppSheet automation and pass it to a later step in the same automation process.
Example uses:
- Write the return value of a UrlFetchApp.fetch call back to one of your Google Sheets so that APIs responses can be incorporated in your app
- Update / Add a new table row or value based on the output of an Apps Script function so that complicated data manipulations can be performed
- Write a multi-step conditional automation flow based on the result on an Apps Script function so that more expressive data flows are possible in AppSheet
This feature will be rolling out gradually starting today. When available, you will see a new option appear at the bottom of the Apps Script task configuration for an optional return value:
What should I try out?
You can try returning values of different types from Apps Script. For example, a simple boolean return value:
function isPastDue(date) {
return new Date() > new Date(date);
}
Or more complex objects:
function returnASampleObject(userId) {
return {
"userId": userId,
"username": "jsmith",
"email": "[email protected]",
"creationDate": new Date(2021, 11, 25),
"isAdmin": false,
};
}
How can I use the return value from an Apps Script function?
See how to configure return values for Apps Script tasks for more details. A quick summary is below:
- Configure an Apps Script task as normal with a function that has a return value
- Enable the Return Value toggle at the bottom of the panel
- Set the AppSheet Return type that is returned from your Apps Script function
- For example, if your function returns an array of strings, the corresponding AppSheet type will be a list of text elements
- Click the return type of your Apps Script function.
- Select the AppSheet type it should convert to in the drop-down.
Use the Return Value in your automation
- To reference the return value in an expression in a subsequent unlinked step in the process, you can refer to the output using the following syntax:
- `[StepName].[Output]` for strings, booleans, numbers, arrays, and dates
- `[StepName].[Key]` for objects where `Key` is the key of your returned object
Limitations
- Return values consisting of arrays of mixed types are unsupported (e.g. cannot mix strings and numbers like [1, “a”])
- Return values consisting of deeply nested objects are unsupported (e.g. objects like {a: “example”, b: [1, 2, 3]} are fine but objects like {a: {b: 1}} are not supported)
How do I give feedback?
To send us feedback on this feature, you can reply or comment on this post.
Hey @Marc_Dillon I have highlighted that the systems you mentioned, ZAP and MAKE, are not exactly the same as the one in question. I referenced your thread to point out a similar workflow.
@Rifad wrote:
1. Update a row in the large app within 2-3 seconds.
2. Trigger the Bot with appscript and return a response takes 15-20 seconds. (My simple GAS actually runs in less than 1 second!)
3. See all changes synced back to the user view in the front end takes another 10-12 seconds.
That is it takes 30 seconds for a user to see the most basic app script results? Isn't that crazy? Seems a little outdated ?
In this situation, I utilized appscript to retrieve data from the maps api. The value was then processed by GAS before being sent to the Google Maps API and returned to appsheet. This entire process took time to complete as shown below
Now, let's see how long it took for AppSheet to complete the process of fetching and running the bot (not including the additional 10-12 seconds for synchronization).
@Marc_Dillon wrote:
another system that can do such combinations of tasks in less time?
I've been testing out different systems, and I must say, Appsmith is impressively fast and real-time. When triggered directly within Google Sheets, it takes less than 5 seconds to complete tasks. It's always interesting to see how practical these frameworks are in real-life scenarios. A colleague of mine was able to run complex workflows using GAS and multiple APIs with Retool for some dash-boarding, updating and fetching data in less than 5 seconds.
I get that AppSheet works in a unique way, but think about how frustrating it must be for a user to wait 30-60 seconds for a value to load. And if there are a bunch of updates in line, they could end up waiting even longer.
Hi,
i'd like to report a bug, happened to me three times so I think it's easily replicable.
When I duplicate an automation that contains a return values, the return values button is switched to off.
There is a visual bug that makes it looks like it's still on, but it's not. I noticed it because I got errors when I tried to use a duplicated automation, the system could not detect any [Output]
A gif of what is happening (sorry my pointer is invisible, i'm just clicking on the button)
It deactivated every return value of that automation by the way
Hey there, first of all, thanks for this feature it is very very useful. Unfortunately, I think I have found an issue, or maybe the problem is on my side...
What is the problem?
I noticed that my bot where I use return value stopped setting it in one of the tasks but set it correctly in others throughout the whole process.
What does the bot structure look like?
There is a "Set Offer URL" task in the process. The return value is not being set here. It looks like the whole task is omitted and in the next one "Log Operation" the return value is set correctly... This is strange. I cannot understand the case here as it was working fine before. Could you please guide me @nico?
Thanks in advance!
I have an error while doing your example,
This is my App Sheet Automation:
Input data in a table:
Execute App Script: (with returned values, key = keys of the function return with their respective data types)
Return userid:
This is my Apps Script: (the same as your example)
When I click "Save" it gives the error:
"Output 'userId' is invalid: Error in expression '[Execute App Script].[userId]' : Unable to find table 'Execute App Script Output'"
@nico Any idea why this is not working?
Hi Emilio,
Your Apps Script task setup looks correct and you've hooked up everything up correctly except that you can't use the task return values in a return process step today. You just have to reference the expression `[Execute Apps Script].[userId]` inside another Task or Data action in the same process instead of a process return value step. Example for the Email task below:
The limitation of not being allowed to use task (Apps Script / Webhook or call process) return values in a process return step is arbitrary so I'll see if I can lift that limitation. It's a fairly advanced automation concept in case you want to re-use processes in your app, the vast majority of apps work with a single process per bot.
Hopefully that helps!
I am trying to utilise this feature, but I have stumbled upon an issue.
I am calling an app script which returns the following values
I am setting these values as part of object in the automation
In the next step, I am calling another action to add the values into the respective columns
But these values are validated in the expression editor under automation. But when I am saving the app, it is throwing error that it is not being evaluated to column in the actions
Can anyone help, where am I going wrong.
Ahh I think I see the problem. You have chosen to use the 'Run action on rows' and then chosen your Update Row action as the action to run on the row?
You cannot do this unfortunately, you have to define the row updates from within the bot. So when it asks if you want to 'Reuse an existing action' do not choose that. Make a new Configure action in the bot.
