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.