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:
How'd We Do It?
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.
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.
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.
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.
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
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?
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.