This article assumes you are familiar with how the AppSheet platform works.
Most of our customers already have an app that they want to build. They have two questions:
1: Can I build this app with AppSheet? --- the answer is usually "yes"
2: How should I build this app with AppSheet? -- this article describes the conceptual design of your app.
For example, here is a request recently posted on our community forum. We will use this as an example in this article.
"I have a construction business. I am building a set of apps to have full control of what is going on on our job sites. One part I need to followup is a daily production for every employee. I built a spreadsheet (google sheet is my data source) where I have entered all data previously referring to a specific task, in this case is water coating :
Columns = Areas to coat (ex.: Column A = Tower A, Column B, Apartment 301, Column C = subdivisions inside the apartment for example master suite, or other bathrooms, the rest of the columns filled with the service that we provide, such as 100 = coating a bathroom floor, 101 = coating the shower walls, and so on...)
Now I have populated this table with existing data, and my goal is to create an app that the supervisor at the job site is able to "check Box" once verified that the task is complete. this will then add up the coating applied every day by each employee and I will track how much is applied in every job site.
Anyone have an idea how to build this on appsheet?"
Of course, your app will have different requirements from this example, but you will still follow the same four steps:
- Define the data
- Define the UX
- Refine the app behavior
- Review the app security
Please note that you do not need to do all of this before you have the app running. Instead, you will have an app running almost immediately, and you will gradually improve it as you enhance these four aspects of the app definition.
We always advise customers to add the simplest initial version of steps 1, 2, and 3 and get the app working, then iteratively refine these steps. Finally, when the app is ready to share with others, focus on step 4 (security).
Step 1: Define the Data
AppSheet apps are all about the design of the data. You will need to think about entities and their properties.
1.a: Conceptual model: data entities
The conceptual model is how you think about the data that your app represents.
You should think of your data as representing a set of things (products, people, bills, orders, etc) in the real world. In the case of our example app, these things might be Customers, Employees, Buildings, and Jobs. We call these entities. The example app has four entities and many instances of each entity. Your app will have other entities depending on the problem you are addressing.
Each entity has a set of standard properties. For example, each Customer may have a name, address, phone number and email address. All the instances of an entity will have the same properties, though of course, their values will be different.
Every entity must have a property that helps to uniquely identify an instance of the entity. For example, an Employee may have an id number property or a Customer may have a phone number property. We call this the key for the entity.
Finally, entities may be related to each other. In this case, each Job is for one Customer, in one Building, and assigned to one Employee. In terms of data modeling, the Job entity references the Customer entity, the Building entity and the Employee entity.
1.b: Logical model: from entities to tables
The logical model is how you represent the data in your app. Once you know the entities and their properties, it is straightforward to model them in your app.
- Each entity is represented by a Table and each property of the entity is a Column of that table.
- Each of the columns has an explicit column type (eg: Number, Text, etc) to specify the nature of the values allowed in that column.
- Each table has a column that is identified as the key column.
- If one table references another, it should have a column of type Ref which references the other table.
- A table has many rows or records. Each represents one entity instance. In our example, the Customers table will have one row for each customer.
To summarize, the logical data model of the app is a set of Tables, each of which has a set of Columns (aka the Column Structure). There will be one Table in your app for each entity in your conceptual data model, and each table will have one column for each entity property. The Column Structure tab of your app editor shows you the tables, their structure, and their relationships.
1.c: Physical model: from tables to spreadsheets
The physical model tells you where the data is actually stored. In the most common case for AppSheet apps, the data is physically located in spreadsheets. The physical model is usually a straightforward 1:1 mapping from the logical model.
- There is a spreadsheet (or a worksheet) for each table
- There is a spreadsheet column for each table column.
- There is a header row that has the column names
- Each row in the spreadsheet corresponds to one table row.
1.d: Constructing an app: physical data -> logical data
When you construct an app, you start by creating the physical spreadsheets and add them to the app definition.
- Create a spreadsheet (or a worksheet) for each entity
- Add a column for every property and give it a meaningful name in the header
- Make sure you have added the key column -- usually, it is the first column and has a name like 'Job ID'
- If the entity is supposed to reference another entity B (eg: Job should reference Employee), make sure there is a column that is used to represent the reference (eg: a column called Employee Id).
- If you don't already have actual data to populate the sheet, put in some sample rows
- In your AppSheet app, add each sheet as a separate Table
Note that you will use the spreadsheet as the initial design tool for your data model. Your choice of spreadsheets and columns is directly driven by the conceptual data model for your app. You can then continue to refine the data model using the AppSheet app editor.
1.e: Refining the table definition
There are many rich options to refine the table definitions you just added. Here are three important options to be aware of:
- For each table, you get to decide what its update mode will be. Is it Read-Only? Can new rows be Added? Can existing rows be Updated or Deleted? You can change these in the app editor's Data -> Tables pane. In our example, let us assume that Jobs can be Added and Updated
- For each column, you get to decide if it is hidden, if it is read-only, and a number of other behaviors. You can change these in the app editor's Data -> Column Structure pane. In our example, let us assume that all the properties are visible.
- You can add "virtual" columns. These are columns that appear in your app definition but do not actually exist in the spreadsheet. Instead, they are computed using a formula. For example, you might want to add a virtual column to the Jobs table that computes the amount of coating material used based on the square footage and the number of coats.
1.f: Working incrementally
You do not need to get this all perfectly right at the beginning. Always start by adding just one table and get an initial app working. Then you can make additions or changes. It is common for app creators to make hundreds of changes as they develop their app.
Every time you add or change columns in the sheet, you can go to the app's Column Structure tab and ask the system to "Regenerate" the structure. In other words, you are telling the system to pick up the latest structure from the sheet.
You can learn a lot more about modeling data in this article and the more detailed articles that it links to.
Step 2: Define the UX
The UX is the user experience of your app. The UX definition describes what is shown to the app user as well as how it is shown (aka the user interface).
2.a: The UX model
The UX model describes how the end-user of the app understands what the app shows and allows them to do. This is based on the conceptual data model of the data. The UX is composed of views over the data you just defined, and transitions between the views when the user clicks/taps on things shown in the view.
In each view, the user sees one or more entity instances (eg: Customers or Jobs) and their properties. If appropriate, the user can add or delete entity instances, or modify their properties.
There are two kinds of views --- Display views and Form views. As the name suggests, Display views are used to show data whereas Form views are used to capture or update data.
The UX definition controls when views are shown, what they show, and how the user launches and transitions between views.
2.a: Launching views from menus
A "menu" is just a place from which the app user can find and launch views. There are two standard locations for such menus in an AppSheet app.
- The main menu is at the bottom of the app and can hold a small number of view launch buttons. This typically holds buttons to launch the most important views.
- There is also an overflow menu at the top left of the app.
In our example, let's assume that we need only two views and both are shown in the main menu at the bottom of the app.
2.b: Using Display views
A display view shows the contents of a table (i.e. a set of entities/rows). The most common display view type is a Tabular view -- as the name suggests, it shows data in a tabular format.
There are other views that you may prefer -- a Deck view or a Gallery view are suitable when you have an Image column in your data. A Map view is suitable when you have an Address or LatLong column in your data. If your data is numeric, you may want to consider a Chart view.
Most apps have at least one display view in the main menu. For now, let us assume this is a Tabular view over the Jobs table.
2.c: Transitions between views
Interactions with the contents of one view can cause another view to be shown. For example, tapping on one of the entities in a Tabular view transitions the app to a detailed (aka "Slideshow") view of the specific entity. Individual property/column values may also show tappable icons that make natural transitions. For example, a phone number value may have a phone call icon, and tapping on it starts a phone call.
2.b: Understanding Form views
Form views are used to capture data for a new records of a table, or to edit an existing record of a table. Because the table allows new rows to be added, a big Plus action button is shown as part of the Display view of the table. Tapping on this button opens a Form view that allows the user to create a new Job record.
You do not need to explicitly create a Form view. AppSheet automatically creates a Form view for any table that allows Adds or Updates.
Likewise, when viewing a specific entity/row, and if the table allows updates to existing entities/rows, a big Edit action button is shown as part of the view. Tapping on this button opens a Form view that allows the user to edit that entity.
Form views have a Save button and a Cancel button. Some form views span multiple pages and these will also have Next/Previous buttons to navigate acros the pages.
It is common to explicitly add a Form view in the main menu if it is a common activity for app users to add a new entity. In our example, let us also add a Form view for the Jobs table.
2.d: Other UX choices
Each view definition has many options and choices that control their presentation (UI) and interaction (UX).
You can also assign the app your own logo, choose a color theme, define formatting rules, and refine the UX in a variety of ways.
You can learn a lot more about refining the user experience of your app in this article and the more detailed articles that it links to.
Step 3: Refine the App Behavior
The app itself displays data, allows the user to navigate, capture and add to the data, and sync the data with the backend spreadsheet. While there are default behaviors for all aspects of this process, the app creator can modify and refine these behaviors. There are three different aspects to this:
- Actions -- richer operations that go beyond the default operations and navigations. For example, the app creator can define an action to Complete a Job, and this action might set the values of two different properties in the job record and then open an email to send to the customer.
- Offline behavior -- various options that whether the app should work offline and how often to sync data between the app and the backend spreadsheet. These options have a significant impact on the perceived performance of your app.
- Workflow rules -- this logic runs when each new or updated record is being sent to the backend spreadsheet. You can also construct logic that runs on a schedule to send regular data reports.
Workflow rules are very commonly used in apps that capture or update data. In our example app, it would be natural to check any update to a Job record, and if the Job is complete, to send an email to the manager with the summary details for the Job.
Since this is an introductory article, we will not elaborate on these concepts here, but instead direct you to this article and its related articles for more details.
Step 4: Review App Security
When you create an app, it is initially something that only you would use. However, as soon as possible, it makes sense to share the app with a few test users so that you get feedback. It is easy to do so via the Share/Users pane of the app editor. At this stage, it is important to think about the security of the app.
AppSheet offers a variety of rich security features. The most important choices though are simple:
- Will you restrict access to the app? --- the answer to this is almost always "Yes" if you are using your app in any business internal setting or if the app allows users to update data. You would say "No" only if you are creating an app for broad public consumption. In our example, the app is being created for use in a construction business and so it should restrict access to employees of the business. In AppSheet, you restrict access by requiring app users to sign in to use the app. You also provide a whitelist of allowed users.
- Who is allowed to use the app? --- assuming you required user signin, you then provide a list of email addresses of users who are allowed to use your app.
Here is an article that can get you started with more information on app security.
Iterative App Design
Nobody gets to design an app completely before they get started implementing it. Instead, it is an iterative learning process. AppSheet helps the process in two ways: you always have a working app and you can make incremental changes and immediately deploy them to your users. It is common for app creators to make hundreds of changes to their apps over the span of a few days. Most likely so will you. Enjoy your app design!