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.
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
Open the link in another browser tab to copy the Customer Contacts app to your AppSheet account: Customer Contacts app.
Click Copy app from the left pane.
On the Copy App form, specify the following, and leave the remaining settings as their defaults:
App name
Customer Contacts
Click Copy app.
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
Check my progress
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:
Copy and paste the link below in a separate browser tab: A copy of companies.
Click Make a copy to make a copy of the spreadsheet in your Google drive folder.
At the top left corner of the sheet, click anywhere in the filename and change the name to companies.
Navigate to drive.google.com to confirm the file has been saved to your My Drive folder.
In the Customer Contacts - AppSheet UI, use the left navigation bar and go to Data > Contacts.
To add a second data source to your app, click Add new Data (+) next to
Data.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.
In the file picker, select the companies sheet that you uploaded in the previous step and click Select.
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.
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
Check my progress
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
In the AppSheet editor left navigation menu, select Data.
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.
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.
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.
- 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.
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
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.
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
Accounting
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.
After all the values are entered, click Done in the form.
Click SAVE to save the app configuration changes.
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
Open a browser tab and navigate to Google drive. If you already have it open then switch to that tab.
In Google drive, open the
appsheet/datafolder, by double-clicking appsheet then double-clicking data.Open the CustomerContacts-NNNNNNN folder.
Note: The actual folder name contains numeric digits represented by NNNNNNN.
Open the contacts sheet.
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.
Switch to the AppSheet editor in your browser and navigate to Data > contacts.
Click More(three dots) of the contacts tile and then select Regenerate Schema.
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.
Using the Type dropdown list, change the type of the Last Contacted column to DateTime.
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.
Click SAVE to save your app configuration changes.
Preview the new column in the app
In the app live preview, select one of the contacts from the list.
Click the pencil icon to edit the contact.
Scroll to the bottom and click the calendar icon to set the Last Contacted value for the contact.
Click Save in the app preview to save the data for this contact.
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
Check my progress
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
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.
Switch to the AppSheet editor in your browser and navigate to Data > Contacts.
Click More(three dots) of the contacts tile and then select Regenerate Schema.
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.
Click the pencil icon to the left of the Company ID column to edit its properties.
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.
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.
In the Expression Assistant form for Display Name expression for column Company ID (Text), type Company.
Click Save in the expression assistant form.
Click Done to save the changes to the column properties.
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
In the app live preview, select one of the contacts from the contacts list.
Click the pencil icon to edit the contact.
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.
Click Save in the app preview to save the data for this contact.
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
Solution of Lab
Download file: quicklab.xlsx
