How to Build an Inventory Management System in Google Sheets

How to Build an Inventory Management System in Google Sheets

Managing inventory manually can become difficult very quickly.

You need to know how many products you have, which products are running low, where stock came from, where it went, and how much your current inventory is worth.

The good news is that you do not always need expensive inventory software.

In this guide, we will build an inventory management system in Google Sheets using Google Apps Script and ChatGPT.

Instead of writing the entire application manually, we will ask ChatGPT to help generate the code.

The finished application will include:

• Dashboard

• Product management

• Stock in

• Stock out

• Stock adjustments

• Inventory movement history

• Filters

• Reports

• PDF report generation

• Business settings

• Activity logs

We will also create sample data so the dashboard does not look empty when we first run it.

This tutorial focuses on building an MVP, or minimum viable product. You can improve and customize it later based on your business requirements.

What We Are Going to Build

Our inventory system will use two main technologies.

Google Sheets will work as the database.

Google Apps Script will handle the application logic and web interface.

The interface itself will be built using HTML, CSS, and JavaScript inside the Apps Script project.

For this project, we will organize the application into four files:

1. Code.gs

2. Index.html

3. Styles.html

4. Scripts.html

Keeping the application separated into these files makes the code easier to understand and maintain.

Main Features of the Inventory System

Before generating any code, it is important to define what we want the application to do.

Our MVP will contain six main sections.

1. Dashboard

The dashboard gives us a quick overview of the inventory.

It should show information such as:

• Total products

• Current stock

• Low-stock products

• Inventory value

• Stock in

• Stock out

• Stock movement chart

• Low-stock alerts

We also want filters so the dashboard can be analyzed by date, product, or other criteria.

2. Products

The Products menu will be used to manage the product master data.

For example, each product can contain:

• Product ID

• SKU

• Product name

• Category

• Purchase price

• Selling price

• Current stock

• Minimum stock

• Unit

• Status

• Notes

Users should also be able to filter the product list.

Possible filters include:

• Category

• Product status

• Low stock

• Product name

• SKU

3. Inventory and Stock Movement

This section manages all inventory transactions.

We want three main actions:

• Stock In

• Stock Out

• Adjustment

When entering a transaction, the user should be able to add information such as quantity, date, product, transaction reason, and notes.

For example:

Stock In: Purchase from supplier.

Stock Out: Customer order.

Adjustment: Physical stock count correction.

Every transaction should also appear in the stock movement history.

4. Reports

The Reports section will let users generate inventory reports.

Users should be able to choose:

• Date range

• Product

• Transaction type

• Other available filters

After selecting the criteria, the application generates the report.

We also want the system to create a PDF automatically so it can be opened or downloaded.

The PDF can include the business information configured inside the Settings section.

5. Settings

The Settings section stores company information.

For example:

• Business name

• Address

• Phone number

• Email

• Website

• Other report information

This information can then appear automatically in generated reports.

6. Activity Logs

The final section records important activities inside the application.

For example:

• Product added

• Product updated

• Stock added

• Stock removed

• Inventory adjusted

• Settings changed

• Report generated

Activity logs are useful because they make it easier to understand what happened inside the system.

Step 1: Create a New Google Sheet

Start by opening Google Sheets.

Create a new blank spreadsheet.

Give it a clear name such as:

Inventory Management System

You do not need to manually create every database sheet at this point.

Later, we will use a setup function in Apps Script to create the required sheets automatically.

Step 2: Open Google Apps Script

Inside your spreadsheet, open:

Extensions → Apps Script

A new Apps Script project will open.

Because the Apps Script project is connected to the spreadsheet, our script can work directly with the spreadsheet data.

You will normally see a default file called:

Code.gs

We will use this file for our backend code.

Step 3: Ask ChatGPT to Design the Inventory System

Before asking ChatGPT for code, I recommend starting with brainstorming.

This is important.

If you immediately ask AI to generate hundreds of lines of code without defining the system first, it is easier to end up with missing features, inconsistent database structures, or code that becomes difficult to maintain.

We will therefore give ChatGPT our complete application requirements first.

Copy the following prompt into ChatGPT.

Prompt for ChatGPT

I want to build an inventory management system application using Google Sheets and Google Apps Script.

The application should have the following features:

1. Dashboard

The dashboard should show:
- Total number of products
- Low-stock products
- Current inventory value
- Stock in and stock out
- A line chart showing stock movement
- Low-stock alerts
- Filters such as date and product

2. Products

In the Products menu, I want to:
- Add products
- Edit products
- Store price
- Store category
- Store SKU
- Store minimum stock
- Store current stock
- Store unit
- Store product status
- Add other useful product details

The product page should also have filters such as:
- Category
- Low stock
- Status
- Search

3. Inventory & Stock Movement

I want to create:
- Stock In
- Stock Out
- Stock Adjustment

Use a popup or modal form for entering transactions.

The transaction should contain:
- Product
- Quantity
- Date
- Transaction reason
- Notes

I also want to see a list of all stock movements and be able to filter the list.

4. Reports

I want to generate reports about inventory and stock movements.

The reports should support filters such as:
- Date range
- Product
- Transaction type

After generating a report, I want the application to automatically create a PDF report that I can open or download.

5. Settings

I want a Settings page where I can store:
- Business name
- Address
- Phone number
- Email
- Other company information

This information should automatically appear on generated PDF reports.

6. Activity Logs

I want the application to record important activities such as:
- Product created
- Product edited
- Stock in
- Stock out
- Stock adjustment
- Report generated
- Settings changed

I want to build this application as an MVP.

However, the UI and UX should still look modern, clean, professional, responsive, and polished.

Use Google Sheets as the database and Google Apps Script as the backend.

Structure the project using only these four files:

1. Code.gs
2. Index.html
3. Styles.html
4. Scripts.html

Do not generate all files immediately.

First, brainstorm:
- Application architecture
- Database structure
- User flow
- Dashboard layout
- Pages
- Features
- Important functions
- Recommended improvements

Wait for my approval or revisions.

After I approve the architecture, generate the files one at a time.

Start with Code.gs.

Code.gs must include a setupDatabase() function that automatically creates all required database sheets, headers, configuration, and simple sample/seed data.

The seed data should be realistic enough that I can immediately see products, stock movements, dashboard statistics, charts, and low-stock alerts after setup.

Make the code ready to copy and paste directly into Google Apps Script.

Do not generate Code.gs until I approve the brainstorming stage.

Start with brainstorming only.

Why Start With Brainstorming?

This step may feel unnecessary, but it can save a lot of time.

The brainstorming response gives us a chance to check the application before code is generated.

Look at things such as:

• Database structure

• Sheet names

• Product fields

• Transaction fields

• Dashboard calculations

• Navigation

• Report structure

• Activity logging

• UI layout

If something is missing, tell ChatGPT to change it.

For example:

Please add supplier information to the product database and add a supplier filter to the Products page.

Or:

I don't need supplier management for the MVP. Keep the database simpler.

Continue adjusting the plan until you are happy with it.

Step 4: Generate Code.gs

Once you approve the application plan, ask ChatGPT:

The brainstorming looks good. No more revisions.

Please generate Batch 1: Code.gs.

Make sure setupDatabase() creates all required sheets, headers, settings, and sample data.

Give me the complete Code.gs file without omitting any code.

ChatGPT should now generate your backend file.

Copy the entire output.

Return to the Apps Script editor.

Delete the existing example code inside Code.gs.

Paste the generated code.

Then click Save.

What Does Code.gs Do?

Code.gs is the server-side part of the application.

Depending on the generated architecture, it may handle tasks such as:

• Reading products from Google Sheets

• Saving products

• Updating products

• Recording stock movements

• Calculating inventory balances

• Loading dashboard data

• Saving settings

• Creating activity logs

• Generating reports

• Creating PDFs

• Serving the web application

It also contains one particularly important function for our first setup:

setupDatabase()

Step 5: Run setupDatabase()

After pasting Code.gs, find the function selector at the top of the Apps Script editor.

Choose:

setupDatabase

Then click Run.

What Is setupDatabase() For?

This is an important part of the tutorial.

Our application needs somewhere to store information.

Instead of asking you to manually create multiple sheets, columns, headers, and sample records, the setupDatabase() function does the initial setup automatically.

Depending on the final code generated by ChatGPT, it may create sheets such as:

• Products

• StockMovements

• Settings

• ActivityLogs

It may also insert column headers and sample records.

For example, the Products sheet may receive fields such as:

Product ID | SKU | Product Name | Category | Cost Price | Selling Price | Stock | Minimum Stock | Unit | Status

The StockMovements sheet could include:

Movement ID | Date | Product ID | Type | Quantity | Reason | Notes

The exact structure will depend on the architecture you approved earlier.

Why Add Seed Data?

A new inventory system normally has no information.

That means the dashboard would initially show:

• 0 products

• 0 inventory value

• No chart data

• No stock movement

• No low-stock warnings

That makes it difficult to know whether the dashboard is working correctly.

Seed data solves this problem.

The setup function creates several example products and inventory transactions.

As a result, we can test the interface immediately.

After everything works, you can delete the sample records and replace them with your real products.

Step 6: Authorize the Apps Script

The first time you run the setup function, Google may ask you to authorize the script.

This is normal.

Google Apps Script requires authorization when a script needs access to Google services or private account data.

Our application needs to read or modify the spreadsheet, so Google needs your permission before the script can perform those actions.

Click the option to review the requested permissions.

Choose the Google account connected to your spreadsheet.

Review the permissions carefully.

Only continue when you understand and trust the code you are running.

Why Does Google Ask for Permission?

Apps Script determines which Google services your code uses.

For example, if your code accesses Google Sheets, Apps Script may request spreadsheet-related permissions.

If later you add another service, additional authorization may be requested.

This security process prevents scripts from silently accessing private Google data without permission.

Important: Never authorize Apps Script code that you do not understand or trust.

AI-generated code should also be reviewed before it is executed.

What If You See an “Unverified App” Warning?

Depending on the services, OAuth scopes, account configuration, and how the application is distributed, Google can display an additional warning for an application that has not gone through Google's verification process.

For your own development project, this does not automatically mean the code is malicious.

However, you should never bypass a security warning for code from an unknown source.

Review the script first.

If you plan to distribute an application publicly, OAuth verification requirements can become more important, especially if the application uses sensitive or restricted permissions.

Step 7: Check the Generated Database

Once authorization is complete, run setupDatabase() again if necessary.

Wait until the execution finishes successfully.

Return to Google Sheets.

You should now see the database sheets created by the setup function.

Open each sheet and inspect the data.

Make sure:

• Headers exist

• Sample products exist

• Sample stock movements exist

• Settings exist

• IDs look correct

• No obvious errors appear

At this point, our backend database is ready.

Step 8: Generate Index.html

Now return to ChatGPT.

Ask for the second file.

Code.gs has been added successfully and setupDatabase() completed without errors.

Please generate Batch 2: Index.html.

Follow exactly the architecture we approved earlier.

Give me the complete file without placeholders or omitted sections.

Copy the generated code.

Inside Apps Script, click the + button next to Files.

Choose HTML.

Name the file:

Index

Apps Script will create:

Index.html

Paste the code and save it.

What Is Index.html For?

Index.html contains the basic structure of the application's interface.

Think of it as the skeleton of the web application.

It can contain elements such as:

• Sidebar

• Navigation

• Dashboard containers

• Product page

• Inventory page

• Reports

• Settings

• Modals

• Tables

• Forms

The visual styling will be handled mainly by our next file.

Step 9: Generate Styles.html

Ask ChatGPT:

Index.html has been added successfully.

Please generate Batch 3: Styles.html.

Make the interface modern, responsive, professional, and consistent with the UI/UX plan we approved.

Give me the complete Styles.html file.

Create another HTML file in Apps Script.

Name it:

Styles

Paste the generated code into Styles.html.

Save the project.

What Is Styles.html For?

This file controls the visual appearance of the application.

It may control:

• Colors

• Fonts

• Cards

• Sidebar

• Buttons

• Tables

• Forms

• Modals

• Responsive layout

• Mobile behavior

• Spacing

• Dashboard design

• Hover effects

Separating CSS from the main HTML makes the application easier to maintain.

Step 10: Generate Scripts.html

Now request the final file.

Styles.html has been added successfully.

Please generate Batch 4: Scripts.html.

Make sure it connects correctly with Code.gs and all existing HTML element IDs and functions.

Include all client-side logic required for:
- Navigation
- Dashboard
- Products
- Stock movement
- Filters
- Forms
- Modals
- Reports
- Settings
- Activity logs

Give me the complete Scripts.html file without omissions.

Create another HTML file named:

Scripts

Paste the code into it.

Save everything.

Your Apps Script project should now contain:

Code.gs
Index.html
Styles.html
Scripts.html

What Is Scripts.html For?

Scripts.html normally contains the browser-side JavaScript.

It controls how users interact with the application.

For example, it can handle:

• Button clicks

• Opening modals

• Closing modals

• Changing pages

• Sending form data

• Loading tables

• Applying filters

• Displaying dashboard data

• Calling server-side Apps Script functions

Google Apps Script applications commonly use client-side calls to communicate between HTML interfaces and server-side Apps Script functions.

Step 11: Check for Errors Before Deployment

Before deploying, save all four files.

Look for obvious errors inside the Apps Script editor.

You can also ask ChatGPT to perform a consistency check.

For example:

All four files are now installed.

Before I deploy the application, review the architecture of all four files we generated.

Check for:
- Missing functions
- Mismatched HTML IDs
- Function naming inconsistencies
- Missing includes
- Incorrect server/client calls
- Syntax issues
- Missing report functions
- Missing database functions

Do not rewrite the whole application unless necessary.

Tell me exactly what should be corrected before deployment.

This extra step can prevent simple mistakes from causing problems later.

Step 12: Deploy the Inventory Management System as a Web App

Once the code is ready, we can turn it into a web application.

In Apps Script, click:

Deploy → New deployment

Then select the deployment type:

Web app

You will then see deployment configuration options.

Add a description such as:

Inventory Management System MVP

You will also need to choose how the web application runs and who can access it.

The options available can depend on your Google account or Workspace configuration.

For a personal internal application, select the access configuration appropriate for your account and security requirements.

Do not make private business inventory data publicly accessible unless that is genuinely what you intend.

Click Deploy when ready.

Google may ask for authorization again if the deployment requires permissions you have not already granted.

Why Does Apps Script Ask About Who Executes the Web App?

Apps Script web apps can run under different identities depending on the deployment configuration.

This matters because it affects whose permissions and data the application can access.

For an inventory system, you should think carefully about:

• Who should use the application?

• Should every employee have access?

• Should users only use a company account?

• Should the application operate using the owner's access?

• Does the app contain confidential inventory information?

Security should be part of the design, not something added only after the application is finished.

Step 13: Open the Web App URL

After deployment completes, Apps Script provides a web app URL.

Copy the URL.

Open it in a new browser tab.

If everything has been configured correctly, the inventory dashboard should appear.

Because we created sample data earlier, the application should already contain information.

You may immediately see:

• Product totals

• Inventory value

• Low-stock products

• Stock movements

• Charts

• Product records

Screenshot: 

Congratulations.

You have now turned a Google Sheet into an inventory management web application.

Step 14: Test the Product Module

Do not replace your seed data immediately.

Test the application first.

Open the Products menu.

Try adding a new product.

For example:

Product Name: Wireless Keyboard
SKU: KB-001
Category: Accessories
Cost Price: 20
Selling Price: 35
Opening Stock: 15
Minimum Stock: 5
Unit: pcs
Status: Active

Save the product.

Return to your Google Sheet and confirm that the new record appears in the correct database sheet.

Step 15: Test Stock In

Go to Inventory or Stock Movement.

Select Stock In.

Choose one of your products.

Enter a quantity and reason.

For example:

Quantity: 20
Reason: Supplier Purchase
Notes: Restock from main supplier

Save the transaction.

The current stock should increase.

A new transaction should also be recorded in the stock movement database.

Step 16: Test Stock Out

Repeat the test using Stock Out.

For example:

Quantity: 3
Reason: Customer Order
Notes: Order INV-001

After saving, verify that:

• Stock decreases

• The transaction appears in movement history

• Dashboard totals update

• The activity log records the action

Step 17: Test Inventory Adjustment

Inventory adjustments are useful when the number inside the system does not match the real physical quantity.

For example, the system might show 20 units, but a warehouse count finds only 19.

An adjustment allows you to correct the difference while recording why the change happened.

Possible reasons include:

• Damaged product

• Lost inventory

• Counting correction

• Expired product

• Data entry correction

Always include a clear note when making inventory adjustments.

This makes your stock history easier to audit later.

Step 18: Test Low-Stock Alerts

Choose one sample product.

Reduce its stock until it falls below the minimum stock level.

Return to the dashboard.

The product should now appear in the low-stock area if this feature was implemented correctly.

This is one reason seed data is useful.

It allows us to test different scenarios without using real business inventory.

Step 19: Test the Reports

Open the Reports menu.

Choose a date range.

Optionally select a product or transaction type.

Click Generate Report.

Check whether the correct stock movement records appear.

Then test the PDF function.

The generated PDF should ideally include:

• Business information

• Report title

• Date range

• Product or transaction filters

• Stock movement table

• Totals

• Generated date

Step 20: Configure Your Business Information

Open Settings.

Replace the sample information with your real company information.

For example:

Business Name: ABC Trading
Address: 123 Business Street
Phone: +1 555 000 000
Email: hello@example.com

Save the settings.

Generate another report.

The updated business information should now appear in the PDF if the report template was designed to use these settings.

Step 21: Check the Activity Logs

Open Activity Logs.

You should now see several actions from your testing.

For example:

Product Created
Stock In
Stock Out
Stock Adjusted
Report Generated
Settings Updated

Activity logs become especially useful when several people use the same inventory system.

Step 22: Replace the Seed Data

Only remove sample data after you have tested the system properly.

Before deleting anything, make a backup of the spreadsheet.

Then remove the sample products and transactions according to the database structure created by your script.

Be careful not to delete:

• Header rows

• Configuration values

• Required formulas

• Settings structure

If you are unsure, ask ChatGPT which rows are safe to remove based on the final Code.gs implementation.

How the Inventory Management System Works

At this point, it helps to understand the full flow.

A simplified version looks like this:

User
↓
Web App Interface
↓
Index.html + Styles.html + Scripts.html
↓
Google Apps Script Functions
↓
Code.gs
↓
Google Sheets
↓
Products / Movements / Settings / Logs

When a user submits a Stock In transaction, for example:

1. The user fills in the Stock In form.

2. Scripts.html collects the form data.

3. The browser sends the data to an Apps Script function.

4. Code.gs validates and processes the transaction.

5. The transaction is written to Google Sheets.

6. Product stock is updated.

7. An activity log can be created.

8. The interface refreshes with the new values.

This is how a spreadsheet becomes more than a spreadsheet.

Google Sheets stores the information, while Apps Script turns that information into an interactive application.

Why Use Google Sheets for an Inventory Management MVP?

Google Sheets has several advantages for a small MVP.

Easy to Inspect Data

Your data remains visible inside a familiar spreadsheet.

You can inspect records without building a separate database administration system.

Fast to Develop

Google Sheets and Apps Script work together closely.

That makes them useful for prototypes and internal business tools.

No Separate Server Required

Apps Script handles much of the hosting and execution environment for the application.

Easy to Customize

Because you control the spreadsheet and code, you can customize the workflow around your business.

However, Google Sheets is not a replacement for every type of production database.

As your inventory, traffic, concurrency, or security requirements grow, you may eventually need a more scalable architecture.

For an MVP or smaller internal system, however, it can be a practical starting point.

Important Security Tips

An inventory system may contain sensitive business data.

Use a few basic precautions.

Review AI-Generated Code

Never blindly run code simply because ChatGPT generated it.

Read it first.

Check what Google services it accesses.

Review Requested Permissions

When Google shows an authorization screen, read the permissions before approving them.

Limit Web App Access

Do not give public access to an internal inventory application unless public access is actually required.

Keep Backups

Make regular copies of the spreadsheet, especially before making major code changes.

Avoid Storing Secrets Directly in Client-Side HTML

Passwords, private API keys, and sensitive tokens should not be exposed inside browser-side code.

Troubleshooting Common Problems

setupDatabase Does Not Appear

Save Code.gs first.

Then refresh the Apps Script editor and check the function dropdown again.

Also confirm that the function name is actually:

setupDatabase

Google Requests Authorization

This is expected when the script uses Google services that require permission.

Review the permissions and authorize only code that you trust.

Dashboard Is Empty

Check whether setupDatabase() successfully inserted sample data.

Also confirm that the sheet names used by Code.gs match the sheets in your spreadsheet.

Buttons Do Nothing

Check the JavaScript console in your browser.

Also verify that functions in Scripts.html use the same names and HTML element IDs defined in Index.html.

Data Is Not Being Saved

Check the Apps Script execution history for errors.

Make sure the server-side function exists and that it is writing to the correct spreadsheet and sheet.

Changes Do Not Appear in the Live App

If you changed the source code after creating a deployment, make sure your deployed version reflects the latest code.

During development, you can also use Apps Script's web app testing tools before updating the production deployment.

PDF Report Does Not Generate

Check that the report function has permission to use any Google services required by its implementation.

Also inspect the Apps Script execution log for errors.

How to Improve the Application After the MVP

Once the basic version works, there are many ways to expand it.

You could add:

• Supplier management

• Purchase orders

• Sales orders

• Barcode scanning

• Product images

• Multiple warehouses

• User roles

• Approval workflows

• Inventory transfer

• Batch numbers

• Expiry dates

• Email alerts

• Automatic reorder reminders

• Scheduled reports

• Import and export

• Advanced analytics

Do not add everything at once.

Build the MVP first.

Test it with real workflows.

Then add features based on actual problems instead of assumptions.

Final Thoughts

Building an inventory management system in Google Sheets is a good example of how Google Apps Script can turn a simple spreadsheet into a useful business application.

You do not need to write every line manually.

ChatGPT can help with architecture, database design, frontend code, backend functions, and debugging.

The key is to use AI in stages.

Start with requirements.

Review the architecture.

Generate one file at a time.

Run the database setup.

Understand the authorization request.

Test each feature.

Then deploy the application as a web app.

This approach makes the project much easier to manage than asking AI to generate a huge application in a single response.

Once the MVP is stable, you can continue improving it with suppliers, purchase orders, barcode scanning, multi-location stock, automated alerts, and more advanced reporting.

The result is not just a Google Sheet.

It is your own lightweight inventory management application powered by Google Sheets and Apps Script.