<img height="1" width="1" src="https://www.facebook.com/tr?id=1824058264555430&amp;ev=PageView &amp;noscript=1">
Filter By Categories
Gwen Chen
By
March 12, 2018

How to Turn Google Sheets into an Inventory Management App

Inventory App.jpegWelcome back to my How to Make + Function + App series! I received so many comments on the Project Management and Property Management posts that I decided to continue with other use cases in this series.

Determining which Functions to focus on can be tricky so I took a look at AppSheet’s Sample App page and Support page. By the way, AppSheet’s Support Page is the perfect place to search for AppSheet documentation, how-to apps and our responses to all kinds of app-making questions. Here is what I found:

  • Inventory management apps are among the most popular sample apps (my first criteria for inclusion).
  • There are lots of questions on how to make an inventory app (my second criteria for inclusion).

So I made an inventory app that (I hope) addresses some of your most frequently asked questions around using bar codes and how to create and apply restocking levels. Feel free to copy this app, customize it and make it your own. And don’t forget to leave your comments below—either for this post or the post series!

Inventory Management App

Story App Showcase.png

Use case and target users

This app was made for operation managers who want to keep track of inventory with barcode scanners to ensure they have enough stock for customers.

Features

The app enables operation managers to do the following:

  • Use barcode scanners to record stock in and stock out
  • Automatically calculate current stock level
  • Display on a view what items need to be restocked
  • Send “Add Stock” email notifications to the purchasing department

Without further ado, here is how I built the app.

Step 1: Organize your data and make an app

First, make sure that your data is organized in a meaningful way so that your app (next step) can understand how to present that data. For example, in Google Sheets (check out other data sources AppSheet supports here), I have three sheets that organize different sets of data:

  • Product, for all product information;
  • Sales, for items sold or removed out from stock;
  • Purchases, for products added to stock.

Next, convert your data in Google Sheets to an AppSheet mobile app (see video 1 in the property management app). Now, the above three sheets become three views of data in your app. You can change view names to whatever makes more sense to you. I call my views Product List, Sell, and Add Stock.

Data to App.png

Step 2: Record stock in and stock out with a barcode scanner—the camera on your phone

AppSheet can use the camera on your mobile device to capture barcoded data. To do this: Mark Product Barcode column (Data --> Columns in App Editor) in both the Purchases and Sales sheets as searchable and scannable. Your app is now ready to record any inventory movement, whether it is a stock in or stock out. All you have to do is to tap on the barcode scanner button (under Add Stock or Sell view) and scan the item!

Add Stock Barcode.png

Keep in mind that if an item is new and doesn’t have a product barcode already stored in your Product sheet, you will have to capture its barcode first so that in can then be stored on the Product sheet. Instead of simply scanning it, you will need to follow these three steps:

  1. Tap on Product Barcode on Add Stock View
  2. Click New
  3. Scan the barcode, fill out the form, and save

Stock in and Stock out New Update 2.png

Step 3: Calculate the real time inventory level

The calculation formula is pretty simple. But while the Initial Stock field is static, Stock In and Stock Out data can be a nightmare, especially if you want to monitor your stock multiple times a day.

This is the formula you want to use:

Current Stock Level= Initial Stock + Stock In – Stock Out

Stock Calculation Update.png

To configure your app to automatically record real-time inventory levels, do the following:

First, connect your Product Barcode columns in the Sales and Purchases sheets with the Product Barcode column in Product sheet. To do this: Go to Data --> Columns --> Sales, and click on Product Barcode and edit the column definition by following the three steps below:

  1. Name the Column Product Barcode
  2. Select Ref on the Type drop-down list
  3. Select Products as ReferencedTableName

Reference update.png

Now you have pointed all data (needed to do the calculation) to one place, in this case, your Product sheet.

Next up: Tell your app how to calculate! Create a virtual column Current Stock in Product sheet and add your App Formula:

COUNT([Related Purchases]) - COUNT([Related Sales]) + [Initial Stock].

And that’s it. If you go to:

UX --> Product view --> Column Order and select Current Stock,

you will see every product’s Current Stock level.

Product List View.png

We get questions on data referencing at almost every Office Hour. To learn more about it, go to AppSheet’s YouTube channel for tutorials or AppSheet’s support page for documentation.

Step 4: Display “Restock Needed” for low inventory products

Inventory managers need to make sure there is enough inventory to sell and that shelves are full. For example, if you keep running out of stock, there is no reason for customers to choose you over your competitors. In this section, I will show you how to let your app tell you: “You need to purchase the following products ASAP.”

To set restock alerts for low inventory products, follow these steps:

  1. Set a restock level for every product. The restock levels for products might be different. To determine the right “number” for each product I suggest you review your historical data, check out your demand forecast and then determine your restock points.Restock Level-1.png2. Create a slice. Go to Dataà Slices and create a slice: Restock Needed. Set your Row Filter Condition to be: [Current Stock] <= [Restock Level]. This formula says, “give me the data if a product’s Current Stock level is lower than or equal to Restock Level.”Restock Needed Slice Update.png3. Create a view for the slice. Once you create a view, you will see all the products that you need to restock.

Restock Needed View.png

The following screenshot shows the view you created.

 Restock Needed.png

Step 5: Send email notifications on “Restock Needed” products

Let’s say that you don’t want to check your app to know what products need to be replenished. Instead, you want your app to send SMS or email notifications to you or to the purchasing department.

To do so, create a report in the Behavior section of the Editor and tell your app to notify you or purchasing managers when the Restock Needed view has at least one product whose current stock is lower or equal to the restock level. Follow the instructions in the image below and messages will be sent out everyday at 7 PM PST.

Add Stock Please.png

 Add Stock Email Template.png

Here is what you will see in your email:

Add Stock Please Email.png

Okay—that’s it for now! Hopefully my mini-tutorial helped to fill some of your inventory management gaps. Questions, comments or even requests for my next app are always appreciated!

 

Inventory Management App

Google Search

Subscribe Email