Apps Script Tasks are now available: Call Apps Script functions in AppSheet Automation

[nico]

We're happy to announce that Apps Script Tasks be rolling out for all users in AppSheet this week! This will allow you to natively call Google Apps Script functions within your AppSheet automation workflows, enabling your apps to tightly integrate with over 30 Google Workspace apps and APIs like Drive, Calendar, Gmail, and even external services.

Example uses:

• Create a calendar appointment when a button is clicked
• Add a slide to a presentation when a new row is added
• Save photos to Drive and share with specific individuals when uploaded via a form
• Create an audit log by generating a Google Docs file with data from a table

The feature will be rolling out to everyone this week (starting April 12th) and be available to all users later this week. 

What should I try out?

We would love for you to help us validate and give us feedback on:

• Task configuration usability
• Authorizing a script
• Any problems with parameter passing or task execution
• Any error states and edge cases 
• Any specific use cases you will find useful to test
• Important note: please do not use production data for preview features

Find code samples and our walkthrough video in our Help Center.

How can I configure an Apps Script Task?

1. Go to the Automations page in the AppSheet app editor.
2. Create a Bot (AppSheet Automation)
3. Create a new task
4. You will see a new top-level task type: Call a script in the task configuration panel
5. Select an Apps Script project from your Google Drive. Note: you may have to authorize the Script with the “Authorize” button in order to execute it
6. Finally, select the function to call, and specify the expressions to pass as arguments

You can also click the button to open the Apps Script project in a new window, which will bring you to the Apps Script editor, to edit the script directly.

Limitations

• Apps Script executions are subject to standard Google Workspace quotas
• Apps Script tasks are available to all creators in the prototype phase and available for deployed apps for the AppSheet Core plan and above
• Return values from the function are not yet supported
• Running Apps Script from a Service Account is not supported
• Please visit the Help Center article for details & limitations

How do I give feedback?

Please add comments/reply to this post!

We'd love to hear if you have any specific use cases in mind for Apps Script Tasks. If you have any other feedback, please share it in this thread as well!

[pravse]

@Koichi_Tsuji and @SkrOYC -- your passion for AppSheet is something I admire and have appreciated for years. Keep pushing us!
And a big thank you to @zito for underscoring your commitment to everyone in the community, and @carlinyuen for all your work on the AppsScript-AppSheet interaction.

[takuya_miyai]

Hi @nico @carlinyuen 

Just to confirm, is it correct that only Standalone Script can be specified from AppSheet?
I believe Container-bound Scripts are not covered, but it doesn't appear to be explicitly stated in Help or this thread.
(Sorry if I'm missing something.)

https://developers.google.com/apps-script/guides/bound
https://developers.google.com/apps-script/guides/standalone

[nico]

That's right, only Standalone Scripts are supported.

[takuya_miyai]

@nico 
Roger that!
I will explane so to my cutomers.

[carlinyuen]

Thanks for asking, Takuya, and yes we should update our Help Center documentation in that case @lizlynch. I'd be interested whether you think that's an important blocker/functionality to have. When we first spec'd this feature it was discussed that it was more complex to do container-bound scripts, but we can check in on that again if a big problem.

[takuya_miyai]

@carlinyuen 
Personally, I felt that Standalone only would be fine.
However, I also feel that citizen developers often use Container-bound Scripts when creating Apps Scripts related to Sheets that serve as data sources.

As for our customers, however, they do not use Apps Scripts that much, and we have not been able to catch up on their exact opinions.😅

I am curious to know what the results of Google preliminary survey were.

[lizlynch]

Great input! I've added a note to the documentation indicating the current support of standalone versus container-bound scripts.

Call Apps Script from an automation

Thank you!

[scott192]

While I personally like the features that are coming to Appsheet, could somebody from Google remind us again why they felt the need to kill a product that already had all of the UI and Scripting features, already integrated with Workspace, handled all the messy authentication issues like a pro, was a Core Google service and was built into the price of a Business User Account and the developer merely had to pay for hosting the database?

App Maker (If the site hasn't been deleted by now)...

Now, 3 years later you are trying to add those features into a product that was designed for mobile phones and tablets and quite rightly the people who just want a low code platform for mobile are feeling a bit upset at all the attention these new features are getting.

The development path for me is that Appsheet is aimed at the no code crowd, AppMaker was aimed at the Business/Citizen Developer crowd leading into the Google App Engine for Enterprise and Software Developers crowd.  A clear direction for anyone who wants to make things with software.

Maybe adding all these features to Appsheet is a mistake and you should bring back App Maker from the grave...by the way, I know this won't happen, but boy do I yearn for it!

[paul1]

@nico 

A quick question: Does Appsheet apply a time limit on the function execution?

[nico]

The standard limits on Automation executions apply which you can find here, 5 minutes for a scheduled bot and 2 minutes for a data change bot. It's unlikely you'll hit anywhere near that time unless you do a lot of expensive operations (like copying a lot of Drive files, for example).

Also be aware that Apps Script has daily quotas for various features. 

[paul1]

Thanks for your reply. I am trying to merge a lot of data into a document template in Drive. I asked because my script gets cancelled if it runs longer than 59 seconds and gets restarted until it runs under 59 seconds. Was hoping it was an Appsheet limitation.

[nico]

If you look at the "Execution Time" it says 128 seconds. The limitations doc I linked shows that we have a 2 minutes = 120 second limit on execution for the entire bot if it was triggered from a change so if it needs to retry multiple times, it will go over that time limit.

Are you able to run the function standalone from your Apps Script project? The `execution cancelled` error is coming from Apps Script and can happen when you either exceed quota or hit some runtime error. If you need further assistance, you can contact support@appsheet.com and ask to be forwarded to the Nico from the Workspace team and can take a look at your specific app and Apps Script project and see if I can trace it down better for you.

[nico]

Apps Script Task is now exiting Preview and rolling out to all users, updated the post to reflect this. Thanks for all the feedback on this thread!

[takuya_miyai]

@nico , great job!🤗🎉

I have confirmed that the Apps script is available in Tasks even if "Preview new features" is turned off.
The Beta display is still there, but will it eventually disappear as well?

 

[carlinyuen]

Thanks so much, Takuya for confirming and the kind confirmation note! Yes, we will remove the beta tag eventually, we are rolling out gradually to the user base and when it's fully rolled out, we will be removing the label completely soon after.

[takuya_miyai]

@carlinyuen 

I see
It's true that each user has a different release status right now.😀

[takuya_miyai]

@nico 

There seems to be a bug in the sample script for creating calendar events.

https://help.appsheet.com/en/articles/6048533-use-apps-script-to-create-a-calendar-event

https://script.google.com/home/projects/1Q63UF-FwQzD4ih4MrDgUjWkmBbX1XEe4-k0ulVrLx1jB-xqowwgJ3RPn

I changed StartDate to StartTime as follows and it works fine.

/**
 * Adds a one hour long calendar event to the caller's default calendar
 *
 * @param {string} eventName the name of the event
 * @param {string} startDatetime the starting date time of the event
 */
function createHourLongCalendarEvent(title, startDatetime) {
  const eventName = typeof title === 'string' ? title : 'My event';
  //const startDate = typeof startDatetime === 'string' ? new Date(startDatetime) : new Date();
  const startTime = typeof startDatetime === 'string' ? new Date(startDatetime) : new Date();

  const endTime = addHoursToDate(startTime, 1);

  var event = CalendarApp.getDefaultCalendar().createEvent(
    eventName,
  //  startDate,
    startTime,
    endTime
  );

  console.log('Created event with id', event.getId());
}

Thanks,

@carlinyuen 
@Koichi_Tsuji 

[nico]

Yes, thanks for pointing this out -- updated the variable name and it should be okay now, sorry about that!

[popust_dana]

apps script seems to be working only logged in as app creator and apps script owner, other users cant trigger the script (in automation monitor it says complete but script not running). is this intended behaviour or am i doing something wrong with setting up the apps script perhaps?

[nico]

You can only add an Apps Script task initially as the app owner. After you add a script, a co-author can add the same script if the script is also shared with them. But once the app owner adds a task, it can be triggered by any user of the app.

If you're not seeing this behavior, can you send an email to `support@appsheet.com` with your app name and ask to be directed to Nico from the Workspace team? I can take a look at your app and get you more direct help.

[takuya_miyai]

@nico 

I have encountered similar cases.
I will get back to you from Support.

[paul1]

@nico 

My user is currently getting the following, according to the monitor, when attempting to run a script.

"Exception": "Script was not allowlisted. Please add the script from the editor again.",

Thanks

[nico]

Was the script initially added by the app owner of the app? If not, it needs to be added there before it will have permission to the run. For example, if you have `testApp-12345`, the owner is user with id `12345` and needs to be added by them before the script will be considered allowlisted to run.

[paul1]

Hi Nico,

Yes, the script was added by me and works fine for me. The user last used the app on the 14th and the scripts worked fine then.

Thanks

[takuya_miyai]

Hi @nico 
I have contacted support and I am experiencing the same problem.

The user I added is logged in the Automation monitor, but the Apps Script log is not.
In other words, the Task in Automation is running, but the Call in Apps Script is failing for some reason.

 

 

 

[nico]

Execution from other users should work so long as the script was originally added by the App Owner of the app. This is done for security reasons, to prevent a co-authors from executing your scripts you didn't intend but it can be confusing. If you look at either the audit logs or automation logs you should see an error like this:

Error: "Script was not allowlisted. Please add the script from the editor again."

This means the script was not added from the App Owner's account (or perhaps our call failed). To fix this, can you select the script and select it again from the file picker WHILE LOGGED IN AS the owner of the app. This will allowlist the script and should allow it to run.

If you originally added the script as a co-author either add the script again in a separate task or selecting a different script then selecting the same script to allowlist it for the app owner's account:

Then execution should continue as normal. Hope this helps.

[takuya_miyai]

Hi @nico 

As I have replied in support, the problem is occurring with Automation created by the AppSheet app owner who is also the owner of Apps Script.

* When the owner runs it, the Automation works.
* When executed by the app user, Automation does not work.

If the problem is caused by an Automation added by someone other than the app owner, then it should either work or not.
Please point out if my understanding is incorrect.

[nico]

I think I found bug on our end where the task executes only for the app owner. Working on rolling out a fix, will keep you posted when it's live for everyone.

[nico]

Should be live as of a few days ago

[takuya_miyai]

Thanks @nico !
I have confirmed that this has been fixed in the application reported in this thread.😁

[Joseph_Seddik]

Hello @nico,

Again thank for this great addition to AppSheet. I'm coming back to this post after finally managing to arrange a change window with my customer to move all scripts running on their sheets to be launched directly from the app. It went away well without glitches. Yes, I had to move the scripts from their containers to be standalone and modify them a bit accordingly, but it's OK; I rather support the idea of the scripts being standalone. 

The customer appreciates that updates now appear faster; before you'd have to wait till the app syncs and updates the sheet, then the scripts trigger based on sheet updates, then you'd have to do another sync for the changes to be reflected in the app. Now, changes inside the app directly causes the scripts to trigger, which are mainly getting and writing data from/to remote sources using APIs. The customer now feels that his systems and his AppSheet's app are better integrated. Well, all the better 🙂

One particular case caught my attention though. The customer is working on a separate accounting platform unrelated to his AppSheet's app. A script was running on a schedule, once before the end of the morning and another before the end of the afternoon, during working hours to fetch data from his accounting platform. This was not done by me. I replaced this with an overlay button on his Table View, so once he's done working on the accounting platform, he, at anytime instead of waiting for the scheduled event to occur, can now at anytime just press the button and see the new rows added to his table view. He's very happy with it. 

My point is, since the Apps Script task can only be triggered through a bot, I had to use a new single row table with a dummy value being updated upon this button's click, and the bot triggering the script would monitor update this dummy value to trigger. This unnecessary overhead could've been avoided if we had the possibility to trigger Apps Script from a Behavior Action, in addition to, not instead of, the bot. 

Please take this into consideration. Thanks in advance!

[carlinyuen]

Very cool to hear about this, Joseph! That's helpful feedback and we're tracking this feature request.

[nico]

Thanks for the feedback Joseph. Yes, this is a fairly common frustration. What you're doing now, inserting a row into a dummy table is currently the only way to trigger an automation from a button. Agree this should be easier.

[Joseph_Seddik]

Thanks @nico. The idea is not to make automation easier, it is rather why would we need automation from the first place? Clicking a button is a manual not an automated event. We resort to automation because the behavior actions are lacking. 

The idea is to make tasks available to actions, not exclusive to automation. I hope this further clarifies my comment. 

[ScottE]

In a similar vein, but slightly different use case.  I use an on-screen icon/behavior to "toggle" the "viewability" of two different rows of on-screen action buttons, which requires multiple dummy trigger & syncs to Open/Close the icons.

[InfoHand]

When will function return be supported?

[nico]

Soon 😉

[Joseph_Seddik]

Thanks Carlin!

[takuya_miyai]

Hi @nico and @carlinyuen 

I share this because I had a specific case where I wanted to save the return value from Apps Script.

It is a simple Apps Script, just copy the file.
However, if I wanted to record the newly created file ID in the AppSheet record at this time, I needed to implement something quite tricky in the current AppSheet.

Since Automation cannot monitor the file creation status of Apps Script, I specifically created a VC to check if a file name identical to the file name specified in the argument as a newly created file name exists in the Google Drive data source.

ANY(select(GoogleDriveFile[_ID], [ファイル名]=[_Thisrow].[プロジェクト名]))

This still has some problems, such as the ref relationship losing if the file name is changed, and the application operation slowing down due to the VC being evaluated each time Sync is performed.

I believe that getting a return value from Apps Script would make the integration between GWS and AppSheet more powerful, as we could easily associate a newly created Google Drive file with a record in AppSheet.🤗

[Rich_E]

Hi Takuya.  Until we implement the ability to parse a response directly from a script, you can setup your script to send a call to your app's API by using the URLFetch() function in Apps Script.  

You can directly update your table with the document id using this method.  Example script below:

https://script.google.com/u/5/home/projects/1eeKO6bYMUGgMycDTYvXBqcs_7diF1xMfWFLc298nINdl3nLn7tscCBa...

Invoking the API documentation

https://support.google.com/appsheet/answer/10104497?hl=en&ref_topic=10105767