Call Apps Script from an automation

Call Apps Script from an automation as described in the following sections:

Overview

Apps Script is a cloud-based JavaScript platform that lets you integrate with and automate tasks across Google products. With Apps Script, you write code in modern JavaScript and have access to built-in libraries for Google Workspace applications.

You can use custom logic from within your AppSheet app by calling Apps Script from an AppSheet automation bot.

Examples of custom logic that you can implement include:

  • 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 using a form
  • Create an audit log by generating a Google Docs file with data from a table
  • Call an external service for machine learning prediction using data from a newly added row and write back model prediction

Notes:

  • The Apps Script service must be enabled in your organization to call Apps Script from an automation. See Turn Apps Script on or off for users. Otherwise, you'll receive a message similar to the following:
    We are sorry, but you do not have access to Google Apps Script. Please contact your Organization Administrator for access
  • Apps Script always executes the head deployment regardless of the deployed versions. You can't designate a different version to be called by the task.

  • AppSheet supports only standalone scripts. It does not support container-bound scripts at this time.

New to Apps Script? See Google Apps Script overview.
New to AppSheet Automation? See AppSheet Automation: The Essentials.

Get started calling Apps Script from an automation

Get started calling Apps Script from an automation.

Create an Apps Script project

First, you must create an Apps Script project for your script files and related resources. An Apps Script project includes:

  • One or more script files (.gs) to define your custom integrations
  • Optionally, HTML files, that might include JavaScript and CSS, to create custom templates and layouts

Create an Apps Script

To create an Apps Script project:

  1. From the Apps Script home page you can:

    • Create a new Apps Script project, as described in Create and delete projects.

    • Copy an existing Apps Script project:

      1. Open the Apps Script project editor.

      2. Click Overview in the left sidebar and click Make a copy (copy icon) in the right side of the page.

  2. Add one or more scripts to the project or modify the existing scripts. For examples, see Use the AppSheet Apps Script Examples project.

Add and configure the "Call a script" task in a bot

Add and configure the Call a script task in a bot by performing the following steps.

For more information about adding and configuring tasks in an automation bot, see Tasks: The Essentials.

  1. Add the Call a script task to a bot, as described in Add a task to a bot.
  2. Configure the "Call a script" task.
  3. Click Authorize to authorize the Apps Script project to run.
    Note: You'll need to authorize the first time you access the project and anytime authentication scopes are added. As indicated, the script will always run as the app owner regardless of the account used to authorize the project.
  4. Select the Apps Script function that you want to call in the Function Name drop-down.

    The list is populated using the functions defined by the Apps Script project selected in the previous step. After you select the function, the list of associated arguments is displayed. 
  5. Enter an expression that defines each argument that will be passed to the function in the Function Parameters field. For example:
    For the msg argument, enter 'Hello from AppSheet' using the Expression Assistant
  6. Optionally, enable the Return Value option to use the return value from the Apps Script function. See Use return values from Apps Script tasks for more information.
  7. Optionally, enable the Run asynchronously? option to run the Apps Script task asynchronously as a background task. Enabling this option can improve client response time.

    Before setting this option, consider whether your application requires the Apps Script to complete synchronously to execute correctly. For example, if your Apps Script is changing data and your application relies on that data being updated before the sync completes, then using an asynchronous call may yield incorrect results.
  8. When you are done, save the task by selecting one of the following:
    • Save - Save the app.
    • Save & verify data - Save the app and verify that it is runnable based on external dependencies.

Configure the "Call a script" task

Configure the Call a script task by using the settings in the following table.

Setting

Description

Linking
Enable or disable reuse of this component by expanding the Linking panel and toggling the Linking setting. See Reuse automation components.

Expand the Linking panel and toggle the Linking setting to enable or disable component reuse

Task name

Name of the task that appears in the automation process flow. It must be unique within your app.

Table name

Table defined in the event and used by the task. 

To view or edit the structure of the selected table, position your cursor in the field and click Edit icon . See Tables: The Essentials for more information.

Note: To use a table that is different from the table defined in the event, add a Call a process step.

Apps Script Project

Apps Script project that contains the function you want to call. To populate the field:

  1. Click Browse (File icon).
  2. Click Files on Google Drive.
  3. Navigate to the Apps Script project in your Drive. See Create an Apps Script project.
  4. Click Select.

Click Open Project to open the project, if desired.

 Expand and customize the Advanced settings, as described below.

Setting

Description

Inputs

Add inputs to dynamically configure a task. For details, see Set input values dynamically in tasks

  1. Expand the Advanced section.
  2. Click Add under Inputs.
  3. Enter the name of the input value in the Name field.
  4. Select the data type from the Type drop-down.
    Only a subset of data types are supported for inputs.
  5. To add an initial value or edit any fields:
    1. Click  to open the Input dialog.
    2. Add a value or expression in the Initial value field.
      If you don't specify an initial value, then the input value will be required. If an initial value is specified, then the input value is optional.
      Use of [_THIS] or [_INPUT] are not supported in the Initial value field.
    3. Edit other fields, as required.
    4. Click Done.
  6. Repeat to add additional input values.

Then, use the following expressions to access the input values from the current step: [_INPUT].[inputname]

Share an Apps Script project

Share your Apps Script project by sharing it directly or using a shared drive. For details, see Collaborating with other developers.

Manage Apps Script projects in your AppSheet account

The following sections describe how to manage Apps Script projects in your AppSheet account:

View Apps Script projects in your AppSheet account

To view Apps Script projects in your AppSheet account:

  1. Sign in to AppSheet.
  2. Go to the My account page.
  3. Select Integrations > Apps Scripts.

The Apps Script projects are displayed, as follows:

View Apps Script projects in your account on the Integrations > Apps Script pane

Add an Apps Script project to your AppSheet account

When you add and configure a Call a script task, the Apps Script project is added to your account automatically.

Remove an Apps Script project from your AppSheet account

Remove access to an Apps Script project from your AppSheet user or team account so that it is no longer available for use by other apps.

Note: Before removing an Apps Script project from your AppSheet account, you should delete all references to the Apps Script project in your apps to avoid errors. If you removed a project from the My Account page, and want to add the project back to your account, select the script again from within the “Call a script” flow.

To remove an App Script to your user or team account:

  1. Sign in to AppSheet.
  2. Go to the My account page.
  3. Select Integrations > Apps Scripts.
  4. Click Delete (Trash icon) to delete access to a particular script.

Quotas and limitations

  • Apps Script executions are subject to standard Google Workspace quotas and limitations as stated in Quotas for Google Services.
  • Apps Script tasks will be available for users in the AppSheet Core plan and above. See AppSheet pricing plans.

Was this helpful?

How can we improve it?

Need more help?

Try these next steps:

Search
Clear search
Close search
Google apps
Main menu
2161418458168504555