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!

[scott192]

Hi @Rich_E 

I'm currently doing this task by passing the spreadsheetid, recordid, returncolumnnumber as part of my call to the various appscript functions I have written.

They do the required processing logic for the script and then return values directly to the sheet using SpreadsheetApp etc.

Appsheet seems to do a very timely update on this return data and the new values appear within a few seconds in the client.  But usually after a manual client sync.

Does the API method you have described have some extra magic regarding client updates that I should be moving towards?  What are the benefits to the User viewing the App?

Many thanks,

Scott

[keithb1]

Thanks for the script.

Is there a way to use regular old fetch in vanilla javascript to read AppSheet API data? I've got a html table that I'd like to populate with AppSheet data but I keep getting a status 500 with "An error has occured." message. I'm guessing it's something to do with authentication. Can you give me some insights?

[nico]

Hi Takuya,

Rich's suggestion to use the REST API is the workaround today. We should be announcing return value very soon (before end of month) which will make this flow much easier.

-Nico

[takuya_miyai]

Thanks @nico 
So that's how the Return value implementation is going, on such a schedule.
Looking forward to it!🤩

[Koichi_Tsuji]

Very good to hear it is around the corner!!   Just few days to go? 😀

@takuya_miyai 

[nico]

I may have spoken too soon -- there is another rollout the Apps Script Return Value is dependent on that I forgot needs to be rolled out first so it's seems like it's going be longer than just this week. Sorry 😞

[Joseph_Seddik]

@nico Still good news 🙂 thank you!

[takuya_miyai]

Okey @nico 

I also need to try the workaround using the API that @Rich_E  and you taught me, so I am waiting patiently.🤗

[Koichi_Tsuji]

@nico  thank you for your update.  I stay patient then!

[Koichi_Tsuji]

Ni @nico 

Any light on the horizon??

[nico]

I'm actively working on it. The issue is that I first need to complete an automation rollout related otherwise it makes it such that you can't pass a Task/Action variable to another Task or Action.

The good news is that the return value work is ready to rollout as soon as that dependent feature is rolled out.

[Koichi_Tsuji]

Thanks @nico @for update, is any ETA avail even roughly?

[nico]

There is no publicly released ETA on this but I'd optimistically ~2 weeks: it'll take 1 week to rollout the use-variables-in-process rollout and another week to rollout the return value feature.

[Kirk_Masden]

Now that I know that ETA in this context means "estimated time of arrival," it seems easy enough.  But, since I imagined it might be some sort of computing term at first, it through me for a loop.  If there's anyone else out there who suffers from ACD (acronym confusion disorder), I'm sure they'd appreciate having things spelled out when possible. 😉

[nico]

One update on this. The pre-requisite feature has been announced here: https://www.googlecloudcommunity.com/gc/Announcements/Introducing-Unlinked-Automation-Components/m-p... it'll be rolling out but once that's done I can immediately roll out return values for Apps Script Tasks as well.

[dbaum]

Maybe now @Koichi_Tsuji's countdown is applicable...

We're eagerly awaiting this...

If it's relevant, I hope the help documentation will cover whatever @nico was  referencing as the prerequisite use-variables-in-process feature that was apparently part of the recently released support for unlinked automation components.

[Jonathon]

@nico

If AppSheet is able to read API response, you're going to remove a good chunk of API integration consulting work I do with AppSheet. I'm not sure how I feel about this.

[Karimmc2]

Hi, I'm new to App scripts and the "call a script" integration to bots.

I have created some pretty handy google home automations at work and the next natural step in getting these automations more autonomous would be to call them using AppSheet. But before deep diving into this I would like to know, is it possible to run a Google assistant automaton/ Google home routine by triggering it with an AppSheet event?

Has anyone here done this? Does it perform well?

Thanks,
Karim

[Christophe_Boul]

Great functionality. This will extend the power of AppSheet.

However, I have an inexplicable bug.

• My script (Apps Script) works fine and terminates correctly after 100 seconds when i call it from my editor.
• On the other hand when it is called via AppSheet, it starts to run well but ends and restarts after approximately 60 seconds
• The message is "Execution cancelled."
• The script restart forever

I know the Google Quota of 6 minutes but we are far from it.
If you had an explanation...

[Koichi_Tsuji]

@nico 

Other community member is claiming the same problem. Obviously this is a bug.

@takuya_miyai 

[nico]

Yes, I'll have a fix for this. We should have a higher timeout but our servers are currently configured with a 60 second timeout. I'll increase this and it should be live sometime next week.

[Koichi_Tsuji]

Hi @nico  Thanks for working on this to fix a problem.  Could you please make sure to put the final confirmation over a fix release either on this thread or daily release notes so that we follow up for another testing? Cheers.

@takuya_miyai 

[pravse]

A quick question --- is it necessarily the case that the timeout _should_ be longer than 60 seconds? @nico pl correct me if I'm wrong, but don't these calls run synchronously as part of the bot execution, which itself is synchronous with the processing of an update on the server. 
Is the actual requirement to kick off an asynchronous AppsScript call (which could run for however many minutes) or is the requirement to have a long timeout for a synchronous call. A synchronous call is necessary if the results are going to be used in subsequent steps of the bot process.

[Koichi_Tsuji]

Hi @pravse 

I'm not entirely sure if this was questioned to me or Nico, but let me share mine.

@takuya_miyai 

We have a script which runs for approx 3 mins as it is relatively heavy operation behind. Within this script, we call a API call to AppSheet servers by retriving the data from the AppSheet app (using FIND verb as well), and then pass to another AppSheet app.  When we run this script, as I said, it is completed within 6 min limit of GAS. Once again, our script runs within this bar/limit given by the GAS.

Our expectation to AppSheet BOT is we trigger the GAS from AppSheet app, then AppSheet app should not pose any restriction lower than GAS in case we set a step with run GAS.   From AppSheet BOT perspective, the running time to trigger the GAS should be an instant job, just send a request to GAS server.  Should be that's it. Bot does not need to wait for response, as it is just a POST request to GAS from Appsheet App.

Once the AppSheet bot send a request to GAS, then BOT job should be done.

However, as I privately reported to Nico in DM, GAS is killed in 60 sec and start to retry for approx 10 times, even though the GAS is successfully runnable when we run on schedule trigger or manually.

Hope this makes sense.

We understood AppSheet team is working to deal with the API response and manipuate the response body inside BOT.  In that case, yes, BOT need to wait for the response, so this possible new feature is subject to response of the call which is initiated by BOT itself. However, currently, we are using GAS task alone. If we dont have any other steps inside the same bot, the BOT should not pose any time limit.

Thanks.

[pravse]

Thank you @Koichi_Tsuji . This makes sense. So really what you want is a fire-and-continue asynchronous invocation of the AppsScript rather than a synchronous invocation. Which is exactly why I asked the question. @nico maybe it is worth considering if we should add an asynch option instead of (or in addition to) controlling the timeout?

[Koichi_Tsuji]

@pravse  thanks for your understanding, and glad to hear it is making sense to you as well.

For the moment (until @nico introduces new feature to deal with the API response body), all the webhook (inc. API calls) which is done from AppSheet webhook task is just POST, i.e. throw the request to the external service.  Once it is done, then BOT should always move to the subsequent step, rather than waiting for response back from this call.

Either way, we wait for a solutions to make our script runnable from AppSheet Automation.

@takuya_miyai 

[nico]

Yes, Koichi I understand the concerns and I'm glad you understand why we wait for the response. Supporting return values in an asynchronous Apps Script Task is more challenging because we'd effectively have to wait on it before steps that use the response. We could support asynchronous Apps Script task without return values without too much work but that work is not planned at the moment. If it's common enough to have long-running Apps Script tasks, it seems like a sensible feature rather than waiting for a long sync and I can make a good case to add a sync/async toggle.

With that being said, I'm working on the 1 minute time limitation and it we should be able to match what is posted here at the time of writing it's 2 minutes for a change execution and 5 minutes for a scheduled execution. So at the very least, you should be able to schedule a long-running task.

[Koichi_Tsuji]

To make a complex story easy, we just want to safely run the script (taking longer but less than 6 min) from AppSheet app. Any possible implementation is welcome.

Currently, there is not try and catch error mechanism is down there in Automation. Let s assume we call external service (webhook/api), but currently AppSheet does not know if or not such as request is ended up with success or not.  This can be confirmed with Monitor app, where the execution is in sucess (to call external services), but it actuall failed.   We are internally in my company saying "AppSheet is sciliently failed to do a job..."

In the perfect world, it is making our life easier if AppSheet could implement the mechanism in terms of try and catch error handling.

If the bot need to wait for response to deal with wait for response to catch error, then it is also making sense. However, we do not welcome any case where Automation's limit is lower than GAS limiation ......  2 min or 5 mil well below than 6 min.

[Jonathon]

You can always fall back to publishing your GAS as a webapp, and run it asynchronously for up to 6 minutes. You could do the same with AWS lambda, which has a 15 minute timeout.

[Christophe_Boul]

@Nico2 
@Koichi_Tsuji 
Thanks for working on this subject.
I am of course interested in a distinction between

• synchronous calls (with error/success return)
• asynchronous calls which have no time limits and no link with bot.

To note :
I found a workaround.
Instead of calling the main function of my script, I call a small function that schedules the execution of the main function of my script (within the next minute).
With one limitation... the passing of parameters!

[nico]

Agreed. Either way, the server timeout needs to increase which as of today is rolled out (it takes 1 week for this type of Google config to roll out to production servers hence the delay). Please try it out, it should allow for executions up to 2 / 5 minutes depending on the type. I agree it makes sense to have an async toggle to get the full 6 min and not block the sync for the "fire and forget" type tasks and I think my team it's important enough to just add so keep an eye for that rolling out soon.

[Christophe_Boul]

Thank you for your reply.
I'm not used to the appsheet forums yet.

[Marc_Dillon]

The listing of function parameters when you select a function is amazing. I have one suggestion for improvement with that though. Can it somehow be indicated on the UI if a parameter is optional? Here's an example. I have a function definition set up like this:

Where the "test" parameter is set to false by default, and is thus optional to pass any value to it. But on the editor screen it shows up the same as the other parameters:

In the GAS editor itself, this is indicated by a question mark on the helper pop-up when you are typing the function call:

Thanks for considering.

[nico]

This would be a nice enhancement but unfortunately, we just get the parameter names from our API and getting the optionality of a parameter requires reading and parsing the contents of the script which we cannot easily do. One small tip I can give is to always list the optional parameters at the end and/or name them prefixed with "optional" to make it easier for you.

[Marc_Dillon]

---

@nico wrote:

getting the optionality of a parameter requires reading and parsing the contents of the script which we cannot easily do

---

Oh, darn. Well thanks anyways 😄

[SkrOYC]

---

@nico wrote:

One small tip I can give is to always list the optional parameters at the end and/or name them prefixed with "optional" to make it easier for you.

---

Makes sense as a workaround

[Marcos_Carneiro]

Fala galera do App Sheet. Muito bom estar aqui.

Então, estou com um protótipo onde ao agendar pessoas para reuniões ou algum projeto, já consegui com que ele envia, tanto para instituição que está agendando como para os agendados, sendo um por um ou todos de uma só vez, bastando todos terem um email.

O fato é que pelo uso do whatsapp, ainda não consegui localizar uma integração, onde ao agendar essas pessoas, seja para uma ou mais reuniões ou projetos, que o app possa enviar, ao invés do email, que seja enviado para o whatsapp o agendamento. Eu tentei fazer pelo App Script, mas sou bem iniciante e não localizei nada que pudesse.

Outro fato, é que eu gostaria que também esse agendamento fosse direto para o calendário da pessoa. A integração eu conseguir, mas como ou iniciante e meu app possui campos específicos, data e horário da reunião, check-in, check-out, pessoa, projeto envolvido, reunião, tipo de reunião, pauta de reunião, local de reunião, e outros, eu não consegui vislumbrar aqui quais termos poderia utilizar na alteração do código de integração google agenda que localizei. Alguém me ajuda?

Grato.

[dbaum]

ICYMI:

• WhatsApp Tips Search - Google Cloud Community  
• Use data from Google Calendar - AppSheet Help 

[Marcos_Carneiro]

Obrigado, amigo! Vou testar...

[gigstada]

Is there throttling on how often AppSheet can execute Apps Script functions? I have a task that calls a script on edit and when I edit a few rows in the bulk edit view in my app, it takes 6+ seconds to update each row which I can't see accounted for anywhere in the performance monitor. I see this time gap by looking at the invocations list in my Apps Script project.