Coopers Recipe Spreadsheet

[Dank Meme Brewing]

Slammed with the day job again, but fingers crossed I get a bit of time over Easter to make a bit more progress. Am keeping track of all these issues/enhancements/suggestions separately, so they don't get lost.

• Spent a bit of time investigating support for "AND / OR / brackets" in the global query. Turns out it's trickier than I thought, with the Javascript table component I've chosen. There's a ticket open with that team to assess whether it's achievable, but being a free open-source project I'm not holding my breath. The alternative would mean migrating to a much larger paid component (more bandwidth, bigger downloads, licence fees, substantial effort to rewrite). Keen to keep it as lightweight & cheap as possible, so will park this one for the time being & look into some of the others for quicker wins.

 

On 3/4/2024 at 10:10 PM, Shamus O'Sean said:

• Possibly even expose the data source for live query from Excel (with a bit of Power Query ninjitsu, Excel gurus could still custom-build their own frontend exactly how they like, but there would only need to be one single source of truth for data, and everyone would always have the freshest data without needing to download another copy of the spreadsheet each time)  I am not sure I follow what you mean here, but I am sure some will be interested.

OK so the thinking with this one is, it should be possible to let users build any Excel sheet they want, with whatever functionality etc. but set it up so that the raw data in that sheet comes from the same place as the website (ie. the data is not embedded directly into the sheet itself - rather the sheet contains a query that sources the raw data off the internet & then it uses that data when displaying the Excel sheet to the user). Once the user has (live) raw data in the sheet they can then do whatever they want with it... maybe they want to add their own personal notes to recipes, attempt to back-calculate FG from ABV, augment it with additional columns like their own dates/batches brewed, add pivot tables/graphs, whatever their hearts desire (and their Excel skills permit). This allows everyone to standardise on a single, common centrally-maintained "core" dataset but then add their own personal tweaks on it around the edges.

As an example: if we converted your spreadsheet to follow this approach, it should still look/feel/function exactly the same as it does today. However, if a simple data change needed to happen (eg. new recipe added, or error in an old recipe fixed) you would not necessarily need to publish an updated version of the sheet... Anybody who already had the spreadsheet would get the latest data for free, without needing to come into this thread & download another copy of the spreadsheet. Update the data in one central place, and then it's immediately available for everyone everywhere. You would still want to publish new versions of the spreadsheet if you added new features/tabs, new functionality, or made major changes (eg. added new columns)... but for a simple recipe addition/correction, a republish of the spreadsheet itself would no longer be necessary.

Prerequisites for even considering this would be:

• getting the web data editable
• and possibly considering adjustments to the data model, so it's relatively stable & unlikely to introduce breaking changes going forwards

So a "medium term" goal at the earliest.

Edited March 26 by Dank Meme Brewing

[Shamus O'Sean]

5 hours ago, Dank Meme Brewing said:

Slammed with the day job again, but fingers crossed I get a bit of time over Easter to make a bit more progress. Am keeping track of all these issues/enhancements/suggestions separately, so they don't get lost.

• Spent a bit of time investigating support for "AND / OR / brackets" in the global query. Turns out it's trickier than I thought, with the Javascript table component I've chosen. There's a ticket open with that team to assess whether it's achievable, but being a free open-source project I'm not holding my breath. The alternative would mean migrating to a much larger paid component (more bandwidth, bigger downloads, licence fees, substantial effort to rewrite). Keen to keep it as lightweight & cheap as possible, so will park this one for the time being & look into some of the others for quicker wins.

 

OK so the thinking with this one is, it should be possible to let users build any Excel sheet they want, with whatever functionality etc. but set it up so that the raw data in that sheet comes from the same place as the website (ie. the data is not embedded directly into the sheet itself - rather the sheet contains a query that sources the raw data off the internet & then it uses that data when displaying the Excel sheet to the user). Once the user has (live) raw data in the sheet they can then do whatever they want with it... maybe they want to add their own personal notes to recipes, attempt to back-calculate FG from ABV, augment it with additional columns like their own dates/batches brewed, add pivot tables/graphs, whatever their hearts desire (and their Excel skills permit). This allows everyone to standardise on a single, common centrally-maintained "core" dataset but then add their own personal tweaks on it around the edges.

As an example: if we converted your spreadsheet to follow this approach, it should still look/feel/function exactly the same as it does today. However, if a simple data change needed to happen (eg. new recipe added, or error in an old recipe fixed) you would not necessarily need to publish an updated version of the sheet... Anybody who already had the spreadsheet would get the latest data for free, without needing to come into this thread & download another copy of the spreadsheet. Update the data in one central place, and then it's immediately available for everyone everywhere. You would still want to publish new versions of the spreadsheet if you added new features/tabs, new functionality, or made major changes (eg. added new columns)... but for a simple recipe addition/correction, a republish of the spreadsheet itself would no longer be necessary.

Prerequisites for even considering this would be:

• getting the web data editable
• and possibly considering adjustments to the data model, so it's relatively stable & unlikely to introduce breaking changes going forwards

So a "medium term" goal at the earliest.

No worries.  Take all the time you need.  I have not even updated the spreadsheet with March's Recipes of the Month yet. 

[Triple B Brewing]

6 hours ago, Shamus O'Sean said:

No worries.  Take all the time you need.  I have not even updated the spreadsheet with March's Recipes of the Month yet. 

And @Shamus O'Sean with @Dank Meme Brewing suggested approach sounds like your monthly burden of updating a spreadsheet then posting might be slashed to some simple data entry once. 
Hoping the pdf’d recipes (provided for our international members) can also be included in DMB’s work. 
Gotta go, it’s brew day for me 

Cheers guy

[Shamus O'Sean]

Coopers Recipe Spreadsheet - March 2024

Hi Team

Latest Coopers Recipe Spreadsheet is attached.  March 2024 ROTM added: Same Same But Similar Pale Ale & Chocolate Eggstravaganza Stout.

Click on the recipe name in red for pdf's of these recipes.

Coopers Recipe Spreadsheet V3.03.24.xlsm

Spreadsheet functionality: 

• Links to Recipe artwork and pdf of most recipes at the far right. Handy if you want to make bottle or beer tap labels.
• You can click on the recipe name to jump to the recipe on the Coopers website (for those in au only)
• The little triangle icons beside each column title are auto-filters and can be used to sort the spreadsheet by the respective column.
• You can also use the drop boxes of the variables in each column to show those variables you want to see.  
• For example, under Difficulty if you choose the Easy variable then only those recipes rated as Easy will be displayed.
• Or under Can 1, if you choose Real Ale only those recipes that have the Original Series Real Ale (as the main recipe can) will be displayed.  
• Even better, in the Coopers Kits tab, you can see @Triple B Brewing's table "Recipes with multiple Extracts Used" to identify what Cooper's Kits are listed as Can 1, 2 and or 3 and in which recipes.  Use this table if you have a can of, say English Bitter, and you want to know what recipes you can make with it.
• TBB also brought over the worksheets of “Cooper's Kits”, “Legend” and “Colour” from @Thirsty Jim’s workbook and also found Thirsty’s Recipe Scaler so TBB revved that up a tad and also added it to the workbook.
• Any queries?  Post them to this thread.

[Triple B Brewing]

1 hour ago, Shamus O'Sean said:

Coopers Recipe Spreadsheet - March 2024

Hi Team

Latest Coopers Recipe Spreadsheet is attached.  March 2024 ROTM added: Same Same But Similar Pale Ale & Chocolate Eggstravaganza Stout.

Click on the recipe name in red for pdf's of these recipes.

Coopers Recipe Spreadsheet V3.03.24.xlsm 278.91 kB · 1 download

Spreadsheet functionality: 

• Links to Recipe artwork and pdf of most recipes at the far right. Handy if you want to make bottle or beer tap labels.
• You can click on the recipe name to jump to the recipe on the Coopers website (for those in au only)
• The little triangle icons beside each column title are auto-filters and can be used to sort the spreadsheet by the respective column.
• You can also use the drop boxes of the variables in each column to show those variables you want to see.  
• For example, under Difficulty if you choose the Easy variable then only those recipes rated as Easy will be displayed.
• Or under Can 1, if you choose Real Ale only those recipes that have the Original Series Real Ale (as the main recipe can) will be displayed.  
• Even better, in the Coopers Kits tab, you can see @Triple B Brewing's table "Recipes with multiple Extracts Used" to identify what Cooper's Kits are listed as Can 1, 2 and or 3 and in which recipes.  Use this table if you have a can of, say English Bitter, and you want to know what recipes you can make with it.
• TBB also brought over the worksheets of “Cooper's Kits”, “Legend” and “Colour” from @Thirsty Jim’s workbook and also found Thirsty’s Recipe Scaler so TBB revved that up a tad and also added it to the workbook.
• Any queries?  Post them to this thread.

Fantastic - As always many thanks @Shamus O'Sean

[Shamus O'Sean]

Coopers Recipe Spreadsheet - April 2024

Hi Team

Latest Coopers Recipe Spreadsheet is attached.  April 2024 ROTM added: Coopers Botanic Ale & Lemondrop Lager.

Click on the recipe name in red for pdf's of these recipes.

Coopers Recipe Spreadsheet V3.04.24.xlsm

Spreadsheet functionality: 

• Links to Recipe artwork and pdf of most recipes at the far right. Handy if you want to make bottle or beer tap labels.
• You can click on the recipe name to jump to the recipe on the Coopers website (for those in au only)
• The little triangle icons beside each column title are auto-filters and can be used to sort the spreadsheet by the respective column.
• You can also use the drop boxes of the variables in each column to show those variables you want to see.  
• For example, under Difficulty if you choose the Easy variable then only those recipes rated as Easy will be displayed.
• Or under Can 1, if you choose Real Ale only those recipes that have the Original Series Real Ale (as the main recipe can) will be displayed.  
• Even better, in the Coopers Kits tab, you can see @Triple B Brewing's table "Recipes with multiple Extracts Used" to identify what Cooper's Kits are listed as Can 1, 2 and or 3 and in which recipes.  Use this table if you have a can of, say English Bitter, and you want to know what recipes you can make with it.
• TBB also brought over the worksheets of “Cooper's Kits”, “Legend” and “Colour” from @Thirsty Jim’s workbook and also found Thirsty’s Recipe Scaler so TBB revved that up a tad and also added it to the workbook.
• Any queries?  Post them to this thread.

 

[Triple B Brewing]

As always, the maintenance of this resource for us is greatly appreciated @Shamus O'Sean