gistfile1.js

// Available under Creative Commons Attribution-ShareAlike 2.5 UK: Scotland License
// Bashed by mhawksey

var SPREADSHEET_ID = ""; // If using within Google Sites paste the spreadsheet key here (this is everything between key= and &hl in the address bar)

// JavaScript Document
function onOpen() {
  if (SPREADSHEET_ID == ""){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [ {name: "Setup", functionName: "setup"}, {name: "Send Booking Confirmations", functionName: "sendBookingConf"}];
    ss.addMenu("Super Event Registration", menuEntries);
  }
}

// CSS Styling Script by James Ferreira http://sites.google.com/site/scriptsexamples/gs-interactive
function applyCSS(element, style){
  for (var key in style){
    element.setStyleAttribute(key, style[key]); 
  }  
}

function doGet(e){
  var doc = SpreadsheetApp.openById(getAnID());
ScriptProperties.setProperty('debug', getAnID());
  var headers = [];
  var soc = "";
  var eoc = "";
  var sop = "";
  var eop = "";
  try{
  // setup booking form styling
    var _app = {"padding": "10px",
                "width": "500px"}
    var _eventLogo = {"float":"right",
                      "paddingLeft":"500px"}

    var _eventTitle = {"textAlign":"center",
                       "fontSize":"135%",
                       "fontWeight":"bold"}

    var _eventByline = {"textAlign":"center",
                        "fontSize":"100%",
                        "fontStyle":"italic",
                        "paddingBottom":"10px"}

    var _eventDesc = {"textAlign":"left",
                      "fontSize":"100%",
                      "paddingBottom":"10px"}

    var _helperText = {"color":"red",
                       "fontStyle":"italic" }

    var _respHead = {"textAlign":"center",
                     "fontSize":"110%",
                     "fontWeight":"bold"}

    var _respText = {"text-align":"center",
                     "fontSize":"100%",
                     "fontStyle":"italic",
                     "paddingTop":"10px"}

    var _itemLabel = {"fontWeight":"bold",
                      "marginTop":"8px" }

    var _itemHelp = {"fontStyle":"italic" }

    var _button = {"marginTop":"15px" }

                
    // grab data for the programme
    var sheet = doc.getSheetByName("Programme");
    var dataRange = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
    var data = dataRange.getValues();
    
    // Build UI
    var app = UiApp.createApplication().setTitle("Booking Form");
    applyCSS(app, _app);

    var headPanel = app.createFlowPanel();
    headPanel.setStyleAttribute("maxWidth", "500px");
    
    // add title description
    if (data[5][1]){
      var eventLogo = app.createImage(data[5][1]);
      applyCSS(eventLogo, _eventLogo);
      headPanel.add(eventLogo);
    }

    var eventTitle = app.createLabel().setText(data[2][1]);
    applyCSS(eventTitle, _eventTitle);
    headPanel.add(eventTitle);

    var eventByline = app.createLabel().setText(data[3][1]);
    applyCSS(eventByline, _eventByline);
    headPanel.add(eventByline);

    var eventDesc = app.createLabel().setText(data[4][1]).setId("desText");
    applyCSS(eventDesc, _eventDesc);
    headPanel.add(eventDesc);
    
    var helperText = app.createLabel().setText("").setId("helperText");
    applyCSS(helperText, _helperText);
    headPanel.add(helperText);
    
    var respPanel = app.createFlowPanel();
    respPanel.setStyleAttribute("maxWidth", "500px").setId("response_area").setVisible(false);
    var respHead = app.createLabel().setText("Confirmation").setId("respHead");
    applyCSS(respHead, _respHead);
    respPanel.add(respHead);

    var respText = app.createLabel().setText("").setId("respText");
    applyCSS(respText, _respText);
    respPanel.add(respText);

    var formPanel = app.createFlowPanel();
    formPanel.setStyleAttribute("maxWidth", "500px").setId("data_entry");
    // loop through remainder of data grabbing credential and programme blocks
    for (var i = 0; i < data.length; i++){
      if (data[i][0] == "SoC"){
        soc = i;
      }else if (data[i][0] == "EoC"){
        eoc = i;
      }else if (data[i][0] == "SoP"){
        sop = i;
      }else if (data[i][0] == "EoP"){
        eop = i;
      }
    }
    if (soc && eoc){
      var sessionNumber = 0;
      var reqString = "";
      // build credentials block
      for (var i = soc+1; i < eoc; i++){
        var itemLabel = app.createLabel(data[i][0]);
        var normName = normalizeHeader(data[i][0]);
        applyCSS(itemLabel, _itemLabel);
        formPanel.add(itemLabel);

        var itemHelp = app.createLabel(data[i][2]);
        itemHelp.setId(normName+"_help");
        applyCSS(itemHelp, _itemHelp);
        formPanel.add(itemHelp);

        var itemField = "";
        switch(data[i][1]){
          case "textfield":
            itemField = app.createTextBox();
          break;
          case "textarea":
            itemField = app.createTextArea();
            itemField.setStyleAttribute("height", "80px");
          break;
          case "checkbox":
            itemField = app.createCheckBox();
          break;
          case "radio":
            var temp = data[i][3];
            var tempArr = temp.split(",");
            for (var j = 0; j < tempArr.length; j++){
              var radio = app.createRadioButton("A"+(i+1), tempArr[j]); 
              formPanel.add(radio);
            }
          break;
          case "selectlist":
            itemField = app.createListBox();
            var temp = data[i][3];
            var tempArr = temp.split(",");
            for (var j = 0; j < tempArr.length; j++){
              itemField.addItem(tempArr[j]); 
            }
          break;
          }
        if (itemField){
          itemField.setStyleAttribute("width", "500px");
          itemField.setName(normName);
          itemField.setId(normName);
          formPanel.add(itemField);
          headers.push(data[i][0]);
        }
        // build a list of required fields
        if (data[i][4] == "yes"){
          reqString = reqString + data[i][0] + ",";
        }
      }
      ScriptProperties.setProperty('required_fields',reqString);
    }
   ScriptProperties.setProperty('debug',"here");
    if (sop && eop){

      // build programme
      var progPanel = app.createFlexTable();
      progPanel.setStyleAttribute("marginTop", "10px")
      progPanel.setWidth("100%");
      for (var i = sop+1; i < eop; i++){
        var timeLabelText = "";
        if (data[i][0]){
          timeLabelText = Utilities.formatDate(data[i][0], "GMT", "HH:mm");
        }
        var timeLabel = app.createLabel().setText(timeLabelText);
        if (!data[i][2]){
          // guess it is a main session
          var sessionEntry = app.createLabel().setText(data[i][1]);
        } else {
          //guess it's a selectbox
          sessionNumber ++;
          var sessionName = 'Session '+sessionNumber;
          var sessionEntry = app.createListBox().setName(normalizeHeader(sessionName));
          sessionEntry.setStyleAttribute("fontSize", "13px"); // IE fix
          sessionEntry.setStyleAttribute("width", "450px"); // IE fix
          headers.push(sessionName);
          sessionEntry.addItem("No Selection"); 
          // get current bookings
          if (doc.getSheetByName("Bookings")){
            var bookingSheet = doc.getSheetByName("Bookings");
            var bookings = getRowsData(bookingSheet);
            var c = countUniqueInCol(bookings, normalizeHeader(sessionName));
          }
          for (var j = 1; j < data[i].length; j++){
            if (data[i][j]){
              var sesMax = getSessionMax(data[i][j]);
              var sesText = getSessionDetails(data[i][j]);
              var sesID = getSessionId(data[i][j]);
              if (sesText && c && sesMax && sesID){
                var itemText = "";                  
                if (sesText != "No Selection" && c[sesID] >= sesMax){
                  itemText = "FULL ";
                } 
                itemText = itemText + sesText;
              }
              if(itemText!=""){
               sessionEntry.addItem(itemText); 
              }
            }
          }
        }
        progPanel.setWidget(i-sop-1, 0, timeLabel);
        progPanel.setWidget(i-sop-1, 1, sessionEntry);
        formPanel.add(progPanel);
        ScriptProperties.setProperty('number_of_sessions',sessionNumber);
      }
    }
      
      app.add(headPanel);
      app.add(respPanel);
      app.add(formPanel);
      var button = app.createButton('Submit Booking');
      var ch = app.createServerClickHandler('click').addCallbackElement(formPanel);
      button.addClickHandler(ch);
      applyCSS(button, _button);

      // bit that makes sure a booking form is there and has all the headers
      if (!doc.getSheetByName("Bookings")){
        var sheet = doc.insertSheet("Bookings");
      } else {
        var sheet = doc.getSheetByName("Bookings");
      }
      headers.push("Admin Timestamp");
      headers.push("Admin Status");
      headers.push("Admin Comment");
      var cell = sheet.getRange('a1');
      var col = 0;
      for (var j in headers) {
        cell.offset(0, col).setValue(headers[j]);
        col++;
      } 
      formPanel.add(button);
     
      return app;
//doc.show(app);
  } catch(e){

    Logger.log(e);
  }
}

function click(e) {
  var app = UiApp.getActiveApplication();
  var reqString = ScriptProperties.getProperty('required_fields');
  var reqArr = reqString.split(",");
  var errorMsg = "";
  var noSessions = ScriptProperties.getProperty('number_of_sessions');
  var passfail = false;
  var doc = SpreadsheetApp.openById(getAnID());
  // basic form validation
  for (i in reqArr){
    if (e.parameter[normalizeHeader(reqArr[i])] == "" || (reqArr[i] == "Email" && validEmail(e.parameter.email))){
      errorMsg = errorMsg + "There is something wrong in "+reqArr[i]+". ";
      passfail = true;
    }
  }
  if (noSessions > 0){
    for (var s=1; s <= noSessions; s++){
      var sessionValue = e.parameter["session"+s];
      if (checkIfSpace(sessionValue)){
        errorMsg = errorMsg + "Your session "+s+" selection is full\r\n";
        passfail = true;
      }   
    }
  }
  // check session isn't full
  if (passfail){
    app.getElementById('helperText').setText(errorMsg);
  } else {
    app.getElementById('helperText').setText("");
    // if it is all clear push the entered data to the booking form  
    var sheet = doc.getSheetByName("Bookings");
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];;
    var nextRow = sheet.getLastRow();
    var cell = sheet.getRange('a1');
    var emailText = "";
    var val = ""
    var col = 0;
    for (i in headers){
      if (headers[i].substr(0,7) == "Session"){
        val = getSessionId(e.parameter[normalizeHeader(headers[i])]);
        emailText = emailText + headers[i] + ": " + e.parameter[normalizeHeader(headers[i])] + "\n";
      } else if (headers[i] == "Admin Timestamp"){
        val = new Date();
      } else if (headers[i] == "Admin Status"){
        val = "unconfirmed";
      } else if (headers[i] == "Admin Comment"){
        val = "";
      } else {
        val = e.parameter[normalizeHeader(headers[i])];
        emailText = emailText + headers[i] + ": " + val + "\n"; 
      }
      
      cell.offset(nextRow, col).setValue(val);
      col++;
    }
    // change window to confirm submission
    var progSheet = doc.getSheetByName("Programme");
    var customTextTemplate = progSheet.getRange("B8").getValue();
    var customText = fillInTemplateFromObject(customTextTemplate, e.parameter);
    var emailSubject = progSheet.getRange("B9").getValue();
    emailText = progSheet.getRange("B10").getValue() + emailText;
    //Logger.log(emailSubject);
    MailApp.sendEmail(e.parameter.email, emailSubject, emailText);
    app.getElementById('data_entry').setVisible(false);
    app.getElementById('response_area').setVisible(true);
    app.getElementById('respText').setVisible(true);
    app.getElementById('respText').setText(customText);
  }
 return app;
}

function getAnID(){
  if (SPREADSHEET_ID != ""){
    var id = SPREADSHEET_ID;
  } else {
    var id = ScriptProperties.getProperty('active');
  }
return id;
}
function sendBookingConf(){
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = doc.getSheetByName("Bookings");
  var dataRange = dataSheet.getRange(2, 1, dataSheet.getLastRow(), dataSheet.getLastColumn());
  var headerNames = dataSheet.getRange(1, 1, 1, dataSheet.getLastColumn()).getValues();
  for (var j = 0; j < headerNames[0].length; ++j){
    if (headerNames[0][j] == "Admin Status"){
      var emailSentColumn = j+1;
    }
  }
  var templateSheet = doc.getSheetByName("Email Templates");
  var emailSubject = templateSheet.getRange("B1").getValue();
  var emailTemplate = templateSheet.getRange("B2").getValue();
  var replyAdd = templateSheet.getRange("B4").getValue();
  var replyName = templateSheet.getRange("B5").getValue();
  //var emailSentColumn = 14;
  
  // Create one JavaScript object per row of data.
  objects = getRowsData(dataSheet, dataRange);

  // For every row object, create a personalized email from a template and send
  // it to the appropriate person.
  for (var i = 0; i < objects.length; ++i) {
    // Get a row object
    var rowData = objects[i];
    if (rowData.adminStatus == "confirmed") {  // Prevents  sending duplicates
      var emailText = fillInTemplateFromObject(emailTemplate, rowData);
      var emailAddress = rowData.email;
      MailApp.sendEmail(emailAddress, emailSubject, emailText, {htmlBody: emailText, name: replyName, replyTo: replyAdd });    
      dataSheet.getRange(i + 2,emailSentColumn).setValue("emailed").setBackgroundRGB(221, 221, 221);
      dataSheet.getRange(i + 2,emailSentColumn-1).setValue(new Date());       
    } 
  }
  doc.toast("", "Emails sent", 20);
}
// countUniqueInCol counts the occurence of items in a column and returns an array
// Arguments:
//   - object:  an Array of Objects, each of which contains data for a row (use getRowsData())
//   - colName: normalized name of the column you want to count unique items
function countUniqueInCol(object, colName){
var myArray = [];
for (var i = 0; i < object.length; ++i){
  myArray.push(object[i][colName]);
}

// Modified from http://www.htmlforums.com/showpost.php?p=600433&postcount=2
var o = [];          
for (var j=0; j<myArray.length; j++) {
    var key = myArray[j].toString(); // make it an associative array
    if (!o[key]) {
        o[key] = 1;
    } else {
      o[key] = o[key] + 1;
    }
}
return o;
}
function checkIfSpace(txt){
  var re1='(FULL) \\[';  // Word 1
  var p = new RegExp(re1,["i"]);
  var m = p.exec(txt);
  if (m != null){
    return true;
  }
}
function getSessionMax(sessionString){
  //parse parallel details
  var re1='\\[max:(.*?)\\]';	// Non-greedy match on filler
  var p = new RegExp(re1,["i"]);
  var m = p.exec(sessionString);
  return m[1];
}
function getSessionId(sessionString){
  var re1='\\[(.*?)\\]';	// Square Braces 1
  var p = new RegExp(re1,["i"]);
  var m = p.exec(sessionString);
  if (m != null ){
    var r = m[1];
  } else {
    var r = "No Selection";
  }
  return r;
}
function getSessionDetails(sessionString){
  sessionString = sessionString.replace("&amp;","&");
  var re1='(\\[.*?\\].*?)';	// Non-greedy match on filler
  var re2='(\\[.*?\\])';	// Square Braces 1
  var p = new RegExp(re1+re2,["i"]);
  var m = p.exec(sessionString);
  return m[1];
}

// http://www.google.sc/support/forum/p/apps-script/thread?tid=345591f349a25cb4&hl=en
function setup() {
  ScriptProperties.setProperty('active', SpreadsheetApp.getActiveSpreadsheet().getId());
}

// http://www.google.com/support/forum/p/apps-script/thread?tid=218f411d2d3d0362&hl=en&fid=218f411d2d3d0362000492bcafc5eb21&hltp=2
function validEmail(emailAdd){
  var emailPattern = /^([A-Za-z0-9_\-\.])+\@([A-Za-z0-9_\-\.])+\.([A-Za-z]{2,4})$/;
  if(!emailPattern.test(emailAdd)){
  return true;
  }
}


// setRowsData fills in one row of data per object defined in the objects Array.
// For every Column, it checks if data objects define a value for it.
// Arguments:
//   - sheet: the Sheet Object where the data will be written
//   - objects:  an Array of Objects, each of which contains data for a row
//   - optHeadersRange: a Range of cells where the column headers are defined. This
//     defaults to the entire first row in sheet.
//   - optFirstDataRowIndex: index of the first row where data should be written. This
//     defaults to the row immediately below the headers.
function setRowsData(sheet, objects, optHeadersRange, optFirstDataRowIndex) {
  var headersRange = optHeadersRange || sheet.getRange(1, 1, 1, sheet.getMaxColumns());
  var firstDataRowIndex = optFirstDataRowIndex || headersRange.getRowIndex() + 1;
  var headers = normalizeHeaders(headersRange.getValues()[0]);

  var data = [];
  for (var i = 0; i < objects.length; ++i) {
    var values = []
    for (j = 0; j < headers.length; ++j) {
      var header = headers[j];
      values.push(header.length > 0 && objects[i][header] ? objects[i][header] : "");
    }
    data.push(values);
  }

  var destinationRange = sheet.getRange(firstDataRowIndex, headersRange.getColumnIndex(), 
                                        objects.length, headers.length);
  destinationRange.setValues(data);
}


// getRowsData iterates row by row in the input range and returns an array of objects.
// Each object contains all the data for a given row, indexed by its normalized column name.
// Arguments:
//   - sheet: the sheet object that contains the data to be processed
//   - range: the exact range of cells where the data is stored
//       This argument is optional and it defaults to all the cells except those in the first row
//       or all the cells below columnHeadersRowIndex (if defined).
//   - columnHeadersRowIndex: specifies the row number where the column names are stored.
//       This argument is optional and it defaults to the row immediately above range; 
// Returns an Array of objects.
function getRowsData(sheet, range, columnHeadersRowIndex) {
  var headersIndex = columnHeadersRowIndex || range ? range.getRowIndex() - 1 : 1;
  var dataRange = range || 
    sheet.getRange(headersIndex + 1, 1, sheet.getMaxRows() - headersIndex, sheet.getMaxColumns());
  var numColumns = dataRange.getEndColumn() - dataRange.getColumn() + 1;
  var headersRange = sheet.getRange(headersIndex, dataRange.getColumn(), 1, numColumns);
  var headers = headersRange.getValues()[0];
  return getObjects(dataRange.getValues(), normalizeHeaders(headers));
}

// Replaces markers in a template string with values define in a JavaScript data object.
// Arguments:
//   - template: string containing markers, for instance ${"Column name"}
//   - data: JavaScript object with values to that will replace markers. For instance
//           data.columnName will replace marker ${"Column name"}
// Returns a string without markers. If no data is found to replace a marker, it is
// simply removed.
function fillInTemplateFromObject(template, data) {
  var email = template;
  // Search for all the variables to be replaced, for instance ${"Column name"}
  var templateVars = template.match(/\$\{\"[^\"]+\"\}/g);

  // Replace variables from the template with the actual values from the data object.
  // If no value is available, replace with the empty string.
  for (var i = 0; i < templateVars.length; ++i) {
    // normalizeHeader ignores ${"} so we can call it directly here.
    var variableData = data[normalizeHeader(templateVars[i])];
    email = email.replace(templateVars[i], variableData || "");
  }

  return email;
}

// For every row of data in data, generates an object that contains the data. Names of
// object fields are defined in keys.
// Arguments:
//   - data: JavaScript 2d array
//   - keys: Array of Strings that define the property names for the objects to create
function getObjects(data, keys) {
  var objects = [];
  for (var i = 0; i < data.length; ++i) {
    var object = {};
    var hasData = false;
    for (var j = 0; j < data[i].length; ++j) {
      var cellData = data[i][j];
      if (isCellEmpty(cellData)) {
        continue;
      }
      object[keys[j]] = cellData;
      hasData = true;
    }
    if (hasData) {
      objects.push(object);
    }
  }
  return objects;
}

// Returns an Array of normalized Strings. 
// Empty Strings are returned for all Strings that could not be successfully normalized.
// Arguments:
//   - headers: Array of Strings to normalize
function normalizeHeaders(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    keys.push(normalizeHeader(headers[i]));
  }
  return keys;
}

// Normalizes a string, by removing all alphanumeric characters and using mixed case
// to separate words. The output will always start with a lower case letter.
// This function is designed to produce JavaScript object property names.
// Arguments:
//   - header: string to normalize
// Examples:
//   "First Name" -> "firstName"
//   "Market Cap (millions) -> "marketCapMillions
//   "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
function normalizeHeader(header) {
  var key = "";
  var upperCase = false;
  for (var i = 0; i < header.length; ++i) {
    var letter = header[i];
    if (letter == " " && key.length > 0) {
      upperCase = true;
      continue;
    }
    if (!isAlnum(letter)) {
      continue;
    }
    if (key.length == 0 && isDigit(letter)) {
      continue; // first character must be a letter
    }
    if (upperCase) {
      upperCase = false;
      key += letter.toUpperCase();
    } else {
      key += letter.toLowerCase();
    }
  }
  return key;
}

// Returns true if the cell where cellData was read from is empty.
// Arguments:
//   - cellData: string
function isCellEmpty(cellData) {
  return typeof(cellData) == "string" && cellData == "";
}

// Returns true if the character char is alphabetical, false otherwise.
function isAlnum(char) {
  return char >= 'A' && char <= 'Z' ||
    char >= 'a' && char <= 'z' ||
    isDigit(char);
}

// Returns true if the character char is a digit, false otherwise.
function isDigit(char) {
  return char >= '0' && char <= '9';
}