Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google Apps Script SuperEventForm
// 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';
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment