Weekly Square restock brief, one message per supplier

Every Monday at 7am, build a supplier by supplier reorder brief from my Square inventory and post it to Slack, one message per supplier.

Start by getting my locations from Square with List Locations, then pull my full product list with List Catalog, covering items and item variations including the vendor recorded against each variation. Get current stock on hand for every tracked variation with Batch Retrieve Inventory Counts.

Work out how fast each item is genuinely selling instead of relying on a flat stock threshold. Use Search Orders to pull completed orders and their line items across the trailing 30 days, add up units sold per variation, and turn that into an average daily sales rate. Then calculate days of cover for each item as current stock on hand divided by that daily rate. Rank the reorder list by days of cover ascending, so the items about to run out come first rather than the items that merely have a small number on hand. Note that orders taken offline on Square Point of Sale can take up to 72 hours to appear, so treat very recent figures as provisional.

Ignore discontinued and seasonal lines. Run a second Search Orders pass over the trailing 60 days and drop any item that has sold zero units in that window. Also skip items Square does not track stock for, since there is no meaningful cover figure for them.

Group the results by supplier so each message maps to one purchase order. Use Search Vendors to list active vendors and Bulk Retrieve Vendors to pull details for the vendor IDs attached to the items being reordered. If an item has no vendor recorded in Square, collect those lines into a final unassigned supplier section rather than dropping them.

For each line, recommend an order quantity. Order enough to bring the item back up to roughly 45 days of cover at its current sales rate, rounded to a sensible whole number. Use your judgement on the edge cases: items with only a few days of sales history, items where one large spike order is distorting the average, and items already sitting on plenty of cover that need nothing this week. Flag anything already at zero stock as urgent.

Post one message per supplier to the #purchasing channel using the Slack Bot Send a Message action. Lead with the supplier name and how many lines need ordering, then give a compact list showing item name, current stock, average units sold per day, days of cover, and recommended order quantity. Put the urgent out of stock lines at the top. Keep each message short enough to act on without opening anything else.

Finally, append every recommended reorder line to the tracking tab of my purchasing Google Sheet using Append Values, so purchasing keeps a running history. Write one row per line with the run date, supplier name, item name, variation, current stock, units sold in the last 30 days, average daily sales, days of cover, and recommended order quantity.