• Home

  • Automation with Zapier

  • Zapier tutorials

Zapier tutorials

5 min read

Google Forms to Google Sheets: How to automatically update inventory

By Elena Alston · February 22, 2024
A purple Google Forms app icon and a green Google Sheets icon on an orange background with a dark orange dotted line connecting the two icons.

Managing a growing eCommerce business and need details on your inventory? Spreadsheets are a great tool to track your store's inventory, list all of your products and supplies, and log whenever anything's added or taken out.

The only problem is that it can be tedious—and time-consuming—to update and edit your spreadsheet every time you add a new product to your stock or a new sale decreases your inventory.

Fortunately, there's a way to automatically manage and update your inventory in a Google Sheets spreadsheet every time a new Google Forms response is submitted, so your stock quantity updates the moment something changes. 

Read more: How to use VLOOKUP in Google Sheets

Jump ahead

  • Before you begin

  • Set up your Google Forms trigger

  • Set up your Google Sheets search step

  • Set up your Formatter step

  • Set up your Google Sheets action

Note: This workflow requires Formatter and multi-step Zaps, both of which are available on our paid plans and during the first two weeks of a free Zapier trial. 

Before you begin

You'll need to create a Google Form and an inventory spreadsheet in Google Sheets if you don't have one already. You should have at least one submission in your Google Form and some inventory in your spreadsheet.

If you're starting from scratch, follow the instructions below.

How to set up an inventory spreadsheet

In a separate window, open Google Sheets, make a new spreadsheet, then list your inventory there. Be sure to add at least a column for your product ID numbers—or SKU for stock keeping units—and the quantity of the items you currently have.

A Google Sheets spreadsheet titled Inventory sheet with the tab Stock sheet highlighted with an orange box around it

Now, we'll create a new Google Form to update inventory. Inside the same spreadsheet, click Tools > Create a new form in your spreadsheet.

The "Tools" menu in a Google Sheet is shown open with an orange arrow pointing to "Create a new form"

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).

The questions menu of a Google form is shown open with the "Quantity" menu settings highlighted.

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.

A tab will automatically be created within your Google Sheet, so every time you fill out your Google Form, your responses will be routed there. We'll call this tab "Inventory update form". 

A Google Sheets spreadsheet titled "Inventory sheet" with the tab "Form Responses 1" highlighted with an orange box.

Once you have an inventory spreadsheet and your Google Form set up, you can move on to setting up your Zap.

Set up your Google Forms trigger

First, set up your trigger—the event that starts your Zap. In the Zap editor, search for and select Google Forms as the trigger app and New Form Response as the event. Click Continue

Now, connect your Google Forms account. If you've connected Google Forms to Zapier before, your account will automatically show up. Click on Change to select a different account from the dropdown menu (if you've connected others before) or click Sign in to add a new account.

Connecting your Google Forms account to Zapier.

Once you've connected your account, click Continue. Select the Google Form you'd like to use and click Continue once more. 

A Form field in a trigger step in the Zap editor.

Now it's time to test your trigger. When you click Test step, Zapier will find a recent form response. If you've never submitted a response before (because the form is new), submit a Google Forms response now with some new product numbers. (You can delete this later.) This data will be used to set up the rest of your Zap.

Once your trigger successfully returns a form submission, click Continue with selected record.

A purple Google Forms icon and the orange Zapier icon above a blue button with the text "Test trigger".

Set up your Google Sheets search step

Now let's set up the action—the event your Zap will perform once it's triggered. Select Google Sheets as your action app and Lookup Spreadsheet Row as your action event. Click Continue

The green Google Sheets icon next to the text "Lookup Spreadsheet Row in Google Sheets".

Connect your Google Sheets account to Zapier and click Continue.  Just like with your trigger step, you'll either select from a list of previously connected accounts or click Sign in to connect a new account to Zapier.

The text "Choose account" with a dropdown menu underneath that reads "Choose an account..." with the Google Sheets app icon.

Now it's time to customize your action step by specifying how Zapier will find the inventory you want to update. First, select the Drive that your spreadsheet lives in. Then, under Spreadsheet, select your main spreadsheet. Under Worksheet, select the sheet with your inventory list (in our example, this is called Inventory sheet).

Under Lookup Column, choose your product ID or SKU column from your inventory tab. Then under Lookup Value, select the SKU field from your Google Form. 

A series of dropdown menus underneath the text "Set up action".

Tip: Want to add new products to your spreadsheet from this form, too? Tick the Create Google Sheets Spreadsheet Row if it doesn't exist yet? box, then fill in the info from your form to add new products if this item isn't in your inventory yet.

After you've adjusted these fields, click Continue

Now it's time to test your action. When you click Test step, Zapier will send a spreadsheet row to Google Sheets according to how you set up your step. 

A successful test message which includes the text "Test was successful", the orange Zapier app icon, the green Google Sheets icon, and a blue button that reads "Retest & Continue".

Set up your Formatter step

It's time to crunch the numbers and calculate your new stock quantity. Add another step to your Zap by clicking the plus sign below your previous Google Sheets step. 

The Formatter app icon, which is two orange wavy lines, next to the text "Numbers".

Select Formatter by Zapier as your action app and Numbers as your action event. Click Continue

An action in the Zap editor with Formatter selected for the aaction app and Numbers selected for the action event.

Under Transform, select Perform Math Operation. Under Operation, select Add. 

Now it's time to input the values you want to add together. Under Input, click in the first field and select the Quantity value from the dropdown of data from your previous step. In the next field, click and select the Stock value from your Google Sheet. Click Continue

The text "Transform" with a dropdown underneath with an icon with two orange wavy lines and the text "Perform math operations".

Now it's time to test your trigger. Click Test step. If your Formatter step is working correctly, you'll see the sum of the inputs you specified earlier—in this case, 10 + 1 = 11.

The orange Zapier icon and the orange formatter icon with a blue button that reads "Test & Continue".

Set up your Google Sheets action

Now it's time to set up the very last action: Updating your spreadsheet row with the right stock quantity. 

Click the + sign after your previous action step to add a new step to your Zap. Select Google Sheets as your action app and Update Spreadsheet Row as your action event. Click Continue

The green Google Sheets app icon next to the text "Update Spreadsheet Row in Google Sheets".

Once again, choose the Google Sheets account you'd like to use or connect a new one to Zapier. Then click Continue. Under Spreadsheet, select the same inventory sheet from your previous step (ours is called Inventory test). Under Worksheet, select your stock sheet tab.

The text "Set up action" with a series of dropdown menus underneath that all include the green Google Sheets icon.

Now, under Row, click on Custom and select Lookup Spreadsheet Row in Google Sheets.

An orange box around a green Google Sheets app icon and the text "Lookup Spreadsheet Row in Google Sheets".

Select the ID value from your first Google Sheets step. 

A blue search bar with a series of green Google Sheets icons underneath with different text next to each line.

Now, scroll down to the last field. Click in the Quantity in sock field, click on the Numbers in Formatter by Zapier dropdown, and select the Output value from your Formatter step. That will add the new quantity to your spreadsheet to keep your inventory up to date.

A field called "Stock" with the words "Output" in the field with a blue button that reads "Continue" underneath.

Click Continue

Now it's time to test your Zap. If your Zap is working, you'll see an updated item quantity in your inventory spreadsheet.

An inventory spreadsheet with an orange box around an 11 in the final column.

Once you've tested the Zap, remember to click Publish to turn it onThen Share the form with your team. Now, whenever you need to update inventory in Google Sheets, just fill out that form, and your inventory spreadsheet will update automatically.

New to Zapier? It's workflow automation software that lets you focus on what matters. Combine user interfaces, data tables, and logic with 6,000+ apps to build and automate anything you can imagine. Sign up for free.

This article was originally published in May 2017, written by Matt Guay, with previous updates by Elena Alston. It was most recently updated in February 2024 by Hannah Herman.

Related reading:

  • Connect OpenAI and Google Sheets for easy prompt creation

  • How to automatically answer form responses with OpenAI and Zapier

  • How to connect Google Sheets to WordPress

  • How to connect Gmail with Google Sheets

Get productivity tips delivered straight to your inbox

We’ll email you 1-3 times per week—and never share your information.


Related articles

Improve your productivity automatically. Use Zapier to get your apps working together.

Sign up
A Zap with the trigger 'When I get a new lead from Facebook,' and the action 'Notify my team in Slack'