Connect AppSheet with Apps Script

1. Introduction

In this codelab, you create an Apps Script project named "Hello World" and then add a simple function, logThis, that logs a message that was passed to it. Then you create an Appsheet automation and have it call the script. After the script is called, you return to the Apps Script editor and confirm its execution.

What you'll learn

• How to create an Apps Script project with a simple function.
• How to create a simple AppSheet app based on an existing Google Sheet.
• How to create an AppSheet Automation that calls the script function.
• How to check for successful execution of the script from AppSheet.

What you'll need

• Access to AppSheet development environment (appsheet.com).
• Access to Apps Script development environment (script.google.com).

2. Create an Apps Script project

First, create an Apps Script project named "Hello World" and then add a simple function, logThis, that logs a message that was passed to it.

1. Go to the Apps Script home page and then click + New Project.
2. Change the title of the project to Hello World.
3. Update the script in the editor like this:

function logThis(msg) {
  Logger.log('Message from AppSheet was: ' + msg);
}

4. Click Save project.

For more information, see Create and delete projects.

3. Create an AppSheet app

Next, you quickly generate a new AppSheet app based on a copied example Google Sheet, and then update it to call the Apps Script project that you created before.

1. Open the Shirt Orders Example spreadsheet and then click Make a copy to download and open a copy in Google Sheets. You can optionally rename the spreadsheet, if you wish (e.g.,"Shirt Orders").
2. Sign in to AppSheet.
3. On the My apps page, click + Make a new app. The Create a new app dialog displays.
4. Click Start with your own data.
5. Optionally, edit the app name and select a category.
6. Click Choose your data.

7. Click google to access your Google Drive.
8. Navigate to and select the Shirt Orders spreadsheet that you copied in earlier.

AppSheet sets up your app.

9. Click Customize your app to open the app in the AppSheet editor.

4. Create an automation

Next, you create an Appsheet automation and have it call the script.

1. Select Automation > Bots.
2. Click + New Bot.
3. Select the first item in the list of suggestions: When a Shirt Orders record is created or updated, send a notification.

4. Click the Send a notification step to edit it.
5. Change the name to Call a script.

6. Under Settings, change the task type to Call a script.

7. In the Apps Script Project field, click Browse (File icon).
8. Navigate to and select the Hello World Apps Script project in your Drive.
9. Click Select and, if prompted to authorize, click Authorize and follow the prompts.

The Apps Script Project is added and the Function Name field is populated with a list of available functions.

10. Select logThis(msg) from the Function Name list.

The Function Arguments field displays.

11. For the msg argument, enter the following expression using Expression Assistant: ‘Hello from AppSheet'

12. Click Save to save the app.
13. In the Preview Panel, click the mobile or tablet icon to display the app emulator.

14. Add a new shirt order and click Save.
15. Click Sync to sync your changes.

5. Confirm the execution of Apps Script

After the script is called, return to the Apps Script editor and confirm its execution.

1. Return to the Apps Script home page and view the Hello World project.
2. Click Executions in the navigation bar to confirm that the logThis function was successfully completed. Click Refresh if the log has not appeared.

6. Optional additional steps

As an additional step in this workshop, review the Apps Script Examples project and run some of the examples from AppSheet.

Get started with the Apps Script Examples

1. To get started, first make a copy of the Apps Script Examples Project.
2. Click the copy icon.
3. Save the new project and click Run to authorize the apps in the project.

Experiment with several of the examples

• createCalendarEvent() - Create a new Calendar event.
• createDoc() - Automatically create a new Document.
• createSheet() - Create a new sheet.
• createSlideDeck() - Create a new slide deck using an image and text.

For more information on this important feature, see Call Apps Script from an automation, which provides a detailed overview, feature video, tutorials, and a copyable Apps Script project that contains many AppSheet-Apps Script working examples.