Overview

The goal of this lab is to use AppSheet to connect and configure data from a cloud database for use with your app.

In this lab, you use AppSheet to enhance a basic application with additional functionality using data from a second data source, a Cloud SQL for MySQL database that is hosted on Google Cloud.

Objectives

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

• Use the AppSheet UI to configure an app to use data from a Cloud SQL for MySQL database.
• Enhance your app by using data from the database to add functionality.

Setup and requirements

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 or your company's 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 on some occasions it's possible that the app built in a previous lab is still listed in the Recent section. You cannot use this app to continue working on this lab since 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 Inventory Manager 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 Inventory Manager app in this lab, you must first recreate the app from a template.

1. To copy the Inventory Manager app to your AppSheet account, click the link: Inventory Manager app to your AppSheet account.

2. In the app preview, click Accept.

   Note: Ignore any error in the app preview, if one is displayed.

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

4. In the Copy app form, for App name, type Inventory Manager, 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/InventoryManager-nnnnnnn folder under the My Drive folder on Google Drive.

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 contacts data source, 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. Set up your app with a Cloud SQL data source

The Inventory Manager app currently uses a Google sheet that contains product and other information in multiple worksheets.

In this task, you add a second Cloud SQL datasource that contains information about users and roles that you will use in your app.

Add a Cloud SQL for MySQL data source

In order for AppSheet servers to access your data, the MySQL database and its network must be configured to accept inbound connections from AppSheet servers to the database.

Note: The database and its network have been pre-provisioned for this lab.

1. In the My account page of the AppSheet UI, navigate to the Sources tab.

   Note: To access the My account page, click the blue circle in the top right corner, and select Account settings from the menu.

2. To add a new data source, click + New Data Source.

3. In the Add a new data source form, click Cloud Database.

4. In the Add database connection information form, specify the following, replacing [Cloud SQL IP] and [Cloud SQL Password] with the value from the Qwiklabs credentials panel for your lab:

   Property	Value (type or select)
   Type	MySQL
   Server	[Cloud SQL IP]
   Database	inventory_users
   Username	appsheet-user
   Password	[Cloud SQL Password]
   SSL Mode	Required

5. To test the connection, click Test.

6. To authorize access to the database, click Authorize Access.

   The Cloud SQL for MySQL database is now authorized for use by your app.

For more information, see using data from MySQL with AppSheet.

Task 3. Configure your app's data structure

After you add a MySQL data source to your account, you can add tables from the database to your app.

After data sources are added to the app, AppSheet treats them all similarly. In fact, it's common to combine data from a database with data from other sources in the same app.

In this task, you add the tables from the MySQL database to your app and update their column properties.

Add the tables to your app

1. In the AppSheet UI, in the left navigation bar, click Data ().

2. To add a data source to your app, click Add new data (+).

3. In the Add data dialog, click Cloud Database.

   Note: If you do not see this option, refresh the AppSheet UI in the browser, or close the app and re-open it from the Owned by me page.

4. In the Choose a Sheet/Table dialog, click Tables, and then click Members.

5. In the Create a new table dialog, click Add to app.

6. Repeat the previous steps to add the MemberRoles table to your app.

Update the Members table structure

The Members table contains data about each member who uses the app.

1. In the AppSheet UI, in the left navigation bar, click Data (), and then expand the Members table column definition.

2. For the MemberID column, clear Label.

3. To display the member's email as the row label in the app, for the Email column, select Label.

Update the MemberRoles table structure

In the MemberRoles table, each member is assigned a role. In this task, you define the relationship between the MemberRoles table and the Members table.

1. In the AppSheet UI, in the left navigation bar, click Data (), expand the MemberRoles table column definition.

2. For the MemberRoleID column, clear Label.

3. To display the member's role as the row label in the app, for the Role column, select Label.

4. For the MemberID column, change its Type to Ref.

5. In the MemberRoles:MemberID column definition form, for Source table, select Members.

6. Click Done.

7. For the MemberID column, clear Show.

8. To edit the Role column definition, click Edit () next to the column name.

9. In the MemberRoles:Role form for Type, select Enum.

10. For Values under Type Details, click Add, and then type Member

11. Repeat the previous step to add Manager and Readonly to the list of values.

12. Clear Allow other values, and then click Done.

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

Task 4. Create a view for member data

In this task, you create a view to the Members table to view and update the data in the table via the app.

Create the Members view

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

2. To add a new view in the app, 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	Members
   For this data	Members
   View type	card
   Position	menu

5. Click Save.

View member data

1. In the app preview, from the app's main menu, select Members.

   The data from the members table in the MySQL database is displayed in the app.

2. To view the member's role information, click the member name.

Task 5. Use the members and roles data to control access

In this task, you leverage the data from the Members and MemberRoles tables in the MySQL database to control access to data in the app.

Set a table's update mode

A table's update mode allows the app user to add, update, or delete data from the table via the app. It can also be set to read-only to prevent the user from changing the data via the app.

1. To set the update mode of the Products table, in the left navigation bar, click Data () > Products, and this expand the Products table definition.

2. Click Table setting ().

3. For Are updates allowed, click Filter (), and then to open the Expression Assistant, click into the field.

4. For the expression, type:

   SWITCH(TEXT(SELECT(MemberRoles[Role], [MemberID].[Email]=USEREMAIL())), "Manager", "ALL_CHANGES", "Member", "ADDS_AND_UPDATES", "READ_ONLY") Note: The expression retrieves the role of the signed-in user based on their email address and sets the table's update mode based on text value of the user's roles.
   If users have multiple roles, the expression must be modified accordingly.

5. To save the table's update mode expression, in the Expression Assistant, click Save.

6. Click Done.

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

Test the update mode

1. In the bottom navigation bar of the app preview, click Products.

2. Note that the Add action is no longer displayed in the app. This is because the signed-in user (your Qwiklab username) does not exist in the Members table as a valid app user.

3. Click on any product and verify that the Edit () and Delete () actions are also not available, because you are now in READ_ONLY mode in the app.

4. In the app's main menu, select Members.

5. To edit the member with ID: 1000, click Edit (). This member has a role of Member in the database.

6. Copy your Google Cloud Username from the Qwiklabs credentials panel, and paste it as the Email for this member.

7. Click Save in the app, and then wait for the app to sync the data update to the backend.

8. In the bottom navigation bar, click Products.
   The Add () action should now be displayed in the app.

9. Click on any product and verify that the Edit () action is also available, but the Delete () action is still not available because the update mode is now set to ADDS_AND_UPDATES.

10. In the app's main menu, select Members, and then click the email address of the same member with ID: 1000.

11. Scroll the details view in the app and in the Related MemberRoles table, click the Member role.

12. To change the member's role to Manager, click Edit ().

13. In the edit form, for Role, select Manager, and then click Save in the app.

    Wait for the app to sync the data updates to the backend.

14. As the signed-in app user with a role of Manager, verify that the Add, Edit and Delete actions are visible for the products in the app.

Click Check my progress to verify the objective. Set the updade mode of a table

Congratulations!

You have successfully used AppSheet to configure data from multiple data sources for your app.

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

• Configure an app to use data from a Cloud SQL for MySQL database.
• Implement role-based access control in your app using data from the database.

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.