AppSheet is a great way to create apps from your data. To get the best results, it helps to organize the data in an app-friendly way. Once you understand the basics, your data will be easier to manage and your apps will be more powerful.
Data is Made Up of Records
A record is an individual piece of data in your app. It might describe an individual customer, a specific job site, or one product. A record is stored as a single row in your spreadsheet.
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, 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, etc.
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 that 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.
Get Started with Your Data
What Are the Tables?
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 worksheet for each table. Name the worksheet 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 Check-Out Date, Return Date, Book Name, 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.
Now it's time to 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-Outs will each get an ID column as their primary keys, too.
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 books 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 books, 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 Book column instead of the old Book Name column. That way, when you look at a Check-Out row, it will have a link to the book. Similarly, you can give it a Patron column so it can reference rows in the Patrons table.
If you want to create a list of related rows, you’re in luck! AppSheet will automatically create a list of related rows.
This can be counter-intuitive at first: 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 Review rows when you look at a book in the app.
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:
Putting 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:
When you have data in your spreadsheet, make sure your column headers are bold so AppSheet will recognize them as headers.