A colleague recently approached me with a question.  She wanted to create a sign-up form for weekend events for our dorm students with a limited number of available participants.  The idea was that once a certain number of students signed up, no further students would be allowed to join that event.  I had not yet tried Google Scripts, but thought this might be the perfect project.

It took a bit, and some serious internet help, but I finally came up with a script that works.  I am sure there is a more elegant way to do this, but it works for now.  There are two parts to the solution.  The first is a spreadsheet that looks like the following:

The important thing is the order of the columns, the actual titles are not important.  Also, when setting up the form, you do not have to fill in the cell under Form ID.  The script will generate that automatically.  You can also list as many events as you want.  

Copy the script from below and paste it in the Script Editor (found under Tools).  Save the script (name does not matter) and close both the script editor and the spreadsheet.  The next time you open the spreadsheet, you will see an additional menu called Dorm (feel free to change it in the script), with one option, "Set up form."  Choose that option, and if all goes well, you will see the Form ID pasted in your spreadsheet, a new sheet added called Form Responses 1, and a new form (with the title you specified in cell F2) in your Google Drive.

The form can now be distributed and once the max participant number is reached, the "Yes" option will be removed for that activity.  If you manually change the numbers in the spreadsheet, the form should automatically be updated.

One final thing, if you want any errors to be sent, change the email address line in the script.

/**
 * A special function that inserts a custom menu when the spreadsheet opens.  Changes the name of the main sheet to Activities
 */
function onOpen() {
  var menu = [{name: 'Set up form', functionName: 'setUpForm'}];
  SpreadsheetApp.getActive().addMenu('Dorm', menu);
  SpreadsheetApp.getActiveSheet().setName('Activities');
}

/**
* Gets the range of information from the current (i.e. which ever spreadsheet this script is attached to)
* spreadsheet.  Passes those values on to the actual function that creates the form.
*/
function setUpForm() {
  var ss = SpreadsheetApp.getActive();
  var range = ss.getDataRange();
  var values = range.getValues();
  setUpWeekendForm(ss, values);
}

/**
* This function sets up the form to be filled out.  Questions will be multiple choice and required.  The description of
* the events are taken from the cells in the spreadsheet
*/
function setUpWeekendForm(ss, values) {  
  
  var form = FormApp.create(values[1][5]); // creates a form using the title from cell F2
  var formID = form.getId();
  
  // create triggers for form submission and for spreadsheet editing
  ScriptApp.newTrigger('onFormSubmit').forSpreadsheet(ss).onFormSubmit().create();
  ScriptApp.newTrigger('onSpreadsheetEdit').forSpreadsheet(ss).onEdit().create();
  
  ss.getActiveSheet().getRange(2, 8).setValue(formID); // adds the form key as a string to the sheet so it can be retrieved later
  
  form.setCollectEmail(true); // forces students to log in to SSFS Google Apps account
  form.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId());
  
  //Setup the section header with date/time information.
  var section = form.addSectionHeaderItem()
  section.setTitle('Weekend Activities');
  
  
  // create an array of choices.  Each one will become a Multiple Choice question item
  var activities = new Array();
  var description = new Array();
  var words = ''; //To be used in the section description.
  for (var i = 1; i < values.length; i++) {
    var activity = values[i];
    var title = activity[0];
    var from = activity[2]
    var date = activity[1].toLocaleDateString();
    var maxParticipants = activity[3];
    words += title + ' on ' + date + ' from ' + from + ' (' + maxParticipants + ' people max.)\n';
    description.push(words);
    activities.push(title);
  }
  
  section.setHelpText(words); //Sets the text describing the activities.  More can be manually added in the form later.
  
  // Actually creates the multiple choice questions.  One for each title on the spreadsheet.
  for (var i = 0; i < activities.length; i++) {
    var mcItem = form.addMultipleChoiceItem();
    mcItem.setTitle(activities[i]);
    mcItem.setRequired(true);
    mcItem.setChoices([mcItem.createChoice('Yes'), mcItem.createChoice('No')]);
  }
  
}

/*
* Run when the form is submitted.  Checks to see if there are still spaces left for each activity.  If not,
* the "Yes" option is removed from that 'question.'
*/
function onFormSubmit(e) {
  
   try {

     var ss = SpreadsheetApp.getActive();
     var sheet = ss.getSheetByName("Activities"); 
     var range = ss.getDataRange();
     var values = range.getValues();
     var ssValues = SpreadsheetApp.getActive().getSheetByName('Activities').getDataRange().getValues();
     var formKey = ss.getSheetByName("Activities").getRange(2, 8).getValue();
     var form = FormApp.openById(formKey);
     var questions = form.getItems(FormApp.ItemType.MULTIPLE_CHOICE);
     
     /* Checkes each row of the info spreadsheet and first gets how many people are currently registered.
     * Then, adds 1 to each activity where the submitter chose "Yes."
     */
     for (var i = 1; i < ssValues.length; i++) {
       var currentValue = ssValues[i][4];
       
       if (e.values[i+1] == 'Yes') {
         sheet.getRange(i+1, 5).setValue(currentValue + 1);
       }
     }
     
  } catch (e) {
    MailApp.sendEmail("[email protected]", "Error report", e.lineNumber + e.message); // for debugging
  }
  
  /* Loops through all the rows of the info spreadsheet.  Gets the current enrollment
  * and checks to see if that number is greater than or equal to the max enrollment.
  * If it is, the "Yes" option is removed so that people cannot enroll in that activity.
  */
  for (var j = 1; j < ssValues.length; j++) {
    var currentValue = ssValues[j][4];  
    if (currentValue >= ssValues[j][3]) {
       var question = questions[j-1].asMultipleChoiceItem();
       question.setChoices([question.createChoice('No')]);
    } else {
       var question = questions[j-1].asMultipleChoiceItem();
       question.setChoices([question.createChoice('Yes'), question.createChoice('No')]);   
    }
  }
}

/*
* This function checks to see if the current enrollment totals have changed.  If the total drops below
* the max participant number, than the "Yes" option on the form is re-added.
*/
function onSpreadsheetEdit() {
  
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Activities"); 
  var range = ss.getDataRange();
  var values = range.getValues();
  var ssValues = SpreadsheetApp.getActive().getSheetByName('Activities').getDataRange().getValues();
  var formKey = ss.getSheetByName("Activities").getRange(2, 8).getValue();
  var form = FormApp.openById(formKey);
  var questions = form.getItems(FormApp.ItemType.MULTIPLE_CHOICE);
  
  for (var j = 1; j < ssValues.length; j++) {
    var currentValue = ssValues[j][4];  
    if (currentValue >= ssValues[j][3]) {
       var question = questions[j-1].asMultipleChoiceItem();
       question.setChoices([question.createChoice('No')]);
    } else {
       var question = questions[j-1].asMultipleChoiceItem();
       question.setChoices([question.createChoice('Yes'), question.createChoice('No')]);   
    }
  }
}

I am starting the process of creating my first android app.  My idea is to create a simple app that will display our school's lunch each day.

This past weekend, our school was visited by Steve Hargadon and David Warlick, two gentleman with very interesting ideas about the future of education.  I spent a wonderful Saturday engaged in thought-provoking dialog about current teaching practices, both at our school and elsewhere.  One of Steve's main points was he felt students today needed to have a positive digital footprint.  This could take the form of a blog or online portfolio of their work.  As teachers, Steve felt it was our job to not only encourage this practice, but to model it as well.

Hence this site.  I teach one class, an introduction to computer programming.  My hope is that this site can serve as a collection of resources for individuals learning to program as well as a place to record my thoughts about programming and education in general and share what I have learned.   Perhaps it will inspire a student to create their own site someday.