Using Google sheets to build a very simplistic Inventory Management solution for your small business.
Before I dive into the process of building a Google sheets solution for a very simplistic Inventory Management system, I thought I would just provide the reasons for selecting Google sheets over other similar options like Excel and others.
The factors that made me lean towards Google sheets:
- Most people today have a Google account and they can access the basic free versions of Sheets, Form, Data Studio, etc, which are sufficient for small business requirements. Other usually charge a subscription fee.
- Google sheets allows multiple users to edit or view a sheet by simply setting the permissions in the Share option and sharing the file with other Google accounts.
- Google sheets has Apps Script which extends its functionalities — allowing the automation of tasks, creation of UIs, webapps, add-ons, etc. This makes it a very powerful tool to create customized solutions for your needs.
- Google sheets has an API library which allows you to connect the sheet with other softwares.
- Google sheets is further extendable by connecting it with libraries available on the net.
- Google Apps script allows Javascript, HTML, CSS, Python and other scripting/programming languages for ease of use.
Now, I will dive into how I created a basic inventory management system using Google sheets.
What problem I was trying to solve?
My brother owns a retail automotive parts store. He asked me to take an inventory of the goods stored. Well, as there was currently no system to track the inventory, I decided to start taking the inventory on Google sheets as I could make the entries using my phone. I began with very simple columns of data like Product Name, Quantity, Stock Date.
After a week, I managed to finish taking down all the inventory and also had expanded my data columns, as I kept thinking about all the important information regarding the product, distinguishing between products with similar names but differing sizes/volumes/weight, entry date into the warehouse, exit date out of the warehouse, etc. My sheet grew to many columns which I further refined by keeping ones that were most important after discussions with my brother.
I applied the ‘Alternating colors’ option to the sheet from the ‘Format’ menu option on the sheet and highlighted the column that needs to be edited with ‘yellow’. This was done to avoid errors when employees who work on the sheet do no make mistakes while changing the data. Also, I protected the sheet from accidental changes by activating the ‘Protect Sheet’ option under ‘Tools’ option in the Menu bar.
My sheet looked finally like this:
I made other sheets in the same spreadsheet for other products like Mobil (Engine Oils/Hydraulic Oils/Gear Oils), Batteries and Inverters, Greases and Pipes.
Then, I thought, when a product’s quantity is reduced it can be assumed as a sale and when a product’s quantity is increased it can be assumed as a purchase. I then googled as to how to automatically capture data when the value of a cell is changed. I found the solution was to create an Apps Script Project and write Javascript based scripts to automate tasks.
I made two separate sheets of Purchase and Sale and wrote the script with help from resources on the internet and other codes written by people on the internet and understanding it and adapting it for my own purpose. Best resource: stackoverflow.com.
The sheet captures data from the product sheets when the Quantity column for the sheets changes. When quantity is reduced it captures the data in the ‘Sales’ sheet and when quantity is increased it captures the data in the ‘Purchase’ sheet, automatically.
The code for automating capture of data when cell value is changed can be accessed from this Github repository.
We also need to set up a trigger in Apps script to run this code. A trigger can be created by selecting the ‘Edit’ option from the Apps Script Project Menu bar and selecting “Current project’s triggers” from the list.
After setting up the project and editing the code and when you are ready, run the Apps script project. When the project is run for the first time, it will ask for authorization. Grant it permission by following these steps.
If you find this tutorial helpful for your project and would like to see more content like this and for any other queries drop me an email at vicky.lalani@gmail.com.
