How do you Manage Inventory With Google Forms and Sheets?

By Matthew Guay

You are reading: Chapter 5 of 2

Spreadsheets are a great tool to track your store's inventory, list all of your products and supplies and resources, and log whenever anything's added or taken out.

The only problem is, it can be tedious to update and edit your spreadsheet every time a new product is added to your stock or a new sale is made that decreases your inventory.

Here's how to automatically manage your inventory in a Google Sheets spreadsheet with a Google Forms form, and some Zapier workflows.

Manage Your Inventory in a Google Sheets Spreadsheet

A Google Sheets inventory system
List all of your products in a Google Sheets spreadsheet

We'll do the easy part first—listing your initial inventory. Just open Google Sheets, make a new spreadsheet, then list your inventory there. Be sure to add a least a column for your product ID numbers—or SKU for stock keeping units—and the quantity of the items you currently have.

Add and Remove Inventory with a Google Sheets Form

Google Forms
Google Forms is a great tool to gather data for your inventory system

Now, we'll create a new Google Form to update inventory. Open Google Forms, or click Tools -> Create a Form in your spreadsheet.

In the form, add the fields you'd want to update—including at least the product ID or SKU, and the number of stock you're adding (or removing—which you'll do by adding a negative quantity). You can also add a validation to your quantity—click the 3 dot menu on that question, select Response Validation, and add the details you need to make sure you have the correct data.

Google Forms will now log every update made to your form to a new sheet in your spreadsheet. All we need to do is use that data to update your inventory spreadsheet.

Update Your Inventory Spreadsheet with Zapier

Google Sheets and forms in Zapier

It's time for the fun part—tying it all together. We'll use app automation tool Zapier to take your form answers and merge them with your inventory list in Google Sheets.

Just make a new Zap using our Google Forms integration, and select the New Response in Spreadsheet trigger. Choose the spreadsheet you made before, and the sheet in it with the responses from your form.

Find row Google Sheets

Now add another step to your Zap, choosing the Google Sheet app and the Lookup Spreadsheet Row action. We want to find the original inventory that you want to update.

Select the correct spreadsheet and your inventory sheet again, then choose your product ID or SKU column as the Lookup Column. Then, click the + icon beside the Lookup Value field in Zapier, and select the product ID value from your Google Form.

zapier formatter

It's time to crunch the numbers and see what your new quantity is. Add another step to your Zap, select the Formatter app and the Numbers action, then choose the Perform Math Operation transform. Select the Add operator, then click the + icons under Input to add the quantity values from your original inventory list and the form entry.

Update Google Sheets spreadsheet

Now let's put it all together with one final step. Select the Google Sheets app, and the Update Spreadsheet Row action. Select your spreadsheet and sheet again, and select Use a Custom Value under the Row field. Now, select the ID from your first Google Sheets step in the Custom Value field

Only one thing left: Click the + icon beside your product quantity fields, and select the Output value from your Formatter step. That'll add the new quantity to your spreadsheet, to keep your inventory up to date.

Turn on the Zap, and share the form with your team. Now, whenever you need to update inventory in Google Sheets, just fill out that form and your inventory will be updated automatically.


To help you set that up, here are some pre-made Zaps to start with:


Whether you're managing a growing eCommerce business and need details on your inventory, or just want to track where all of the books from your personal library are, Google Forms plus Google Sheets are a great combo to keep tabs on all of your stuff.


Have another question about Google Sheets, Google Forms, or other apps? Ask the Zapier team

Published May 19, 2017

Question asked by Anthony

Answer written by Zapier writer Matthew Guay

Workflow

Take the Work out of Workflow

Zapier is the easiest way to automate powerful workflows with more than 1,500 apps.