Google Sheets Inventory Dashboard: Build a Web App With Apps Script and Gemini

Introduction

A Google Sheets inventory dashboard can be much more useful than a spreadsheet full of rows. With Google Apps Script, you can turn the same inventory data into a browser-based dashboard, add low-stock alerts, create a mobile-friendly receiving form, and even use Gemini to extract information from receipt images.

The basic idea is surprisingly practical: Google Sheets remains your data source, while Apps Script acts as the backend and serves the web interface. Gemini can help with both parts of the project—understanding your spreadsheet structure and generating or refining the JavaScript and HTML needed for the web app.

There is one important update to keep in mind, though. Gemini features built directly into Google Sheets aren't simply available to every free Google account. Google currently says Gemini in Sheets requires an eligible Google Workspace or Google AI plan, although availability can also vary through testing programs.

Here's how the overall system works and what you should know before building one.

Google Sheets Inventory Dashboard: Build a Web App With Apps Script and Gemini

1. Start With Your Google Sheets Data Schema

Before asking an AI model to create an inventory dashboard, you need to describe the data accurately.

Suppose your workbook contains two main tabs:

• Items: SKU, product name, category, supplier, safety stock, and other product information
• Transactions: date, SKU, transaction type, quantity received or used, supplier, and notes

You could paste several sample rows into an AI prompt, but that's not always ideal. A small sample may leave out categories, suppliers, transaction types, or unusual values that exist elsewhere in the spreadsheet.

A better approach is to first create a compact data schema describing each tab and column.

For example:

Sheet	Column	Purpose
Items	SKU	Unique inventory ID
Items	Product Name	Item description
Items	Safety Stock	Minimum preferred quantity
Transactions	Date	Transaction date
Transactions	SKU	Links transaction to item
Transactions	Type	Incoming or outgoing inventory
Transactions	Quantity	Number of units moved

If Gemini in Sheets is available on your account, its side panel can work with spreadsheet data and help analyze the workbook. Google's current documentation says Gemini in Sheets can create tables, formulas, charts, summaries, analysis, filters, pivot tables, and other spreadsheet operations. It can also focus on selected tabs when a workbook contains multiple data sources.

That makes schema generation a useful first AI task.

Instead of repeatedly giving your coding assistant raw spreadsheet data, you can provide:

• Sheet names
• Exact column headers
• Data types
• Relationships between sheets
• Allowed values
• A few examples only when necessary

This also makes future changes easier. If you add a new Reorder Quantity column, for example, you update the schema rather than rebuilding your entire prompt around another spreadsheet sample.

2. Use Gemini to Design the Inventory Dashboard

Once the data structure is clear, you can ask Gemini or another coding assistant to generate the Apps Script project.

A simple Google Sheets dashboard typically needs two main parts.

Code.gs contains the server-side Apps Script logic. It can read spreadsheet rows, calculate current stock, identify low-stock items, process inventory updates, and return data to the browser.

Index.html contains the interface the user sees. It can display summary cards, tables, filters, buttons, charts, dialogs, and responsive layouts.

For an inventory dashboard, useful requirements include:

• Total number of inventory items
• Current stock totals
• Low-stock item count
• Recent incoming and outgoing transactions
• Date filters
• Category or supplier filters
• Search
• Mobile-responsive design
• A button for recording incoming inventory

The prompt should describe behavior rather than merely asking AI to "make a nice dashboard."

For example, specify that current inventory must be calculated from incoming and outgoing transactions, that an item becomes low stock when its current quantity falls below its safety-stock value, and that spreadsheet column names must be matched exactly.

This significantly reduces one of the biggest risks of AI-assisted coding: receiving code that looks polished but doesn't match the actual workbook.

You should still review generated code before granting it access to business data. Apps Script automatically determines many of the Google permissions a project requests by scanning the services used in its code.

AI-generated code should be treated as a starting point, not automatically trusted production software.

3. Connect the Dashboard to Google Apps Script and Deploy It

For a spreadsheet-bound project, open the Apps Script editor from Google Sheets and place the generated server-side logic in the script project.

Then create an HTML file for the dashboard interface and add the generated HTML, CSS, and client-side JavaScript.

For a browser-based Apps Script application, the server normally exposes a doGet(e) or doPost(e) function that returns HTML or text. Google documents these as the entry points used when publishing Apps Script as a web app.

Once the project is ready, Google currently documents the deployment process as:

Deploy > New deployment > Select type > Web app.

From there, pay close attention to two settings: who the application runs as and who is allowed to access it.

Apps Script supports access configurations ranging from only the deployer to logged-in users or anonymous visitors. The script can also run with the authority of either the person accessing it or the person who deployed it.

That distinction matters.

A dashboard that only displays non-sensitive information might have relatively broad access. A web app capable of writing inventory transactions into your spreadsheet should usually be far more restricted.

Don't automatically choose "anyone" just because it makes deployment easier.

There is another security detail beginners often encounter. An Apps Script project using sensitive Google permissions can show a "This app isn't verified" warning during development. That warning doesn't mean every self-created script is malicious, but you should only bypass it when you understand the code, created or trust the project, and recognize the permissions being requested. Google specifically warns that sensitive scopes can trigger the unverified-app flow.

4. Add Low-Stock Alerts and a Better Mobile Interface

A useful inventory dashboard shouldn't force you to scan a large table just to find out which supplies need attention.

Instead, make the Low Stock summary card interactive.

When you tap it, the dashboard could open a panel showing:

• Item name
• Current quantity
• Safety-stock level
• Amount below the target
• Recent usage
• Recommended reorder quantity

That turns a simple KPI into an actionable tool.

For example, knowing that three products are below safety stock isn't nearly as useful as immediately seeing that disposable cups are down to 40 units, normally consume 25 units per day, and should be reordered before tomorrow's business.

You can also make the interface more usable on a phone by designing around large touch targets, compact summary cards, collapsible tables, and a prominent Receive Inventory button.

Decorative elements such as a large hero image or background video can make an internal dashboard look polished, but they shouldn't interfere with loading speed or the primary workflow. For an inventory tool, getting from "low stock" to "what do I order?" should remain more important than visual effects.

5. Scan Receipts With Gemini and Add Inventory Automatically

The more interesting extension is a receiving form that accepts a receipt or invoice image.

Instead of manually entering every purchased item, the workflow could be:

1. Open the receiving page on your phone.
2. Take or upload a photo of the receipt.
3. Send the image to a Gemini model.
4. Ask Gemini to return structured fields such as product name, quantity, date, and supplier.
5. Display the extracted information for review.
6. Confirm the entries.
7. Write the approved transactions into Google Sheets.
8. Refresh the inventory dashboard.

Gemini supports image input and can analyze visual information without requiring a separate traditional OCR model for every task. Google's Gemini API documentation describes multimodal image capabilities including image understanding, classification, and visual question answering.

However, don't design the system around the assumption that receipt extraction will always be correct.

Receipts can contain:

• Abbreviated product names
• Low-contrast printing
• Handwritten changes
• Multiple quantities
• Tax lines
• Discounts
• Duplicate product descriptions
• Poor camera angles

For that reason, the safest workflow is analyze → preview → confirm → save, rather than automatically modifying inventory immediately after an image is uploaded.

Protect your Gemini API key

Google AI Studio can be used to create credentials for the Gemini API, and a free usage tier exists for supported configurations. But Google does not define this as unlimited free usage. Rate limits depend on the model, project, and usage tier, and Google advises checking the active limits in AI Studio.

More importantly, never embed the Gemini API key directly in Index.html or other browser-side JavaScript.

Anything delivered to the browser should be treated as potentially visible to the user.

A better Apps Script design is to keep the credential on the server side—such as in Apps Script properties—and have your server-side function make the Gemini API request. The HTML interface sends the image to your Apps Script backend rather than contacting Gemini with a exposed secret.

That becomes especially important if billing is enabled.

6. Turn the Dashboard Into a Phone-Friendly Tool

Because an Apps Script web app runs in the browser, you don't necessarily need to develop a native iPhone or Android application.

Once the dashboard is responsive, you can open its URL in your mobile browser and add it to the phone's Home Screen.

That gives you an app-like shortcut for everyday inventory work.

A typical workflow might look like this:

Dashboard → Low Stock → Receive Inventory → Photograph Receipt → Verify Items → Save

For a small store, office, café, workshop, or other operation that already depends on Google Sheets, that can be significantly more convenient than opening the full spreadsheet on a phone and editing cells directly.

Just keep the distinction clear: adding a website to the Home Screen doesn't turn it into a native mobile app. It's still an Apps Script web application running through the browser.

Conclusion

Google Sheets can work as the foundation of a surprisingly capable inventory management system when you combine it with Apps Script and AI-assisted development.

The strongest architecture is straightforward: keep the inventory records in Sheets, use Apps Script for calculations and secure server-side operations, build an HTML dashboard for the interface, and optionally use Gemini to interpret receipt images before adding incoming stock.

AI can eliminate a lot of repetitive coding, but it shouldn't eliminate verification. Check the generated formulas and JavaScript, restrict write access, keep API keys out of client-side code, and always let a user confirm AI-extracted receipt data before it changes inventory.

One correction is particularly important if you're following older tutorials: as of August 2026, Google says the integrated Gemini experience in Sheets requires an eligible Google Workspace or Google AI plan rather than being a universally free Google Sheets feature. The Gemini API does have a free tier for supported usage, but quotas and limits vary by model and project.