Apps Script Tasks are now available: Call Apps Scr...

nico · 03-16-2022 02:12 PM

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?

Go to the Automations page in the AppSheet app editor.
Create a Bot (AppSheet Automation)
Create a new task
You will see a new top-level task type: Call a script in the task configuration panel
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
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!

takuya_miyai

Hi @nico
Thanks for the heads up on the great feature release.🎉🎊
It will greatly expand the possibilities for integration with Workspace and other systems.😍

I will test it and provide feedback as soon as possible.

One question.

Was the initial announcement that we would need to add this from Outbound Channel dropped?

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/How-do-I-add-an-Appscript-Outbound-Channel/m-p/...

@Koichi_Tsuji

nico

This feature will simplify the Outbound Channel requirement from the Prototype feature, you simply need to add your Apps Script project and the Outbound Channel will be automatically configured for you, now!

More details in the help doc here under the Manage Apps Script projects in your AppSheet account section.

takuya_miyai

Thanks! @nico

I'm also pleased to see that this has been remedied, as it was a difficult configuration item for the citizen developer!😃

Luke_H

Hey, This looks great! However, I don't see it even though I am a part of the preview program.

SkrOYC

You should be on the preview program as well as use a Google Datasource. It may take some time for you to show since it's a new feature. Try clearing cache

carlinyuen

Hey Luke & SkrOYC, yes, you would need to make sure that:

Your app is opted-in to preview features (this setting is on a per-app level, found in UX > Options)
You need to have at least one Google Datasource on your account.

Let us know if you're still not seeing the feature, and we will try to debug.

nico

Like Carlin mentioned, you'll have to opt into that setting (full details here). After you save the preview program setting in your app you'll need to do a full page reload before you see the Apps Script task type.

Kirk_Masden

If someone makes a template / sample app that utilizes Google Apps Script, will the script be copied when the template is copied? Or, will it need to be added manually to the template / sample app after it has been copied? Also, is there a template / sample app that utilizes Google Apps Script? If there is such an app and if all I have to do is copy it and then start "looking under the hood" that's what I'd like to do.

carlinyuen

Hi Kirk, we don't yet have sample apps that use this feature yet, although we are looking into it. The script will not be copied if you copy an app although we will copy the task configuration so it will still show the same script selected and configured for execution. For security reasons, scripts will always execute as the app owner, and you may need to re-authorize the script execution. @nico can expand on this further and correct me where wrong. 🙂

Kirk_Masden

That you very much! Your explanation is very helpful to me. 🙂

nico

Yes, this task is a little different than most Tasks in that it is linked to the account so you can copy your own apps that have already been authorized to your account and it'll work fine.

If you want to copy someone else's app with an Apps Script Task, you'll have to add the script before it can execute (so you also need to have access to the project).

Rafael_ANEIC-PY

Amazing, looking forward to trying it out

Joseph_Seddik

AMMMMAZZING. Thanks to the whole great team !

ScottE

WOW, my dreams of deep Google integration are coming true.

I'm drooling at the idea of a repository of scripts. Having spend 2 years creating rather noobish scripts to create/merge docs, calendars, Task and contacts in Google, without the intermediary "helper" spreadsheets. 😄

Can't wait for "Return Values"

takuya_miyai

Hi @nico and every one,

I'm checking the sample code.
https://help.appsheet.com/en/articles/6048537-use-the-appsheet-apps-script-examples-project

Perhaps we should use SpreadsheetApp or DocumentApp instead of DriveApp class for shareFile function?

The following error is logged if the DriveApp class is left unchanged.

DriveApp.openById is not a function

If I change the code as follows and pass the SheetId, it succeeds.

function shareFile(fileId, email) {
  Logger.log('fileId is: ' + fileId);
  try {
    // Share the document with a particular email address
    //DriveApp.openById(fileId).addViewer(email);
    SpreadsheetApp.openById(fileId).addViewer(email);
    Logger.log('Successfully sent email with doc to: ' + email);
  } catch (err) {
    Logger.log('Failed with error %s', err.message);
  }
}

I could not find an OpenById method in DriveApp.

https://developers.google.com/apps-script/reference/drive/drive-app

Thanks,

nico

Thanks for pointing this out! Indeed, I meant to use the `DriveApp.getFileById` I think I converted it from `SpreadsheetApp.openById` at some point when working on this example. I've updated the sample to do this instead.

The `SpreadsheetApp.openById` or `DocumentApp.openById` will work with sharing Sheets or Docs files, respectively which might be sufficient for your use-case and require lower scopes but if you want to share any type of file in Drive you can use the `DriveApp` variation in the example.

takuya_miyai

Thanks @nico ,

> `DriveApp.getFileById`

Certainly, using this one is more versatile and preferable as a sample.
And it worked in my environment!😄

takuya_miyai

Hi @nico

After selecting Apps script, the dialog does not close.
I think this is a good place to have it automatically closed.🤗

@Koichi_Tsuji

nico

It should automatically close already. It seems like ~1% of the time it doesn't automatically in which case you can click the "ESC" button to do it manually until I find and fix the root cause.

takuya_miyai

@nico

Automatically closed!🤩
Thanks for the quick response.👍

scott_robinson

A really exciting development in Appsheet!

You have created infinite possibilities with this new feature if you are a Workspace user.

I love being able to trigger scripts when I need to rather than using onchange events in the sheet and checking if certain cells have been flagged to run a script. This will help with quota management for scripts as well.

Passing variables to scripts is done so well. Such an amazing effort by your team to get this feature added! I'm not so bothered about returning values back to Appsheet at the moment, but I know you'll have an equally great way of doing this cooking on the developer stove 😃

Thank you!

carlinyuen

Thanks so much for the kind words, Scott! The team really appreciates it and there certainly was a LOT of work involved including a deep collaboration with the Apps Script team. 🙂

ScottE

Error "Your Apps Script parameter is too long, write a shorter expression."

My AppSheet formula is > 1000 characters but the resulting value that is to be passed as a variable to Apps Script is < 10. Is this an intended limitation?

nico

It was an intentional limit to prevent large payloads but from taking with the team it doesn't make sense to limit it specifically for this feature. We've made the decision to remove this limit entirely so in the next day or so you should be able to have expressions as long as you want 🙂

Note that there is still a limit to the resulting string but it's so high that you'll likely never hit it.

scott_robinson

Could you expand on the payload part?

What is the limit on the payload in terms of a list of refs say?

nico

The only limit is if you pass an argument that gets evaluated to a very long string or a very long list of refs. It's high, you'd have to pass thousands of refs in a ref list before you hit it.

takuya_miyai

Hi @nico , @carlinyuen

I know this is a minor detail, but it caught my attention, so I thought I would share.

We, AppCreator, have been using the View button to access Workspace content templates.

It may be easier for users to place a similar View button for accessing Apps Script.🤗

nico

Yes if you click the "Open" button, below the file selection that should land you straight into the Apps Script editor. It has similar functionality to the "View" button.

takuya_miyai

@nico

Sorry, The nuances of the English language were wrong.

I think it would be easier for App Creator to understand if we unify to View button.
Both of these are not comfortable with different notations, considering that they refer to Workspace content and are used within AppSheet Automation.

carlinyuen

Thanks for pointing that out, @takuya_miyai. We are generally working on making things more consistent with design standards for Google's Material Design framework, so may end up actually changing the "View" to "Open" instead, but agree that it should be consistent where possible. We will forward this along to folks on the team who are working on improvements to the experience!

takuya_miyai

Thanks @carlinyuen ,
Yes, I think the preferred approach is to unify one or the other.
By the way, my first impression of this UI was that it was an "open sample code" link.😁

carlinyuen

That's helpful to hear, what we probably should do is really place the open
button more clearly next to the selected Apps Script project so there's a
clearer visual grouping for what the particular button does and what it
applies to. Thanks, Takuya, we'll look into this.

Steve

A consistent interface would be appreciated.

takuya_miyai

Hi @nico @carlinyuen

This one may be a very difficult issue, but it is a request that we would like to see addressed.
I would like to copy Apps Script as well as Sheets and Images when copying apps.

@Koichi_Tsuji

nico

Can I ask what the use-case you have for this? In most cases, it should be sufficient to copy the task and re-use the existing Apps Script project.

There are rare cases you might want to copy the project too for example if you want to create an app that's completely independent of other apps but in general it's good practice to re-use as much of your existing code as possible.

takuya_miyai

@nico

The first thing to consider is that it will be needed when the sample app is published.
Even if only the metadata of the app is copied, it is difficult to tell other App Creators what kind of app it is because Apps Script cannot be executed.

In addition, one large client has a standard workflow app that is published internally and then customized by each department.
Equipment purchase requests, vehicle usage requests, vacation requests, and many others.
If Apps Script is used in a case where this type of deployment is being considered, it is desirable to be able to copy the Script as well.

Thanks!

carlinyuen

Thanks, @takuya_miyai that's helpful context for us to understand the use case. Obviously we do not have this yet, but we will add it to our backlog and work with the team to see where to prioritize it. Appreciate the feedback and feature request!

Steve

@nico wrote:
In most cases, it should be sufficient to copy the task and re-use the existing Apps Script project.

Given that opinion. it's clear AppSheet Engineering would do well to have access to someone with more real-world AppSheet app development. So many engineering decisions do not mesh with common practice, especially that of the more advanced app creators.

carlinyuen

@Steve we appreciate your feedback, but please understand that we're all human beings trying to do our best here. Would appreciate if you can focus your feedback on constructively helping us learn and understand what we got wrong. We don't always get it right out of the gate and it's part of the product development process to get feedback and iterate, and we're working on improving. I hope you understand.