CODEWITHSUNDEEP
google-sheets
DRing
DRing

Reputation: 7039

Select Random item from list in Google Sheet

Im working on a google sheet document to randomly pick a protein and a side dish for weeknight meals based on a selection. The idea is you select your meat (chicken, beef, pork, etc) and the max amount of calories for the meal and it will select a protein and side dish randomly from a list. Not sure how to go about writing either the equation or a script to do this though. Heres what im working with so far:

This is the main page where you select what you want

Selector

This is the protein sheet that will have all the possible selections, so based on the image above would want to randomly pick a chicken dish with 800 or less calories Protein Sheet

And finally this is the side, for this I would want to choose a side that is less than the remaining calories, so if the protein had say 500 calories would want a side with 300 or less:

Sides

Upvotes: 0

Views: 4334

Answers (1)

Ed Nelson
Ed Nelson

Reputation: 10259

On the Proteins sheet add E3 =randbetween(1,100) and copy the formula down. On the Side/Vegetables add C3 =randbetween(1,100) and copy down. On the Dinner Generator sheet add this query to A8:

=query(Sheet2!A3:E,"select A,B where D contains '"& A3 &"' and B <= "& B3 &" order by E desc Limit 1")

and this to A9:

=query(Sheet3!A3:C,"Select A,B where B <= ("& B3 &" - "& B8 &") order by C desc Limit 1")

And this to A10:

=B8+B9

I named the sheets Sheet1, Sheet2, and Sheet3. Change the sheet names in the queries as needed. I also used desc in the queries so blanks would be at the bottom allowing the lists to be expanded without having to change the queries. I suggest you change fish entries in column D to Fish instead of Tuna, Salmon, etc. Below is a shared example spreadsheet. Make a copy to test.

https://docs.google.com/spreadsheets/d/17SWtKfzDFDV8OeffR7s5PcmmwRMzu6UKzWK26CX7KDU/edit?usp=sharing

Upvotes: 2

Related Questions