Collect Quick Formative Feedback

We often send our students to complete independent writing work. We circulate the room and the students that are vocal get the attention. But what about those students that don't raise their hand to ask for help? How can we provide an easier way for them to reach out to us? Well, here's a way to create a custom menu for a document and collect some formative feedback.

Sample:
Spreadsheet collecting data, Document which can be copied with the code.

Our starting function is the onOpen function. This is a reserved word which the doc looks for in the script. As the document is opened this script is run. In this case we engage the DocumentApp library of code. The getUi begins the user interface. We createMenu and name it something. I've named it with a emoji of a hand to support the idea that we don't always need menus with words. This menu will show up as a new main menu like File or Edit. We then add a menu item with addItem. The first item is a sad face. the sad face is connected to the function of helpMe. The second item is a thumbs up connected to the function called doingGood. Lastly we all it all to the user interface with addToUi. These menu items are on separate lines but they are really strung together commands. The semicolon identifies the end of the line of commands.

function onOpen(e) {
  DocumentApp.getUi()
      .createMenu('✋')
      .addItem('😕', 'helpMe')
      .addItem('👍', 'doingGood')
      .addToUi();
}

The helpMe and doingGood functions are nearly identical except for the message we set in the secon column. I'll use the helpMe function to detail what's happening. The same will be true for the doingGood and any others you want to make.

To begin we set a global variable as the sheet ID. A global variable is set outside of the functions and is used for all the functions. Because our variable isn't changed by the program we could also declare it as a constant but we'll keep it simple and just use var to declare the sheetid variable. The sheet id can be found in the url of the sheet. The sheet needs to be shared as editable for it all to work.

Our helpMe function starts by assigning the spreadsheet by the id and the specific sheet by name to the sheet variable. We next identify the last row used and assign it to the variable lrow. We then get the range of the last used row represented by lrow plus 1. This would be the next empty row. This row with the column of 1 is the cell we get and set the value of the currently logged in user email. The range then is grabbed for the second column and the value is set to the words 'Help me!'.

var sheetid = '1C1bUbiouvFgeE6qfZjknW9zumpMLOHYcqQNjO39sVAI';

function helpMe() {
  var sheet = SpreadsheetApp.openById(sheetid).getSheetByName('Sheet1');
  var lrow = sheet.getLastRow();
  sheet.getRange(lrow+1, 1).setValue(Session.getActiveUser().getEmail());
  sheet.getRange(lrow+1, 2).setValue('Help me!');
}

function doingGood() {
  var sheet = SpreadsheetApp.openById(sheetid.toString()).getSheetByName('Sheet1');
  var lrow = sheet.getLastRow();
  sheet.getRange(lrow+1, 1).setValue(Session.getActiveUser().getEmail());
  sheet.getRange(lrow+1, 2).setValue('Doing Good');
}

This is a pretty simple code to create a quick menu for formative feedback. Consider stopping the class a few minutes into independent work and asking the class to select the menu item that best identifies how they currently feel about the topic. You would get immediate feedback on the spreadsheet. They may have to accept permissions the first time around. You can also add a timestamp to another column by setting the value to .setValue(new Date()) You may also want to set another column with a particular class name. There's alot you can do with this from here.

Auto Add User to Sheets

Ever needed to grab the name of the user that edited a line of a Sheet? Sure you can comb through the version history but isn't it better to just see the the information right on the sheet? Google Scripts provides a way to capture the user that edited. Let's take it one step further and make a bathroom log. Students logged in to their Google Account on the same domain as you will be able to click an icon and create a record that they are going to the restroom. No one will be able to mark the sheet while they are out. Sample

So the code we need to know to identify the user currently editing the sheet would be Session.getActiveUser().getEmail()
To break this down, the Session refers to the period a user is logged in. A session can be time limited or limited to the particular device being used. We use the Session to access the method of
getActiveUser()
The active user is the user logged in. From here the only selection is
getEmail()

Stringing these together and we  can identify who is using the sheet.

We begin our function creating our variables to represent the Spreadsheet and the currently active sheet.

function bathroom() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
}

Because we will be adding the user to the last empty row we want to identify which row that is. sheet.getLastRow() returns the value of the last filled row. So the next available row would be sheet.getLastRow() + 1.

function bathroom() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastRow = sheet.getLastRow() + 1;
}

We need to consider the structure of our spreadsheet. We'll create a sheet with the following headers.

EmailOutInDuration
We can use the function getColumnByName used in the previous post here. But I'll keep it very simple and we'll use the column numbers. We're going to want to know if a student has come back from the bathroom first. To do this we want to check the last filled row and the 3rd column which is labeled "In".

function bathroom() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastRow = sheet.getLastRow() + 1;
  var lastcell = sheet.getRange(lastRow-1, 3).getValue();
Logger.log("Last Row " + lastRow + " Last Cell Value " + lastcell)
}

I've thrown a loger line here to test the code and see what values it returns. It's helpful to do this as your write. I've torn my hair out and spent days staring at code not realizing I only thought I knew the value of a particular variable. Don't assume, debug.

The last entered user is also going to be needed. We can get this in the same manner we retrieved the lastcell variable.

function bathroom() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastRow = sheet.getLastRow() + 1;
  var lastcell = sheet.getRange(lastRow-1, 3).getValue();
  var user = sheet.getRange(lastRow-1, 1).getValue();
Logger.log("Last Row " + lastRow + " Last Cell Value " + lastcell + " User " + user)
}

Now we start doing some comparisons to know the condition our sheet is in.
 if (lastcell.length == 0 && user == Session.getActiveUser().getEmail())
Here we are checking if the number of characters in the lastcell variable is equal to 0 AND the user variable has the same email in it as the current user in the sheet then we will do something. If these two things are true then it means the student has come back from the bathroom and is checking in. We will need to enter the current timestamp in the "In" column and we can set the formula to calculate how long they were in the bathroom.
We initialize and set a variable to the result of setting the timestamp. Assigning this to a variable can help to identify errors if things are going wrong but not absolutely needed. We grab the range using the last row variable which we already advanced by 1. To get the last filled row we reduce the variable by 1. The column is column number 3. We set the value to a new date object which we typically read as a timestamp.
we also move forward with setting a formula in Row and Column format. The formula here is relative to the cell the formula is entered in. In this case R[0] stands for the current cell's row. C[-1] is one column less. since we are putting our formula in column 4 one column back would be column 3 which we just put our In timestamp in. We then subtract the C[-2] column  or column 2 of the same row. This gives us a duration of how long they were in the bathroom.

function bathroom() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastRow = sheet.getLastRow() + 1;
  var lastcell = sheet.getRange(lastRow-1, 3).getValue();
  var user = sheet.getRange(lastRow-1, 1).getValue();
  if (lastcell.length == 0 && user == Session.getActiveUser().getEmail()){
    var col2 = sheet.getRange(lastRow-1, 3).setValue(new Date());
    var formula = sheet.getRange(lastRow-1, 4).setFormulaR1C1("=R[0]C[-1] - R[0]C[-2]")
  }
}

We now have the if statement identifying when a user returns. But what if the user clicking the icon is not the user in the bathroom. They need to know that they can't go yet. We again check if the lastcell length is 0 meaning it's blank. We also check to determine the user in the last row is not the current user. The usage of != means not equal. If both conditions are true we assign the variable ui an alert with a message.

function bathroom() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastRow = sheet.getLastRow() + 1;
  var lastcell = sheet.getRange(lastRow-1, 3).getValue();
  var user = sheet.getRange(lastRow-1, 1).getValue();
  if (lastcell.length == 0 && user == Session.getActiveUser().getEmail()){
    var col2 = sheet.getRange(lastRow-1, 3).setValue(new Date());
    var formula = sheet.getRange(lastRow-1, 4).setFormulaR1C1("=R[0]C[-1] - R[0]C[-2]")
  }else if(lastcell.length ==0 && user != Session.getActiveUser().getEmail()){
    var ui =SpreadsheetApp.getUi().alert('Sorry, someone is currently out.')
    }
  }
}

Finally at the end if the user is not the one returning and if the user is not still out and we can add a new user we do so. The else statement is our default statement. If 1 then 1a, else if 2 then 2a, else default. We can think of the progression like this. In our action for the default response we use the variable col1 to capture the setting of the user. The range is gotten of the last row variable which represents a new row and the column of 1. We set this cell to the value of the current users email address. We then set the second column on the same row as a new date object which we know of as a timestamp.

function bathroom() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastRow = sheet.getLastRow() + 1;
  var lastcell = sheet.getRange(lastRow-1, 3).getValue();
  var user = sheet.getRange(lastRow-1, 1).getValue();
  if (lastcell.length == 0 && user == Session.getActiveUser().getEmail()){
    var col2 = sheet.getRange(lastRow-1, 3).setValue(new Date());
    var formula = sheet.getRange(lastRow-1, 4).setFormulaR1C1("=R[0]C[-1] - R[0]C[-2]")
  }else if(lastcell.length ==0 && user != Session.getActiveUser().getEmail()){
    var ui =SpreadsheetApp.getUi().alert('Sorry, someone is currently out.')
  }else{
    var col1 = sheet.getRange(lastRow, 1).setValue(Session.getActiveUser().getEmail());
    var col2 = sheet.getRange(lastRow, 2).setValue(new Date());
  }
}

This function can then be assigned to an image or a menu. Consider using some of these techniques for an onEdit trigger to track who changes a line on a spreadsheet.

Tour Creator Drawings

I have admittedly been sitting on this for a while now and have just had the opportunity to post about it. How can we take advantage of Google's Tour Creator in education even when we don't have a 360 camera? Make Tour Creator Drawings with your class!


If you don't know Tour Creator yet it's a web browser app online from google which allows users to create 360 immersive tours. Traditionally we think of these tours using photos. Here's my tour I made of Auschwitz. But we can also create the tour using drawings. Here's my sample drawing tour (turn the sound on for this).

Well how did I do that and how can you do it? Easy. Below is my brief intro video.

Here's my slide deck on how to make them.  Here's a Google Drawing Template you can use. And below is my more detailed how to video demo. 



Looking forward to seeing your Tour Creator Drawings!




How do you make a Gif Accessible?

How do you make a G if Accessible? T he question came up after I sent off a Gif of some teenage Caucasian boys flashing gang signs in a wan...