Easily build an issue management app with AppSheet

What’s AppSheet? I will describe it as a more mobile-device-friendly version of GSheet. Within a few clicks, we can setup an app to collect, display, modify data (can be text, date, image…) in a GSheet 💛

In this article, we will create an issue management app using these handy AppSheet tips:

1. Valid if
2. Editable if
3. Data Slicing

Our Goal

As a logistic company, we want to efficiently manage problems in different stores. For each store, managers need a channel to report issues of abnormal parcels and get next step instructions from the company.

Company will collect issues reported by store manager Andy, Wendee, and Lily (Mobile app icons created by catkuro / Sheet icons created by Mercury429)

Our Data

We have Store sheet, Issue Category Sheet, and Issue sheet for problem collection.

Issue Sheet

Setup

Here is a nice tutorial to create a new appSheet. In our app, we can add the 3️⃣ tables above.

1. Issue: Can Add + Update, 🔑 table key: [_RowNumber]
2. Store: ReadOnly, 🔑 table key: [Store]
3. Issue Category: ReadOnly, 🔑 table key: concatenate([Level 1],[Level 2]) → This is a combined key, created via “Add Virtual Column”.

Don’t forget to set Formula in [Manager Email] to UserEmail()

Valid if

Valid if is hided in the column’s edit panel, but it’s super handy for restricting inputs 🤩 In our case, a manager can only report issues in his/her store, so we need to add this restriction in column Store :

Edit → Data Validity → Valid If

Tada 🎉 Managers won’t see other stores now

Relational Dropdown

We can even create an relational dropdown with Valid If 🎃 The Category and Subcategory Columns in Issue correspond to Level 1 and 2 in Issue Category 👉

Editable if

In most issue collection requests, users normally will not be allowed to modify their issues after submitted. However, we may want them to edit parts of the data sometimes. For example, when Company Feedback column has value, we can enable users to edit Final Status column to know if the manager has returned or disposed the parcel.

In each column’s edit panel, we can replace Editable’s checkbox with expressions:

Edit → Update Behavior → Editable

Data Slicing

Almost done with all settings! With all these issues collected, we do not want Manager A to see issues reported by others. In order to achieve this, we need to slice data 🧈🧀

As the column [Manager Email] is set to each issue reporter’s useremail(), this helps us create a sliced version of Issue, which the login user will only see issues created by themselves. In UX, set the sliced data as the source👇

If we want to change column orders or even hide some column (e.g. Manager Email), it can be set in System View → Sliced Version of Data → Column Order (Detail is view mode, while Form is edit mode 📝)

Finally, managers will only see their issues in this app!

Pictures will be stored in the drive where we placed our Gsheet. I always use the following AppScript to get sharable links of each photo and use vlookup to map filenames with urls 🌠

Oh wait!

You know appSheet supports barcode scanning? Watch this! It’s AMAZING 🎠