Before you jump in with your data, consider these important table concepts. You'll also learn how to add tables in the editor.
Tables are comprised of columns and rows
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 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.
To add a table:
- Go to Data > Tables and click + New Table.
- When prompted, choose your data source.
- Navigate to and select the data to be added.
Once you've added your tables, you can:
- Edit the table name
- Set whether users can add data, update it, delete it, or just read it
- Expand the other sections to configure settings, such as storage, security, scale, localization, and documentation.
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
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.
You can repeat the steps described above to add another table.
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.
Or when you click + New table, AppSheet will provide suggestion 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.
- 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".