Overview

The goal of this lab is to connect and configure data from multiple data sources for your app using AppSheet.

In this lab, you use AppSheet to enhance a basic application with additional functionality using data from a second data source. Using the AppSheet editor, you configure the type of data used in the app. You also create a relationship between the data sources or tables to provide additional functionality in your app.

Objectives

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

• Use the AppSheet UI to configure an app using data from multiple data sources.
• Add a second data source to the app using a separate Google sheet.
• Configure the data types of the data from the tables that are used in the app.
• Create a relationship between the existing and new tables.
• Verify that the newly added and modified data is present in the underlying Google sheet.

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.

Delete the app before continuing with this lab.

1. If the Customer Contacts app is listed, click the 3-dots menu and select Delete.

2. To confirm the deletion, click Delete.

   

Copy a template app to your AppSheet account

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

1. To copy the Customer Contacts app to your AppSheet account, click the link: Customer Contacts app

2. In the app preview, click Accept.

3. To create a copy of the app for use in this lab, in the left navigation bar, click Copy app ().

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

   Property	Value (type or select)
   App name	Customer Contacts

5. Click Copy app.

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

6. Click Customize your app to go to the AppSheet editor.

   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 second data source

Our Customer contacts app currently uses a Google sheet containing sample person contact information.

In this task, you add a second Google sheet that contains information about companies to the app.

Select data for your app

AppSheet refers to data files used in your app as tables. A table is a description of the rows and columns in your spreadsheet. While the data is stored in your spreadsheet, this description becomes part of your app definition.

Adding a table to your app is usually one of the first steps involved in creating an app.

To add additional tables after you have created your app, perform the following steps:

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

   

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

   If you see an error "As a co-author you don't have the permission to add new data. Please ask the app owner to add new data", please refresh the AppSheet UI in your browser. Then, retry the step.

3. From Add data, select Google Sheets.

4. In the file picker, select the companies sheet from the My Drive folder, and click Select. This sheet was automatically provisioned for the lab.

5. In the Create a new table form, leave the default settings, and click Add this table.

   The settings in this dialog let you select a specific worksheet from the spreadsheet or a different type of data source; and lets you allow or disallow modifications to the data by users of the app.

AppSheet adds the companies table to your app. This table contains information about various sample companies.

Click Check my progress to verify the objective. Set up your app with a second data source

Task 3. Configure your app's data structure

When a data source is added as a table for your app, AppSheet reads each column header to define the column structure of the app. You need a column header in your data source for each column in which you store data.

In this task, you inspect and if needed update the columns' type and properties (the default structure) that AppSheet has inferred and set for the app.

Update column structure for: contacts

1. In the left navigation bar, click Data (), and then click contacts ().

   An expanded view of the contacts table is displayed.

2. The table below lists the columns and some of their properties as inferred by AppSheet.

3. Determine if you need to change the type or other properties of each column (Scroll to the right in the AppSheet editor to view all the column properties).

   Name	Type	Key?	Label?	Formula	Show?	Editable?	Require?
   _RowNumber (System generated virtual column)	Number
   ID	Number	(checked)			checked	checked	checked
   Email Address	Email				checked	checked	checked
   First Name	Name				checked	checked
   Last Name	Name				checked	checked
   Phone	Text				checked	checked
   _ComputedName (System generated virtual column)	Name		(checked)	CONCATENATE([First Name]," ",[Last Name])	checked

   Which of the above columns' properties do you think needs to be updated?

   This generally depends on your app's requirements. For the purposes of this lab, the highlighted columns in the table above are candidates whose properties should be changed.

4. Given that this is a person contact app, there must be valid data in all cells of the First Name column, so the Require? property for this column should be checked.

   Notice that AppSheet has set the type property of the Phone column as text.

   This allows the column to contain a single line of text. Since this column is intended to store a valid phone number, change the type to Phone using the drop-down list.

5. To save your changes to the column configuration, click Save.

Update column structure for: companies

Follow the same process to update the structure of the columns of the companies table, where required.

1. Perform this step by referring to the highlighted items in the table below as a guide to update the column properties:

   Name	Type	Key?	Label?	Formula	Show?	Editable?	Require?
   _RowNumber (System generated virtual column)	Number
   ID	Number	(checked)			checked	checked	checked
   Phone	Text				checked	checked
   Company Name	Name				checked	checked
   Industry	Text				checked	checked
   Business Address	Address				checked	checked
   Shipping Information	LongText		(checked)		checked	checked

2. Repeat the previous step for each of the highlighted columns in the table above, except the Industry column which is updated in the next step.

   AppSheet supports the Enumerated Type for columns. Columns of these types are constrained to having one or more allowed values from a fixed list.

3. In this step, you change the Industry column's type property to use the Enum (single value) or EnumList (multiple values) type:

• Click the pencil icon to the left of the Industry column.

  

• On the column details form, specify the following, and leave the remaining settings as their defaults:

  Property	Value (type or select)
  Type	Enum
  Type Details Values	Accounting Finance Healthcare Retail Travel and Hospitality

• Click Add for each enum value to be added to the list of allowed values.

  

  Note:
  The Allow other values option enables users to enter any value they need in addition to the allowed values displayed in the drop-down list.
  The Auto-complete other values option makes it easier for the user to choose from the set of previously entered values and helps to ensure that all entries are submitted in the same way avoiding typos.
  By setting the Input mode, you can control whether the values are displayed as buttons arranged naturally or as a vertical stack, or as a drop-down set of radio buttons in the app.

1. After all the values are entered, click Done in the form.

2. Click Save to save the app configuration changes.

3. Preview the change in the live app preview:

• Select the companies table, and select any one of the companies from the list.

• Click the pencil icon to edit the company record.

  

• Scroll to select the Industry column using the drop-down. The enum values are displayed. Select any of the values.

  

• Click Save to save the data changes.

For more information, refer to the Column data types documentation.

Task 4. Regenerate your app's data structure

AppSheet reads column metadata from your data source to define the column structure of the app. For spreadsheets, AppSheet uses the column headers to derive this information. Every time you modify the columns in the spreadsheet, you need to regenerate the column structure within the app, or AppSheet won't know how to locate the columns to read and write data and your app will stop functioning.

In this task, you regenerate your app's data structure after adding a new column to the sheet on Google drive.

Add a new column to the contacts sheet

1. To expand the contacts table definition, in the AppSheet editor, in the left navigation bar, click Data () > contacts ().

2. Click View data source. AppSheet opens the contacts sheet from Google Drive in a separate browser tab.

3. Add a new column header to the contacts sheet in row 1, column F with a value of: Last Contacted.

   We will use this new column to store the last contacted date and time when the person was contacted.

   

4. Switch to the AppSheet editor in your browser and navigate to Data () > contacts ().

5. Click Regenerate schema ().

   

6. Click Regenerate.

   AppSheet regenerates the column structure for the contacts table, and resyncs the app in the live preview.

   AppSheet also infers the type of the new column which may not be the intended column type.

7. Using the Type drop-down list, change the type of the Last Contacted column to Date.

8. Because it is not mandatory for every contact to have a value for this column, scroll the column attributes to the right and verify that the Require? attribute is cleared. If not, then select it to clear the attribute.

9. To save your changes to the app configuration, click Save.

Preview the new column in the app

1. In the app live preview, select one of the contacts from the list.

2. To edit the contact, click the pencil icon.

3. To set the Last Contacted value for the contact, scroll to the bottom, and click the calendar icon.

   

   Note: In some browsers, the calendar widget may not open. If this occurs, enter the date manually in the edit box.

4. To save the data for this contact, in the app preview, click Save.

5. View the contacts sheet on Google drive to verify that the contact that was updated using the app reflects the updated value in the Last Contacted column.

Simulate column headers error

AppSheet automatically extracts structure from a spreadsheet so that your app can be generated. Errors with spreadsheet structure typically occur when AppSheet is unable to properly identify the column headers.

1. Simulate this condition in the contacts sheet by deleting the first header row from the sheet on Google Drive.

2. To expand the contacts table definition, in the AppSheet editor, navigate back to the Data () > contacts () tab.

3. Click Regenerate schema (), then Regenerate to confirm.

   AppSheet reports the error as shown below:

   

4. To resolve the error, undo the deletion of the header row in the sheet by clicking the undo arrow.

5. As in the earlier step, regenerate the data structure by clicking Regenerate schema (), then Regenerate to confirm.

   The error should now be resolved.

Click Check my progress to verify the objective. Regenerate your app's data structure

Task 5. Create relationships between tables

The person contacts in the contacts sheet are likely employed at the companies whose information is stored in the companies sheet.

This implies that there is a relationship between the two tables. AppSheet lets you define references between rows in related tables using a special column type called Ref.

In this task, you use the Ref column type to define the relationship between rows in the two tables.

Read the AppSheet documentation to learn more about the Ref column type.

Set up the reference column in the contacts sheet

1. In the contacts sheet on Google drive, add a new column header in row 1, column G with a value of: Company.

   This new column will be used to store the ID of the company that the contact is associated with.

   

2. Switch to the AppSheet editor in your browser and navigate to Data () > contacts ().

3. Click () Regenerate schema, then click Regenerate

   AppSheet regenerates the column structure of the contacts table and adds the new Company column to the list of columns.

4. Click the pencil icon to the left of the Company column to edit its properties.

5. On the column details form, specify the following, and leave the remaining settings as their defaults:

   Property	Value (type or select)
   Type	Ref
   Source table	companies

   This changes the type of the column to be a reference that refers to the companies table.

   

   When contacts are added or updated in the app to include the company that is associated with the contact, AppSheet automatically stores the Company ID in this column in the contacts sheet.

6. To save the changes to the column properties, click Done.

7. Finally, to save the changes and refresh the app, in the AppSheet editor, click Save.

Preview the Company column in the app

1. In the app live preview, select one of the contacts from the contacts list.

2. To edit the contact, click the pencil icon.

3. Scroll to the bottom and select a Company from the drop-down list of companies. AppSheet has automatically populated the list from the companies table.

4. Click Save in the app preview to save the data for this contact.

5. View the contacts sheet on Google drive to verify that the contact that was updated using the app, contains the ID of the company that was selected.

Click Check my progress to verify the objective. Create relationships between tables

Congratulations!

You have successfully configured data from multiple data sources for your app using AppSheet.

In this lab you learned how to:

• Use the AppSheet UI to configure an app using data from multiple data sources.
• Add a second data source to the app using a separate Google sheet.
• Configure the data types of the data from the tables that are used in the app.
• Create a relationship between the existing and new tables.
• Verify that the newly added and modified data is present in the underlying Google sheet.

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.

Manual Last Updated: December 1, 2022

Lab Last Tested: December 1, 2022

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.