Turn last week's cafe sales into this week's grocery order

Build me an app for my cafe that turns what we actually sold into this week's grocery run, so I stop walking the shelves with a clipboard and guessing. The people using it are me and whoever does the ordering, and we open it once or twice a week just before placing the order.

The main view is the order planning table. When it loads, pull the last 7 days of completed sales from Square using Search Orders for my chosen location, then resolve every order line item to a menu item using Square Search Catalog Items so I get real menu item names and counts sold. Then read my recipe bill of materials from Google Sheets using Batch Get Values: one tab maps each menu item to its raw ingredients with a quantity and unit per serving, and another tab holds the current on-hand count and par level for each ingredient. Explode the week's sold menu items through the bill of materials to get projected ingredient usage for next week.

Show one row per raw ingredient with projected usage for next week, current on hand, par level, the resulting shortfall, and the quantity to order. Anything below par gets flagged and sorted to the top so it is impossible to miss. Every quantity cell is editable inline, because I know when a catering order is coming and the raw sales number will be wrong. Keep my edits as overrides that survive a refresh of the sales data, and mark clearly which rows I have touched. Also show any sold menu item that has no rows in the bill of materials tab in a small 'not mapped' list, so I can see what the projection is missing instead of silently under-ordering.

Units matter here. Instacart only accepts a limited set of measurement units (each, lb, oz, g, kg, ml, L, cup, tbsp, tsp, gallon) and returns a 4xx on anything else, so normalize the bill of materials units into that set during the explosion, converting where needed. Also round up to quantities I can actually buy: I cannot order 1.3 onions or 0.4 of a milk jug, so round each ingredient to a sensible purchasable amount and show me the rounded number in the table.

A 'Build the order' button takes every flagged ingredient at its current quantity, where my override always wins over the projection, and calls Instacart Create Shopping List Page, grouping the items into sensible store sections like produce, dairy, dry goods and paper goods. Reference each line item by name. Note that product_ids and upcs are mutually exclusive on the same line item, so never send both. Show me the returned one-click cart link prominently. Cache the returned products_link_url with the order record and show the cached link rather than calling Instacart again on every page load; only generate a new link when I build a new order or the existing link has expired.

Right after the cart is created, append the order to a log tab in the same spreadsheet using Google Sheets Append Values: the order date, each ingredient with its ordered quantity and unit, and the cart link. That log is what lets me compare week over week.

Add a second view for order history that reads the log tab and lists past orders newest first, each one expandable to its line items with its cart link, so I can see what I bought and when.

Add an 'Explain the changes' button that kicks off a background agent. The agent pulls several weeks of Square sales history with Search Orders, resolves the items through Search Catalog Items, reads the order log and the bill of materials tabs with Batch Get Values, and compares this week's projection against that history. It writes a short plain-language note back into the app covering which ingredients are trending up or down, what I consistently over-order (ordered heavily while actual usage stayed flat), and which menu items are driving the biggest swings. Show the note in the app with the date it was written, keep previous notes readable underneath, and show live progress while the agent runs rather than a frozen button.

Give me a settings view where I can set the spreadsheet, the tab names for the bill of materials, the on-hand and par tab, the order log tab, the Square location, and the sales lookback window (default 7 days), so none of that is hard-coded.