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 detail 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 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 four approaches to refine the behavior of an app.
- Static configuration
- Dynamic configuration
- Automation workflows
3.a: Static vs dynamic behavior
Static configuration is the simplest approach. The word "static" implies that this configuration is fixed for all users of the app and for all data in the app. Every column in every table has several static configuration options. For example, is the column read-only or can it be edited by the user. Likewise, every UX view has several static configuration options. For example, the image size in a Gallery view or the sort order for a Tabular view.
There are also various static configuration options that control whether the app should work offline and how often to sync data between the app and the backend spreadsheet. These options can have a significant impact on the perceived performance of your app.
The other three approaches are increasingly powerful but also involve some complexity. They all require that you (the app creator) define the behavior of some aspect of the app in a way that can be "run" or "evaluated" at the time that a behavior must be chosen. Naturally, such dynamic behavior is much more powerful than static configuration. Distinct and appropriate behaviors can be specified for different users, for different rows in the same table, or even for different values for the same column.
For each such decision, you have to define the behavior in a form that the system can evaluate dynamically to control the app. In AppSheet, the fundamental building block for such dynamic behavior is an Expression (aka Formula).
In syntax as well as in behavior, an AppSheet expression is almost identical to a spreadsheet formula.
An expression is usually defined in the context of a row of a specific table. For example, an expression [Status] = "Complete" can be used to filter the rows of a table and only retain those marked "Complete" in the Status column.
Expressions can define natural combinations of conditions (eg: AND([Status] = "Complete", [Quantity] > 5)), arithmetic expressions, text manipulations, and a wide variety of more complex computations.
The important thing to remember is that an expression by itself does not cause any behavior. It just defines a computation that will produce a value when computed. Depending on where the expression is used (see the sections that follow), its computed value modifies the behavior of the app.
3.c: Dynamic configuration
Many configurations in the app definition accept expressions instead of static values. For example, a column of a row can be shown or hidden based on the result of an expression. Likewise, the colors used to show specific column values can be modified using conditional formatting rules -- the conditions in these rules are expressions.
An important use of expressions is to compute "virtual" data of two kinds:
- Virtual columns --- these are columns that are never actually materialized in the spreadsheet data source, but instead are dynamically computed using expressions. For most practical purposes, they behave like regular columns.
- Virtual tables (aka Slices) --- these are virtual subsets of the tables, having just some of the rows and some of the columns. The filtering is defined by expressions. For most practical purposes, they behave like regular tables.
An Action is a single operation that represents a meaningful logical step for the end-user of the app. All the default behaviors of the app are actually the result of system-created Actions (UX navigation, form editing, adding a new row, making a phone call). In fact, all of these system-created Actions can be controlled, modified, or overridden.
The easiest Actions to define are app navigation actions that transition the user to a different state (i.e. UX view) within the app, or to an external app (like a phone app or messaging app).
Data change Actions provide significant power and richness. You can create your own Actions that reflect the semantics of your app. For example, you can define an Action to Complete a Job, and this Action might internally set the values of two different properties in the job record and then open an email to send to the customer.
Actions in AppSheet are not code programs however. They involve simple declarative expressions to describe desired changes in the app's data and/or presentation state. By composing individual actions into composite actions on a single record or on sets of records, very powerful behaviors can be defined.
Usually, an Action is executed in the app as a result of an explicit end-user interaction with the UI (eg: a tap/click of a button). In fact, it may be best to think of the app UI as waiting for end-user interaction events, and then reacting to each such event by executing the Action(s) bound to that event and the data row(s) associated with the event.
3.e: Automation workflows
The most complex but also the most powerful form of app behavior is to automate app behavior so that it does not need to be triggered explicitly by the end-user. Instead, automation rules can specify actions that should run either on a schedule or when specific other events occur (like a data change). In fact, automation workflows can even be the trigger for end-user interaction --- for example, by driving a push notification that informs the end-user of some information.
Automation is sometimes called "workflow" because in its full richness, automation logic can trigger a complex sequence of activity.
In AppSheet, such automation is defined using Event-Condition-Action workflow rules. These rules can execute in two possible environments:
- Within the app running on a device -- this is not yet been supported but will be in the near future. Common scenarios for such rules include running some kind of data capture logic on a recurring schedule on the device (for example, to record the GPS location periodically).
- Within the AppSheet cloud service -- this logic runs either on a schedule (ideal for periodic reporting) or when each new or updated record is being sent to the backend spreadsheet. This form of automation is already supported. 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 further, 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 that provide access control at different levels of granularity.
4.a: Access control at the App level
The most important choice is who can access the app. There is a simple question: 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 and their email addresses.
4.b: Access control at the UX level
Individual UX views can be accessible to some users but hidden for others. This is achieved via expressions that use the identity of the current user to make a dynamic decision.
4.c: Access control at the Data level
Each table in the app specifies permissions --- are Adds, Deletes, and Updates permitted. This decision can be dynamic via an expression. Further, this expression can be based on the identity of the current user, leading to rich permission control capabilities.
It is also a common requirement that different users see different subsets of the data. This "row-level security" can be achieved using Security filter expressions that apply to every row of the data.
4.d: Access control at the Behavior level
Individual actions and workflow behaviors can also be customized with expressions that use the identity of the current user. As a result, the behavior of the app can be personalized, and access to various behaviors can be controlled in a completely data-driven manner.
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!