Return Values in Apps Script Tasks

[nico]

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": "john.smith@example.com",
        "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:

1. Configure an Apps Script task as normal with a function that has a return value
2. Enable the Return Value toggle at the bottom of the panel
3. 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

1. Click the return type of your Apps Script function.
2. Select the AppSheet type it should convert to in the drop-down.

Use the Return Value in your automation

1. 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:
1. `[StepName].[Output]` for strings, booleans, numbers, arrays, and dates
2. `[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.

[Koichi_Tsuji]

@lizlynch 

You better to state this achievement in your following annoucement so that more people get aware of this new achievement.

[lizlynch]

@Koichi_Tsuji - Thank you! It will be included in today's release notes (with a doc update)!

[DP_Vargas]

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?

[nico]

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.

[jaichith]

@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 

[nico]

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.

[nico]

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"]
}
*/

[jaichith]

(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 " ?

[nico]

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.

[jaichith]

Ok I will try and update you.

[Rifad]

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:

1. Bot triggers on adds of a scanned tracking number record.
2. Call GAS function and passes through the tracking number.
3. GAS function redirects that tracking number to an Integromat scenario.
4. Integromat looks up the associated CRM records from the tracking number, changes the status value, and returns the necessary values to GAS.
5. GAS then returns the same values to Appsheet.
6. 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 ?

[Marc_Dillon]

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?

[Rifad]

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. 

[baba_sawane]

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] 

[nico]

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.

[baba_sawane]

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