Overview

In this lab, you use various data features that are provided by AppSheet in your app. These features are:

• Data slices
• Initial values
• App column formulas
• Expressions and functions
• Column types
• Column constraints

You use these features to implement your app's functionality while preserving the validity, consistency, and integrity of the data used by the app.

Objectives

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

• Starting with a Google sheet as a data source, use the AppSheet UI to create an app to manage a list of products and their inventory.

• Preview the app's functionality in the live preview feature of the AppSheet UI.

• Implement the data-related features listed above in your app.

• Build app column formulas with expressions and AppSheet's built-in functions.

• Use some of the Change and Content AppSheet column types in your app.

• Use column constraints to implement data validation in your app.

Setup and reuirements

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

In this task, you create a new app by using the AppSheet UI to connect to a data source.

Create the Inventory Manager app and select data

1. To create an app, in the AppSheet UI, click +Create > App > Start with existing data.

2. In the Create a new app dialog, for App name, type Inventory Manager.

3. For Category, select Inventory Management from the list.

4. Click Choose your data.

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

6. In the file picker, select the products 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. Update table configuration

AppSheet has created your new app and connected it to the products spreadsheet that provides data for the app.

AppSheet creates your app with default table configuration. In this task, you add tables to your app and update their configuration in the app editor.

Add other tables to your app

1. In the AppSheet UI, in the left navigation bar, click Data (). The previously added Products () table is listed.

2. In the Products panel, click View data source.
   AppSheet opens the Products sheet from Google Drive in a separate browser tab.

   Note: The data for the app is contained in separate sheets in the same workbook, which is the recommended way to store spreadsheet data for app performance reasons.

3. To review the contents of each tab in the sheet, click the sheet name at the bottom of the workbook.

4. To add the Product Categories table to the app, in the left navigation bar, click Data (), and click Add new data (+).
   All products in the app belong to a product category. The Product Categories table contains information about each category.

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

6. In the file picker, select the products sheet from the My Drive folder, and click Select.

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

8. Leave the default authorization settings for updates, adds and deletes, and click Add This Table.
   AppSheet adds the table to your app.

9. Repeat the previous steps to add the Shipping, Receiving and Suppliers tables to the app.
   These sheets contain information about product shipments, receipts, and suppliers.

Update the column configuration of the Products table

When a table is added to your app, AppSheet uses default configuration for the table's columns that might need to be updated. Because products belong to a product category, the Products table and the Product Categories table are related. Use the Ref column type to configure this relationship in AppSheet.

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

   An expanded view of the Products table is displayed.

2. To edit the column definition, click Edit () next to the Product Category column.

3. To change the type of the Product Category column, for Type, select Ref.

4. For Source table, select the Product Categories table.

5. In the column definition form, click Done.

6. Repeat the previous steps to edit the Supplier ID column definition:

   a. For Type, select Ref.

   b. For Source table, select the Suppliers table.

7. In the column definition form, click Done.

8. In the properties of the Product ID column, clear the Show and Search checkboxes.
   This step prevents the display and search of the key column of the Products table in the app.

9. Change the Type of the column: Lead Time (days) to: Number.

10. Change the Type of the column: Current Inventory to: Number.

11. Click Save.

Update the column configuration of the Shipping and Receiving tables

Because shipping transactions in this table are for specific products, the Shipping and Products tables are related via the Product ID column. The same relationship exists between the Receiving and Products tables.

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

   An expanded view of the Shipping table is displayed.

2. In the Product ID column definition form for Type, select Ref.

3. For Source table, select the Products table.

4. In the column definition form, click Done.

5. Click Save.

6. Repeat the previous steps to change the type of the Product ID column in the Receiving table to Ref.

7. Click Save.

Update the column configuration of the Suppliers table

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

   An expanded view of the Suppliers table is displayed.

2. Clear the Label checkbox of the _ComputedName column.

3. Select the Label checkbox of the Supplier Name column.
   The Label property tells AppSheet which column to use when displaying a row of data in the app.

4. Click Save.

Task 3. Customize the app's views

The Inventory Manager app is a live, functional prototype that is connected to the various datasheets on Google Drive.

In this task, you create and update views in the editor to enable your app users to browser and edit data via the app.

Create the Product Categories view

To view the data from the Product Categories table as a gallery of images, create the Categories view.

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

2. To add a new view in the Inventory Manager app, click +.

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	Categories
   For this data	Product Categories
   View type	gallery
   Position	first

5. Click Save.

   The app preview now displays the data from the Product Categories table in an easy to view gallery format.

6. Click any of the product category images to view more details about that category.

Create the Shipping and Receiving views

1. Repeat the previous steps to create a view for the Shipping table.

2. In the New View form, specify the following, and leave the remaining settings as their defaults:

   Property	Value (type or select)
   View name	Shipping
   For this data	Shipping
   View type	table
   Position	ref

3. Click Save.

4. Repeat the previous steps to create a view for the Receiving table.

5. In the New View form, specify the following, and leave the remaining settings as their defaults:

   Property	Value (type or select)
   View name	Receiving
   For this data	Receiving
   View type	table
   Position	ref

6. Click Save.

Create a dashboard view

Create a dashboard view to contain the Shipping and Receiving views that were created in the previous task.

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

2. Click + Add View.

3. Then 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	Ship/Recv
   View type	dashboard
   Position	last

5. For View entries, click Add, and then select the Shipping view that was created in the previous task.

6. Click Add again, and then select the Receiving view that was created in the previous task.

7. Enable Use tabs in mobile view.

   Note: When enabled, this option displays the dashboard's views in tabs when the app is run on a mobile device and in the app preview in the AppSheet editor.

8. Click Save.

Create the Suppliers view

Create this view and provide access to it via the app's main menu.

1. On the same App () > Views () tab, click + Add View.

2. 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	Suppliers
   For this data	Suppliers
   View type	Card
   Position	menu

4. In the View Options section, customize the card layout:

   • If not selected, choose the large card layout.
   • Click Title goes here. For Column to show, select Supplier Name.
   • Click Subtitle goes here. For Column to show, select Supplier Contact Phone.
   • Click the main airplane icon. For Column to show, select Location.
   • Click Header goes here. For Column to show, select _ComputedName.
   • Click Subheader goes here. For Column to show, select Supplier ID.

5. Click Save.

Delete the default Map view

AppSheet by default creates a view of type Map based on the data in the tables used by your app. Because you created a separate Suppliers view, you do not need the default map view in the app.

1. On the App () > Views () tab under Primary Navigation, select the Map view to expand its definition.

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

3. Click Save.

View the app's data

• In the app preview, access each view created in the previous tasks to view the data from their corresponding tables.

Task 4. Using formulas and expressions

In this task, you use app formulas to add functionality to your app. You also set initial value expressions for some of the columns in your app.

Create a spreadsheet formula

You can use worksheet formulas with AppSheet. When your app reads data from the sheet, AppSheet computes the formula values. When data is updated in the app and synced back to the sheet, the formulas are computed again.

1. To create a formula in the Products worksheet, in the AppSheet UI, in the left navigation bar, click Data () > Products ().

2. An expanded view of the Products table is displayed. Click View data source.
   The spreadsheet opens in a separate browser tab.

3. Select the Price column in the sheet.

4. To add a new column to the sheet, on the Insert menu at the top of the worksheet, click Columns > Insert 1 column right.

5. In the header row, type a name for the new column: Tax

   This column will contain the sales tax amount for each product whose value will be computed using a worksheet formula.

6. Click into the cell F2 in the first data row of this column, and type (include the = sign):

   =E2*0.05

   This sets the tax amount to be 5% of the product's price.

7. Click Enter in the cell.

8. To use the same formula in the remaining cells of this column, select the Auto fill checkbox.

Update the Products table structure

With the new column added to your worksheet, you must now update your app's data structure to use the new column.

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

2. Click Regenerate schema ().

3. Click Regenerate.

   AppSheet regenerates the column structure for the Products table, and resyncs the app in the live preview. Ignore any warnings that may appear in the AppSheet editor.

Test the formula via the app

1. In the app preview, select any of the products to view details of the product.
   The newly added Tax column is displayed.

2. Click Edit () to edit the product information.

   Note: The Tax column in the app is not editable. AppSheet treats any column that contains spreadsheet formulas as read-only in the app, because spreadsheet formulas cannot be evaluated in the app. However, they are evaluated when data is transferred to or from the backend data source.

3. Update the price of the product in the app, and click Save.

4. To sync the price change to the backend, click Sync () in the app preview, or wait a few seconds for the app to automatically update the changed data.

   After the sync process or the automatic update completes, the value in the Tax column in the app is updated based on the spreadsheet formula.

5. Return to the Products worksheet on the other browser tab. Verify that the Price and Tax column values for the product are updated.

Click Check my progress to verify the objective. Price and Tax columns are updated

Use an App formula

An app formula is an AppSheet expression that is evaluated whenever an app user changes a value in a form either while creating a new row or editing an existing row of a table.

You can use an app formula to compute the value for specific columns in your app. In this task, you add a virtual column in the app to display the total price of a product in the app. The value in this column will be computed using an app formula.

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

2. This expands the Products table definition, and click (+) Add virtual column.

3. In the New Virtual Column form, for Column name, type Total Price.

4. To open the Expression Assistant, click anywhere in the App formula field.

5. In the expression field, type [Price] + [Tax].

   Note: A table column name enclosed in square brackets is called a column expression. When used, it generates the value of the column from the current row in the table.

6. In the Expression Assistant, click Save.

7. In the form, click Done.

8. Click Save.

9. Select a product in the app preview to view its details, and note the value displayed for the Total Price column.

10. Edit the selected product, and change its price to a different value.

11. Click Save.
    After the changes are saved, the product details view displays the updated Total Price value for the product using the app formula defined earlier.

Set the initial value for a column

An initial value expression is used to assign an initial or default value for a column when a new entry is created via the app.

In this task, you set the initial value of a column in the Products table.

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

2. To open the Expression Assistant, click into the INITIAL VALUE field of the column Product ID.
   This is the key column of the table that contains the UNIQUEID() built-in AppSheet function. This function generates a random string that is used as a unique identifier within the app.

3. In the Expression Assistant, open the Text tab.

4. Find the example that uses the CONCATENATE(text-value1, [text-value2, ...]) function, and click Insert.
   This inserts a sample expression in the expression field.

5. Edit the expression to concatenate the Product Name column value, a hyphen, and the UNIQUEID() function:

   CONCATENATE([Product Name], "-", UNIQUEID())

   This expression uses AppSheet's built-in CONCATENATE function to generate a string that contains the value of the Product Name column from the current row in the Products table concatenated with a hyphen character and a unique random value.

6. In the Expression Assistant, click Save.

7. Click Save.

Add a new product via the app

Test the initial value configuration created in the previous task by adding a new product via the app.

1. To download a product image, click the following link and save the file to your computer: Picture frame

   You will use this image when creating a new product via the app in the following steps.

2. In the app preview, click Add .

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

   Property	Value (type or select)
   Product Name	Picture Frames
   Product Category	Home Décor
   Price	5
   Lead Time (days)	2
   Minimum Acceptable Inventory	10
   Minimum Shipment Quantity	1
   Supplier ID	Kitchen Source

4. Click the image icon in the app preview to upload an image for the new product. Use the image that you downloaded previously.

5. To create the new product, in the app preview, click Save.

6. To sync the price change to the backend, click Sync () in the app preview, or wait a few seconds for the app to automatically update the changed data.

Validate the initial value

1. After the app has updated the backend, navigate to the Products worksheet on the other browser tab.

2. To find the newly added Picture Frames product, scroll through the list of products in the sheet.

3. Verify that the value stored in the Product ID column matches the value generated by the initial value configuration that you defined in the app.

4. To find the new Picture Frames product, in the app preview, click Products in the bottom navigation bar and scroll through the list of products.

Click Check my progress to verify the objective. Set the initial value of the new product

Task 5. Use AppSheet column types

AppSheet supports many different column types for use in your app. In this task, you use some of these types to add functionality to your app.

Use Change column types

Change column types are used to track changes to the data used by your app. ChangeTimestamp is one such type that is used to record a timestamp whenever the value in a table's column changes.

1. Navigate to the Products worksheet on your browser tab.

2. To store the date when a product's price is updated, in the header row at the top of the worksheet, add a new column: Price Updated

3. In the AppSheet UI, in the left navigation bar, click Data () > Products (), this expands the Products table definition.

4. Click Regenerate Schema (), and click Regenerate again to confirm.

   AppSheet regenerates the data structure for your app. Ignore any warnings that may appear in the AppSheet editor.

5. To edit the definition of the Price Updated table column, click Edit ().

6. Change the column's data type: For Type, select ChangeTimestamp.

7. Under Type Details for Columns to watch for changes, click Add, and select the Price column.

8. Click Done in the column definition form.

9. Click Save.

10. To test your changes, modify the price of any product in the app preview and save the change.

11. After the changes are synced to the backend, verify that the Price Updated column in the Products worksheet is updated with the current date and time.

To read more about Change column types, view the Track changes using Change column types documentation.

Use Content column types

Content column types contain values that are shown as inline content in the app or open in an external content viewer. Image and Thumbnail are two examples of Content column types. To use a file or PDF in your app, use the File column type.

1. Navigate to the Products worksheet on your browser tab.

2. In the header row at the top of the worksheet, add a new column: Datasheet
   This column will contain a URL or path to a datasheet file for a product.

3. In the AppSheet UI, in the left navigation bar, click Data () > Products (), this expands the Products table definition.

4. Click Regenerate Schema (), and click Regenerate again to confirm.

   AppSheet regenerates the data structure for your app. Ignore any warnings that may appear in the AppSheet editor.

5. Next to the Datasheet column, click Edit ().

6. Change the column's type: For Type, select File.

   Note: Leave Image/File folder path empty to use the default folder location where the uploaded file is stored on Google Drive.

7. In the column definition form, click Done.

8. Click Save.

Test the File content column type

1. To download a sample PDF document, click the following link and save the file to your computer: Product Datasheet - Sample PDF

2. Edit any product in the AppSheet app preview.

3. In the edit form, scroll to the Datasheet field, and click the file icon.

4. In the file picker, select the previously downloaded datasheet file, and click Save.

5. Save the product changes in the app preview.

6. After the changes are synced to the backend, verify that the Datasheet column in the Products worksheet is updated with the path to the file on Drive.

7. In the app preview in the product details view, scroll to the Datasheet column and click the name of the uploaded file.

   A new browser tab displays the contents of the file.

Use the Signature content column type

The Signature column type is a content column type that is used to capture a user signature that is stored as a small inline image in the data source.

1. Switch to the Receiving worksheet in the other browser tab.

2. At the top of the worksheet, add a new column in the header row: Member Signature

3. In the AppSheet UI, in the left navigation bar, click Data () > Receiving (), this expands the Receiving table definition.

4. Click Regenerate Schema (), and click Regenerate again to confirm.

   AppSheet regenerates the data structure for your app. Ignore any warnings that may appear in the AppSheet editor.

Test the Signature content column type

1. In the app preview, in the bottom navigation bar, click Ship/Recv.

2. Click the Receiving tab.

3. Click on any of the transaction rows in the table, and then click Edit ().

4. To unlock the touch pad for editing, click anywhere in the Member Signature field.

5. Draw your signature, and click Save.

   Note: You may need to repeat the initial strokes before you can see the drawing in the app preview.

6. After the changes are synced to the backend, verify that the Member Signature column in the Receiving worksheet is updated with the path to an image file on Google Drive that contains the signature.

   The signature is also displayed in the app preview in the receiving transaction details view.

To read more about AppSheet column types, view the Column data types documentation.

Click Check my progress to verify the objective. Use the Change and Content column types

Task 6. Use data slices

A slice is a subset of the rows, columns, and actions of a table. You typically use slices when you want to create a App view of part of a table in your app.

In this task, you create a slice of the Products table that contains products that are low on inventory. You also create a App view to view the data from the slice.

Create a slice

1. In the AppSheet UI, in the left navigation bar, click Data () > Products (), and click Add slice to filter data.

   

2. Click Create a new slice for Products.

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

   Property	Value (type or select)
   Slice Name	Low Inventory
   Source Table	Products
   Slice Columns	All Columns
   Update mode	Updates

4. To open the Expression Assistant, for Row filter condition, click into the field and select Create a custom expression.

5. In the Expression Assistant for Row filter for slice Low Inventory (Yes/No), type: [Current Inventory]<[Minimum Acceptable Inventory]

6. In the Expression Assistant, click Save.

7. Click Save.

Create a view for the slice

Now that you created the slice, you can use it in a view to display the data from the slice.

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

2. Click + Add View.

3. Then 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	Low Inventory
   For this data	Low Inventory
   View type	deck
   Position	menu

   Note: The app user will access the Low Inventory view from the app's main menu.

5. Click Save.

Test the slice

1. In the app preview, navigate to the app's main menu.

2. Click the Low Inventory menu item.
   The Low Inventory view displayed in the app contains a subset of the products that are low on inventory.

3. Click on any of the products to view its details and verify the row filter condition that was used in the slice definition.

Task 7. Use constraints

In AppSheet, you can use constraints to control the display and usage of table column data and to implement data validation in your app.

In this task, you create a constraint to control the visibility of columns in the Product Categories table and implement data validation in your app.

Create a Show_If constraint

The Aisle and Shelf columns contain the aisle and shelf numbers of a warehouse where the products of a specific category are stored. In this task, you conditionally display the Shelf column of a product category in the app.

1. In the AppSheet UI, in the left navigation bar, click Data () > Product Categories (), this expand the Product Categories table column definition.

2. Edit the Shelf column definition.

3. Click the Expression Assistant filter icon next to the Show property, and click in the field to open the assistant.

4. In the Expression Assistant form, select the Yes/No tab.

5. Scroll through the list of examples to find the ISBLANK(value-or-list) entry, and click Insert.

   Note: ISBLANK() is a built-in AppSheet function that returns TRUE if the parameter passed to it is empty.

6. Edit the expression field to wrap the ISBLANK function with the NOT conditional operator, and replace the [Shelf] column with [Aisle]:

   NOT(ISBLANK([Aisle])) Note: The Shelf column will only be displayed in the app if the Aisle column is not empty in the sheet.

7. In the Expression Assistant, click Save.

8. In the column definition form, click Done.

9. Click Save.

Create a Valid_If constraint

To validate data input via the app, you create a Valid_If constraint for each column of a table that is used by the app.

1. In the AppSheet editor, to ensure the value entered for the Aisle column is numeric, edit its column definition.

2. In the column definition form, expand the Data Validity section.

3. Click into the Valid If field to launch the Expression Assistant.

4. In the Valid_If formula expression, type:

   AND([_THIS] > 0, [_THIS] < 51) Note: [_THIS] is a column expression that refers to the value in the current column of the current row that is displayed or edited in the app.

   The app assumes that there are 50 aisles (maximum) in the warehouse that are numbered from 1 through 50.

5. In the Expression Assistant, click Save.

6. In the column definition form for the Aisle column under Data Validity, click into the Invalid value error field.

   You use this field to provide an error message to the app user when the typed value for the Aisle column is out of range.

7. In the error message expression field, type:

   Please enter a valid Aisle number from 1 through 50.

8. In the Expression Assistant, click Save.

9. To make the Aisle column optional when creating a new product category via the app, in the column definition form, clear the Require checkbox.

10. In the column definition form, click Done.

11. Click Save .

Test the constraints

1. To display the product categories, in the app preview, click Categories in the bottom navigation bar.

2. Click the Holiday product category to view its details.

   The Aisle and Shelf column values are not displayed for this product category because the Aisle column is empty in the sheet.

   Even though the Shelf column is non-empty, the Show_If constraint prevents it from being displayed in the app.

3. To edit the Holiday product category, click Edit ().

4. For Aisle, click the minus (-) sign to decrease the number to -1.
   The invalid error message is displayed in the app.

5. For Aisle, type 51.
   The invalid error message is again displayed in the app.

6. For Aisle, type 5, and for Shelf, type 9X.

   Note: The Shelf column is of type Text.

7. Click Save.

   The product category details page is displayed with the valid values as entered.

8. View the Product Categories worksheet on a separate browser tab. After the app syncs the data, the valid values are updated in the sheet.

Congratulations!

You have successfully used some of AppSheet's data features in your app to:

• Configure initial values and app column formulas.
• Use different content column types.
• Create partial views using data slices.
• Implement data validation and control the display of columns using constraints.

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.