SQL-like Queries in Spreadsheets

The App

2

The three types of macronutrients (macros) are carbohydrates (carbs), fat, and protein.

My use-case is simple: I have daily macronutrient2 targets, and I want to plan a full day of meals to achieve those targets.

3

A food’s macro breakdown comprises its total caloric value. The exact relation is kcal = 4c + 4p + 9f, where c, p, and f are grams of carbs, protein, and fat, respectively.

For example, if I want to lose weight, then I might have minimums for fat and protein, and a maximum for total caloric value.3

When I first started planning meals like this way back when I was in high school, I would start by sketching up a day’s meals without concrete amounts, e.g.:

eggs and oats for breakfast

steak and yams for lunch

chicken and broccoli for dinner

Then I would itemize each food used in the plan, along with a breakdown of its macros per gram. Continuing with our example, that might look something like this:

          C       F       P

eggs      0.01    0.11    0.13
oats      0.66    0.07    0.17

steak     0       0.07    0.28
yam       0.28    0.02    0

chicken   0       0.04    0.31
broccoli  0.07    0       0.02

Actually it would look exactly like that because I just did it in Notepad.

And finally, using a calculator, I would start quantifying my foods, calculating the macros those quantities would yield, and summing them over the day. I would then tweak the quantities and repeat the process until the sums were close enough to the targets for my satisfaction.

The final plan might look something like this:

                             C      F      P     kcal
8    large   egg             4     44     52      620
45   gram    oats           30      6      4      178
1    tbsp    butter          0      8      0       72

1    whole   round steak     0     16     63      393
200  gram    yam            56      4      0      260

1    whole   chicken breast  0      9     70      360
1    cup     broccoli       12      0      4       63

Total                       98     87    193     1946

Notice that I am using alternate units in some cases, e.g. a cup of broccoli is 175g.

So, what I wanted my app to do was allow me to enter just

1. a food name (e.g. egg, rice),

2. a unit of that food (e.g. gram, cup), and

3. a quantity of that unit (e.g. 100, ½),

to see the corresponding macro totals.

It turns out we can fully realize this functionality by (ab)using some Google Sheets functions.

SQL-like queries in Google Sheets

Here’s an example spreadsheet for doing my kind of meal planning.

4

You can switch between sheets at the bottom left.

It’s actually three sheets working together: PLAN, FOODS, and UNITS.4

The FOODS and UNITS sheets are like tables in a relational database, defining foods and units of food respectively.

The PLAN sheet “queries” these tables to find food and macro weights based on our input (which is what I want to showcase with this post).

To see how this all works, we can just focus on one particular plan row. Let’s look at how the macros for the breakfast oats are calculated.

In an attempt to make the spreadsheet easier to visually navigate, I have made all input cells yellow, and output cells blue.

So, for breakfast oats, we have specified

• quantity = 0.5,

• unit = cup, and

• food = oats.

To figure out how many grams of oats that is, we need to look for a row in UNITS that has oats in the food column and cup in the unit column. In SQL that might look something like

SELECT grams FROM units WHERE food='oats' AND unit='cup'

5

Any cell or row in the formula prefixed by a $ will stay fixed if you copy the formula to another cell. I do this so I can simply insert rows to add more foods and have every keep working.

The tool that makes this possible here is the DSUM function. Here’s the exact formula for the grams cell (G3):5

=if(
    OR(isblank($D3), $D3 = "gram"),
    1,
    dsum(UNITS!$A:$C, UNITS!$C$1, {$D$1,$E$1;$D3,$E3})
) * ($C3)

For reference, these are the exact PLAN cells being used:

The outer if statement is just saying “if the unit is blank or gram, then return 1” so that we don’t have to define a gram unit equal to 1 gram for every single food. Otherwise, it returns the result of the DSUM:

dsum(
    UNITS!$A:$C,
    UNITS!$C$1,
    {$D$1,$E$1;$D3,$E3}
)

The arguments to the DSUM query are:

• UNITS!:$A:$C, specifying the entire UNITS sheet as the table to query.

6

The DSUM is actually doing a SELECT SUM(grams) WHERE …, but as long as we don’t duplicate rows in the UNITS sheet that won’t matter.

• UNITS!$C$!, specifying the grams column in the UNITS sheet as the aggregate field.6

7

The syntax here looks weird, but it is building a 2x2 table to specify the criteria. Read it row by row: $D$1,$E$1 is unit, food, and $D3,$E3 is cup, oats.

• {$D$1,$E$1;$D3,$E3}, specifying food = oats and unit = cup as the criteria to select on.7

So, the DSUM will look at the UNITS sheet, find all rows that have oats and cup in the food and unit columns respectively, and return the sum of their grams values.

Finally, the result of the if statement is multiplied by the quantity (C3) to yield the total grams.

Once we know how much food we have, we can figure out its macros by multiplying the weight of the food by the percentage of weight each macro comprises (which is precisely what is defined in the FOODS sheet).

Because we only need to match on one value, i.e. food = oats, we can use the simpler SUMIF function. This works just like DSUM but only allows specifying criteria on a single field.

All macros work the same, so lets just focus on carbs.

Here’s the exact formula for the carbs cell (H3):

=sumif(
    FOODS!$A:$A,
    $E3,
    FOODS!G:G
) * $G3

The arguments to the SUMIF are:

• FOODS!$A:$A, specifying the food column in the FOODS sheet as the field to apply criteria on.

• $E3, specifying oats as the criteria.

• FOODS!G:G, specifying the carbs/g column of the FOODS sheet as the field to aggregate.

8

Duplicate entries for a food or unit would mess things up, but that’s easy enough to avoid for our use case.

So, this will look through all rows in the FOODS sheet, summing precisely the carbs/g values on rows where the food value is oats. As long as we avoid duplicate entries, this works to return precisely our food’s macros.8

We can calculate all macros like this, and the rest of the spreadsheet is straightforward summing and cell references.

You can see my personal sheet on my website here. I don’t know why you would want to, but you can.