Leveraging Data

Hansel Miranda
Technical Curriculum Developer
Google Cloud

Welcome to the module on leveraging data in your app with AppSheet.

 01 Using data sources

02 Defining the data structure in your app

03 Using more than one table in your app

Agenda
04 Demo

05 Lab

06 Review

When you are designing an app, one of the first tasks is to determine what data
sources are needed for your app to work.

AppSheet supports many different types of data sources, including spreadsheets and
databases.

In this module, you will learn how to use, connect, and configure data sources and
also define the data structure in your app. We will also demonstrate some of these
AppSheet capabilities.

You will complete a lab to configure data for your app using AppSheet.

Finally, you will learn how to use more than one table in your app.
 01 Using data sources

02 Defining the data structure in your app

03 Using more than one table in your app

Agenda
04 Demo

05 Lab

06 Review

Let’s first discuss what data sources you can use and how you can connect and
configure data sources for your app.
Types of data sources

Before we start, it is important to understand the different types of data sources that
you can use in AppSheet.
 01

Spreadsheets

● Office 365
● Excel
● Google Sheets

AppSheet supports various types of data sources.

They can be grouped into 3 categories:

● Spreadsheets:

○ Office 365, Excel, Google Sheets

 01 02

Spreadsheets Databases

● Office 365 ● MySQL, PostgreSQL,

● Excel MariaDB
● Google Sheets ● Microsoft SQL Server
● Oracle
● BigQuery
● On-premises database
● AWS DynamoDB
● Redshift

● Databases:

○ MySQL, PostgreSQL, MariaDB

○ Microsoft SQL Server
○ Oracle
○ BigQuery
○ On-premises database
○ AWS DynamoDB
○ Redshift
 01 02 03

Other data or
Spreadsheets Databases
provider services

● Office 365 ● MySQL, PostgreSQL, ● Google Drive

● Excel MariaDB ● Google Calendar
● Google Sheets ● Microsoft SQL Server ● Apigee
● Oracle ● Smartsheet
● BigQuery ● Dropbox
● On-premises database ● Box
● AWS DynamoDB ● OData
● Redshift ● Salesforce

● Other data or provider services:

○ Google Drive
○ Google Calendar
○ Apigee
○ Smartsheet
○ Dropbox
○ Box
○ OData
○ Salesforce
Connecting your app
to your data source

After you create your data source, you need to connect your app to it.
 3 ways to connect AppSheet to your data:

‘My Account’ > New Data Source

‘My Apps’ > Make a new app

Google Sheets > Extensions> AppSheet > Create an app

As we discussed in the previous module, you can connect AppSheet to your data in
one of three ways:

1. You can link a new data source from the ‘My account’ section in the AppSheet
UI.

2. Or from the My Apps page by making an app.

3. If you are using Google Sheets, you can connect your data to AppSheet
directly from your Sheet by clicking Extensions > AppSheet > Create an app.
 3 ways to connect AppSheet to your data:

‘My Account’ > New Data Source

‘My Apps’ > Make a new app

Google Sheets > Extensions> AppSheet > Create an app

Let’s look at how you can add a new data source from the My Account tab.
To connect your app to a data source, use the My account tab in the AppSheet UI.
From there, click New Data Source and select the type of data source to add to your
app.
Note that data from multiple sources (for example: Google Drive and Dropbox) can be
used together in the same app.

All data sources in your account are available when you add tables to your AppSheet
app.

You can use this section to configure additional data sources for your account.
Configuring your app
to use the data source

After you have added and connected your app to your data source, you then need to
configure your app to tell it how to use that data source.
To do this, navigate to the AppSheet editor’s Data tab and click New Table.
A table in AppSheet is a single dataset. For example, it can be a single spreadsheet
or a worksheet that is part of a workbook containing many worksheets.

The Get data from dialog displays the data sources that were added and are available
for your app.
It also lets you search for additional documents available in your provider account and
add additional provider accounts that may contain other data sources.
After selecting the location of your data source, for example, Google Drive, you then
select the actual spreadsheet from your drive folder to use with your app.
AppSheet also allows you to decide whether the app user should be able to add rows
to a table, update or delete existing rows, or use a table as read-only via the app.

For example, for an app that allows you to manage personal or professional contacts,
you may allow the user to add new contacts, delete old contacts, or update the details
of existing contacts.

After the table is added to your app, you can then configure the column structure and
data validation and set other column types and properties for use in the app.
 01 Using data sources

02 Defining the data structure in your app

03 Using more than one table in your app

Agenda
04 Demo

05 Lab

06 Review

After you have created, connected, and configured your app to use your data source,
you then need to define the data structure in your app.
AppSheet automatically infers your table column structure, including the name, type,
and other column attributes for use by the app.

You should review the types and other settings of the columns that have been
automatically set and change them if required based on your app’s requirements.

From the Columns tab in the Data section of the navigation bar, click the table name
to see a list of columns that make up the table.
Here, you can change the settings of the column properties.

● Name: This property is automatically populated from the name given to the
column in your spreadsheet or table. It should match the column name in the
spreadsheet or table.

● Type: This property determines the role of the column in the app and is based
on the data that is stored in the column. You should change the default value
of the column type to match the use of the column’s data in the app.
There are 35 different data types (and we expect this to grow). The choice of data
type affects the behavior of this column in the app.

For example, you might want a particular text column to be of type "LONGTEXT", …
… or a particular image column to be a "THUMBNAIL".
 Date or DateTime

Image

Commonly
Enum or EnumList
used data types
Address

Email

Some commonly used data types are:

● Date or DateTime, which indicates that the value in the column is a date or a
date and time.

● Image, which indicates that the value in the column is an image.

● Enum, which allows the app user to select one value from the list of allowed
values.
EnumList, which allows the app user to select zero or multiple values from the
list of allowed values.

● Address, which is used to specify a postal or street address. Values of type

address can be plotted on a map in the app.
Using a complete address that includes the street, city, state, country and
postal code values helps ensure that AppSheet geo-codes it correctly.

● Email, which indicates that the value in the column is an email address.
 [Link]/en/articles/
1013271-column-types-diving-deeper

Details on the various types of columns that can be used in AppSheet are
documented at the link provided:

[Link]
In addition to Name and Type, you can also set the following column properties:

● Key: You must have a key column that uniquely identifies a row in the table.
Its value must remain constant.

● Label: The label property enables you to choose the most important columns
in a table and indicates whether the column name is displayed in the app. One
image column and one non-image column can have this setting.

● Formula: The formula property enables you to provide a calculation to be

applied to the column data whenever the user changes a value in a form.

● Show: The show property allows you to display or hide the column data in the
app.

● Editable: The editable property lets you enable or disable the user from
editing a column’s value in the app.

● Require: The require property enforces a rule that the column must have valid
data in all rows of the table.
You can also set additional column properties in the AppSheet editor:

● Initial Value: To provide an initial value for new rows added to the table, set
the Initial Value property.

● Display Name: To display the column header in the app, set the Display
Name property.

● Description: To provide a description of the column’s purpose or role, set the

Description property.

● Search?: To enable this column for indexing in app search results, set the
Search property.

● Scan?: To enable barcode scanning in the app using the user’s device
camera, set the Scan property.

● NFC?: To enable near field communication scanning of NFC tags using the
user’s device, set the NFC property.

● PII?: To prevent "Personally Identifiable Information" or "PII" data from being

retained in system logs, set the PII property for the column that stores
personal or sensitive data.
The Data section can initially feel a little overwhelming because many powerful
features are available in it, but don’t worry; as you create your views, you’ll be able to
adjust it as needed. Also, AppSheet provides a lot of resources and documentation to
help you along the way.
Virtual columns

Occasionally, AppSheet will add virtual columns automatically to your table structure.
Let’s see what these are:
 __RowNumber: computes a unique number for each row.

__ComputedName: concatenates a First Name and Last Name column.

Virtual columns are automatically added when a table is first added to the app, or
when the table's structure is regenerated.

A virtual column is a column that doesn't correspond to an actual column in the

underlying spreadsheet or table. Instead, it is a column whose value is automatically
computed via an app formula expression. Their values are not actually stored
anywhere, so they won't show up in your spreadsheet. They do, however, affect your
app’s functionality, behaving much like an ordinary column.

These columns are usually added as a convenience based on common use.

Examples of automatically added virtual columns are:

● _RowNumber, which computes a unique number for each row.

● _ComputedName, which concatenates the values in a First Name and Last

Name column.
 CONCATENATE([First Name], ", ", [Last Name])

A common use case for virtual columns is to combine other columns in the app.

● For example, you can combine First Name and Last Name columns to
construct a virtual Full Name column for display in the app.

● To implement this, use the app formula: CONCATENATE([First Name], ", ",
[Last Name]).

● You can also use virtual columns to construct complex conditions using app
formulas that operate on real columns in your table.
Images and videos

Let's review how you can display images or videos in your app.
To display images in your app, set the data type of your columns that contain the
image or thumbnail to the Image or Thumbnail column type.

You can do this from the Columns tab under the Data section of the navigation menu
in the AppSheet editor.
To display videos, set the column type as Video.

The values in your table for these columns can contain the name of a file that
contains the image or video, or a publicly accessible URL to the image or video.
Storing images

Let’s discuss how images are stored and used by apps in AppSheet.
As previously mentioned, to use images in your app, you use the Image column type
in your data source.

The actual value that is stored in a column of this type is either a URL or a filename or
path to the location of the image.

If you store the value as a URL, make sure the URL is publicly accessible. In the
example shown, the photo column of the sheet contains the URL to an image for
some rows in the table.
If you store the value as a file name, the image must be stored in your cloud file
system, not on your own computer.

If you specify an image file name in your spreadsheet, the file should be in the same
folder location as your spreadsheet.

For example, if you use Google Drive and your spreadsheet is in the
/appsheet/data/CustomerContacts folder, and if you have the image [Link] in
the same folder, …
you can just use the value, [Link], in the appropriate column in the
spreadsheet.
It is sometimes easier to organize images in their own folder. For that reason, image
file names can be specified relative to the location of the spreadsheet.

For example, if your image files are in a subfolder called Images, …

the cell value in the image column of your spreadsheet must contain the relative path
to the image file that includes the name of the folder, in this case, Images, followed by
/ (slash) and the image filename.
Regenerating the
column structure

When changes are made to the column structure of the underlying data source, you
must regenerate the column structure for your app.

Let’s review how this is done in the AppSheet editor.

As you work with your table in the editor, you may need to make changes to your
underlying table structure. You may decide to remove a column, add a column, or
rearrange your columns.

With these changes made to the table structure, you may see an error in the app
when it syncs with the data source.
To fix this issue, you have to regenerate the column structure for your app.
From the Columns tab under the Data section of the navigation menu, select the table
whose underlying data structure was modified.

Then, click Regenerate Structure. After you confirm, the new column structure is
loaded in the Editor and the app emulator syncs with this new structure to resolve the
error.
 01 Using data sources

02 Defining the data structure in your app

03 Using more than one table in your app

Agenda
04 Demo

05 Lab

06 Review

As we’ve discussed earlier, an app can use more than one table.
To add another table to your app, simply click on New Table in the Tables tab under
the Data section of the navigation menu.
You then configure the table columns as described in the previous lesson.

Note that you'll need to modify the default view or create a new UX view pointing to
the new table to get that table’s data to display in the app.
References
between tables

Let’s now learn about references between tables, when to create them, and how to
use them to add more meaningful functionality to your app.
 App

Table 1
Reference
Table 2

Table 3

Table 4

Table 5

When an app uses multiple tables, it's often useful to create connections, known as
references, between the app's tables.
 Order capture app

Order
Reference
Order details Reference

Customers

For example, an order capture app might contain the following tables:

1. Orders, with one row per order.

2. Order Details, with one row per line item in the order.

3. Customers, with one row per customer that contains details of the
customer.

Each Orders row will reference a row in the Customers table of the customer who
placed that order.
Each Order Details row will reference its parent Orders row.
 References can be used to:

Represent relationships

Easily retrieve information from a related row in another table

Navigate from one row to another

Create dynamic dropdowns

Show related lists of items

References serve multiple purposes:

● They allow you to represent relationships.

For example, the reference between the Orders row and the Customers row
allows you to capture the relationship between an order and the customer who
placed that order.

● They allow you to easily retrieve information from a related row in

another table.
In the same example, the reference between the Orders row and the
Customers row allows you to start from an individual Order and retrieve
information about the customer, like their shipping address, from the related
Customers row.

● They allow you to navigate from one row to another.

For example, you might retrieve a particular Order Detail row then navigate to
the related Orders row, and then to the related Customer row.

● They allow you to create dynamic dropdowns.

When you create a form to add or update data in a table that uses a reference
column to another table, the form can contain a dropdown that enables the
app user to select a value from the referenced table. The dropdown is
dynamically populated from the referenced table column.
● They allow you to show related lists of items.
Referenced columns can be used to display the related list of items that are
present in the other table.
For example, with the Orders table referencing the Customers table, a row
from the Customers table whose data is displayed in the app can also display
the list of related orders from the Orders table that have been placed by this
customer.
Adding a reference

To create a reference, you add a column of type Ref to a table and specify the name
of the source or related table that this column refers to.
For example, in the Orders table you would add a column of type Ref that referred to
the customer Name column in the Customers table.
A Ref column always stores the key column value of the referenced row in the other
table.
For example, if the key column value of a Customers row is Ann Adams, the Ref field
in the related row in the Orders table will contain the value Ann Adams.

A table's key column value uniquely identifies each row in that table.

The copy of the key column value in the Ref column allows the system to
unambiguously retrieve the correct row in the referenced table.
System-added
references

AppSheet automatically infers and adds references when you use related tables in
your app or regenerate a table that is used by your app.
For example, if you have a Customers table with a Name column as its key, …
… and if the Orders table has a column called Customer Name, the Customer Name
column in the Orders table is assumed to be a Ref column, that refers to the Name
column in the Customers table.
Reverse references

Whenever a Ref column is created either by the system or by you, the app creator,
AppSheet automatically creates a "reverse reference" virtual list column in the
referenced table.
For example, when you add the Ref column to the Orders table that refers to the
Customers table, the system automatically adds a reverse reference column to the
Customers table that references the customer’s orders in the Orders table.

Note that this reverse reference column is virtual and will not exist in the underlying
sheet or table.
 A reverse reference enables the app to:

Navigate from one row of a table to all of its related rows.

Easily display a row of a table along with all of its related rows in
another table.

Compute aggregates.

A reverse reference enables the app to:

● Navigate from one row of a table to all of its related rows.

● Easily display a row of a table along with all of its related rows in another table.

● Compute aggregates like the count of a customer's orders or the total dollar
value of a customer's orders.
The system-added reverse reference column is a list of the key column values of the
related rows.

● For example, the reverse reference column in the Customers table for
customer Ann Adams would contain a list of the order IDs that exist in the
related Orders table for this customer.
 01 Using data sources

02 Defining the data structure in your app

03 Using more than one table in your app

Agenda
04 Demo

05 Lab

06 Review

Let’s practice what we learned in this module by doing a lab.

Lab
Configuring Data for your App Using AppSheet

The goal of this lab is to connect and configure data from multiple data sources for
your app by 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.
 01 Using data sources

02 Defining the data structure in your app

03 Using more than one table in your app

Agenda
04 Demo

05 Lab

06 Review

When you’re designing an app, determining what data sources are needed is one of
the most important things to consider.
In this module, you learned to:

01 Use different types of data sources.

02 Connect and configure data sources.

03 Define the column data structure in your app.

Use more than one table in your app, and create

04 references between tables.

In this module, we reviewed the different types of AppSheet-supported data sources

that can be used in your app.

You learned how to connect and configure the data sources used in your app, and
how to define the column data structure.

You also learned how to use more than one table in your app and how to create
references between those tables to add functionality to your app.