I love that PE and Technology are coming together, there is such a great host of Google scripts that can enhance our feedback to students and parents.  I believe that if I am assessing and recording it (iPad using Google Drive) then the students really should get to see it and understand what I am doing and so how they are doing/did.

Inspired by @PENathan‘s Nathan Horne and his sharing of how to use Google Spreadsheets and Conditional Formatting, I have decided to run a little tutorial on how I use @astillman’s autoCrat script to mass produce individual student summative assessment pdf’s for sharing with students and parents, from Google Spreadsheets.  I have blogged about this before, but I think it might help to actually document the process, so that others can hopefully get into this very cool way to share student results.  Andrew Stillman is gaining a reputation for his range of scripts that allow teachers to automate services often used.  These include Fluberoo which @Physed_Pomeroy (Matt Pomeroy) has been sharing with PE Geeks.  Fluberoo allows for automated grading of tests that have true/false or multiple choice questions.  If you set a test using Google forms, then add the Fluberoo script, you can have your tests graded and ready to look at the result.  Other scripts to check out are Doctopus and Goobric, which are very well explained by @JayAtwood.

Using autoCrat

Before you start installing autoCrat you need to organise both your Template and your Google Spreadsheet.  The Google Spreadsheet needs to have all of your assessment, student names, etc. and all the information that you are assessing or sharing with others.  Your template needs to look exactly like the document you are intending to share with your students and/or parents.

This is an example from my Grade 7 Track and Field unit:

I have set the templates up to show all the unit information and rubrics as well as the columns to add my personal comments and grades for students.  You can check out the full document in here.

Secondly you need to set up the Google Spreadsheet where the data for the Template is kept.  You can see here that I have the columns showing each assessment item, as well as grades and my comments.  You can view the full spreadsheet here.

Once your assessment is complete then it is time to go into autoCrat mode.  Simply go to ‘Tools’ and then to ‘Script Manager” as below and type in ‘autoCrat’:

and choose AutoCrat in your preferred language.  Click ‘Install’ and then the script will ask your permission to run.  You need to authorise the script and you will then have a new menu on the Google Spreadsheet menu bar as shown:

Now comes the more tricky part.  It is important that each of the headings of your Google Spreadsheet columns correlate with a ‘field’ in your Template.  For example:

Template – to show that you are adding a ‘field’ you need to have the heading of your column in <<brackets>>.

Spreadsheet:

I should add here that on my assessment spreadsheet I usually have the full assessment text. And on the autoCrat script I run an abbreviated heading so that it is easy to put into the Template and copy/paste my student info into the new tab.  I edit the new Merge version so that I only have the information I am going to be sharing, nothing extra.

Example:

Assessment Tab on my Google Spreadsheet with full headings making it easier for me to assess!

autoCrat headings – easy to add to the Template:

The headings of each column (so row 1) need to have EXACTLY the same text as the Template.  If you have any variation or complete this in Row 2 then the script will not work.  Check carefully.  The script only allows letters and numbers, no !@#$ or “<>,. etc.

Once your Template and your Script is ready to go, you need to then run through autoCrat.

1. Your Template and Spreadsheet need to be finished, you won’t be able to edit – you ask the script to just show you one pdf to check it (and I strongly suggest that you do this) and make sure the merge works just as you wish it to.
2. Click on the AutoCrat menu and Initialise the script – accept and continue.
3. Select your template.  Click on ‘Choose template from Drive’:
4. Check that the mail tags (fields) are listed on the menu on your Template – scroll and check.  If something is missing, stop and edit and then start again from ‘Choose template’.  when you are happy, click ‘save settings’.
5. If you have multiple tabs on your Spreadsheet, then select the tab that you want your data to come from 
6. The next step allows you to skip students if you only want to merge select data.  Set a field condition that must be met for the document to be created.  If you want to merge all data, just click ‘submit’.
7. Field mappings is next, check that the headings on your Google spreadsheet match exactly the <<field>> from your Template.  If you see a mistake, go back and fix it then start the autoCrat process again.   When you are satisfied, click ‘save mappings’ (bottom of your list).
8. Set Merge Type – this is where you are telling the script what you want to name each document and what kind of document you are after.  Click on ‘save merged files to docs’ and then tell the script where you want the new files to be saved.  If you don’t have a folder set up, the script will set up a folder for you, and you can rename it later.  Put in a File name – I like to use a generic first part and then the student name at the end.  You can draw on any of the fields for your naming so that each new document will be named individually based on the names in your spreadsheet.  You can see below I have used the $familyName and $firstName in my document names.  I have then chosen either Google document or PDF for the format of my new file.  There are other options here – emailing documents or to trigger merge on form submit (if you create a Google form eg. help desk and then set up the script to run, it will create a new document for each new form submitted, not wait for your to run the script when everyone is ready).  When you are happy click on ‘save settings’
9. Merging… last screen is to ask you whether you want to preview, run or keep settings.  I strongly suggest that if you are going to run the merge that you preview the first row to check that it works.  The merge itself takes a while and if you get it wrong you have to delete and go again!
10. Once run, your spreadsheet will have a new column on the RHS that tells you when each new merged document is created.  I have had issues with the merge pausing and then you can just run again.  If you need to you can delete the text in the ‘complete’ column and run again and any student that has a blank box here will get a new document merged.

All done!  Now you have magically shared all student data with each individual with all grades, comments and the rest.  This makes a very neat sparkly document to share from a PE teacher.  I used these on all summative assessments and placed them into student portfolios as a record of each unit.  You could share your Google spreadsheet with the other teachers who teach G7 or other and then all merge and create in one go, so everyone is sharing summative assessment in the same way – very professional.  I then asked my students to blog post a reflection or comment on this work to ensure that they took some time to read over it.

I hope that you enjoy using autoCrat!

Image: Merge by Brendan Lynch licensed by CC BY NC