DEV Community

Financely Templates
Financely Templates

Posted on

A Google Sheets Meal Planner That Builds Your Grocery List Automatically

#googlesheets #spreadsheets #tutorial #productivity

Planning meals saves money, but only if the grocery list is painless. Here's a two-tab sheet where you pick meals for the week and the shopping list (with an estimated cost) fills itself in.

Tab 1: Recipes

One row per ingredient, per recipe:

A: Recipe B: Ingredient C: Qty D: Unit E: Cost per unit
Chili Ground beef 1 lb 4.50
Chili Kidney beans 2 can 0.89
Tacos Ground beef 1 lb 4.50

Tab 2: Week

  • A2:A8: Monday to Sunday.
  • B2:B8: dinner. Add a dropdown with Data > Data validation > Dropdown (from a range) pointing at =UNIQUE(Recipes!A2:A).

The grocery list

In Week!D2, list every ingredient needed this week, combined:

=QUERY(
  FILTER(Recipes!B2:E, COUNTIF(B2:B8, Recipes!A2:A)),
  "select Col1, sum(Col2), Col3 group by Col1, Col3 label sum(Col2) 'Qty'", 0)
Enter fullscreen mode Exit fullscreen mode

COUNTIF(B2:B8, Recipes!A2:A) returns how many nights each recipe row is planned. FILTER treats that count as true or false, so chili twice in a week still shows the ingredients only once. To scale for repeats, swap to the version below.

Scaling for repeat meals

Add a helper column F in Recipes:

=ARRAYFORMULA(IF(A2:A="",, C2:C * COUNTIF(Week!B2:B8, A2:A)))
Enter fullscreen mode Exit fullscreen mode

Then point the QUERY at Recipes!B2:F and sum Col5 instead of Col2.

Estimated cost 

=SUMPRODUCT(Recipes!E2:E, Recipes!F2:F)
Enter fullscreen mode Exit fullscreen mode

That's your weekly grocery estimate, and you can compare it against your food budget line.

Done

Change the dropdowns each Sunday and the list rebuilds.

If you'd rather grab a ready-made version, I make templates as Financely Templates (disclosure: these are my products):

Top comments (0)