AppSheet enables you to create apps from your data as described in the following sections.
Get started: Prepare your Google Sheets for use with AppSheet
Watch how to prepare your Google Sheets for use with AppSheet.
See also: Best practices with Sheets and AppSheet
Preparing your Google Sheet for AppSheet
Prepare your data
To get the most out of AppSheet, it helps to organize your data in an app-friendly way. Once you understand the basic concepts described in this topic, your data will be easier to manage and your apps will be more powerful.
- Basic concepts
- How data changes
- What tables do you need?
- Be smart: Don't repeat
- Give each row a unique identity
- Avoid lists
- Relationships between records
- One-to-many relationships
- Many-to-many relationships
- Put data in your spreadsheet
Basic concepts
First, let's review the most basic concepts about data:
Data is made up of records
A record is stored as a single row in your spreadsheet. It is an individual piece of data in your app, and might describe an individual customer, specific job site, or single product.
What we’ll want to do is keep each type of record separate from the others. To do that, each type of record should have its own table.
Tables are collections of records
A table is a collection of records of the same type. You might have a Customers table (like below), a Job Sites table, or a Products table. Think of tables as simulating things in the real world: people, objects, locations, events, etc.
Columns are the attributes of records
The rows in each table share the same set of columns. These columns are the attributes that describe the records: fields like Name, Address, Date, ID, and so on.
Each row is going to have its own values for each column, but the overall column structure determines the parameters and requirements of the table as a whole.
Think of a table like a form. A form asks the same set of questions of everyone who fills it out, but everyone has different answers. If you were making a table out of this form, each question would be a column, and each person’s answers would be in their own row.
How data changes
The row data within the table is what changes. As you use your app, you might add a new Customer, change the address of an existing Customer, or remove an old Customer. In fact, almost everything an app does is done by adding, updating, and deleting rows. But the column structure remains the same.
What tables do you need?
The first thing to do is figure out what tables you need. You'll probably have several tables, since you'll have different questions of different types of data.
A good way to create tables is to create a single spreadsheet for the app, then add a sheet (tab) for each table. Name the sheet after the kind of records the table contains, such as Customers or Project Locations.
For instance, a library app that keeps track of books would probably need a table of Books and a table of Checkouts.
Next, figure out the columns for each table. What information is needed for each record? What questions need to be asked?
In this library app, the Book table will have columns for Title, Author, Publisher, and Copyright date. The Checkouts table will have columns for Checkout date, Return date, Title, Patron name, and patron Email.
Be smart: Don’t repeat!
If you have a set of columns whose values will be repeated across many rows, you might want to take those columns and put them in their own table.
These column sets usually describe a person or place related to the current row. But imagine you had to update that information. You’d have to change it in every single related row, possibly making mistakes along the way.
It would be much easier to keep that information in one place. Fortunately, AppSheet lets you reference rows from other rows, which we’ll get to in the next section.
Let's create a new table, Patrons, that will have the columns Name and Email. This way, you can update a patron’s information in one place instead of changing it in every Check Out row to which they're attached.
Give each row a unique identity
It’s important that each row can be uniquely identified. One of the columns should be a unique identifier, or primary key, that has a different value for each row.
Names and addresses stick out as obvious choices, but they aren’t always the best choice. Multiple people can have the same name, and addresses can change--that’s the reason you see seemingly random customer IDs and order numbers.
It’s not a bad idea to follow suit and give every table an ID column.
The Patrons table has a ID column as its primary key. That way, if I have two patrons named Alex Green, they won’t accidentally check out books on each other’s accounts. Books and Check Out will each get an ID column as their primary keys, too.
Avoid lists
Tables, like spreadsheets, can only have one value per cell. You can’t pack in a whole list of records. For example, you might want to have a Cats column, but since you can’t list multiple cats in a single spreadsheet cell, you’ll need a workaround.
At first, you might think a good solution is to use multiple columns: Cat 1, Cat 2, Cat 3, and so on. But then you have a fixed number of cats, and it can be hard to add new columns later.
A better solution is to create a new table. It may seem silly to create an entire table if all you want is a single piece of information in each row, but this option is much more flexible. You can connect rows in this new table to rows in other tables using reference columns.
Relationships between records
If you have multiple tables, you probably want to connect them in some way. A row in one table will have a reference to a row in another table, or you might want to have a list of related rows. These relationships are created using a special type of column called a reference, or Ref for short.
A Ref column takes a row’s primary key as its value. If you name the column something like Book, AppSheet will automatically figure out it should contain a key to some row in the Books table. Then in the app, you’ll be able to link directly from a row to its related row and back again.
The Check Out table should have a Title column so that when you look at a Check Out row, it will have a link to the book from the Books table. Similarly, you can give it a Patron column so it can reference rows in the Patrons table.
One-to-many relationships
If you want to create a list of related rows, you’re in luck! AppSheet will automatically create a list of related rows.
If you want a record in my Books table to have a list of reviews, you need to reference the book from each Review record. Then when you look at a book in your app, you’ll see a list of related reviews.
Next, add a Reviews table. It’s going to have columns for ID, Book, Reviewer, Star Rating, and Comments. You don't need to add anything to the Books table: you'll see links to related Reviewer rows when you look at a book in the app.
Many-to-many relationships
If you want two tables whose records each contains a list of records in the other table, you have hit upon a slightly more advanced topic. You can learn how to do this, and many other things besides, from a more general guide to data modeling or database design. The following aren't AppSheet-specific, but the same principles apply:
Put data in your spreadsheet
Now that you know how to organize your data, we have a few tips for how to set up your spreadsheet. See 5 Tips for Building Better Spreadsheets for your AppSheet Mobile Apps.
When you have data in your spreadsheet, make sure your column headers are bold so AppSheet will recognize them as headers.
Notice how those columns also have clear and specific names. This is much more helpful for you than naming your columns Column, Column1, and Column2.
Make sure that the data type format is consistent through each cell in the column. New data should be added as new rows. AppSheet can only interpret new data as a row; if you add it as a column, it won't work.
Bottom line: make sure that AppSheet reads a single table as your main source of information. Avoid using unstructured data, multi-format columns, or data outside of a table.
Manage your data in AppSheet
Manage your data in AppSheet, as follows:
- Add and edit data in your app, as described in:
- Add and edit data slices, as described in Slices: The Essentials
- Configure user settings
Watch the following video for a high-level overview of the AppSheet data model.