I really want to lose some weight this month, but nutrition is hard, and it can’t run on instinct. So I made a spreadsheet to calculate calories and macros. I’m not a dietician or a nutritionist so my diet numbers should definitely not be trusted. I just followed examples and combined them to build my diet. What can be trusted is how I loaded the information from official government sources, and how I did my research.
The main reason I built it was because I found no free online macro diet planners that didn’t require account sign up. I found things that told me my macros, but nothing that suggested a diet to me or let me start trying to goal seek towards the suggested macros. I used a generic online macro calculator set to low carb. The next step was to locate a database of macro and nutrition information. Luckily the USDA publishes such a database, and someone kindly went to the trouble of converting it to sqlite.
I would much prefer to work with SQLite, because I can join tables, and perform calculations in a much easier fashion than in a bunch of spreadsheets, and joining things manually. I used the following query to compute the information to import into Google Sheets.
SELECT
food.id,
food.long_desc || ', serving=' || weight.description as food_item,
weight.gm_weight as weight,
(SELECT amount FROM nutrition WHERE nutrition.food_id = food.id AND nutrition.nutrient_id = 203) * weight.gm_weight / 100 as protein100g,
(SELECT amount FROM nutrition WHERE nutrition.food_id = food.id AND nutrition.nutrient_id = 204) * weight.gm_weight / 100 as fat100g,
(SELECT amount FROM nutrition WHERE nutrition.food_id = food.id AND nutrition.nutrient_id = 205) * weight.gm_weight / 100 as carbs100g,
(SELECT amount FROM nutrition WHERE nutrition.food_id = food.id AND nutrition.nutrient_id = 208) * weight.gm_weight / 100 as calories100g
FROM food
INNER JOIN weight ON weight.food_id = food.idThe database is organized in a relative normalized form. I wanted it
denormalized to include macros and calories per serving size. I find
that using serving size makes it easier for my monkey brain to get a
grip on the quantity. After all, how much really is 100 grams of eggs?
It’s not a human like quantity. I want to know the values for 3 eggs,
and they are unlikely to be perfect. I used correlated subqueries, since
that was easier than joining or pivoting any special way. The
nutrient_id fields come from the nutrient table which has specific
nutrients for every food item in 100 gram quantities.
This information was exported as a CSV and imported into a single sheet.
I then used =INDEX(MATCH(...)) statements to look up foods from this
table. I also had it extend quantities out and perform some
autocompletion via the data validation features of Google Sheets. The
autocomplete made it really easy to locate the macros and quickly create
a balanced meal plan.
If there’s any interest I can share my actual spreadsheet. Otherwise I think it’s a good exercise to go and build it yourself. The data munging is fairly interesting as is digging through the USDA’s data dictionary on this database. Future work could include automatic goal seek to balance out the macros or porting to use real excel for better performance. Google Sheets leaves something to be desired with autocomplete speed.