Overview

The goal of this lab is to introduce techniques to improve your app's scalability and performance.

When working with large datasets, it is beneficial to partition the data based on how users access that data via the app. For example, users of a project management app might only use project and task data that is relevant to their own projects. Other app users who work on different project teams might only use data that is specific to those projects.

You can design your app with security filters and data partitions based on the app user's affiliation with that data and thereby improve the performance of your app.

Objectives

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

• Create an app without partitioned data as the default.

• Create security filters to limit the data used by the app.

• Partition the data in separate worksheets, and configure the app to use the different partitions.

• Enable the app to support user settings.

• Apply the user's choice of settings to control access to the partitioned data in the app.

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 your app and select data

In this task, you use the AppSheet UI to create a new app that connects to a data source.

Create the Project Manager app

1. On the Apps page in the AppSheet UI, click + Create > App > Start with existing data.

2. For App name, type Project Manager

3. For Category, select Planning & Project Management.

4. Click Choose your data.

5. In the Select data source dialog, select Google Sheets.

6. In the file picker, select the Projects spreadsheet from your My Drive folder on Google Drive, and click Select. This sheet was automatically provisioned for the lab.

   AppSheet creates the app and displays it in the AppSheet editor UI.

7. Click Customize your app.

   You are now in the AppSheet editor UI. The UI also provides a live preview of the app running in an emulator, which you can use to test the app's functions.

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

Task 2. Customize your app's data structure and views

The Project Manager app uses a Google sheet that contains project information.

In this task, you update your app's data structure and add the task data to your app. You also create a view to display the projects' tasks in the app.

Update the data strcuture

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

2. To expand the table configuration, click Projects ().

3. For the Project ID column, change its Type to Text.

4. Select the Key property for this column.

   Note: The Project ID column in the sheet contains values of type "text" and is the unique key column for this table.

5. For the Name column, clear the Key property.

6. Click Save.

Add Task data to your app

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

2. To add the project task data to your app, click Add new Data (+), and then click Add Table "Tasks" from suggestions.

3. Click Add This Table.

4. For the Tasks table, clear the Key property for _RowNumber column.

5. The Task ID column contains unique values for the individual tasks in the sheet. To set this column as the key column for the Tasks table, select the Key property.

The Project ID column in the Tasks table contains values that refer to the Project ID column in the Projects table.

1. Change the Type of the Project ID column in the Tasks table to Ref.

2. In the Tasks : Project ID column definition dialog, for Source table, select Projects.

3. Click Done.

4. Click Save.

Add the Tasks view

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

2. Click Add View (+), and then click Create a new view.

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

   Property	Value (type or select)
   View name	Tasks
   For this data	Tasks
   View type	table
   Position	later

   Note: If the Tasks view has been automatically created, change its type to table.

4. Click Save.

Test your app

Your app is now configured with the Projects and Tasks data.

1. To view the list of projects, in the bottom navigation bar of the app preview, click Projects.

2. To view the list of tasks of all the projects, in the bottom navigation bar of the app preview, click Tasks.

Task 3. Implement security filters

With the current app design, the entire list of projects and tasks is fetched by the app from the AppSheet backend and the cloud provider.

All app users access all the project and task information in the app. In reality, a given app user might only need to access a specific project and its tasks while they work on that project.

In this task, you implement security filters to improve app performance by limiting access to data that is relevbant only to the signed-in user.

Define the project team

The first task in this process is to define project team membership.

1. In a separate browser tab, open Google Drive, and if prompted, sign in to Drive.

2. Open the Teams sheet that has been provisioned by the lab. This sheet has two worksheets: Members and ProjectTeams.

   The Members worksheet contains data about each team member, and the ProjectTeams worksheet contains data about the project membership of each member.

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

4. To add the new data to your app, click Add new Data (+).

5. In the Add data dialog, select Google Sheets.

6. Select the Teams sheet, and then click Select.

7. In the Create a new table dialog, for Worksheet Name/Qualifier, select Members.

8. Leave the remaining settings as their defaults, and click Add This Table.

9. Repeat the previous steps to add the ProjectTeams worksheet to your app.

   Note: To directly add the table to your app, you can also click Add new Data (+) and then click Add a Table for "ProjectTeams" if it is displayed.

10. In the ProjectTeams table definition, for the Member ID column, change the Type to Ref.

11. For the Member ID column, change the Type to Ref.

12. In the ProjectTeams : Member ID column definition form, for Source table, select Members, and then click Done.

    Note: The Member ID column in the ProjectTeams table refers to the same key column in the Members table.

13. For the Project ID column, change the Type to Ref.

14. In the ProjectTeams : Project ID column definition form, for Source table, select Projects, and then click Done.

    Note: The Project ID column in the ProjectTeams table refers to the same key column in the Projects table.

15. In the ProjectTeams table column defition, for _RowNumber, clear Key, and then select the Key property for both the Project ID and Member ID columns.

16. Click Save.

    AppSheet automatically generates a _ComputedKey column to serve as the key column for this table.

Create a security filter for the Projects table

Security filters are typically used to control access to data in the app, and they also serve the purpose of improving an app's performance by reducing the amount of data transferred between the cloud provider, the AppSheet backend, and the app running on the user's mobile device.

1. To create a security filter for the Projects table, in the AppSheet UI, in the navigation bar click Security > Security Filters tab, and expand the Projects table definition.

2. To open the Expression Assistant, click into the Security filter expression field.

3. In the Expression Assistant form, for Security filter for table Projects (Yes/No), type:

   IN([Project ID], SELECT(ProjectTeams[Project ID], [Member ID].[Email]=USEREMAIL())) Note: This expression returns TRUE for all rows in the Projects table whose Project ID column value is in the list of project IDs from the ProjectTeams table, where the Member ID's email matches the email of the signed-in app user.
   In effect, this expression returns only those projects in which the signed-in app user is a project team member.

4. In the Expression Assistant, click Save.

5. Click Save.

   Note: After the app syncs the latest changes from the AppSheet backend, note that none of the projects are now displayed in the app preview. This is because none of the member email addresses in the Members worksheet match the user email you used to sign in to AppSheet.
   AppSheet uses your email address as the signed-in user in the app preview.

6. In the AppSheet UI, in the left navigation bar, click Data (), and select the Members table, and then click View data source.
   AppSheet opens the Members worksheet in a separate browser tab or window.

7. Edit the email column of the row with Member ID: 1002, and change the email address to your lab user email address.

   

   Note: For the email address, you can copy and paste the value of the Google Cloud Username from the Qwiklabs credentials panel in the lab window.

8. To refresh the app's data, in the app preview, click Sync ().

   Only projects in which you are a member (based on your email address), are now displayed in the app.

   Note: You can also apply the same security filter expression for the Tasks table to limit the tasks shown to only those that belong in projects in which the signed-in user is a member.

With the use of security filters, the amount of data transferred between the app and the AppSheet backend and the amount of data stored on the user's device is reduced.

Click Check my progress to verify the objective. Create a security filter

Task 4. Partitioning your app's data

You can partition the data used by your app to further improve its scalability and performance. To partition the data, you divide it into separate sections with the same column structure.

Each partition contains a different subset of the rows of data, and app users access data from a single partition at any given time when using the app.

In this task, for the projects' tasks data, you use separate tables that are sourced from separate worksheets. Each worksheet contains task data for a single project.

Add partitioned data to your app

1. In the AppSheet UI, in the left navigation bar, click Data (), and select Tasks to expand the Tasks table definition.

2. Click More (), and then click Delete.

3. Once asked Are you sure, click Delete to remove the table from the app.

   Note: The app preview will display an error. You can ignore it now because you will fix it in a later step.

4. In a separate browser tab, open the Partitioned Tasks sheet on Google Drive.

5. View the content of each worksheet. Each of the worksheets has an identical column structure and each contains the tasks that make up an individual project.

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

7. To add the new data to your app, click Add new Data (+).

8. In the Add data dialog, select Google Sheets.

9. Select the Partitioned Tasks sheet, and then click Select.

10. In the Create a new table dialog, for Worksheet Name/Qualifier, select Project 1 Tasks.

11. Leave the remaining settings as their defaults, and click Add This Table.

12. To edit the Project 1 Tasks table column definition, change the Type of the Project ID column to Ref.

13. In the Project 1 Tasks : Project ID column definition dialog, for Source table, select Projects, and then click Done.

14. Click Save.

Configure data partitions

1. In the Project 1 Tasks table definition, click Table settings (), and then expand the Scale section.

2. Enable Partitioned across many worksheets.

3. To open the Expression Assistant for Partition Expression, click into the field.

4. In the Expression Assistant form, for the Partition selection formula, type:

   ANY(SELECT(ProjectTeams[Project ID], ([Member ID].[Email] = USEREMAIL())))

5. Click Save.

   Note: The expression uses the AppSheet built-in ANY function to return a random project ID from the ProjectTeams table where the signed-in user is a member.
   This works when an app user is a member of only one project at any given time. You will see how to modify this implementation to manage multiple project memberships in the next task.

6. In the Partitions section, for Partition, type P1

7. For WorkSheet to use, select Project 1 Tasks.

8. To add another partition, click Add.

9. For Partition, type P2

10. For WorkSheet to use, select Project 2 Tasks, and then click Add again.

11. For Partition, type P3

12. For WorkSheet to use, select Project 3 Tasks.

    Note: The result of the Partition Expression configured earlier must match the value entered for Partition.

13. Click Done.

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

Test data partitioning

1. In the AppSheet UI, navigate to App () > Views (), and then click the Tasks view.

2. In the view definition, for For this data, select Project 1 Tasks.

3. Click Save.

4. In the bottom navigation bar of the app preview, click Tasks.

   The tasks that are displayed are based on your user email's project membership in the ProjectTeams table.

   Based on the current project assignment, tasks from either Project 2 or Project 3 are displayed in the app.

5. In the Members worksheet on Google Drive (in Teams sheet), copy the value in the email column of the row with Member ID: 1002, and paste it in the email column of the row with Member ID: 1001.

6. Edit the email column of the row with Member ID: 1002, and change the email address to: e2@company.com.

7. To refresh the app's data, in the app preview, click Sync ().

   You should now see that the tasks displayed in the app are from Project 1 because the signed-in user is now a member of this project.

Click Check my progress to verify the objective. Use data partitions

Task 5. Use UserSettings in your app

The User Settings feature in AppSheet allows the app user to customize the app's behavior by changing the settings of certain options in the app.

In this task, you define user settings to enable the app user to select a project from a list of projects that they are a member of. You then leverage the previously configured data partitions to provide app user access only to the selected project's tasks in the app.

Configure user settings

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

2. Under Options, click User Settings.

   Note: In the AppSheet UI, the user settings definition is similar to a table's column definition.

3. To edit the Options Heading option definition, click Edit ().

4. To display this option as a user setting in the app, in the option definition form, select Show.

5. To open the Expression Assistant for Content, click into the field.

6. In the Expression Assistant form, for Content (LongText), type "Select your project:", click Save, and then click Done in the form.

   Note: Include the quote characters in the content string.

7. To edit the Option 1 option definition, click Edit ().

8. In the option definition form specify the following, and leave the remaining settings as their defaults:

   Property	Value (type or select)
   Column name	Projects
   Show?	Select
   Type	Enum
   Type Details > Base type	Ref
   Base type details > Referenced table name	Projects
   Input mode	Buttons

9. Expand the Data Validity section, and click into the Valid If field.

10. In the Expression Assistant form, for Valid If formula for column Projects (Yes/No,List), type:

    SELECT(ProjectTeams[Project ID], ([Member ID].[Email] = USEREMAIL()))

    and then click Save.

11. Expand the Auto Compute section, and then repeat the step above to set the expression for Initial Value:

    ANY(SELECT(ProjectTeams[Project ID], ([Member ID].[Email] = USEREMAIL()))) Note: The expressions are used to set valid values for the project options based on the signed-in user's project membership.

12. In the option definition form, click Done.

13. Click Save.

Re-configure data partitions

Because you are now using the user settings values, the partition expression must be re-configured to select the app's data partitions.

1. In the Project 1 Tasks table definition, click Table settings (), and then expand the Scale section.

2. To open the Expression Assistant for Partition Expression, click into the field.

3. In the Expression Assistant form, for the Partition selection formula, type:

   USERSETTINGS("Projects")

   and then click Save.

4. In the table settings form, click Done.

   Note: The expression uses the AppSheet built-in USERSETTINGS function to return the current value of the Projects option as set by the app user in the Settings page of the app.

5. Click Save.

Test user settings

1. In the main menu of the app preview, click Settings.

   Note: The Settings menu item is automatically added to your app's main menu when user settings are defined in the app.

2. Select a project from the list and click Save.

3. In the bottom navigation bar of the app preview, click Tasks, and verify that the tasks displayed belong to the selected project.

4. In the Members worksheet on Google Drive (in Teams sheet), copy the value in the email column of the row with Member ID: 1001, and paste it in the email column of the row with Member ID: 1002.

5. Edit the email column of the row with Member ID: 1001, and change the email address to: e1@company.com.

6. To refresh the app's data, click Sync ().

7. Repeat the previous steps to select a different project from the user settings page, and verify that the list of tasks displayed are from the newly selected project.

Congratulations!

You successfully completed the lab to implement techniques to improve the scalability and performance of your app.

In this lab, you learned how to:

• Create security filters to limit the data used by the app.
• Partition the data in separate worksheets, and configure the app to use the different partitions.
• Enable the app to support user settings.
• Apply the user's choice of settings to control access to the partitioned data in the app.

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.