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.
I just made the last rollout bump just now. If you refresh the editor page and select an Apps Script task you should see it after you select a script (but let me know if you don’t).
Just want to validate your suggestion, I think showing a Warning here instead of a success or Error is a good idea since it’ll be easier to find but we currently don’t have a Warning state and adding one requires a wider Automation change. I’ve posted a bug internally but due to low severity it’s unlikely it’ll be prioritized in the near future.
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
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?
Update: I did some basic testing regarding some of my points.
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.
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).
Thanks for the questions and answering your own questions
I’m not an expert on Drive itself but so long as the app owner (which remember is the entity executing the Apps Script Task) has authority over the script it should be able to run. There are lots of different ways to organize: Shared Drive, shared authorship over the project, and (as you discovered) making the app as a shortcut might be required to see that in the file selector.
Yes, for configuration simplicity scripts are always executed at HEAD (as opposed to Apps Script web executable which allows you to version a script). A separate development project seems like a good way to mostly support development flow.
Yes, we try to maintain as many properties as we can so we don’t clear configured properties especially if you have a function with the same name and number of parameters. We just keep the Return Value as is if a project is swapped.
Why Automatic Return Value is Hard:
This is probably more detail than you care about but I wanted to explain why we don’t do any automatic computation of the return value you’ll need to do this manually. Note that in general, it is impossible to determine the return type can only be determined at runtime (in the general case), consider a function that is just:
function simpleUnknownType(x) {
if (x > 0) {
return "some string"; // a string
}
return 42; // a number
}
In this case, the return type is either a string or a number depending on the input x. So by setting a return type you are saying that your function has a consistent return type. We could execute the function and then set that automatically as an enhancement but hopefully it’s not too hard to manually select the return type.
It’s certainly not a problem to have to manually define the return values–especially the value type when a single value is returned. My comment was mostly with regard to the “key” names if the function returns an object since those are explicitly defined with the object. Nonetheless, as your example represents, even that’s not fully reliable since different branches of the function might return objects with different keys.
I’ve since encountered some additional questions:
Project name changes don’t sync to the list of projects that appear on my account’s Integrations page. As explained in the feature’s instructions, projects are automatically added there when you add a project to a task in any of the account’s apps. However, the project names on that page never update when you edit the project’s name in Apps Script even though within the task the updated project name does appear. After adding some projects, then appending “OLD” to their names in Apps Script since I created and added new parallel projects from a different Google Workspace, I now have multiple projects with duplicate names in the AppSheet account page. I don’t know which is the “OLD” one in each pair, so I don’t know which to remove from AppSheet using the trash icon.
Multiple project authorization requests. As explained in the feature’s instructions, when the app owner adds a project to a task, they’re asked to authorize access. Even if they do so, when an app co-author is later editing the app, they’re also asked to authorize the project. If the co-author doesn’t authorize, the app and the script still seem to work for users. The purpose–and potential conflict or override–of requiring separate authorization by app co-authors is unclear. Is it intentional? Required?
Can I use return values from a script within a template on a subsequent email/file-creation step, either a simple body template, or a GDoc template (preferably the latter)?
To be clear, I’m being lazy here and not testing anything myself yet, just wondering about a possible issue with a functionality that I’m planning to build, hoping someone can give a clear answer here to save me some time. Thanks!
Yes, you can use the Apps Script return value in an email or file creation Task step including in the email or file template so long as an unlinked within the process. . Example below:
Actually let me describe what I’m building for a client here, others may find it interesting or helpful.
He’s got an existing simple Appsheet app for scanning tracking numbers on received packages. He wants that when a package is scanned, it updates the status in his CRM, and then creates a label that includes information from the associated records in that CRM. The CRM has an API, but it’s pretty labyrinthian, although Integromat (which he already uses) has the appropriate modules for the integration.
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.
Pretty good timing that he came to me with this request just a couple weeks after this functionality was released!
I’ve now encountered a scenario where the function parameter expressions don’t persist. Without changing the task’s project, I selected a different function. Even though both functions require the exact same parameters and AppSheet appropriately left the parameter names and rows intact, AppSheet nonetheless deleted the expressions I had entered for each parameter.
@nico , that seems counter to the stated goal of “we try to maintain as many properties as we can so we don’t clear configured properties” and, perhaps, not intended behavior.
Originally selected function with parameter expressions
I came across another apparently unhandled edge case, @nico . In this scenario, the app creator doesn’t see any indication in the app editor that something is amiss; so, it’s easy for the issue to go undetected until the app malfunctions. Fortunately, the fix is easy.
I had an app in Account #1 that used GAS projects shared from Account #2. I transferred the app ownership from Account #1 to Account #2. Following the transfer, everything in the various Call a script tasks looked fine. For example, the Apps Script Project property showed the project name and the “Script will be executed as…” message showed the email of Account #2.
However, when the automations triggered, the scripts didn’t run. From the projects’ execution logs, I could see that they were not even called. In the Automation Monitor, the processes containing the tasks had an error message of “Failed Apps Script type translation: Return Value invalid”.
In each task, I reselected the exact same project. I didn’t have to re-authorize the project. All the other task properties remained intact. From there, everything worked fine.
FYI:
Before reselecting the AppsScript project in each task, I first tried simply using the Refresh Apps Script Project info button, but that didn’t fix anything.
After reselecting the project in one task and confirming that fixed the issue, I didn’t think to test other tasks that use the same project before also reselecting the project in each of those. Potentially, the issue was that nothing had triggered adding the project to Account #2’s collection of Integrations > Apps Script Projects, and, after triggering that once, other tasks using the same project might have worked fine.
The key step there was the re-authorizing of the projects, remember that the project is run with from the app owner’s perspective so if you transfer ownership, you’ll need to re-authorize with the new owner (and that implies they need to have access to the project). Of course return values will fail if the authorization fails because it doesn’t run successfully.
Understood. However, again, AppSheet did not prompt me to re-authorize the project. After the transfer, each task’s project still showed that it was already authorized. Also, after I reselected the same project in a task, it still showed as authorized–again, no prompt to authorize the project.
It is maybe an evolution to come in the whole type selection experience, but in my opinion it turns confusing. I assume this is due to non-consistency between Apps Script basic types and AppSheet types as we know it. Anyway
I lost a bit of a time not seeing the “List” option with the Array, hence I did not see the options attached to it, and eventually.
To avoid any omission, I would suggest adding a colored text box to attract attention over this, if any further option should be selected, as it is for this example:
@Aurelien Thanks for the feedback, I acknowledge it’s not as clear as it should be that selecting the element type for the array is actually required, though you should get an app error if you save without a list type.
Yes, the main motivation was to be consistent with the column editor UI as you note. We’ll re-visit this when we revamp the element type selector since this is also a problem there.
We were trying to access to the previous column value using [_thisrow_Before].[Column] expression and passed to Function Parameter, however, this returns error. I suppose this is possible bug in GAS integration with AppSheet.
[_thisrow_Before] state can be used in the data change action step, however, it is currently not possible to access to the same state when it comes to GAS task. Could you have a look, @nico ?
Hooray!!!
