GSP910

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, and 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 yourself in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials that you use 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 are signed into Google Cloud Skills Boost 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.

   Note: If you use other credentials, you will get errors or incur charges.

4. Click Open AppSheet.

5. Click to sign in with Google.

   

6. In the Sign in with Google dialog, enter the Username provided for the lab and click Next.

   Note: If you see other accounts listed, click Use another account, enter the credentials provided for this lab, then click Next.

7. Enter the password provided for this lab and click Next.

8. Click I Understand to accept the terms.

9. On the AppSheet consent page, click Allow. This allows AppSheet to access the Google Drive folders associated with your Google Cloud Skills Boost account.

   

10. You are now signed in to AppSheet.

    

    Click on the X in the top right corner of the Tell us about you so we can make better recommendations dialog to view the AppSheet MyApps page.

    The MyApps page is empty since you do not yet have any apps.

Task 1. Copy the app

When working on your own or your company's app, you normally would incrementally build the app over a continuous project timeline.

In order to continue building the app in this lab environment, you must first copy the app that was previously built in the previous lab.

Copy the app to your AppSheet account

1. Open the link in another browser tab to copy the Customer Contacts app to your AppSheet account: Customer Contacts app.

2. Click Copy app from the left pane.

3. On the Copy App form, specify the following, and leave the remaining settings as their defaults:

   App name

   Customer Contacts

4. Click Copy app.

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

   You can also access the app from the My apps page in the AppSheet UI under Apps.

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 all of 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. Copy and paste the link below in a separate browser tab: A copy of companies.

2. Click Make a copy to make a copy of the spreadsheet in your Google drive folder.

3. At the top left corner of the sheet, click anywhere in the filename and change the name to companies.

   

4. Navigate to drive.google.com to confirm the file has been saved to your My Drive folder.

   

5. In the Customer Contacts - AppSheet UI, use the left navigation bar and go to Data > Contacts.

   

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

7. In Add data form, select Google Sheets.

If you get an error preventing you from adding new data, please refresh the page then try to Add data again.

8. In the file picker, select the companies sheet that you uploaded in the previous step and click Select.

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

   The settings in this dialog allow you to 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.

   Note: The AppSheet editor only recognizes one table per worksheet. If you have multiple tables in the same worksheet, you'll need to move tables to new tabs in the workbook or Google sheet.

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

10. Preview the data from the new table using the app live preview feature in AppSheet.

    

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 AppSheet editor left navigation menu, select Data.

2. To inspect the structure of the columns in the contacts table, click the contacts.

   

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. Click SAVE to save your column configuration changes.

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. In this step, you change the Industry column's type property to use the Enum (single value) or EnumList (multiple values) type.

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

   

2. 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

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

   Note: For information only
   The Allow other values option enable users to enter any value they wish in addition to the allowed values displayed in the dropdown 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 dropdown set of radiobuttons in the app.

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

5. Click SAVE to save the app configuration changes.

6. Preview the change in the live app preview:

   • Select the companies table, and click on any one of the companies from the list.
   • Click the pencil icon to edit the company record.
   • Scroll down to select the Industry column using the dropdown. The enum values are displayed.
   • Select any of the values and click Done.
   • Click Save to save the data changes.

Refer to the documentation for more information on AppSheet column types.

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. Open a browser tab and navigate to Google drive. If you already have it open then switch to that tab.

2. In Google drive, open the appsheet/data folder, by double-clicking appsheet then double-clicking data.

3. Open the CustomerContacts-NNNNNNN folder.

   Note: The actual folder name contains numeric digits represented by NNNNNNN.

4. Open the contacts sheet.

5. Add a new column header 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.

   

6. Switch to the AppSheet editor in your browser and navigate to Data > contacts.

7. Click More(three dots) of the contacts tile and then select Regenerate Schema.

8. Click Regenerate to confirm.

   AppSheet regenerates the column structure for the contacts table, and re-syncs the app in the live preview.

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

9. Using the Type dropdown list, change the type of the Last Contacted column to DateTime.

10. Since it is not mandatory for every contact to have a value for this column, scroll the column properties to the right and verify that the Require? property is unchecked. If checked, then select it to uncheck the property.

11. Click SAVE to save your app configuration changes.

Preview the new column in the app

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

2. Click the pencil icon to edit the contact.

3. Scroll to the bottom and click the calendar icon to set the Last Contacted value for the contact.

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 reflects the updated value in the Last Contacted column.

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 allows you to 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 ID.

   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 More(three dots) of the contacts tile and then select Regenerate Schema.

4. then Click Regenerate to confirm. AppSheet regenerates the column structure of the contacts table and adds the new Company ID column to the list of columns.

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

6. 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.

7. Since the value displayed in the app for this column is the actual company name, you should change the display name of the column. To do this, scroll down in the same form and expand the Display section. Then, click the expression assistant icon in the Display name field.

8. In the Expression Assistant form for Display Name expression for column Company ID (Text), type Company.

9. Click Save in the expression assistant form.

10. Click Done to save the changes to the column properties.

11. Finally, click Save in the AppSheet editor to save the changes and refresh the app in the live preview.

Preview the Company column in the app

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

2. Click the pencil icon to edit the contact.

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.

Finish your quest

This self-paced lab is part of the Building No-Code AppSheet Apps quest.

A quest is a series of related labs that form a learning path. Completing this quest earns you a badge to recognize your achievement. You can make your badge or badges public and link to them in your online resume or social media account. Enroll in Building No-Code AppSheet Apps quest or any quest that contains this lab and get immediate completion credit. See the Google Cloud Skills Boost catalog to see all available quests.

Take your next lab

Continue your quest with Implement the User Experience for your AppSheet App.

Next steps / Learn more

Google Workspace Learning Center

Google Cloud training and certification

...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.

Manual Last Updated: August 23, 2023

Lab Last Tested: May 12, 2023

Copyright 2024 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.