Software for a Small Business

Unlike any of the "tools I've built to make life easier" posts, I own all of the code for this one. In my spare time on a personal computer I built a software backend for a small local business that lets the owner manage their inventory with much less manual work. Because I use a PC and Excel and the end-user uses a Mac and Numbers, we met in the middle and built the tool using Google Sheets. I taught myself Google Apps Script (GAS), which is a form of JavaScript to create similar automation as my previous experience with Microsoft Visual Basic for Applications (VBA), and because it was cloud-based the tool is actually more versatile: it runs on mobile devices and interfaces directly with Square, which the business uses for Point of Sale (POS) management.

Previous posts in this series:

Headline improvement: from 1 day to 30 minutes (90%)

My client Little Babet is a small business in Chagrin Falls, Ohio. They sell "modern children's clothing and accessories for happy healthy little babets": typically US-manufactured, sustainable, and/or organic products. They use Square to track their inventory and make sales both in-store and online. Square does not track what they've ordered from suppliers but have not yet received or the wholesale cost of the items, and getting each item into Square (and therefore available for sale) is a laborious process involving multiple screens of data per item. Because Little Babet sells clothing, much of its catalog changes from year-to-year and new items need to be created every year or even every season.

In addition, each of those items must be labeled with Little Babet's logo, a stock-keeping unit (SKU) identifier, the price, and a barcode. The owner also did this manually by creating a comma-separated value (CSV) file each time she received a shipment and manually duplicating each line until she had the right number of repetitions for the number of items she received (i.e. received 5 of a size 4T shirt, count and create 5 copies of the label and put them in the CSV file).

All told, a large shipment could easily take her a day to properly receive, and those items couldn't be sold until they were in the system.

Phase 1: labels

I started with making label-making an automatic process and left Square integration for later. But to know what labels were to be created, I had to reach further back in the process all the way to "order from the supplier". Side benefit: now she would have a record (in one place) of the wholesale cost of every item when it was received. This meant I needed five tabs in my Google Sheet:

Data: a single flat-file database sheet that every other tab reads, writes to, or both.
Order entry: when the order is placed with the supplier, enter the item, size/variation, description (for the Little Babet website), quantity, wholesale cost, sale price, whether the item is taxable (e.g. strollers are not in Ohio), and the SKU. Hit a button and the order gets pushed to the "Data" tab
Receipts: enter a vendor and receipt date and the sheet will populate with every open order / item from that vendor, as well as the order quantity. The owner can now go down the list and either check a box ("Quantity received = quantity ordered") or mark an exception: a different quantity than expected, Little Babet canceled an order or item, the supplier canceled it, one order got split into two shipments, etc. Hit a button and the receipts get pushed to the "Data" tab including the quantity received and the receipt date.
Labels: Hit a button and every item without a label gets pushed into a CSV-friendly format where the owner can copy and paste them into her label-creating template with all quantities, etc. automatically generated. This originally pulled only those items marked as received, but the owner actually uses the labels to verify shipment quantities: if she has too many or too few labels, that means that the shipment isn't as-expected. So labeling sometimes drives receipts rather than vice-versa.

All of these sheets include Google Apps Script code to push or pull updates as needed, as well as some formulas on the sheets themselves (e.g. to create SKUs) and some in-sheet SQL "Select" queries (e.g. on the "Receipts" tab to populate the open items from a vendor. The other trick was making this work on mobile, which was a strongly-desired feature because the owner is often capturing orders and receipts on her phone or tablet. Google Sheets does not allow push-buttons to activate macros on mobile, but the macro can monitor whether a checkbox is checked on each sheet. If the user checks the box, the macro runs and un-checks the box as the final step.

Phase 2: Square integration

Square integration was the step where I learned the most. I knew (roughly) how the label-making would work, even if I was writing in an unfamiliar language. But I had not worked with APIs and other cross-company tools before, so I had several false starts before I reached a method that worked. The tool needed to:

Identify whether a received item was new to the Square catalog and needed to be entered
Adjust inventory up by the received quantity

Eventually I decided on a four-step process: read the current catalog and inventory counts, and then write updates to both as-needed.

Read

On command, the tool pulls the entire catalog of every item Square has available for sale by Little Babet. The tool needs to run about 50 queries because Square only returns 100 items per "page" and the catalog is much bigger than that. This whole process takes about 30 seconds. Early on we experimented with using Square's "export catalog to spreadsheet" function from their dashboard and that took much longer.

With the catalog pulled, the tool also pulls the count of each item in the inventory and associates it with the catalog item. This is critical because Square uses an internal unique identifier to track inventory, while Little Babet and the tool want to track it by SKU.

By pulling all this data into the spreadsheet, the tool also allows the owner to check with a quick in-sheet search if she has stocked this item before and avoid creating a duplicate item with a different SKU.

Write

The workbook has a "Square Upload" sheet which uses a SQL query to identify every item on the "Data" tab marked as not yet in Square. This gives the owner a chance to verify everything before it changes her catalog and inventory. She hits a button and the macro updates every changed catalog item and inventory count to match the spreadsheet. That includes pulling the current inventory and adding the newly-received quantity rather than assuming that the current inventory level is 0. This process takes less than 30 seconds.

Conclusion

The big manual tasks for receipt-entry are now automated and take 30-45 minutes from start-to-finish while also retaining more data for record-keeping than before. With that 90% time savings, the owner can spend her time on managing her employees, communicating with suppliers (including having more detail at her fingertips about what she ordered from each), and finding new suppliers to keep her selections fresh.

This initial customization took a few months, but with more time to focus and having learned lots of lessons on this first version, I could customize a Square backend for any similar business pretty quickly. And people keep making noises that I should learn Python too...

If you have any comments, please reach out to me at blog@saprobst.com or this page is cross-posted at LinkedIn and you can leave a comment there.

Search