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": "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:
- 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.
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();
}
}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.
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.
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.
Hi @nico
Sorry, same content.
I think it would be a more user-friendly feature for citizen developers if Status could be expressed in more detail regarding Automation Monitor.
For example, this is the log when I forgot to set the Return value in my Apps Script.
It is true that the Error message shows the contents of the message, but the Status is Complete, so it is not highly visible.
Especially for non-English speaking users, it may take time to identify the problem if the Status information is green.
Since most citizen developers do not have good debugging skills, I think this feature will be more widely accepted by a wider range of users if Monitor is made more user-friendly.๐ค
Hey @RensD I just saw this little bit of exchange and I can see what the problem is for you. It's just a misunderstanding between what you are naming your appscript function (geocode) and the name of the Step that is calling it.
When you return values from Appscript and want to write them into a column, the system is expecting you to give the name of the Step, not the name of your script.
In this case, [geocode].[Output] is the name of your function and not the name of your step (task).
Here is a bot that calls a script
On the following task (StoreValues) the data that is returned from the script is stored in columns using the name of the Step.
[CreateNote].[Output] NOT [CreateNewNote].[Output]
Apologies if you have already solved this and I missed it!
Two ideas:
1. is the Task configured as a "string" return type in the Apps Script Task? Should look like this:
2. Like Marc is alluding to, you can only use this return value in the same process as you're calling the script in (but you can write it back to a table if you'd like to persist the value).
As I prepare to upgrade a production app to include new automations I developed in a non-production app, I have some questions regarding potential constraints and hiccups related to Call a script tasks. I'm sure I can test these details if necessary, but it would also be helpful to get the answers documented here for everyone's awareness. Thanks, @nico or community friends who may have already worked through these details, for any guidance.
1. Shared projects
Is it possible for a Call a script task to reference an Apps Script project that is shared to the app owner? I don't see a way to select a project that is only shared to the owner. Maybe by adding a shortcut to My Drive?
I do see that it's possible to transfer ownership of a project. If an app task already references a project whose ownership changes but is still shared back to the app owner, will the task continue to function?
Screenshot: Seemingly no way to select a project shared to the app owner
Screenshot: Option to transfer ownership of project
2. Project versions
Does a Call a script task always call a project's head deployment? Does it depend on whether the project has any versioned deployments? Is it possible to specify in the task a specific deployment version? Flexibility here would facilitate maintaining production and non-production versions of a script within the same project. Without flexibility, it's presumably necessary to maintain separate projects and point production and non-production apps to distinct projects.
3. Task properties persistence
If I change the project referenced by a task (e.g., to reference a production project instead of a development project from an upgraded app), do the task's other project-related settings (e.g., Function Name, Function Parameters, Return Value) persist or do they reset to blank and need to be re-entered even though they're the exact same values compatible with both projects?
Screenshot: Project-related task properties
Update: I did some basic testing regarding some of my points.
1. Shared projects: If a GAS project is shared to you, add a shortcut to your Google Drive. Then you can select it from an AppSheet task and it functions fine.
If you transfer ownership of a GAS project that is already used in an existing AppSheet automation task, the task seemingly continues to function seamlessly with no changes necessary within the app. Upon transfer, it looks like a shortcut is automatically added to the previous owner's My Drive; I did not test whether there's a dependency on retaining that shortcut.
Note that it's not possible to transfer project ownership into/out of a Google Workspace organization (although maybe there's some workspace admin setting that could allow this). Nonetheless, it is still possible to share a project into/out of a Google Workspace.
3. Task properties persistence: If you change an existing task's Apps Script Project, all the other project-related task properties sufficiently persist:
- Function Name: This property persists as long as the function is available in the new project. Even if it isn't available, the other properties do persist.
- Function Parameters: These properties persist, and are helpfully flagged if they're not present in the new project's selected function.
- Return Value: These properties persist; just like when they're originally defined, I think AppSheet doesn't validate they're actually returned by the function (e.g., the key names if the function returns an object).
