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.
For example, below is a request recently posted on our community forum. Let’s use this as an example in this article.
“I have a construction business. I’m building a set of apps to have full control of what is going on at our job sites. I built a spreadsheet (Google Sheets is my data source) where I have entered all data previously referring to a specific task; in this case, the task is water coating.
My data columns are 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 total the coating applied every day by each employee, and then I’ll track how much is applied onto every job site in total.
How would I build this in AppSheet?”
There are 6 steps that every app creator should follow:
- Define your entities
- Build your spreadsheet
- Plug in your spreadsheet
- Define the user experience (UX)
- Refine the app behavior
- Review app security
Step 1: Define Your Entities
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 Employee. The Job entity references the Customer entity, the Building entity, and the Employee entity.
Step 2: Build Your Spreadsheet
We support Google Sheets, Microsoft Excel, and Smartsheet. To plug your data into AppSheet, you should use one of these platforms.
Every entity that you define (Job, Employee, 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:
Here is some in-depth information on how to do this.
Step 3: Plug in Your Spreadsheet
To actually implement your data into your app-to-be, go to Data >> Tables in the app editor, select New Table, and find and select the data table you’d like to use:
Step 4: Define the User Experience
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 (i.e., the user interface).
Views are different ways of visualizing your data. The app editor offers a variety of formats, including: calendar, chart, dashboard, deck, detail, form, gallery, map, and table views. You can add a view by clicking Add View from UX >> Views in the app editor:
Where to find and launch views
There are two locations of views: Primary and Menu.
- Primary views are accessed via the bottom bar of the app, and are the most often-used.
- Menu views are accessed via the menu at the top left of the app. These views are only occasionally used.
You can toggle the location of your views by going to the ‘Display’ section of the particular table in the UX window.
Form views are used to capture data from the user. When in an existing view of the app, you should see a edit action button - tapping on this button allows the user to create a new Job record.
You don’t need to explicitly create a Form view. AppSheet automatically creates a Form view for any table that allows Adds or Updates.
You can also assign the app a logo, choose a color theme, define formatting rules, and so on.
Step 5: 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
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.
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.
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.
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 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.
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'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!