Feedback and Reflection using Google Sites, Sheets, Apps Script and Awesome Table Gadget

I previously posted my presentation at the KL GAFE Summit on how the Alice Smith School uses Google Sites and the Awesome Table Gadget to recreate the functionality of a VLE/LMS. I have continued to refine the presentation of data from our GSheet markbooks back to our students via GSites using the Awesome Table Gadget (ATG) and Google Apps Script (GAS).

The image above shows my test data when I visit the Biology Department's Investigative Biology GSite and look at my data for the Core Practicals we assess our students against. The graphs are generated from GAS; the pull data from the GSheet and present it in this interactive bar graph. The 3 tables of data are generated using the ATG and Proxy Script -- pulls the logged in user's data only -- which are looking at the same GSheet markbook as the GAS that builds the graphs.

A recent addition to the presentation of data in our markbooks has been the use of Sparklines; these are the small graphs that can be inserted into cells in a GSheet. Getting them to appear in the GSite via ATG is thanks to the work of James Pearson (https://sites.google.com/a/nihr.ac.uk/external-demo-site/sparkline-awesome-table).

On the right hand side of the GSites page I have used ATG to return the students' reflective comments on their work so that they can see the progression of their attitudes to their work. I gather this info using a GForm with Autocrat added on to generate a GDoc version of the students' responses. The ATG template is bringing back the students' responses and the blue buttons are links to the Autocrat GDocs. In the future I intend to correlate the students' numerical attitudinal responses to their write-up assessment scores to identify any trends that are affecting their performances.

I will create some YouTube videos to demonstrate how I have put this GSite together in future posts.

Building a Controlled Assessment GSite

I was recently asked to create a system that would allow our Geography teachers run their controlled assessments in Google Docs. This has posed somewhat of a challenge as the students are only allowed to see the doc when the assessment periods are on and are allowed to edit the doc, and what they previously wrote, during the next assessment periods; there are 4 periods in total.

https://plus.google.com/104818190651043693758/posts/REre47dbhrH

I have posted the issue out to the community and received some great ideas; a kiosk extension may be what works in the end to remove the right-click copy issue, but it will take a fair bit of my brain to figure that out, or cash for someone else. 

I did discover that adding ?rm=minimal at the end of any gdoc makes the menu disappear, producing a blank canvas for docs and drawings; cool. What other sneaky tricks are out there? How do we find out about them other than by searching for hours in stackoverflow?

For now, I've settled on a combination of doctopus, ?rm=minimal, awesome table gadget with the proxy script, and sites to show each student their own doc when the edit access to the doc is set to anyone at school with the link can edit. The students can still right click copy but this is fine according to the assessing teachers; they couldn't stop the ability to copy in a .doc but now they don't have the menu to tempt them 😜

GAFE Summit KL - Awesome Table Gadget and Google Sites

Reporting assessment data back to students is made possible with Google Sheets, Sites and the Awesome Table Gadget. This is the presentation I used at the GAFE Summit in KL that has the necessary links to the the ATG website and the proxy script needed to identify the logged in user. I will demonstrate how it is all done in a later post!

Hangout in a Haze

"...let us begin and create in idea a State; and yet a true creator is necessity, which is the mother of our invention." (Plato, in Johnston 2012, p285)

So Plato identifies a "need" to be the driving force behind the creation of new stuff! My need right now is to interact with my students despite the Haze in KL. I am being pressured into using strategies and technologies that I either don't normally get the opportunity to use, or haven't used before. As a biology teacher, I know pressure:

"Selective pressure is any phenomena which alters the behavior and fitness of living organisms within a given environment. It is the driving force of evolution and natural selection..." (Gale, 2005)

The Haze is the pressure and if I don't alter my behaviour, I become unfit and irrelevant as a teacher of my students; I'm not about to let myself become extinct!

Google Apps for Education and a number of my current favourite collaborative apps come into their own when working online. The whole class can be working on a particular doc at once or you could prep one per group ahead of time. 

Some ideas below along with links to good advice by other tech goorus on-the-line, but my top tips are these:

• Practice Hangouts and collaborative apps in normal lessons before the next Haze day! Let the students get used to them and so you can experience the management;
• Prepare your resources ahead of time;
• Train the students on using the mute function and turning the camera off,
• Get the students to respond with "thumbs up/down" while the mic is muted (very effective for 15 attendees!)
• Prepare for failure but be prepared to fail, it's a learning experience and I am constantly learning form the students! Hangouts and the Internet may slow down, but if your activities are ready, the students can be getting on with it; they will find a way to communicate with their classmates if they get stuck.

Gdocs: Take a register! Collaborative writing, Experiment planning, Comments for Peer assessment;

Gsheets: Take a register! Data gathering (Tine Willis used Hangouts and GSheets for her Y9 students to do a reaction timing investigation using caffeinated drinks and a reaction timer online...all from the kids homes!);

Gdrawing: Group poster drawing, Timeline construction, flowchart development, Cut up an image copy and paste style and have the students rebuild it in drawing, label diagrams or subject specific content
Gdrawing is functionally infinite so different groups can be working in different places on the same doc, Gdrawing activities are limited only by your imagination;

Gslides: Presentations! Flash card revision decks, Stop motion from screen captures (publish the slides and set the presentation to run automatically at a fast speed!), A-Z Wiki-cards, collaborative eBooks;

GForms: Students create quizzes that can be Flubaroo'd and shared with class mates to try! (Quizlet is cool too!) I had some students build their own Kahoot Quizzes on our Haze day-split your screen to see their screenshare of the questions and have the answers on another browser tab...what a laugh!)

Padlet and Realtimeboard: collaborative ideas boards and project design, work area with links to sites, pdfs and docs. Realtimeboard works on the iPad and you can write with your finger on it to make scribble notes.

More online advice...

5 top tools for Hangouts: http://www.educatorstechnology.com/2013/05/5-excellent-tools-for-teachers-to-use.html

Hangouts cheat-sheet for Teachers: http://www.educatorstechnology.com/2013/09/a-must-have-google-hangout-tip-sheet.html

Reference

Gale, T (2005). Selective Pressure. Thomson Gale. Retrieved from: http://www.bookrags.com/research/selective-pressure-wob/#gsc.tab=0

Johnston, B. W. (19 March 2012). As We Sow: Why the Great Divide. AuthorHouse. pp. 285. Retrieved 23 October 2015.

Correlating practise with results

The IALs, or international A Levels, by Edexcel have 6 units; 1, 2 and 3 make up the AS level in Year 12,  and 4, 5 and 6 complete the A2 level in year 13. The qualification does not have a practical assessment in the typical sense like the CIE A Level, but relies on written assessments to examine the skills of the students i.e. units 3 and 6.

I hold the belief that providing students with opportunities to demonstrate their skills in an appropriate practical context is the best way for them to hone their skills of analysis, and to be able to draw conclusions and support those conclusions with theory and published data. These skills transcend the unit 3 and 6 papers and are important in all of the units; students need to have these skills and be able to apply them everywhere, not just be good at answering the practical papers.

I have been considering the correlation between my Y12 students' performances in their unit 3 mock, core practical write-ups, research write-up, and the actual unit 3, and overall AS performances in the summer 2015 period. (Correlation coefficients whose magnitude are between 0.9 and 1.0 indicate variables which can be considered very highly correlated. Correlation coefficients whose magnitude are between 0.7 and 0.9 indicate variables which can be considered highly correlated. Correlation coefficients whose magnitude are between 0.5 and 0.7 indicate variables which can be considered moderately correlated.Correlation coefficients whose magnitude are between 0.3 and 0.5 indicate variables which have a low correlation. Correlation coefficients whose magnitude are less than 0.3 have little if any (linear) correlation.)

Unit 3 mock vs AS Unit 3 actual: R = 0.3209; 

Core write-up max score vs AS Unit 3 actual: R = 0.3121; 

Core write-up max score + Research vs AS Unit 3 actual: R = 0.4285;

The data above indicate that the activities during the course--Unit 3 mock, the core write-ups, and the research--have low correlations with the students' AS Unit 3 exam performance. What is of note is the comparison between those students who performed the Research tasks throughout the year and those that did not; the Research scores improved the correlation with the AS Unit 3 actual performances by these students.



In comparing the mock exams with the AS performances, the correlations become much more visable:

Unit 1,2+3 mock vs AS Actual: R = 0.7287; (not enough data for Unit 2) 

Unit 1 mock vs AS Actual: R = 0.7828; 

Unit 3 mock vs AS actual: R = 0.3656;

The Unit 1 mock exam taken by the students halfway through the course is highly correlated with their final AS performance making this a useful tool for guiding students in improving themselves. The Unit 3 mock paper has a low correlation with the final AS performance; considering the students do so well in this mock it may provide a false sense of security and introduce a degree of complacency in our students.



In November 2014, I presented my use of GAFE tools to assess and report my students' performances in their write-ups and research activities. It is this assessment strategy and data I have led my colleagues to bring together to identify the correlations below:

Core write-up max score vs AS actual: R = 0.6281; 

Core write-up max score + Research vs AS actual: R = 0.7204; 

Unit 1 mock + Core write-up max score vs AS Actual: R = 0.7642; 

Unit 1 mock + Core write-up max score + Research vs AS Actual: R = 0.8949;

The scoring of a research write-up was carried out with 13 students with the other 18 students only completing the core practicals. 

The Core Write-up Max scores by the students are moderately correlated to their actual AS performance. The addition of the Research performances increases the correlation to moderately correlated, and with the addition of the Unit 1 Mock, the correlation is climbing towards 'highly correlated'. 


I believe that by targeting the students' Core Write-up and Research performances throughout the year, we can improve our students' AS performances. This will require a shift in attitude by our students towards the activities as they seem to be under the impression that the Unit 3 paper is where their practical analysis skills are assessed, and one which they are good at! 

On analysing the Unit 1 and 2 papers of students who have under-performed, the questions they struggle with are those relating to graph and data analysis, validity, reliability, accuracy and precision, which are addressed directly in the Core Practical Writeups. 

Our instruction needs to consider KASI (Knowledge, Attitudes, Skills, Interpersonal skills). In context of the discussion above, Attitude and Skills needs to be addressed. The skills of analysis and the attitude of value in the write-up process need to targeted. 

The use of a write-up rubric should be adhered to with a Triple Impact Marking (T.I.M. linked with D.I.R.T.) strategy used for self,  peer and finally assessment using the comments within the GDoc writeup. The feedback (Hattie, needs citation!) students receive from themselves from grading their work against the rubric, with reasons for the grade awarded should identify where improvement is needed; the student must act on the assessment and improve their work (different colour?!) Peer assessment brings the student more feedback for improvement as the peer assesses the self-assessment and offers guidance for improvement; this advice needs to be acted upon (another different colour.) The peer also comes into more contact with the rubric causing them to assimilate the requirements for a complete write-up more. Finally, the teacher assesses the final write-up, the self and peer assessments and the action taken by the student to enhance their performance.

T.I.M. and D.I.R.T. require careful time management but the collaborative nature of GDocs and the commenting features make it easy for students to interact with each other. Students must aim to be better the next time they do a write-up; reflecting on their performance is important. Adding this reflection to the GDoc after the teacher assessment would be a straight forward strategy. A more powerful process may be to use DocAppender for GDocs which used a GForm to add the reflection to the GDoc. Since the reflection is first stored in the GForm's Sheet, the Sheet can be used to express the targets back to the student at a later date wthrough Gmail or even a GSites page.

Finally, getting the students to create a Gallery of their write-ups for public scrutiny will enhance the motivation of the students to do a better job as the authenticity of the work will be lifted. This is a standard Project Based Learning tactic to increase motivation. A gallery walk will also bring the weaker performing students into contact with the better write-ups and allow those highly skilled students to support their peers.

Application of research to the classroom: assessment for learning

I use multiple strategies in my lessons to enhance student engagement, motivation, progress and deep learning, but how often do we as teachers pick apart what we are doing and why? I grabbed a book out of my school's library for some light reading over the summer break--Evidence Based Teaching: a practical approach, by Geoff Petty--and I was reminded that the strategies we use to enhance the learning of our students have been thoroughly researched and there are some very worthwhile strategies that we should employ more often.

I was pleased to read Petty's example teacher Tina (p. 88) doing one of my lessons; it is almost identical in its constructivist/social constructivist pedagogical approach but I have applied some extra approaches here and there from my own experience.

The example Petty gives doesn't cover the whole lesson, so I am going to fill in the bits I do extra here; if you find his book you can compare!

Students are presented with the title and objective/s of the lesson. Setting the goals for students activates their thinking and begins the process of information/past experience retrieval from long term memory. I do not use statements such as "you will be able to....", I prefer to turn the learning objectives into questions. These questions are the first things the students see and they begin to address them as they sit down and prepare for the lesson; students don't get to sit and do nothing at the start of my lessons!

After 5 minutes, find out from the students what they know; write up on the board.

Identify what the class is currently struggling with and present on how that skill is done; use of some typical non-examples will enhance the presentation of the skill. Share a graphic organiser with the students that they can add notes to; in the GAFE classroom this would likely be a google drawing.

Students construct criteria for completing the skill effectively; students work in pairs and can snowball to 4s.

The teacher nominates a speaker from each group to offer a criterium. The teacher questions why that has been selected and requests members of the other groups to verify the criterion.

With a complete list of criteria, the students complete some products that are to be peer assessed.

The teacher circulates and identifies issues with the products and discusses these with the student in regard to the criteria listed; "look at your A; how have you done against the criteria?" The student self assesses and reflects on why they have not been successful. The teacher guides the student to identify what the error is and why they have made it. This form of AfL will generate deep learning as the student is actively thinking about the mistake they have made therefore altering and improving their mental model of the skill.

The students self assess their products then peer assessment takes place; all against the agreed criteria. The teacher can continue to circulate and supports those students who are having any difficulty deciding if the criteria are being met. Personally, I prefer to sit and observe/listen to the students as they peer assess. My students help each other and check each other's work before they come to me; "3 before me" I believe the strategy is called! I encourage my students to explain to the person they are assessing as to why they gave a mark from a mark scheme or the criteria provided. This explanation can lead to a productive discussion between the assessed and the assessor until agreement is reached; the teacher is the final arbiter in the process and can often come down to saying "...you are both correct because..... Let's give the benefit of the doubt and award the mark!"

By listening, watching and circulating, I find out what is causing the most issues for the students but to verify I will ask that the assessors write down on mini-whiteboards the criterion that caused the problem on the work they assessed. Doing this removes some of the anxiety and embarrassment from the individual while giving me a window into the class's overall performance. The most common difficult criterion I can then pick apart as to how to best address it for the next time.

Students improve their products clearly highlighting what needed fixing and why they were unsuccessful in the first place. The students take some time to update their ebooks with the success criteria for the skill and add photos of their products as evidence and for their revision. These notes are then reviewed by myself when I look at their GSlides eBooks using Goobric.

To finish the lesson, students can analyse some 'broken' non-examples to identify what is wrong with them and how they can be improved. This could be done from the interactive whiteboard with students using their mini whiteboards to communicate, or they could use their devices to give recorded responses using apps such as Pear Deck, Kahoot or Socrative. Kahoot has timings and leaderboards and brings an element of fun competition to the plenary. Pear Deck allows the students to change their answers if the teacher permits this which can be a bit of a giggle!

So what is going on in this lesson that makes it outstanding? Let's break it down from the top:

This lesson is an example of direct instruction whole-class interactive teaching with constructivist activities; Gangné's nine events of instruction are closely followed here. Hattie states that whole-class interactive teaching produces an average effect size of 0.81. 

Goal setting, Exemplars and Non-examples: Marzano identifies goal setting as having a 0.97 effect size. Giving students specific, achievable goals at the start of the lesson, gives the students a target to reach while activating their prior knowledge and experience of the topic. By providing exemplar material that students can use to 'calibrate' their thinking as to what a perfect piece of work looks like also gives them a finishing line to aim for. I use the finishing line example because, as a runner, when I go running with a coach I need to know how far I will be going in order to pace myself and adjust my effort. The non-examples come afterwards; these are mini assessments and can be used in a game like fashion with the students where they use the success criteria to identify what is wrong with the non-example. The use of the non-examples is also an opportunity for the students to assess and reflect on correctness and how to improve a piece of work.

Graphic organisers have been shown to have an effect size of 1.2; this is very high. As a teacher I can use the graphic organiser to demonstrate conceptual links between topics, but for students they can be used to review and analyse content and present the content in a reformulated fashion that has taken deep thinking to achieve. I am a fan of the atomistic mind-map to illustrate connections, and venn diagrams for compare and contrast activities.

Feedback- self, peer and teacher: Marzano identifies a 1.13 effect size on giving students highly specific feedback on the process and strategies they have employed in doing their work. This feedback can come from themselves, their peers or their teacher.

Identifying what went well, even better ifs, and using these to set appropriate targets for individual students to move their mental models forward.

Mastery learning:

Assessment proformas:

http://www.facdev.niu.edu/facdev/resources/guide/learning/gagnes_nine_events_instruction.pdf
http://www.instructionaldesign.org/theories/conditions-learning.html

GClassroom Share Button in GSites

Google recently released the Classroom API. Part of the API allows for the embedding of a share to classroom button for any 3rd party site. Google has published the code needed to make the buttons appear in web pages on the API pages linked above.

I use Google Sites with my students and have embedded resources, forms, docs etc. that I link to from Classroom so I wanted to embed one of these Share buttons. Adding script to GSites pages is a little tricky but with a little bit of Apps Script it can work!

In the image above, I have added the share button beside the page title; this is done by creating and inserting an Apps Script that has been published as a web app.

I used the HTML Service and the templated Scriptlets to build the HTML I wanted to embed and used the scriptlet to acquire the javascript form Google:

I want to be able to copy a page with the script already in it and have it automagically pull the correct URL from the Site without hard coding the URL each time. The scriptlet code above allows this by using SitesApp.getActivePage().getUrl();

Publish the project as a Web App and insert it into your page:

When pressed, the button opens a window to Classroom and lets you select the class you want to post an announcement or assignment to. Nice :)

Pear Deck in the classroom

I recently bought a subscription to Pear Deck. Pear Deck is an instant response Assessment for Learning tool for 1 to 1 classrooms.

Pear Deck allows you to import Powerpoint and Google Slide presentations, as well as PDFs, and turns them into Pear Deck slides. You can then build questions from several types: multiple choice, text response, draw response, etc. I chose to run the session with a laptop and projector while controlling the presentation with my tablet in Dashboard mode.

The platform allows me to see who is answering what and I can project anonymous responses onto the screen. The teacher can lock responses but I have found the students get much amusement from realising they were incorrect and quickly try to change their answers before I can lock the submissions.

I particularly like the draw and drag question types. The drag type can be used with the thumbs slide that Pear Deck provide as a template; the students drag a red dot to wherever they believe it should be.

My lesson, on rusting and acids, went very well. The students were really engaged and trying to answer within the short timeframes I was giving them and then went to write more equations as posters in their groups. The lesson finished some recap questions and a couple of harder extensions; all-in-all, a pretty good lesson. I found that using the questions built into the presentation allowed me to finish exactly on time, whereas I can tend to overrun a little and not complete my lessons exactly as planned.

Question: do I continue to use Pear Deck regularly, risking the students getting used to it?

Daily Notices with Awesome Table Gadget

Teachers need a simple method of creating a message to be delivered to a particular audience in the school. The message needs to be available for a range of days and disappear when those dates have passed. The format of the messages needs to be uniformly attractive and readable so I needed a way to apply my design aesthetic to the teachers' messages automatically after they have been submitted.

My solution has been to use a Google Form to capture the message to a GSheet, and use the Awesome Table Gadget to present the notices to the school on a Google Sites page.



I have made use of the edit message script supplied by Romain Vialard and his team, along with their new template functionality. The template has allowed me to use CSS to style the messages and make them look uniform, but staff with a basic understanding of embedding and tags can make their messages more interesting if desired by embedding images or GSlides set to loop or static for a poster.

The CSS below is used to build the messages shown above from the "Form responses 1" sheet created from the Form submission. The tags in bold are the column headings from the Form submissions.

Notice

<div style="margin:10px"> <span style="font-size:2em;font-weight: bold;color:#008d4f;">${"Title of Notice"}</span><br> <span style="font-size:1.2em;font-weight: normal;color:#008d4f;">A message for <span style="font-weight: bold;">${"Notice for the attention of..."}</span> from </span><span style="font-size:1.2em;font-weight: normal; color:red;">${"Teacher Name"}</span> </div> <div style="margin:10px"> <span style="font-size:1.5em;font-weight: normal;">${"Notice Information"}</span><br><br> </div>

Awesome Table Gadget shows all the rows within the set range i.e. it lists every row in the sheet and therefore, eevry notice. For the daily notices to work, I needed the messages that were out of date to be removed. Awesome Table doesn't show the rows that are hidden so I used the script below, triggered nightly, to hide the out-of-date messages; I chose not to delete them in case of any issues with the system.

function hideNotice() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Form responses 1");
var datarange = sheet.getDataRange();
var lastrow = datarange.getLastRow();
var values = datarange.getValues();// get all data in a 2D array
var dNow = new Date();
var curDate = new Date(dNow.getFullYear(), dNow.getMonth(), dNow.getDate(), 0, 0, 0, 0);
for (i=lastrow;i>=2;i--) {
var enddate = values[i-1][7];// arrays are 0 indexed so row1 = values[0] and col3 = [2]
if(enddate < curDate)
{
  sheet.hideRows(i);
}
}
}

The GSite shows teachers the tabs they need in order to create notices and edit their previously posted, and still visible, messages. The GSite also has another tab for students that uses Awesome Table to present the same message but without the column with the edit button. This is achieved by using 2 instances of Awesome Table Gadget: one for the teachers with the edit button column visible, and one for the students without the edit button column. By using the Page Level Permissions, I can show the teachers the tabs they need to create a message, while showing the students only the messages. 

Redefining the Exercise Book

I had the pleasure of presenting at the Deep Learning Malaysia conference where I talked about using Google Slides as an eBook. I paid attention mostly to the application of the SAMR model in order to demonstrate how I was using the technology to change the task and make possible the previously impossible. My main focus for the eBooks is to enhance tmy students' presentation and collaborative skills; you will find links to my rubrics and templates that I use with Doctopus and Goobric.

Google Sites as an LMS/VLE

I have seen various uses of Google Sites as a resource repository, but have yet to see it work as a true LMS where data is fed into a database and retrieved from a database the way most VLEs like Moodle, Firefly, and Schoology do. I decided that Sites needed more attnetion in regard to data management and have set myself the goal of replicating VLE behaviour using Sites, Sheets and whatever gadgets and coding is required. 

My first foray into using Gsheets as a database was with Andrew Stillman's Reportlet apps script (I had to request the code for Reportlet directly from Andrew). Reportlet does a nice job of representing info from a sheet and charting data, however it can take some 30 seconds to load. After communicating with Andrew with the hope that New Visions would invest effort into tuning the script, it seems that Reportlet is not part of their development program.

Over my next posts on this topic, I will demonstrate how I have used Awesome Table Gadget to pull data from Gsheets to present specific data to the logged in user. I will show how Apps Sheet can be used to build apps for users to log into the same data on Android, IOS and through a web app. Finally, I will demonstrate how to post data to a GSheet using a simple form (based on the work of Martin Hawksey.)

In order for Sites and Sheets to work like an LMS, the data in the GSheet for each student must be as up-to-date as possible; most VLEs do an extraction nightly so that data stored that day is available for the next morning. For this to work, my Student IMS will need to run a scheduled report each evening that exports as a spreadsheet or html file, so that the import functions in Gsheets can refresh each night automatically. The export and import of data from the IMS is the final phase of feasibility testing and should work in theory!

Lesson planning with Google Sheets and Calendar

When I attended university in Edinburgh, I worked part time in a pizza restaurant. One of the chefs was doing a post graduate degree in psychology; his thesis was on connecting the brain to a peripheral data source to extend the capacity of the brain. I can't help thinking that Smartphones are a big step toward this, along with other research in controlling devices with the mind and eye movement.

Access Timetable GSheet here

For me, I don't remember my timetable; I don't need to if it is on my phone. The problem is getting my timetable and planning off my computer and onto my phone.  Google Apps Script, or GAS, allows the various Google apps to talk to each other so it is possible to build a school timetable into a GSheet and export it to GCal. Inspired by some work by Martin Hawksey and Romain Vialard, I have written/used a script that imports, deletes and exports the content of my calendars, and updates them.

function updateLessons(){
  var sh = SpreadsheetApp.getActiveSheet();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var calName = sh.getRange("B1").getValue();
  var date_deb = sh.getRange("C1").getValue();
  var date_fin = sh.getRange("D1").getValue();
  var cond = sh.getRange("J2").getValue();
  var line = new Array();
  var toChangeTitleCnt = 0;
  var toChangeCnt = 0;
  var blank = "";
  if(calName=='default'){var cal = CalendarApp.getDefaultCalendar()}
  else{
    var cal = CalendarApp.openByName(calName)};
  if (cal)
  {
    var events = cal.getEvents(new Date(date_deb), new Date(date_fin),{max: 4000});
    var sel= sh.getRange(3,1,sh.getLastRow(), 10).getValues();
    for(e=0;e<events.length;++e){
      var changeFlag = false;
      var ID = events[e].getId();
      for(n=0;n<sel.length;++n){
        if ((sel[n][8] == "x"||sel[n][8] == "X")&&sel[n][0]==ID ){
          changeFlag = true
          sh.getRange(n+3,9,1,1).setBackgroundColor('#55ff55');
          //sh.getRange(n+3,7,1,1).setBackgroundColor('#55ff55');
          //sh.getRange(n+3,6,1,1).setBackgroundColor('#55ff55');
          Utilities.sleep(50);
          SpreadsheetApp.flush();
          Logger.log('FLAG '+ e)
          sh.getRange(n+3,9,1,1).setValue(blank);
          sh.getRange(n+3,9,1,1).setBackgroundColor('#ffffff');
          break
        }
      }
      if(changeFlag){
        try{
          var toChangeTitle = events[e].setTitle(sel[n][5]);
          ++toChangeTitleCnt;
          var toChange = events[e].setDescription(sel[n][6]);
          var toChangeLoc = events[e].setLocation(sel[n][4]);
       
          ++toChangeCnt;
          changeFlag = false;
          Logger.log('Lesson Title updated : '+sel[n][5]);
          Logger.log('Lesson updated : '+sel[n][6]);
          }catch(Err){Logger.log('Event from a series')}
      }
    }
  }
  var msg = toChangeTitleCnt + " lesson titles changed + " + toChangeCnt + " lessons updated in calendar '"+calName+"'";
  ss.toast("Update completed", msg, 3)
  Utilities.sleep(3000)            
}

The update feature above is the one I use most. I teach 8 classes, so each has its own sheet. When I make a change to my planning I run the update function and it updates the events in my calendar; my phone syncs with the calendar and my changes are automatically available to me.

When I am finished planning my week and have updated each class, I run the import Calendar function on my main sheet that pulls all of the events in the calendar back into my GSheet. I have a technician that prepares my science equipment for my classes, so this import is important. The script that runs the import splits the content in the calendar description field to separate my lesson plan from the tech requisitions. With the tech requisitions in a separate column, I have created another equipment sheet that builds my requisitions order for my tech.

function importCalendar(){
  clrdisplay()
  var cal = 0;
  var sheet = SpreadsheetApp.getActiveSheet();
  var calendarName = sheet.getRange("B1").getValue();
  if (calendarName == "default"){
    cal = CalendarApp.getDefaultCalendar();
  } else if (calendarName != "default") {
    cal = CalendarApp.openByName(calendarName);
  } 

  var events = cal.getEvents(new Date("September 1, 2014"), new Date("August 31, 2015"), {max: 4000});
   var day = new Array();
    day = ["Sun","Mon","Tue","Wed","Thu","Fri","Sat"]
 
  for (var i=0;i<events.length;i++) {
    var formRow = i+3;
 
    var tech = "=iferror(right(G" + formRow + ",len(G"  + formRow + ")-(find(\"||\",G"  + formRow + "))), \"No Practical Equipment\")";
    var update = "";
    var class = "=LEFT(F" + formRow + ",FIND(\" -\",F" + formRow + "))";
    var temp = ""
     temp = day [events[i].getStartTime().getDay()];
    var details=[[events[i].getId(), temp, events[i].getStartTime(), events[i].getEndTime(), events[i].getLocation(), events[i].getTitle(), events[i].getDescription(), tech, update, class]];
    var row=i+1;
    var range=sheet.getRange(row+2,1,1,10);
    range.setValues(details);
  }
}

function clrdisplay() {
  var sh = SpreadsheetApp.getActiveSheet();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var LL=ss.getLastRow()
  sh.getRange(3,1,LL,10).clear().setVerticalAlignment('MIDDLE');
} 

A couple of my colleagues have used my planner, and one has changed to use Planner.com, or Planneredu.com. These apps are nice, but I like the customisation building my own has permitted, and that my timetable/lesson plans are on my phone....and that I don't have to print out my tech requisitions because my tech is shared into my planning sheet.

Homework research

Homework has always been an issue for me, from when I was in school to now when I have to mark it. I have been reading Visible Learning by John Hattie  and smiled as he references Richard Russo (Russo, 2007, p.157 in Hattie, 2009, p. 235):

"She tried shit like doing her homework for a while, but it was counterproductive since she always did it wrong. Doing homework wrong, to her, was worse than not doing it at all, because doing it required time and effort and yielded the same results as not doing it, which required neither."

This is what Additional Maths and English were like for me at school; put in a lot of effort and not get the answers correct in maths, or receive greater criticism from the English teacher for misinterpreting what was expected.

Hattie refers to various research on the effect of homework on learning. The main conclusion is that it needs to not be complex, and has the highest effects "when homework involves rote learning, practice, or rehearsal of subject matter."

In an effort to generate time in class to allow students to do more practical investigations and increase their skills, I have been using a flipped learning model where students most access the basic knowledge of the topic and learn that before they attend class. Students access interactive learning resources that have poster style worksheets for them to complete as the view the presentation/video/simulation, after which they most complete a quiz. 

This model has been working but there are those students who do not do what is required of them and are not in a position to do the higher order application of the new knowledge in the investigation context. These students are given the opportunity to access the content with their teacher guiding them in class while the other students carry on developing their independent skills acquisition or application/analysis skills.

I expect students to get 100% on the quizzes; the quizzes provide immediate feedback with the correct answer! If a student can get 100% first time then well done, however if they cannot because they have not viewed the presentation content with an eye to identify and learn the content, then there is an issue. Is the task too complex? Are the students rushing the task? Are they even looking at the presentation before doing the quiz?

I need to eliminate the possibilities. The webpages I place the content in can be traced as to who accesses them and when. I believe I can eliminate the possibility of the students not accessing the content and if they rush the tasks by checking if they access and when. This will leave the possibility that the cloze passage activities I set are too complex--I don't think so, but by letting the students know that I am monitoring what they do and when, if there still is no improvement in their performances  then I can only conclude that complexity is an issue. If this is the case, I will have to reconsider my flip design or, at least, the style of pre-lesson homework I assign.

Collaborative eBooks

I want my students to get more out of being in my classes than simply doing well on the end of module test. I believe in Project Based Learning and in 21st Century Skills, and I want my students to be acquiring these skills as part of their normal class and homework behaviour.

The advent of Google Slides in our domain has allowed me to apply the SAMR model to the concept of the exercise book. In my Y8 class, the students work as a group to create an online exercise book, the EBook, in which all of the classroom theory, investigations, photos, video, tables, graphs, etc. must appear. The students are given a rubric at the start of each module that outlines what is expected of them for each lesson, it also defines presentation and group work expectations. 

The key that seems to have made the concept work is that a group is randomly selected at the start of each lesson to review the previous lesson's content and explain what they found; this has forced the students to do their homework and actually know what they are talking about. Feedback is provided by the class and myself as to the level of content knowledge the students have given, their presentation design, speaking skills and team involvement. 

Google docs is particularly effective in the observation of group involvement and task management skills shown by the students. Successful groups use the commenting functionality to tell  team mates what to do, and question each other as to what else is needed. Since I own the docs that the students use, I see all comments in me mail as they are made and I can monitor and respond where necessary to help out, or just give formative feedback. The revision history in Google docs shows precisely who did what and when; this is extraordinarily useful in identifying who is free-riding, and who is doing everything and not giving the others a chance to do their bit. 

Communication, cooperation, collaboration, team management and task management skills, along with presentation and social knowledge acquisition built in; a SAMR redefinition.

On top of the class presentation, I used the presentations on parents evening to highlight each child's knowledge in front of their parents--they had to present a slide of my choosing to their parents! The authenticity of the parent as an audience had a remarkable effect and I could only congratulate the kids on a job well done.

Jigsaw 2

I have used the Jigsaw model using research topics before, but I decided to try using it to allow my Gifted and Talented students have a go at it doing applied practicals for the structure and function of red blood cells and blood plasma.

I tend to overplan some lessons, particularly lessons I'm being observed on, and this was one of them. I designed 3 parts to the jigsaw, with a 4th section on students reviewing their homework theory. Having the 4 groups would allow me to move around a facilitate the various groups. For some reason, during the lesson, I decided to give the 4th group another practical to do that tied me to that table. 




Although the groups seemed to self manage well with the school nurse working with one group, which was the point, I was unable to chat with them and guide them to ensure they were on track to meet the demands of the tasks.

When pulling the jigsaw together, the group that presented did very well, showing that they had managed to complete the tasks with little guidance and connect the activities to the pre-lesson theory that they were asked to complete. Unfortunately, I didn't get to hear all of the groups and so could not confident that the students progressed in their skills.

After the lesson, I found and spoke to some of the students. All enjoyed the challenge of the lesson. Most had managed to build the jigsaw and share their knowledge and experience with their peers. Some felt that they needed more time to build understanding of each of the tasks as their teammates had not provided thorough enough feedback/peer teaching.

I am confident that the majority of students progressed but I need to revisit the practicals at the start of the next lesson on white blood cells. I will have the students rebuild their jigsaws and review the findings from each of the activities. 

On reflection, adding the extra activity which looked at WBCs was unnecessary but also could have been pushed to the next lesson with the blood plasma testing. This would have balanced the activities and created 2 experts per table for the magnification and surface area modelling activity. I believe this is a cool lesson and the next time I teach it, it will be awesome :)