Tables: The Essentials

Before you jump in with your data, consider these important table concepts. You'll also learn how to add tables in the editor.

• About tables
• Add a table
• Add multiple tables
• Explore the Table panel
• Configure table settings
• Preview data in a table
• Delete a table

About tables

The following sections introduce basic concepts about tables:

• Tables are comprised of columns and rows
• Each table must have a key
• You can add multiple tables to your app
• Choose data from any source
• Tips for tables

Tables are comprised of columns and rows

Columns

Tables are comprised of columns, which identify the different components of each record. A record is a row of data in your spreadsheet. For instance, a table of addresses would have a column each for street number, street name, city, state, and zip code.

AppSheet reads each column header to define the column structure of the app. Every time you change 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. Column headers are essential to your app: you need column headers for each column in which you want to store data.

AppSheet reads the first few rows of your spreadsheet to locate your column headers. Normally your column headers should be the first row of your spreadsheet, but AppSheet is capable of finding your headers if they're anywhere near the top of your spreadsheet. 

Before you add a new table, it's a good idea to open your worksheet and make the header row bold, which helps AppSheet locate it.

Rows

Rows contain the data for each individual piece. For the address example, each address would have its own row. Every time you add new data, a new row will be added to the spreadsheet. 

Each table must have a key

Keys uniquely distinguish each row from other rows using a particular column.

The key may be a single column (such as Employee ID) or of two or more columns (such as FirstName and LastName). Each row in the table must have a key value that uniquely identifies it. In other words, no two rows in a table may have identical key values. This is critical, because keys allow AppSheet to reliably find the right table row.

When a user on a mobile device changes data through an AppSheet app, they're making changes to a local copy of the data temporarily stored on the device. When the user syncs these changes to the server, AppSheet sends the updated data to the server, finds the updated row using its unique key value, and applies those changes to that row. AppSheet can only find the right row because each row has a unique key value.

There are several ways keys can be determined: they can be natural, or calculated depending on the structure of the data and the access permissions to the table.

You can add multiple tables to your app

Tables can be different worksheets from the same file, or you can also choose tables from different data sources altogether: other workbooks or even databases. To learn more about different data sources, see Use multiple data sources.

The editor only recognizes one table per worksheet. If you have multiple tables in the same worksheet--a somewhat common pattern in Microsoft Excel and Google Sheets--you'll need to move tables to new tabs until you have only one table per worksheet.

Choose data from any source

The worksheets you use for your tables can exist together in the same workbook, or they can be from different sources. You can learn more about using multiple worksheets in How to use multiple sheets in your app.

Tips for tables

• AppSheet uniquely identifies a row by the value in one of its columns. That's where the concept of keys becomes essential in an AppSheet app.
• Using keys as a way to locate rows in the spreadsheet also allows for multiple users adding data at the same time. AppSheet will serialize the data going back to the spreadsheet from the app, letting the last person to write in a cell "win".

• One consideration to take into account when configuring your table's settings is whether it should be a private table - maybe you want certain tables to be accessed only by certain individuals in your organization. Disable the Shared? setting to make your table private.

  

Add a table

To add a table:

1. Open the app in the editor.
2. Go to Data .
   The Data panel opens.
3. Click + in the top header of the Data panel.
   We've made some improvements to the app editor, currently in preview.
   You are opted in to the new editor by default, but you can switch back to the legacy editor at any time.

   If you are using the legacy navigation

   Go to Data > Tables and click + New Table.  
4. When prompted, choose your data source.
5. Navigate to and select the data to be added.

The table is opened in the editor. See Explore the Table panel.

Add multiple tables

You can repeat the steps described in Add a table to add multiple tables.

If you're already using a table source that has multiple worksheets or tabs, AppSheet will suggest some unused worksheets from which to create a new table and additional suggestions in the dialog.

When adding a suggested table, you select a different table source in the drop-down menu, then choose an unused worksheet to create a new table. If you want to add an entirely new table source--a new spreadsheet, for example--just select Browse for new source from the table source drop-down menu. AppSheet will ask you to select a new file; if you have multiple data sources, it will prompt you to select a new data source. You may select the same spreadsheet, or a different one from a different data source.

When you add a new table--or when you're editing an existing table--you can choose how data is accessed in the app. You can allow people to Add, Update, and Delete rows, or any combination of the three.

Explore the Table panel

Go to Data   and select a table in the list.

The Table panel is displayed, as shown:

As highlighted in the previous figure, you can use the Table panel to:

• Edit columns in the table, as described in View and edit columns
• View the data source (in a new tab)
• Configure the table settings
• Regenerate the table schema, as described in Add, remove, or rearrange columns
• Add a virtual column to the table, as described in Use virtual columns
• Manage the table, including:
  • Preview the data in a table
  • Delete the table
• Expand or scroll the table to display more settings

We've made some improvements to the app editor, currently in preview.
You are opted in to the new editor by default, but you can switch back to the legacy editor at any time.

If you are using the legacy editor

In the title bar, you can click:

• View Columns to view the table columns (open the Data > Columns tab)
• View Source to open your spreadsheet in another tab.
• View Data to open data in the table in another tab - not the original source, but the same information
• Delete to remove the table from the editor

Configure table settings

To configure a table:

1. Open the app in the app editor.
2. Go to Data , select the table you want to configure in the list, and click Table settings  in the table header.
   We've made some improvements to the app editor, currently in preview.
   You are opted in to the new editor by default, but you can switch back to the legacy editor at any time.

   If you are using the legacy editor

   Go to Data > Tables and expand the table that you want to configure.

3. Configure the table settings.

   Setting	Description
   Table name	Name of the table.
   Are updates allowed?	Control whether app users can add, delete, or edit rows in a table. You can specify combinations of these options for each table. See Control, add, update, and delete operations.
   Source path	Path to the data source for the table.
   Worksheet Name/Qualifier	Additional qualifier that is used to identify the data source for the table, such as. the name of the worksheet in a spreadsheet.
   Data source	Name of the data source for the table. Optionally, you can copy the data to another data source: Click Copy Data to New Source. Select the data source from the Save data to drop-down. To include file data, such as images, turn on Include file data. Click Copy data. The data is copied to the new data source. A confirmation dialog is displayed that provides the source path.
   Source Id	Provider-specific path to the table data. This information is not visible to the app user.
   Store for image and file capture	Storage location where images and files that are captured by the app are stored. By default, they are stored in the cloud file system associated with the app owner's account. Alternatively, you can use an object store.  For more information, see
   Filter out all existing rows?	Turn on to capture new data (that is, allow adds) in forms but do not show any existing data.
   Security filter	Define a security filter condition to restrict the rows that are displayed in the table. See also Scale using security filters.
   Access mode	Select the credentials to use when accessing the spreadsheet. Valid values are as app creator or as app user.
   Shared?	Turn on to share the table with all app users. Before turning off this setting, see Private tables.
   Partitioned across many files/sources?	Partition (or divide) the rows of the table into multiple table sources. See Scale using data partitions.
   Partitioned across many worksheets?	Partition (or divide) the rows of the table across multiple worksheets. See Scale using data partitions.
   Data locale	Locale used to read the table. For guidance on setting this value, see Locale support in AppSheet.
   Descriptive comments	Description of the table.

    

4. Save the app by selecting one of the following:
   • Save - Save the app
   • Save & verify data - Save the app and verify the data defined.

Preview the data in a table

To preview the data in a table:

1. Open the app in the app editor.
2. Go to Data  .
3. Select the table in the list that you want to preview.
4. Select More > Preview data in the table header.

A separate tab is opened with a preview of the data. Select the number of rows you'd like to show in the drop-down.

We've made some improvements to the app editor, currently in preview.
You are opted in to the new editor by default, but you can switch back to the legacy editor at any time.

If you are using the legacy editor

To preview the data in a table:

1. Open the app in the app editor.
2. Go to Data > Tables.
3. Expand the table that you want to preview.
4. Click View Data in the table header.

A separate tab is opened with a preview of the data. Select the number of rows you'd like to show in the drop-down.

Delete a table

To delete a table:

1. Open the app in the app editor.
2. Go to Data  .
3. Do one of the following:
   1. Position your cursor over the data in the Data panel and select More > Delete.
   2. Select a table in the list and select More > Delete in the table header.
4. Save the app by selecting one of the following:
   • Save - Save the app
   • Save & verify data - Save the app and verify the data defined.

We've made some improvements to the app editor, currently in preview.
You are opted in to the new editor by default, but you can switch back to the legacy editor at any time.

If you are using the legacy editor

To delete a table:

1. Open the app in the app editor.
2. Go to Data > Tables.
3. Expand the table that you want to delete.
4. Click Delete in the table header.
5. Save the app by selecting one of the following:
   • Save - Save the app
   • Save & verify data - Save the app and verify the data defined.