During the off-season, like many programs around the country, we are deep into our conditioning program. As a coach, few things make me as happy as watching our players push one another to develop both physically and mentally in our off-season strength and conditioning program.
While there are many different philosophies regarding strength and conditioning programs, our program focuses on progressions. Over a 6 week cycle, our players perform a 3 day split workout involving 4 core lifts (bench press, back squat, push press, and hang cleans) as well as a variety of complementary lifts. Players have a set routine for each day: Monday, Wednesday, and Friday. From week to week, we progress to higher weights and more repetitions. The amount of weight lifted by each player during each set is determined by a percentage of their max in that lift. For example, a player’s workout sheet may say the following for a given day:
In December 2014, I had the incredible opportunity to attend the Google Teacher Academy (GTA.) It was a transformative experience; two days of professional learning with 50 amazingly talented, innovative educators in one of the most creative environments imaginable. One of the central themes of GTA is "Moonshot Thinking"; using innovative and creative ideas to generate massive change. As much as it was about technology integration, GTA was also about changing our mindset to think differently about the problems we face. Being around 50 other passionate, motivated, and innovative educators was a powerful experience. Sitting on the flight back, I knew that I would start to integrate these ideas into my classroom as soon as I got back. And I did! However, a conversation I had over lunch with one of our lead learners, Amy Mayer, and another teacher/football coach/#GTAATX attendee, Jordan O'Donnell, sparked my interest in how technology could make our jobs as coaches easier. That discussion spurred my thinking about our weight room issue.
As a Google Apps For Education school, our students and faculty rely on Google products (Gmail, Drive, Docs, etc.) to foster collaboration in the classroom. Why couldn’t we use these same products to make our football program more efficient? By using Google Forms, Google Sheets, and a few "Add-ons", our students can now go to our team website, enter their maxes, and receive an automatically generated, individualized 6 week workout plan.
Before each 6 week lifting cycle, students go to our team website and click on the following link:
While there are many different philosophies regarding strength and conditioning programs, our program focuses on progressions. Over a 6 week cycle, our players perform a 3 day split workout involving 4 core lifts (bench press, back squat, push press, and hang cleans) as well as a variety of complementary lifts. Players have a set routine for each day: Monday, Wednesday, and Friday. From week to week, we progress to higher weights and more repetitions. The amount of weight lifted by each player during each set is determined by a percentage of their max in that lift. For example, a player’s workout sheet may say the following for a given day:
- Set 1: 5 reps at 75% of max bench press
- Set 2: 5 reps at 80% of max bench press
- Set 3: 5 reps at 85% of max bench press.
In December 2014, I had the incredible opportunity to attend the Google Teacher Academy (GTA.) It was a transformative experience; two days of professional learning with 50 amazingly talented, innovative educators in one of the most creative environments imaginable. One of the central themes of GTA is "Moonshot Thinking"; using innovative and creative ideas to generate massive change. As much as it was about technology integration, GTA was also about changing our mindset to think differently about the problems we face. Being around 50 other passionate, motivated, and innovative educators was a powerful experience. Sitting on the flight back, I knew that I would start to integrate these ideas into my classroom as soon as I got back. And I did! However, a conversation I had over lunch with one of our lead learners, Amy Mayer, and another teacher/football coach/#GTAATX attendee, Jordan O'Donnell, sparked my interest in how technology could make our jobs as coaches easier. That discussion spurred my thinking about our weight room issue.
As a Google Apps For Education school, our students and faculty rely on Google products (Gmail, Drive, Docs, etc.) to foster collaboration in the classroom. Why couldn’t we use these same products to make our football program more efficient? By using Google Forms, Google Sheets, and a few "Add-ons", our students can now go to our team website, enter their maxes, and receive an automatically generated, individualized 6 week workout plan.
Before each 6 week lifting cycle, students go to our team website and click on the following link:
This link takes the player to a Google form where they can enter their max for each core lifts.
Once the maxes are entered, each student receives an email (in PDF form so they can save it to their Google Drive account) with their customized workout complete with 6 week progressions..
These sheets allow us to be more efficient in our weight program. With just a little bit of time investment to create the system, we are seeing the benefits!
How'd We Do It?
Creating the Google Form
In our case, creating the Google Form was simple enough. If you have never created a Google Form, there are numerous videos on the Internet with a brief tutorial on how to do so. As an educator, Google Forms are a great, simple way to collect data. I use them to collect student blog addresses, and parent questionnaire data. I have colleagues that use Google Forms to administer short multiple choice tests that automatically grade themselves. Long story short, if you haven't explored Google Forms, you need to!
Our idea required a very simple 4 question Google form as seen above. When a Google Form is submitted, the data entered is exported into a Google sheet spreadsheet. Make sure to clearly label the "questions" in your form as they will be used as headings in the responses spreadsheet. In our case, our questions were: "Bench Press Max," "Push Press Max," "Hang Clean Max," and Squat Max." Now, the fun begins!
Modifying the Google Form Response Sheet
While Google Forms does a great job of collecting data, a few modifications needed to be made to the response Sheet in order to fulfill our goal of an individualized, hard copy work out plan for each player.
The first step was to use formulas in Google Sheets to populate the specific percentages we wanted each day for each lift. The specific progression of percentages per day was chosen based on extensive research performed by our head coach. He provided us a list of these percentages per set, per day. I used these to guide the creation of the individual formulas. Specifically, the formula need to pull data (max lift entered by player) from a specific cell, multiply it by a certain percentage, then round the resulting number to the nearest 5.
In our case, creating the Google Form was simple enough. If you have never created a Google Form, there are numerous videos on the Internet with a brief tutorial on how to do so. As an educator, Google Forms are a great, simple way to collect data. I use them to collect student blog addresses, and parent questionnaire data. I have colleagues that use Google Forms to administer short multiple choice tests that automatically grade themselves. Long story short, if you haven't explored Google Forms, you need to!
Our idea required a very simple 4 question Google form as seen above. When a Google Form is submitted, the data entered is exported into a Google sheet spreadsheet. Make sure to clearly label the "questions" in your form as they will be used as headings in the responses spreadsheet. In our case, our questions were: "Bench Press Max," "Push Press Max," "Hang Clean Max," and Squat Max." Now, the fun begins!
Modifying the Google Form Response Sheet
While Google Forms does a great job of collecting data, a few modifications needed to be made to the response Sheet in order to fulfill our goal of an individualized, hard copy work out plan for each player.
The first step was to use formulas in Google Sheets to populate the specific percentages we wanted each day for each lift. The specific progression of percentages per day was chosen based on extensive research performed by our head coach. He provided us a list of these percentages per set, per day. I used these to guide the creation of the individual formulas. Specifically, the formula need to pull data (max lift entered by player) from a specific cell, multiply it by a certain percentage, then round the resulting number to the nearest 5.
This formula used the bench press max entered by the student (which Google Forms put into cell B2), multiplied by 50%, then rounded it to the nearest 5. We round to the nearest 5 because our weights can only be adjusted in 5 lb increments. Admittedly, this is a tedious process because our workout plan required 108 different versions of this formula (6 lifts per week x 3 sets each week x 6 weeks.) That being said, once the initial work is done, the spreadsheet will work in perpetuity.
A Word on Headings
When Google Forms populates the response Google Sheet with the information entered by the student, it fills that information horizontally across one row and uses the first row of the Sheet as a header row. I suggest highlighting that entire first row, clicking on the "Freeze" option under the "View" tab. That way as you scroll through the rest of the Sheet the headings will always remain as the top row.
A Second Word on Headings
Every time you create a new formula, you will need to create a unique heading for that column of data. These headings will be how we automatically populate each student's individual work out plan. We chose to use a abbreviation that was a combination of the specific phase of the plan (base, development, or peak), the week of the plan, the lift, and the set. Some examples of our headings are below.
A Word on Headings
When Google Forms populates the response Google Sheet with the information entered by the student, it fills that information horizontally across one row and uses the first row of the Sheet as a header row. I suggest highlighting that entire first row, clicking on the "Freeze" option under the "View" tab. That way as you scroll through the rest of the Sheet the headings will always remain as the top row.
A Second Word on Headings
Every time you create a new formula, you will need to create a unique heading for that column of data. These headings will be how we automatically populate each student's individual work out plan. We chose to use a abbreviation that was a combination of the specific phase of the plan (base, development, or peak), the week of the plan, the lift, and the set. Some examples of our headings are below.
Add-On #1: copyDown
Finally, we had the basics of our Google Sheet ready but a new problem presented itself: how to have the formulas created for the first Google Form submission carry down to new submissions without manually entering them in? Thankfully, with some help from tech guru, and all around great guy, Drew McAllister, we were able to find a Google Add-On that accomplished this very task. For those not familiar, "Add-ons" are tools that give even more features to Google Docs and Sheets. Add-ons can be found in the Chrome web store and are very easy to install. Specifically, we used the add-on "copyDown," which provided the ability to have formulas carry into new form submission rows. By using this simple add-on, we saved countless hours of work. Here is a picture of the finalized Sheet.
Finally, we had the basics of our Google Sheet ready but a new problem presented itself: how to have the formulas created for the first Google Form submission carry down to new submissions without manually entering them in? Thankfully, with some help from tech guru, and all around great guy, Drew McAllister, we were able to find a Google Add-On that accomplished this very task. For those not familiar, "Add-ons" are tools that give even more features to Google Docs and Sheets. Add-ons can be found in the Chrome web store and are very easy to install. Specifically, we used the add-on "copyDown," which provided the ability to have formulas carry into new form submission rows. By using this simple add-on, we saved countless hours of work. Here is a picture of the finalized Sheet.
Creating the Work Out Plan Template
Now that our Form and our response Sheet were complete, we needed to generate the physical template for the hard copy, personalized work out plan that would be emailed to the student. This required us to build a template in Google Docs. Below is a copy of the template we created.
Now that our Form and our response Sheet were complete, we needed to generate the physical template for the hard copy, personalized work out plan that would be emailed to the student. This required us to build a template in Google Docs. Below is a copy of the template we created.
This template was fairly easy to create in a Google Doc. By using the "Table" function under the "Insert" tab in Google Docs, we could control the exact layout of the finished product that the player received. Additionally, it allowed for some customization options like a logo and color.
Add-on #2: autoCrat:
Once the template was created, we needed a way to merge the data from the Google Sheet into the specific spot on the template. Enter another add-on: autoCrat! This amazing tool automates the creation and sharing of personalized documents or PDFs from columns of data in a Google Sheet. In layman's terms, it pulls data from a Google Sheet and populates any template you create through the use of a specific convention: << >>.
In our template, you'll see the "<< >>" convention being used over and over. This is how that particular cell in the table recognizes which information to merge from the Sheet into this template. By placing the "<< >>" convention around the heading title, autoCrat pulled that specific data and inserted it into the particular cell in the template to create the individualized work out plan. The process of placing the correct tags in the proper cells on the template is time consuming. However, like the formula writing process, it only needs to be done once. The time saved for our players is well worth the initial investment.
After installing autoCrat, which can also be found in the Chrome Web Store like copyDown, setting up a new merge is very simple. First, you will be instructed to choose your template from Drive, map the <<tags>> (if you've written your headings consistently between the Sheet and the template this will be done automatically) , and then manage your merge setting (which is where you'll choose output format and email options.) Once you are done creating the merge, merely run it!
Add-on #2: autoCrat:
Once the template was created, we needed a way to merge the data from the Google Sheet into the specific spot on the template. Enter another add-on: autoCrat! This amazing tool automates the creation and sharing of personalized documents or PDFs from columns of data in a Google Sheet. In layman's terms, it pulls data from a Google Sheet and populates any template you create through the use of a specific convention: << >>.
In our template, you'll see the "<< >>" convention being used over and over. This is how that particular cell in the table recognizes which information to merge from the Sheet into this template. By placing the "<< >>" convention around the heading title, autoCrat pulled that specific data and inserted it into the particular cell in the template to create the individualized work out plan. The process of placing the correct tags in the proper cells on the template is time consuming. However, like the formula writing process, it only needs to be done once. The time saved for our players is well worth the initial investment.
After installing autoCrat, which can also be found in the Chrome Web Store like copyDown, setting up a new merge is very simple. First, you will be instructed to choose your template from Drive, map the <<tags>> (if you've written your headings consistently between the Sheet and the template this will be done automatically) , and then manage your merge setting (which is where you'll choose output format and email options.) Once you are done creating the merge, merely run it!
Things to Keep in Mind
1. This is a time consuming process. All told, the project probably took 5 to 6 hours, total, over a few days to complete. When evaluating time investment, we valued the time our kids would gain using this system over the time it took us to complete the task. For us, the 5 to 6 hour investment allows us to cycle 50+ kids through a fairly complex workout in a very small space shared by other teams in about 45 minutes. Previous to the creation and use of this system, it took our kids over an hour to complete the same work out. Over the course of a week, the time we save was worth the investment. Additionally, the project is complete and won't ever need to be redone. As along as we use the same work out plan, our system will work. Even if we don't, modifying this system will be easier than creating from scratch.
2. For some reason, the autoCrat merge must be re-run every few weeks. I have seen the same problem reported when using autoCrat, so it must be a systemic issue. You will be able to tell the problem exists because students will no longer receive an email with the work out plan once they've entered data into the Google Form. The solution is very simple: re-run the autoCrat merge. On the Friday of testing week, I open the response Sheet, click on the "Add-Ons" tab, and launch autoCrat. At that point, I highlight the merge and click "Run Merge." Any Form submissions that have been waiting for the merge will run. If anyone knows why this happens or has a solution, I'd love to hear it.
2. For some reason, the autoCrat merge must be re-run every few weeks. I have seen the same problem reported when using autoCrat, so it must be a systemic issue. You will be able to tell the problem exists because students will no longer receive an email with the work out plan once they've entered data into the Google Form. The solution is very simple: re-run the autoCrat merge. On the Friday of testing week, I open the response Sheet, click on the "Add-Ons" tab, and launch autoCrat. At that point, I highlight the merge and click "Run Merge." Any Form submissions that have been waiting for the merge will run. If anyone knows why this happens or has a solution, I'd love to hear it.
Where we Go From Here?
Recently, Drew told me about another add-on that I will be adding to this process: FormRedirector. FormRedirector is an amazing tool that will compile the data entered for the max lifts for each student and place them into a pre-made template that I design. Essentially, every 6 weeks, when a player enters new data, the data will be pulled into their work out and FormRedirector will that the data and compile it in a separate spreadsheet for each user. This will allow me to collect data on the growth of each particular student over the 4 cycles of our power lifting program.
This is one of the reasons I love Google Apps, the possibilities are endless. If I did it all over again, I'd reconfigure the template to more accurately reflect the specific lifts for each day of the 6 week cycle. Thank you to Amy Mayer for planting the seed about using Google Apps for coaching into the heads of myself and Jordan O'Donnell. I hope this is just the beginning of how I can help coaches make the job more efficient using Google Apps. A HUGE THANK YOU to Drew McAllister for his invaluable help in getting this project off the ground and for constantly inspiring me to push myself as an educator.
-Adam
This is one of the reasons I love Google Apps, the possibilities are endless. If I did it all over again, I'd reconfigure the template to more accurately reflect the specific lifts for each day of the 6 week cycle. Thank you to Amy Mayer for planting the seed about using Google Apps for coaching into the heads of myself and Jordan O'Donnell. I hope this is just the beginning of how I can help coaches make the job more efficient using Google Apps. A HUGE THANK YOU to Drew McAllister for his invaluable help in getting this project off the ground and for constantly inspiring me to push myself as an educator.
-Adam