Throughout my teaching career, I have always enjoyed looking for ways that technology can help me to work smarter, not harder. While I was at college I studied AS ICT and I do believe this gave me the basic skills I needed when it came to designing department systems. I have made numerous ‘fancy’ tracking sheets that included long ‘IF’ statements that helped to calculate students grade at the end of the year (see picture below). I have used conditional formatting to change individual cells colour depending on the condition and create tables and graphs to show to admin.

Within my last school, I shared with you how I created reports for fitness testing criteria, how I created a house system for the whole school and how you can bring your data to life using sparklines and conditional formatting. Nearly anything is possible. One of my favourite posts is creating praise postcards as it shows how you can create a simple report for your students.

I was recently approached by a member of the UK #physed about creating a tracker that could track his student’s at the iGCSE level along with creating reports, both class and individual.

YOU CAN CREATE A REPORT FOR ANYTHING THAT IS IN A SPREADSHEET USING AUTOCRAT

If you look around, there is a number of free great trackers you could use, I searched on the TES, the shared Google Drives and even the Google Sheets templates to see what was available (there is a few!).

One I found on the TES was a tracker created by Olli Dunn, I contacted him to see if I could share his tracker for this blog post and he agreed. Click here for a COPY

The beauty of working in Google Sheets is firstly you can work anywhere, you can have multiple teachers working on the sheet at the same time and you can access your trackers from your smart devices to make amendments.

For this example, (Take a COPY) I am going to use this tracker that is based on the AQA GCSE PE (thanks to @MrButts_PE for sending me a copy of his tracker) and show how you can create reports for each of your students to show where they are currently and what they need to be working towards to gain the grade they deserve.

You will need to create a report template or you can amend mine: Get a COPY Here

Now I will show you two ways of creating reports. Individually that you can either email to your students, print or just share with them or a class set that could be easily used on parents evening either on a device or printed out to give to parents.

Firstly you will need to open up your tracker in Google Sheets and click ‘Add Ons‘ then ‘Get Add Ons‘ search for ‘Autocrat‘.

Click ‘free‘, us teachers like free!

You will then be sent to a screen that looks like this

Click ‘ALLOW‘.

You are now ready to start using Autocrat. In Google Sheets click Add-ons, autocrat then open.

<<MERGE TAGS>> Your new best friend

 

To Create a Single Report

Click New Job to start.

STEP 1: Give your job a name, in this case, I will call it ‘Single Report‘

Then click ‘Next’.

STEP 2: Next, you have to choose your Google Doc template which you want to use for your report. 

Click ‘From Drive‘ and choose the template you have saved in your drive.

Click ‘Next‘.

STEP 3: The merge tags in the Google Doc will now appear and you need to ‘map to column’ so the tags need to map to the columns in the Google Sheet.

This needs to be done for each merge tag.

Click ‘Next‘.

STEP 4: Decide how you want to name each file you are going to create, by pressing the blue sidebar it will give you a list of merge tags that you can click and then paste in the file name. For example <<Student name>> will create a different PDF and use each individual student name as the file name.

You can choose either PDF or Doc as the ‘Type’, I tend to use PDF if it is a document that I do not want editing in the future.

Now because we want individual student reports to give out or share, we are going to select ‘Multiple documents (classic mode).

Click ‘Next‘.

Step 5: Choose a folder in your Google Drive in which you wish to put all the new files in. Click ‘Choose Folder’ and select which folder you would like to use.

Click ‘Next‘.

Step 6: I tend to leave this blank.

Click ‘Next‘.

Step 7: For this example, I will leave this blank. You could use this part if you only wanted the Autocrat to run depending on certain conditions.

Click ‘Next‘.

Step 8: Sharing the document -> For this example I do not have the email addresses of the students in which I want to share the file with, so will leave the setting as ‘No’.

If I did have the email address I would click ‘Yes’ so it would generate an email to each student with the file as a PDF attached.

Now I would have the opportunity to choose either PDF/Doc to share the file, allow collaborators the options to re-share the file or not and if I want to send the email from a generic no-reply address. In the ‘To’ I would put <<email>> if that was what the column title was on my sheet.

Click ‘Next‘.

Step 9: Add/Remove Triggers

As I am running this Autocrat manually I will leave these as ‘No’.

If this sheet was connected to a Google Form, I could run the Autocrat automatically when a Google Form was submitted or I could decide on a certain time or day to run the Autocrat.

Click SAVE as you are now finished.

Time to run the autocrat for the first time.

To Create a Class Set of Reports

To create a class set of reports like this:

Follow the steps above, but in step 4 choose ‘Single Document‘.

This will merge all the PDFs into one file, then you can access these on your smart device during class or at parents evening and share the spreadsheet information in a more meaningful way.

If you have any questions or you need help setting up your own autocrats, get in touch.