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

[Marc_Dillon]

Awesome! So now we can effectively handle responses from API calls, by going through a custom built GAS before passing a value back to Appsheet, without having to force the GAS itself to interact directly with the data source.

Use-cases that I can think of immediately:

1. 64-bit encoding of images in order to pass them to other APIs
2. URL shortening for SMS messages
3. Weather services
4. Geo-encoding lat-longs to addresses (or vice versa?)

I’m sure there are others…

[Thales_Taranto]

I’m glad to hear that! That’s very good news!

I was hoping to do some API calls to a adress form auto fill with GAS since i could get return i made a Spreadsheet formula, but now i will give it a try!!

[Jonathon]

Previously, unless you had enterprise/API access, scripts or integrations were limited to silently posting/updating data to the underlying spreadsheet. This effectively removes that limitation whenever the request is initiated from AppSheet.

[takuya_miyai]

Hi @nico

This is an exciting feature.

The column name of the return value is [Output], but if there is a reserved word, I think it would be good to have an underscore at the beginning, like other column names.

I think following this.

[Your StepName].[_Output]

[takuya_miyai]

Hi @nico

isPastDue may need to be changed.
Passing “2000/1/1” did not yield a return value.

The following changes gave the expected return value.

function isPastDue(date) {
  // return new Date() > new Date(date);

  if(new Date() > new Date(date)){
    return new Date();
  }
}

[nico]

Thanks for the input. Unlike table refs like [_THISROW] in the top-level namespace there is no namespace collision with [Output] because it’s in the step name table ref so it’s guaranteed to be unique.

[nico]

Takuya, the example in the post should be correct but the return value in AppSheet should be configured to be “Boolean” instead of a “Date” because it’s just returning true if the current date is past the provided date “the due date” and false otherwise.

Of course you can modify it to return a date instead if you’d like for example:

function currentDate() {
    return new Date();
}

These are both “toy” examples because you can do both of these function in AppSheet’s expression system but hopefully it gives an idea of how you’d use the return value. I expect real-world use-cases to be more complex than this.

EDIT: One more thing, you should notice a conversion error in the automation monitor that says the conversion failed to help for debugging purposes.

[takuya_miyai]

Thanks @nico .
And I’m sorry. I was mistaken.

I’ll try something that AppSheet’s features alone can’t do.

[dbaum]

This is really exciting!

One seeming clarification regarding the bold text in the following part of the announcement is that the subsequent step’s task needs to be an unlinkable “custom action”–not just a linkable task with linking turned off.

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:

[dbaum]

If anyone wants a quick-and-dirty script to try out this new functionality for a nonetheless real use case, here’s one I put together. If you have a country code from a phone number, you can use a script (which in turn relies on an API) to get (and now return!) a corresponding flag emoji. I’m sure the script could be optimized in all sorts of ways, including trapping for errors and variations in what’s considered a country code, but I confirmed it works at least in straightforward cases.

function getFlagEmojiForCountryCode(countryCode) {
   
  // Call REST Countries API (https://restcountries.com/)
  const response = UrlFetchApp.fetch("https://restcountries.com/v2/callingcode/" + countryCode + "?fields=alpha2Code");

  // Parse response
  const responseJSON = response.getContentText();
  const responseData = JSON.parse(responseJSON);
  let flagEmoji;
  let countryAlphaCode;
  if (responseData.length > 1) {
    flagEmoji = "N/A (multiple)";
  } else {
    const objCountryAlphaCode = JSON.parse(responseJSON)[0];
    countryAlphaCode = objCountryAlphaCode.alpha2Code

  // From https://dev.to/jorik/country-code-to-flag-emoji-a21
  const codePoints = countryAlphaCode
    .toUpperCase()
    .split('')
    .map(char =>  127397 + char.charCodeAt());
  flagEmoji = String.fromCodePoint(...codePoints);
  };

  return flagEmoji;
}

[nico]

That’s right, it needs to be an unlinked action or task. The reason is because the return value is scoped to the process so you can only access the return value in later steps in the process. The Apps Script Task itself with the return value can either be linked or not.

[dbaum]

Understood. What initially confused me is probably the phrasing of the requirement from your original post for “a subsequent unlinked step”. I believe “step” components are not themselves custom/reusable/unlinked/linked, but rather that status applies to the action or task that a step includes.

I created an automation by compiling independently created reusable components with linking turned on, including a step referencing a data action in my app, then realized the requirement from your original post for “a subsequent unlinked step”, then turned linking off for various components of the automation (the step/action referencing the returned value, the automation’s other step/task, its process, and its event components) each one by one, and it still didn’t work throughout. I may have made some mistake along the way that I don’t realize, but I ended up starting over with a new step and new data change action defined as a custom action from within the step, and that was finally what worked.

[takuya_miyai]

Hi @nico
I’m currently trying to achieve geocode functionality using return values.

It is a simple Apps Script that will return Lat and Long if the address is passed as text.

function geocode(address) {
 var response = Maps.newGeocoder().setLanguage('ja').geocode(address);
 for (var i = 0; i < response.results.length; i++) {
   var result = response.results[i];
   Logger.log('%s: %s, %s', result.formatted_address, result.geometry.location.lat,
       result.geometry.location.lng);
  //  return result.geometry.location.lat;
   return {
        "lat": result.geometry.location.lat,
        "lng": result.geometry.location.lng
    };
 }
}

At this time, if I set the data type of the Key I receive in AppSheet to TEXT, the return value is Decimal, so I cannot get it and it will be NULL.

If I set it to Decimal, it works as expected.

There is nothing wrong with the functionality, but it is difficult to find the wrong data type in question because the error is not displayed in the Automation monitor nor in the Audit log.
I would like to see an error somewhere if the data type received is different.

Thanks.

[nico]

Takuya, thanks for trying out the new feature!

A few things I wanted to mention. If you want a LatLong translation, one easy wait to do it is to return it as a string like "-28.05722, -51.75104" then you can just configure it as returning a string with “LatLong” as the AppSheet type and you get it directly as a pin on the map etc.

And

function geocode(address) {
   ...
   return result.geometry.location.lat + ", " + result.geometry.location.lng;
}

But there are good reasons to return it like an object if you want (e.g. to do calculations). In your first screenshot notice there is an error even the execution is successful: Failed Apps Script Type Translation: Type 'Number' failed to translate to 'Text'

I made the decision that the automation would still complete even if the type translation fails because some users may return different variable types on errors and I wanted to support that as well as not break existing workflows that might return different type.

There are two ways to fix that, either change Apps Script side to be strings:

1. Explicitly convert it in Apps Script (notice the .toString() at the end:

function geocode(address) {
   ...
   return {
      "lat": result.geometry.location.lat.toString(),
      "lng": result.geometry.location.lng.toString()
   };
}

2. Just configure it to accept a decimal on AppSheet (as you noted)

You’ve observed we’re stricter on translating types from Apps Script. One “trick” I’ll mention is that all types can be converted from a string to any type but not the other way around so a string like "123.42" to decimal is fine but a number like 123.42 to text will not work.

Hopefully that all makes sense.

[takuya_miyai]

Hi @nico
Thanks for the detailed follow-up.

The handling of the Geo code is Apps Script’s forte and is also information that AppSheet can make great use of.
Your suggestions will expand my handling of Geo codes. Thank you very much.

One thing I would like to discuss with you is that when it contains a wrong type error as we mentioned, I would like it to be clear in the Automation error.
Specifically, when similar error messages are included, I think it would be a feature that would be easier to use if we could display something in the log to draw attention to it, such as Warning instead of Complete.

[anonymous]

Has this not yet been rolled out to everyone? I do not see any Return Value toggle or section in the Task configuration panel.

[nico]

It will finish rolling out to everyone tomorrow at roughly 10am PST (assuming monitoring looks okay).

Also note you’ll have to select an Apps Script project before the option to select a use a return value will appear (at the bottom of the task configuration panel).

[anonymous]

That’s great news. I have been banging my head against the wall trying to figure out why I don’t see it. And I do have an Apps Script project selected, that returns a value.

[anonymous]

Did this get rolled out for everyone today? I don’t see it.