Most app creators already have an app that they want to build. They have two questions:
- Can I build this app with AppSheet?
- How should I build this app with AppSheet?
Chances are, you can build your envisioned app with AppSheet.
Sample use case
In this article, we step through the following sample use case.
“I have a construction business. I’m building a set of apps to be able to track what is going on at our job sites. I built a spreadsheet (Google Sheets is my data source) where I have entered all data related to a specific task, in this case, water coating.
My data columns specify different areas that need to be coated. For example, Column A refers to Tower A, Column B refers to Apartment 301, and Column C refers to Tower B.
I’ve populated this table with existing data, and my goal is to create an app that allows the supervisor to check a box once they’ve verified that the task is complete. I hope to then be able to track the total the amount of coating applied each day by each employee, in addition to the amount that is applied across all job sites.
How would I build this in AppSheet?”
Summary of steps
There are 6 steps that every app creator should follow:
- Define your entities
- Build your spreadsheet
- Plug in your spreadsheet
- Define the user experience
- Refine the app behavior
- Review app security
AppSheet apps are all about the data. We think about data in terms of entities and their properties.
Every piece of data should be categorized into an entity. An entity is a set of things in the real world - in the above example, the four entities involved would be employee, job, location, and customer.
Every entity has a set of standard properties; for example, every customer would have a name, address, phone number, and email address. All instances of an entity will have the same types of properties, but with different values.
These different property values help uniquely identify an instance of the entity; for example, an
Employee may have an
ID number property.
Finally, entities can be related to each other. In this case, each
Job is for one
Customer, in one
Building, assigned to one
Job entity references the
Customer entity, the
Building entity, and the
AppSheet supports Google Sheets, Microsoft Excel, and Smartsheet. To plug your data into AppSheet, you should use one of these platforms.
Every entity that you define (
Building) should have its own worksheet within the spreadsheet you are connecting to AppSheet.
In this single worksheet, the different properties of the entity should be the column headers. Following the example, the below shows how the
Customer sheet has columns that correspond with the different properties of the entity:
For more information about how to build your spreadsheet, see Data: The Essentials.
To actually implement your data into your app-to-be, select Data > Tables in the app editor, select New Table, and find and select the data table you’d like to use:
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 (that is, the user interface).
Views are different ways of visualizing your data. The AppSheet editor offers calendar, deck, table, gallery, detail, map, chart, dashboard, and form views. You can select a view type by clicking on the particular table in the UX window.
Where to find and launch views
There are two locations of views: Primary and Menu.
- Primary views are accessed in the bottom bar of the app and are used most often.
- Menu views are accessed in the menu at the top left of the app. These views are used only occasionally.
For more information, see Configure where to launch views in your app.
Use form views to capture data
Form views are used to capture data from the user. When in an existing view of the app, you should see an edit action button - tapping on this button allows the user to create a new
You can customize the app a logo, choose a color theme, define formatting rules, etc.
For more information, see UX: The Essentials.
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 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 (or 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
Expressions can define natural combinations of conditions (such as
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.
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: 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 (or Slices): 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 (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 (that is, a tap 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 actions bound to that event and the data rows associated with the event.
For more information, see Actions: The Essentials.
The most complex but also the most powerful form of app behavior is automation. With automation, app behavior does not need to be triggered explicitly by the end user. Instead, automation can specify actions that should run either on a schedule or when specific events occur (like a data change). In fact, automation 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 logic can trigger a complex sequence of activities.
In AppSheet, such automation is defined using bots. Bots can execute in two different environments:
Within the app running on a device -- Common scenarios for such bots include running some type 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.
For more information, see AppSheet Automation: The Essentials.
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 in the app editor. See Share: The Essentials. 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.
For more information, see Security: The Essentials.
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 list of allowed users and their email addresses.
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.
Access control at the data level
Each table in the app specifies permissions --- are Adds, Deletes, and Updates permitted. This decision can be dynamic using 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.
Access control at the behavior level
Individual actions and automation bots 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.
Iterative app design
Nobody designs an app completely before they get started implementing it. Instead, it's an iterative 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!