![](https://cdn.qwiklabs.com/assets/labs/start_lab-f45aca49782d4033c3ff688160387ac98c66941d.png)
Before you begin
- Labs create a Google Cloud project and resources for a fixed time
- Labs have a time limit and no pause feature. If you restart it, you'll have to start from the beginning.
- On the top left of your screen, click Start lab to begin
Create the initial data structures for the app
/ 10
Create the app
/ 5
Implement user sign up in the app
/ 10
Implement user management in the app
/ 5
Create the management view with data
/ 30
Create and assign tasks
/ 10
Review and complete tasks
/ 10
Generate a daily task report
/ 20
This lab is recommended for students who have completed the labs in the three-course series on Building No-Code Apps with AppSheet:
Topics tested:
Are you ready for the challenge?
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.
To complete this lab, you need:
Make sure you sign in to the lab using an incognito window.
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.
Note your lab credentials. You will use them to sign in to AppSheet for this lab.
Click Open AppSheet to open the AppSheet UI in a separate browser tab or incognito window.
Click to sign in with Google.
In the Sign in with Google dialog, enter the provided Username, and click Next.
Enter the provided Password and click Next.
Click Accept to accept the terms.
To enable AppSheet to access the Google Drive folders associated with your lab account, on the AppSheet consent page, click Allow.
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.
You're an app developer for Cymbal Superstore, a retailer with a multinational presence. Cymbal Superstore is a one-stop shopping destination for all household needs and offers everything from organic produce and groceries to cookware and home goods. Over the past three decades, Cymbal Superstore has expanded to become one of the largest department stores in the world, with more that 7,500 locations worldwide and almost half a million employees.
The company is a legacy retailer that is digitally transforming to more efficiently manage its supply chains and day-to-day operations and improve its online presence. It's investing heavily in digital platforms to accompany its physical branches and roll out new capabilities such as delivery, curbside pick-up, and retail task management.
To help Cymbal Superstore implement these capabilities, you're responsible for creating the first version of a retail task management app on Google Cloud's AppSheet no-code development platform.
You're expected to have the skills and knowledge for working with AppSheet, so step-by-step guides are not provided.
You will create a new app and other resources as needed with Google Workspace products and AppSheet. Read through each task description, and create the required functionality.
As you build you app, remember to periodically use Save () in the AppSheet UI to save your work.
Cymbal Superstore has decided to use Google Cloud's AppSheet platform to build an app that manages and tracks a team's tasks at a retail branch. Before creating the app, however, you must design and create the data structures that will be used by the app.
A. Create a Google Sheets spreadsheet on Drive
B. Create a set of worksheets within this spreadsheet
Name the first worksheet Tasks.
The Tasks worksheet will store common task definitions or task templates for all the managers of retail branch stores to use to create and assign tasks to store staff.
The worksheet should have the following initial column structure:
Column Name | Description |
Task ID | Unique identifier for a task |
Task Name | Name of the task |
Category |
The category in which the task belongs |
Instructions |
Instructions on how to perform the task |
Priority |
Instructions on how to perform the task |
Days |
Number of days required to perform the task |
Create another worksheet to store task details information.
The Task Details worksheet will store detail task information like task status and assignment in addition to other data.
The worksheet should have the following initial column structure:
Column Name |
Description |
Task Details ID |
Unique identifier for a task detail |
Task ID |
Unique identifier for a task |
Task Assigned Date |
The date and time that the task is assigned |
Task Completed Date |
The date and time that the task was completed |
Employee Email |
Email of the employee to whom the task is assigned |
Task Status |
Status of the task |
Create a Categories worksheet to store task category information based on the following data structure:
Column Name |
Description |
Category ID |
Unique identifier for a category |
Category Name |
Name of the category |
Description |
The category description |
Cymbal Superstore employees perform tasks based on their role and the role required to perform a task.
To define these roles, create a Roles worksheet to store role information based on the following data structure:
Column Name |
Description |
Role ID |
Unique identifier for a role |
Role Name |
Name of the role |
Description |
The role description |
Populate the Roles worksheet with an initial set of roles, one role per row:
Role ID |
Role Name |
Description |
1001 |
Admin |
Administrator or super user |
1002 |
Manager |
Store manager |
1003 |
Staff |
Store associate |
Create the Employees worksheet to store employee information based on the following data structure:
Column Name |
Description |
Employee ID |
Unique identifier for an employee |
Employee Name |
Name of the employee |
The employee email |
|
Photo |
The employee photo |
Role |
The employee role in the retail organization |
Click Check my progress to verify the objective.
Now that you've created the initial data structures for the app, you can create the app in AppSheet.
A. Create the app with data
In a separate browser tab or window, sign in to AppSheet with the lab credentials.
Create an app starting with data:
Add the sheets that were created in the previous task as tables for the app.
B. Create table relationships
You may have noticed that the tables created earlier have relationships between certain columns in the tables. In this subtask, you define these relationships in the AppSheet editor.
Tasks are assigned to a Category. Create a relationship or reference between the Tasks table and the Categories table with the Category column.
Task Details are related to a Task. Create a relationship or reference between the Task Details table and the Tasks table with the Task ID column in the Task Details table.
An Employee is assigned a Role in the organization. Create a relationship between the Employees table and the Roles table with the Role column.
C. Configure table columns
When you create tables for your app, AppSheet makes certain assumptions on the data types of the columns in the tables. In this subtask, you review and update some column definitions in the tables.
Update the properties of columns in the Tasks table:
Change the type of the Priority column to an appropriate AppSheet column type. This column will contain a single value from an enum list of values: Low, Medium, High.
Change the type of the Days column to an appropriate AppSheet numeric column type, and set its initial value to 1.
Update the properties of columns in the Task Details table:
Set the initial value of the Task Details ID key column to a unique random string using an AppSheet built-in function.
Change the type of the Task Status column to an appropriate AppSheet column type. This column will contain a single value from an enum list of values: Assigned, Unassigned, To do, Completed.
The column should have an initial value of Unassigned.
Update the type of the Task Assigned Date column to DateTime.
Update the type of the Task Completed Date column to DateTime.
Change the type of the Employee Email column to be an enumerated list of emails. Also, set these properties for this column:
Do not allow other values for this column.
The base type should be an email address type.
To enter a value for this column, an app user must select a value from a drop-down list.
The list of valid values should be populated from the email column value in all rows of the Employees table.
Update the properties of columns in the Roles table:
To implement certain functionality, you must add virtual columns to some of the tables in the app.
Use the following table to determine the name of the app table, name of the virtual column, and its value. Configure the column's value with an appropriate app formula:
Table Name |
Virtual column name |
App formula value |
Employees |
Role Name |
The Role Name value referenced by the Role column. |
Task Details |
Task Name |
The Task Name value referenced by the Task ID column. |
Task Details |
Task Priority |
The Priority value referenced by the Task ID column. |
Task Details |
Task Due Date |
A date and time that is computed based on the date and time when the task was assigned and the number of days required to complete the task. |
D. Create slices
In AppSheet, a slice is a subset of the rows, columns, and actions of a table. To support certain functionality in the app, in this subtask, you create slices for some of the tables.
Create a slice of the Employees table that contains data of the user who is signed in to the app using the following information:
Slice Name |
Columns |
Rows |
ActiveUser |
All columns of the Employees table |
Rows from the Employees table whose Email column value matches the email of the user who has signed in to the app. |
To manage tasks that are assigned to an employee but haven't been started, create a slice for the Task Details table with the following configuration:
Slice Name |
Columns |
Rows |
AssignedTasks |
All columns of the Task Details table |
Rows from the Task Details table whose Email column value matches the email of the user who has signed in to the app, and with a Task Status column value of Assigned. |
To manage tasks that are assigned to an employee that have been completed, create a slice for the Task Details table with the following configuration:
Slice Name |
Columns |
Rows |
CompletedTasks |
All columns of the Task Details table |
Rows from the Task Details table whose Email column value matches the email of the user who has signed in to the app, and with a Task Status column value of Completed. |
Save your changes to the app.
Click Check my progress to verify the objective.
For an employee to use the app, they must first sign up. In this task, you implement the app sign-up functionality in the AppSheet editor.
A. Create the sign-up form
Create a sign-up form view in the AppSheet editor.
Name the view: Sign Up
Use the Employees table as the data for this view.
Position the button in the center of the app's navigation bar.
To store an employee's phone number, add a Phone column in the Employees sheet.
Regenerate the Employees table structure in the app to update the set of table columns to include the new Phone column.
When an employee signs up to use the app, automatically set values for these columns in the Employee table:
For the Employee ID column, set its initial value using the formula:
EMPnnnnn, where nnnnn is a random integer between 0 and 100000.
For the Employee Role column, because it's a column of type Ref, set its initial value to be the Role ID of the row in the Role table that has a value of Staff in the Role Name column.
Configure the set and order of columns in the form to enable the app user to sign up as an employee by entering values for these columns:
Save your changes to the app.
B. Sign up employees
Click the links and save the employee image files to your computer:
Employee 1:
https://storage.googleapis.com/cloud-training/AppSheet/challengelab/employee_1.png
Employee 2:
https://storage.googleapis.com/cloud-training/AppSheet/challengelab/employee_2.png
In the app preview, sign up as an app user, and provide the following information in the sign-up form:
Column |
Value (type or select) |
Employee Name |
[Your name] |
Phone |
1-555-555-5555 |
[Your Google Cloud Username from the lab credentials panel] |
|
Photo |
[Upload the photo named Employee 1 from your computer] |
Sign up as a second user of the app. For this user:
To implement this capability in the AppSheet editor, configure the Show if property of the Sign Up view with a Yes/No expression, that determines if the signed in user's email address exists in the Employees table.
Click Check my progress to verify the objective.
As users sign up to use the app, they need to be managed as team members. In this task, you create a view in the app where administrators and managers can perform user and team management functions.
To manage app users, in the AppSheet editor, create a view named Staff. Make the view accessible from the far-right position in the app's navigation bar.
The view should display a list of employees from the Employee table as a deck of cards.
Save your changes to the app.
In the app preview of the AppSheet editor, edit the user with email that contains the value of the Google Cloud Username that you created in the previous task:
Only employees (app users) with a role of Manager or Admin should be able to see and access the view in the app.
In the AppSheet editor, use the view's Show if property with a Yes/No expression that determines whether the role of the signed-in app user is "Admin" or "Manager".
Click Check my progress to verify the objective.
The employees or staff of Cymbal Superstore manage the tasks assigned to them in the app. In this task, you create the views that enable a staff employee to view and manage their tasks in the app.
The My tasks view will be a dashboard view which contains two other child views: a list of assigned tasks to be completed and another list of tasks that have been completed by the user.
A. Create the assigned tasks view
Create the child view to display the assigned tasks:
Name the view: To do.
For the data to be used for this view, use the slice AssignedTasks that was created earlier.
Display the list of tasks as a deck of cards.
This view is referenced by a parent dashboard view, so select the appropriate position for this view.
Display the name of the task as the Primary header in the view.
Display the priority of the task as the Secondary header in the view.
Display the due date of the task as the summary column in the view.
B. Create the completed tasks view
Create another child view to display the completed tasks:
Name the view: Done.
For the data to be used for this view, use the slice CompletedTasks that was created earlier.
Display the list of tasks as a deck of cards.
This view is referenced by a parent dashboard view, so select the appropriate position for this view.
Display the name of the task as the Primary header in the view.
Display the completed date of the task as the Summary column in the view.
C. Create the My Tasks view
Create the parent view with the following configuration:
Name the view: My Tasks.
Configure the view as a dashboard view.
Select an appropriate position for the view in the app's bottom navigation bar.
Add the To do and Done views as child view entries for this view.
Display the child views in the dashboard as tabs.
The My Tasks view must only be displayed in the app for a user who has signed up to use the app.
D. Update the Sign Up view
Edit the Sign Up view that was created earlier to display the My Tasks view when a user submits the sign-up form in the app.
With this configuration, the app user automatically navigates to the My Tasks view in the app after signing up.
Save your changes to the app.
A store manager uses the app to perform management and other functions to manage template tasks, task categories, and staff tasks. In this task, you add views to the app that enable an app user with the appropriate role to perform these functions.
A. Create the task templates view
The task templates view is used to display and manage a list of common or template tasks in the app.
Create a view of type table and name it Task Templates.
Use the data from the Tasks table for this view.
This view is referenced from the Manage view that you create later in this task. Set the position of this view accordingly.
Configure some of the view options to:
B. Create the task categories view
The task categories view is used to display and manage a list of task categories in the app.
Create a view of type table and name it Task Categories.
This view is referenced from the Manage view that you create later in this task. Set the position of this view accordingly.
Use the data from the Categories table for this view.
Configure the view option to display the values in the Category Name and Description columns in this view.
C. Create the staff tasks view
The staff tasks view is used to display and manage a list of unassigned and assigned tasks.
Create a view of type deck and name it Staff Tasks.
This view is referenced from the Manage view that you create later in this task. Set the position of this view accordingly.
Use the data from the Task Details table for this view.
Configure the following view options:
Only include these specific actions for each row that is displayed in this view:
D. Create data for the management view
The views created in the previous steps are accessed from a separate management view in the app. Before you create the management view, you must create the data for the view. The data for the view is a list of management functions.
To store the data for the view, add a worksheet to the Retail Task Management sheet on Drive:
Name the worksheet Manage.
The worksheet should have the following columns: Function name, Icon, View, and Description.
Populate the worksheet with data rows, one for each row from the following table:
Function name |
Icon |
View |
Description |
Task Templates |
icons/task_template.png |
Task Templates |
Common tasks to assign daily |
Task Categories |
icons/task_category.png |
Task Categories |
Groupings for common tasks |
Staff Tasks |
icons/tasks.png |
Staff Tasks |
Tasks to be performed by staff |
Create the Manage table as read-only in the app, using the sheet above as its source of data.
Create and store a link to each view defined in the table:
Add a virtual column to the Manage table, and name it LinkToView.
Set its formula with a deep link to the view that is configured in the View column of the Manage table.
Configure the column to be visible in the app.
E. Create the management view
The management view lets the app user access management functions in the app through a set of other views.
Create a view in the app and name it Manage.
The data for the view should come from the Manage table.
Use a card layout for the view.
Make the view accessible from the app's bottom navigation bar.
Configure the view layout with columns from the Manage table:
Use the photo layout for the view.
To display the icon for each row, configure the Icon column.
To display the title for each row, configure the Function name column.
To display the subtitle for each row, configure the Description column.
When the app user clicks the card in the view, navigate them to the view that is configured in the View column of the Manage table.
The view must only be seen and accessible by an app user with the Manager role.
F. Add task management data
With the management views created, you can now add data using these views in the app.
Add the following task categories using the Task Categories view in the app:
Category Name |
Description |
Cleanup |
To maintain the health and hygiene standards, regular cleaning is essential. |
Store Opening |
Get ready for business. |
Store Closing |
Closing procedures are important as they determine how the next day will begin. |
Stock Inventory Management |
An overall inventory management plan guides stocking products for the store. |
General |
Basic tasks. |
Add the following common task templates using the Task Templates view in the app:
Task Name |
Category |
Instructions |
Priority |
Days |
Check for sanitization items |
Cleanup |
Sanitizer in place and changed as needed. |
High |
1 |
Clean glass areas |
Cleanup |
Wipe and clean the glass surfaces, remove any spills and stains. |
High |
3 |
Daily housekeeping |
Cleanup |
Complete general cleaning, including wiping down counters. |
High |
1 |
Opening inspections |
Store Opening |
Look for wet spots on the floor, ceiling, and walls. Tidy up any messy areas. |
High |
1 |
Visual merchandising |
Store Opening |
Ensure that shelves and product displays are organized. |
High |
1 |
Aisles |
Store Closing |
Organize items on shelves and tidy up aisles. |
High |
1 |
Clear the store |
Store Closing |
Ensure that all the customers have left the store. Check the bathrooms and other areas of your store to ensure that everyone has left. |
High |
1 |
LockUp |
Store Closing |
Lock the doors, turn off the “Open" sign. |
High |
1 |
Shut down equipment |
Store Closing |
Shutdown POS systems. Set heat/air conditioning to nighttime setting. |
High |
1 |
Restock empty shelves |
Stock Inventory Management |
Set up a fixed time to restock empty shelves. |
High |
1 |
Label all products |
Stock Inventory Management |
Use physical stickers or labels for products. |
High |
1 |
Clean up trash |
General |
Empty the trash bins and replace liners. |
High |
1 |
Click Check my progress to verify the objective.
Now that the management functions and data have been created in the app, as a store manager, you can create individual tasks and assign them to store employees. In this task, you create a form view to add and assign some of these tasks to a store employee in the app.
The Staff Tasks view that you created in the previous task will display a list of tasks as they are created by an app user who is a store manager. From this view, a store manager adds and assigns tasks to individual store employees using a form that you create in the app.
A. Create a form view to assign tasks
Create the form view:
Name the view Assign Task.
For the data to be used for this view, use the Task Details table.
The form will be referenced and displayed from an AppSheet action that you create in the next task, so set the position accordingly.
Display these columns in the form in the order shown:
After the form is submitted, display the Staff Tasks view in the app.
B. Create an action to display the view
The form view created in the previous task must be displayed when an action is invoked in the app. Create this action following these steps:
Name the action Create and assign task.
The action should be invoked for a record of the Task Details table.
The action should navigate the app user to the Assign Task form view that was created in the previous task.
Change the action's display icon to Add ().
Display the action as an overlay on the view in the app.
Because this action replaces the system Add action for the Task Details table, edit the system Add action's configuration so that it does not appear in the app.
C. Automatically update data when a task is assigned
When a store manager creates and assigns a task to a store employee using the Assign task form, some of the table data must be conditionally updated automatically in the sheet.
Create an action named Update new task data that updates the values in certain columns of the Task Details table according to the rules mentioned:
Configure the action so that it cannot be directly invoked by an app user.
This action must automatically be invoked when the Assign Task form is saved in the app. Configure the form to automatically invoke this action.
D. Create and assign tasks
As a store manager app user, create and assign tasks to a store employee in the app.
Click Check my progress to verify the objective.
As a store associate or employee, you review your assigned tasks in the app and mark them completed as they are done. In this task, you review your own tasks in the app and mark them completed.
This lets you validate the app's functionality as a store associate or employee.
A. Review and complete assigned tasks
In the app preview, review the tasks that are assigned to you in the To do tab of the My Tasks view.
Edit the task to update its status to Completed.
Validate that this task is now listed in the Done tab of the My Tasks view.
Repeat the steps above for all tasks that are assigned to you.
Click Check my progress to verify the objective.
As a final task in this lab, you generate a report with AppSheet automation. The report should be automatically generated daily and contain a list of assigned and completed tasks.
Create AppSheet automation components
Create a bot named Daily Task Report that will generate the report.
Configure an event to trigger the bot on a regular daily schedule. Pick an appropriate time of day for the event to occur.
Create a process that will be executed by the bot.
Add a step in the process that runs a task.
Configure the task to generate the report:
Data for the report should come from the Task Details table in the app.
The report should be in PDF file format.
Use a template for the report contents. The template should have the following structure:
Report Title: Daily Task Report
Date: [Today's date]
Section header: Assigned Tasks
For each assigned task, display the values of the columns in the following table:
[Start expression]
Column Name |
Column Value |
Task Name: |
[value in the Task Name column] |
Assigned To: |
[value in the Employee Email column] |
Task Priority: |
[value in the Task Priority column] |
Task Due Date: |
[value in the Task Due Date column] |
Task Assigned Date: |
[value in the Task Assigned Date column] |
[End expression]
Section header: Completed Tasks
For each completed task, display the values of the columns in the following table:
[Start expression]
Column Name |
Column Value |
Task Name: |
[value in the Task Name column] |
Assigned To: |
[value in the Employee Email column] |
Task Priority: |
[value in the Task Priority column] |
Task Due Date: |
[value in the Task Due Date column] |
Task Completed Date: |
[value in the Task Completed Date column] |
[End expression]
Set the report filename to contain the prefix DailyTasks.
Deploy the app.
Wait for the event to trigger the bot, and verify that the report was generated. If necessary, update the event trigger time to run the bot.
Click Check my progress to verify the objective.
Over the course of this challenge lab you've demonstrated your knowledge of creating an app with AppSheet.
This challenge lab is part of Google Cloud’s AppSheet Partner and AppSheet citizen developer learning paths.
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.