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:
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:
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,
};
}
See how to configure return values for Apps Script tasks for more details. A quick summary is below:
To send us feedback on this feature, you can reply or comment on this post.
You better to state this achievement in your following annoucement so that more people get aware of this new achievement.
@Koichi_Tsuji - Thank you! It will be included in today's release notes (with a doc update)!
Hello,
I would like to know if the return of custom functions will also be implemented for use directly in the fields, as a function native to the platform?
No plan for this at the moment, though that would be cool. You can achieve a similar effect by making an Apps Script task that writes to particular fields in a table (using data action in the automation) and then you can use those fields in your regular app flow.
@nico , congrats for the wonderful work done by bringing script function.
I have doubt. Can you please tell me to get values and fed somewhere in the columns of a table if a script returns multiple values .
//Return values
URL 1 : api.hdiiekdndbd....
URL 2 : api.idodoelwle
Something like that. How to refer to the specific value for setting up values ?
In same way , if an api returns nested output. How to pull needed data from it. ?
Sample output by an api
Output:
[
{
"Validation Status": "Valid Input",
"Chargeable Weight": "4500",
"Base Tariff": "300",
"Service Tax": "54"
}
]
_____________________
@Koichi_Tsuji please help
Hi @jaichith
Thanks for the question and the sample output. Unfortunately a list of objects as a return object isn't a supported format within for AppSheet's Apps Script Task return values (see: Apps Script Return Type Docs). You will have to modify the returned object with array values in Apps Script before sending it to AppSheet. For example:
{
"Validation Status": ["Valid Input", ...],
"Chargeable Weight": ["4500", ...],
"Base Tariff": ["300", ...],
"Service Tax": ["54", ...]
}
(Notice the returned object values are arrays instead of singular values). And then you can access each key using the standard list functions.
As an addendum, here is some Javascript code to do the transformation from an array of objects to an object with array values if you'd like (for use in Apps Script):
// Any number of objects here
const arrayOfObjects = [
{
"Validation Status": "Valid Input",
"Chargeable Weight": "4500",
"Base Tariff": "300",
"Service Tax": "54"
},
];
function convertToObjectOfArrays(arrayOfObjects) {
return arrayOfObjects.reduce((acc, item) => {
for (const key in item) {
if (!(key in acc)) {
acc[key] = [];
}
acc[key].push(item[key]);
}
return acc;
}, {});
}
const returnValue = convertToObjectOfArrays(arrayOfObjects);
/*
returnValue will be:
{
"Validation Status":["Valid Input"],
"Chargeable Weight":["4500"],
"Base Tariff":["300"],
"Service Tax":["54"]
}
*/
(Notice the returned object values are arrays instead of singular values). And then you can access each key using the standard list functions.
Here you are asking me use functions like Index(list ,3) to fetch "base tariff " ?
Yes, to get individual values you can use the INDEX() function. So for example `INDEX([ReturnValue].[Base Tariff], 1)` to get the first base tariff.
Ok I will try and update you.
This new feature is a great addition to AppSheet, but I was wondering if the team has any plans to upgrade it or the backend. Sometimes it can be a bit difficult to use, for example, when I'm trying to do a certain task like this (Very basic)
@Marc_Dillon wrote:
So for a fully-chained workflow here I'm going to do:
- Bot triggers on adds of a scanned tracking number record.
- Call GAS function and passes through the tracking number.
- GAS function redirects that tracking number to an Integromat scenario.
- Integromat looks up the associated CRM records from the tracking number, changes the status value, and returns the necessary values to GAS.
- GAS then returns the same values to Appsheet.
- Next Bot step creates the label with the info and emails it out.
It only takes about 3 seconds to do everything in real life, but when it comes to AppSheet, it can feel like a painfully slow process.
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 ?
I'm curious, have you compared how long similar tasks groups are taking in other automation systems like Zapier or Make? Or can you give an example of another system that can do such combinations of tasks in less time?
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]
Thanks for the report, added a bug in our internal tracking system for further investigation. No ETA on it yet. Please use the workaround to set up the return value again after duplicating the task.
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!
Hi @mateo,
Glad to hear you're getting use out of the feature! As for the issue, it's hard to know what the problem is without seeing the Automation Monitor. After execution, if you look at that, it should give you some hints for what the problem is. If you don't see logs or the problem is unexpected, please reach out to support and it'll file a ticket that will get routed back to me with the information I need to diagnose (app id, user id, consent to access apps).
One common problem is that the return value is different than the one specified in the return so it cannot be parsed. For example, if an object from your Apps Script function but the return value is set as a number in the task it will fail to parse the value and set a default. Instead, those types need to be kept in sync. Additionally, AppSheet expects the return value from Apps Script to always be the same type which is a constraint beyond what's allowed in Javascript/Apps Script.
Thanks for the quick reply, appreciate that.
The problem is that it worked for the last 3/4 months without issue + the next task in the process parses the return value correctly. No error or anything that could lead me to the solution in the monitor or log analyzer. Both say it is all good and fine. I will try to reach support, hope they can help this time. Thanks again!
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.
That error can occur when you are editing a bot that is currently set to disabled. Can you verify that your bot is enabled while you are editing?
The bot is not disabled. It is enabled. Only after the first step of calling the script is done which is verified, I have enabled this.
Will app deployment matter here?
No, deployment won't affect whether it runs the bot or not.
Can you rename your task that calls the script to be just 'CallJira' rather than 'Call the Jira Creation Script'? Leave out the spaces so that your values can be accessed via [CallJira].[status], [CallJira].[key]...etc.
I have done the changes in a trying environment
Under Actions
It is still throwing error. I have deployed the app too here. just to check
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.
Yes, it worked. Thanks for the help. @scott192
Wrong Scott, but you're welcome ๐
Corrected the tag. Thanks for your help.
Devil lies in the details right!!!