Overview

Apps Script is a cloud-based JavaScript platform that lets you 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. AppSheet supports integration with Apps Script from within your AppSheet app by calling an Apps Script script from an AppSheet automation bot.

Objectives

In this lab, you learn how to perform the following tasks:

• Create an Apps Script project that includes simple code to create an event on Google Calendar.

• Implement an automation bot in AppSheet that invokes Apps Script from a sample app.

• Test the bot and verify that the calendar event is created.

Setup

Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long lab resources will be made available to you.

This hands-on lab lets you do the lab activities in a real cloud environment, not in a simulation or demo environment. It gives you new, temporary credentials to sign in and access AppSheet for the duration of the lab.

What you need

To complete this lab, you need:

• Access to a standard internet browser (Chrome browser recommended).

• Time to complete the lab.

1. Make sure you sign in to the lab using an incognito window.

2. When ready, click .

   A new panel will appear with the temporary credentials that you must use for this lab.

   If you need to pay for the lab, a pop-up will open for you to select your payment method.

3. Note your lab credentials. You will use them to sign in to AppSheet for this lab.

   If you use other credentials, you will get errors or incur charges.

4. Click Open AppSheet to open the AppSheet UI in a separate browser tab or incognito window.

   Note: If you are not already using an incognito browser window, use the right mouse click in Chrome to open AppSheet and select Open link in incognito window.

5. Click to sign in with Google.

   

6. In the Sign in with Google dialog, enter the provided Username, and click Next.

   Note: If you see other accounts listed, click Use another account and then enter the provided Username and click Next.If you use other credentials, you'll get errors or incur charges.

7. Enter the provided Password and click Next.

8. Click Accept to accept the terms.

9. To enable AppSheet to access the Google Drive folders associated with your lab account, on the AppSheet consent page, click Allow.

   

10. You're now signed in to AppSheet.

    To view the AppSheet MyApps page, click X in the top-right corner of the Tell us about you so we can make better recommendations dialog.

    The MyApps page might be empty since you do not have any apps yet.

Task 1. Create the app

When working on your own app, you normally would incrementally build the app over a continuous project timeline.

Apps that you build are saved and accessible in the Recent section on the Apps page until they are deployed and published.

In this task, you create the app that was built in a previous lab, using a template.

Delete prototype app (if listed)

In the lab environment, the app built in a previous lab might still be listed in the Recent section. You cannot use this app to continue working on this lab because the underlying data is not available for use by the app.

The app must first be deleted before you continue with this lab.

1. If the Employee Onboarding app is listed, on the 3-dots menu, click Delete.

2. To confirm the deletion, click Delete.

   

Copy a template app to your AppSheet account

To continue building the Employee Onboarding app in this lab, you must first recreate the app from a template.

1. To copy the Employee Onboarding app to your AppSheet account, click the link: Employee Onboarding

2. In the app preview, click Accept.

3. In the left navigation menu, click Copy app ().

4. In the Copy app form, for App name, type Employee Onboarding, and leave the remaining settings as their defaults.

5. Click Copy app.

   AppSheet creates the app and copies the Google sheet that is used by the app to the /appsheet/data/EmployeeOnboarding-nnnnnnn folder under the My Drive folder on Google Drive.

   AppSheet creates a folder for each of your apps with your account ID (represented by nnnnnnn) in the data folder.

6. To go to the AppSheet editor, click Customize your app.

   You can also access the app from the Apps page in the AppSheet UI under Owned by me.

Your app is set up with the original employee data, and you can now continue to build out the app's functionality.

Click Check my progress to verify the objective. Create the app.

Task 2. Create an Apps Script project

To use Apps Script, you create an Apps Script project that contains your Javascript code, libraries, and other files. In this task, you create a project and a simple function that creates an event on Google Calendar.

1. In a separate browser tab, navigate to drive.google.com.

2. In the navigation menu, click + New > More > Google Apps Script.

   

3. A new Apps Script project is created and displayed in a separate browser tab.

   To rename the project, click the Untitled project, and in the Rename Project dialog, type Calendar Project, and then click Rename.

4. In the navigation menu, click Project Settings ().

5. In General Settings, for Time zone, select your current time zone.

6. To open the editor pane, in the navigation menu, click Editor ().

7. Copy and paste the following script code into the main project code pane, replacing the empty function:

   function createCalendarEvent(title, startDateTime, endDateTime) { let start = new Date(startDateTime); let end = new Date(endDateTime); var event = CalendarApp.getDefaultCalendar().createEvent(title, start, end); console.log('Created event with id', event.getId()); }

8. To save the project, in the top bar, click Save (). A folder is created on Google Drive that contains your Apps Script project files.

AppSheet provides a sample project that contains a set of functions which you can use to help get started with integrating Apps Script in AppSheet automation.

Click Check my progress to verify the objective. Create the Apps Script project.

Task 3. Create automation components

The next task is to create an automation bot in AppSheet and configure it with an event, process, and task to call a script.

Create the bot and event

The bot will use the Apps Script script created in the previous task to schedule a follow-up meeting on Google Calendar.

1. To create an automation bot, in the AppSheet UI, in the left navigation bar, click Automation () > Bots, and click Create a new Bot (+).

2. Click Create a new bot.

3. For the New Bot, click More () and Rename to Schedule follow-up meeting.

4. To configure an event that will trigger the bot, click Configure event.

5. For the Event name, type Follow-up meeting required, and press ENTER.

6. In the Settings pane, specify the following, and leave the remaining settings as their defaults:

   a. For Event Type, click Data Change.

   b. To trigger the bot for updates to the data rows in a table, click Updates only.

   c. For Table, select Training.

   d. For Condition, click Filter ().

   In the Expression Assistant form, for Condition for event follow-up meeting required (Yes/No), type AND(ISNOTBLANK([Follow-up scheduled]), [Follow-up scheduled] > TODAY())

   e. Click Save in the Expression Assistant form.

   The event is configured to trigger the bot when a row in the Training table is updated in the app. The bot is only triggered for the row if the Follow-up scheduled date and time has been set with a value that is later that the current date.

Create the process and task

Continue configuring the process and its steps to be executed by the bot. Each step is configured to run a task when it is executed.

1. For Run this Process, click the arrow, and select Create new process, and then enable Linking.

2. To change the process name, click Process for Schedule follow-up meeting - 1, and type Create follow-up meeting

3. To add a step to the process, click Add a step.

4. For the Step name, type Create calendar meeting, and press ENTER.

5. To create a task, in the Settings pane, enable Linking.

   a. For Task name, type Create calendar meeting with Apps Script

   b. For the type of task, click Call a script.

   c. For the Table name, select Training.

   d. For the Apps Script Project, click the folder (), and select the Calendar Project that you created in an earlier task.

   e. Click Authorize to authorize the Apps Script project to run.

   i. Sign in to your Google Cloud account with your lab credentials.

   ii. To allow the Calendar Project to access your calendars on Google Calendar, click Allow.

   f. For the Function Name, select the createCalendarEvent function.

   h. For the Function Parameters:

   i. For title, click Filter (). In the Expression Assistant form, for Function parameter: title, type CONCATENATE("Follow-up meeting with: ", [Employee ID].[Name])

   ii. Click Save in the form.

   iii. For startDateTime, click Filter (). In the Expression Assistant form, for Function parameter: startDateTime, type [Follow-up scheduled], and click Save.

   iv. For endDateTime, click Filter ().

   v. In the Expression Assistant form, for Function parameter: endDateTime, type [Follow-up scheduled] + "000:30:00", and click Save.

   The calendar meeting is set using a DateTime expression by adding 30 minutes to the current value in the Follow-up scheduled column in the current row of the Training table.

6. To save your changes to the app, click Save.

Task 4. Test the bot

In this task, you update the training record for an employee to set a follow-up meeting date and time in the app. You verify that the automation bot executes and a calendar meeting is created on Google Calendar.

Create the Training view

To view and update employee training records, create a view in the app.

1. In the AppSheet editor, in the left navigation bar, click App () > Views ().

2. To add a new view, click Add View (+).

3. Click Create a new view.

4. In the new view form, specify the following, and leave the remaining settings as their defaults:

   Property	Value (type or select)
   View name	Training
   For this data	Training
   View type	table
   Position	next

5. Click Save.

   The Training view is created and is now accessible from the app's navigation bar.

Update an employee's training record

1. To view employee training records, in the navigation bar of the app preview, click Training.

2. To view and edit the training record of Employee1, Click Employee 1, and then click Edit ().

3. To edit the Follow-up scheduled date, type tomorrow's date and set an appropriate time.

   For example, to schedule the follow-up meeting for May 25, 2023 at 9 AM, type: 05/25/2023 09:00:00 AM

4. Click Save in the app preview.

Verify the event on Google Calendar

1. In a separate browser tab, navigate to calendar.google.com.

2. If prompted, sign in using your lab credentials.

3. Verify that an event is created on your calendar for the specified date and time. Also verify that the duration of the event is 30 minutes. You can now edit the event details to add the employee, other guests, description, etc.

Click Check my progress to verify the objective. Create the calendar event.

Congratulations!

You've successfully integrated your AppSheet app with Google Apps Script.

In this lab, you learned how to perform the following tasks:

• Create an Apps Script project with code to create an event on Google Calendar.
• Implement an automation bot in AppSheet that invokes Apps Script from a sample app.
• Test the bot and verify that the calendar event was created.

End your lab

When you have completed your lab, click End Lab. Qwiklabs removes the resources you’ve used and cleans the account for you.

You will be given an opportunity to rate the lab experience. Select the applicable number of stars, type a comment, and then click Submit.

The number of stars indicates the following:

• 1 star = Very dissatisfied
• 2 stars = Dissatisfied
• 3 stars = Neutral
• 4 stars = Satisfied
• 5 stars = Very satisfied

You can close the dialog box if you don't want to provide feedback.

For feedback, suggestions, or corrections, please use the Support tab.

Copyright 2022 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.