Create a gist now

Instantly share code, notes, and snippets.

Custom course registration site with Google Apps Script
// ALL SERVER CODE RUNNING ON THE SPREADSHEET
// new property service GLOBAL
// see: https://developers.google.com/apps-script/reference/properties/
var SCRIPT_PROP = PropertiesService.getScriptProperties();
// Grab the correct spreadsheet
var sheet = SpreadsheetApp.openById("spreadsheetIdHere");
/**
* select the sheet
* store in a key value that can be called later
*/
function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("key", doc.getId());
}
// Serve the webapp
function doGet(e) {
return HtmlService.createTemplateFromFile("index").evaluate().setTitle("pageTitleHere");
}
// Post a custom form submission to a Google Sheet
// Based on a tutorial by Martin Hawksey
// https://github.com/mhawksey/html-form-send-email-via-google-script-without-server
function recordData(formObject) {
try {
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
// select the responses sheet
var sheet = doc.getSheetByName('signupSubmit');
// get next row
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow()+1;
// first element in the row should always be a timestamp
var row = [ new Date() ];
// loop through the header columns
for (var i = 1; i < headers.length; i++) { // start at 1 to avoid Timestamp column
if(headers[i].length > 0 && formObject[headers[i]] != undefined) {
row.push(formObject[headers[i]]); // add data to row
}
}
// more efficient to set values as [][] array than individually
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
sheet.getRange(nextRow,2,1,1).setValue(Session.getEffectiveUser.getEmail());
}
catch(error) {
return error;
}
finally {
return;
}
}
// Looks for the current user registrations and removes the selected date.
// @param Obeject formObject
function cancelRegistration(formObject) {
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName('signupSubmit');
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var data = sheet.getDataRange().getValues();
// Start below the header row and loop through all rows in the sheet
for(var i=1; i<data.length;i++) {
// If column 2 = the current user, stop on that row and loop through the columns.
if(data[i][1] == Session.getEffectiveUser().getEmail()) {
for(var j=5;j<data[i].length;j++) {
// If data in the column matches the date submitted by the form, delete the cell contents.
if(data[i][j] == formObject[headers[j]]) {
Logger.log("found a match, clearing row " + i + ", column " + (j+1) + " " + data[i][j]);
sheet.getRange((i+1), (j+1)).clear();
}
}
}
}
}
/* GET CURRENT USER REGISTRATIONS
* return Array usrRegs
*/
function getUsrRegs() {
// Collect the email address of the signed in user
var usr = Session.getActiveUser().getEmail();
// Get all registrations
var allRegs = sheet.getSheetByName("signupSubmit").getDataRange().getValues();
// Create an array to hold the current user's registrations
var usrRegs = [];
// Loop through all registrations
for(var i=0; i<allRegs.length;i++) {
// If there are no registrations scheduled, return a message
if(allRegs[i][1] == "") {
usrRegs.push("No workshops scheduled");
}
// Find the username and loop through their registrations
if(allRegs[i][1] == usr) {
for(var j=0;j<allRegs[i].length;j++) {
usrRegs.push(allRegs[i][j+5]);
}
}
}
// Send the sorted array to the all sessions list
usrRegs = usrRegs.filter(function(v) {
if(v!=="" || v!== null) { return v }
});
return usrRegs;
}
/* Registration counts
* @param String "max"
* return String "remain"
*/
function regCounts() {
// Get the workshop arrays
var max = [];
var remain = [];
var count, index;
var allClasses = sheet.getSheetByName("allSessions").getDataRange().getValues();
var regs = sheet.getSheetByName("signupSubmit").getDataRange().getValues();
// Build an array of dates and the associated count
for(var i=0;i<allClasses.length;i++) {
max.push([allClasses[i][0], allClasses[i][6]]);
}
// Update the count based on the number of instances of each date.
for(var i=0;i<max.length;i++) {
count = max[i][1];
for(var j=0;j<regs.length;j++) {
for(var k=0;k<regs[j].length;k++) {
if(regs[j][k] == max[i][0]) {
count--;
}
}
}
remain.push([max[i][0], count]);
}
return remain;
}
/* List all available workshops to the client
*
* @param Array usrRegs
* @param Array availableClasses
* return Object returnObj
*/
function availableWorkshops() {
// get the user registrations
var usrRegs = getUsrRegs();
var counts = regCounts();
// Get the entire workshop list from the spreadsheet and store in an array
var allClasses = sheet.getSheetByName("allSessions").getDataRange().getValues();
// Get all data for user submitted workshops
usrRegs = allClasses.filter(function(val) {
return usrRegs.indexOf(val[0]) !== -1;
});
// Logger.log(usrRegs);
// Create an array to hold the available classes
var availableClasses = [];
// Check usrRegs against allClasses
// Build the availableClasses array
allClasses = allClasses.filter(function(val) {
return usrRegs.indexOf(val) == -1;
});
// Logger.log(allClasses.sort())
for(var i=0; i<allClasses.length; i++) {
availableClasses.push([allClasses[i][0], allClasses[i][1], allClasses[i][3], allClasses[i][5], counts[i][1]]);
}
availableClasses.slice(0).forEach(function(v) {
if(v[4] <= 0) {
availableClasses.splice(availableClasses.indexOf(v), 1);
}
});
// Return an object of arrays of sorted classes to the client.
var returnObj = {'usrRegs':usrRegs.sort(), 'allClasses':availableClasses.sort() };
return JSON.stringify(returnObj);
}
function getUsrRegs() {
// Collect the email address of the signed in user
var usr = Session.getActiveUser().getEmail();
// Get all registrations
var allRegs = sheet.getSheetByName("signupSubmit").getDataRange().getValues();
// Create an array to hold the current user's registrations
var usrRegs = [];
// Loop through all registrations
for(var i=0; i<allRegs.length;i++) {
// If there are no registrations scheduled, return a message
if(allRegs[i][1] == "") {
usrRegs.push("No workshops scheduled");
}
// Find the username and loop through their registrations
if(allRegs[i][1] == usr) {
for(var j=0;j<allRegs[i].length;j++) {
usrRegs.push(allRegs[i][j+5]);
}
}
}
// Send the sorted array to the all sessions list
usrRegs = usrRegs.filter(function(v) {
if(v!=="" || v!== null) { return v }
});
return usrRegs;
}
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<meta name="description" content="Check registrations and register for PD with Elkhart Ed Tech." />
<style>
body, html { height:100%; width:100%; margin:0; padding:0; }
div { font-size:18px; font-family:Arial, sans-serif; margin:10px auto; }
#body-wrap { margin: 20px; }
#toast-wrap { display:block; position:fixed; background:rgba(0,0,0,0.5); height:100%; width:100%; margin:0; left:0; top:0 }
#toast { position:relative; display:block; width:50%; padding:2%; background:rgb(235,235,235); top:50%; margin: 0 auto; transform:translateY(-70%); }
.hide { display:none !important; }
.past { text-decoration:line-through; }
#header-img { position:absolute; top:0; right:0; margin:20px 25px 0 0; }
#header-img img { width:250px; }
input[type=checkbox] { padding:10px; margin-right:20px; }
#list, #courses { display:table; width:100%;border-collapse: separate; border-spacing:10px; table-layout:fixed; }
.row { display:table-row; margin-bottom:10px; }
.row span { display:table-cell; }
.row span.date { width:10%; }
.row span.time { width: 10%; }
.row span.title { width: 20%; }
.row span.desc { width: 45%; }
.row span.seats { width:15%; text-align:center; }
#titles { font-weight:bold; }
#titles.span { margin-bottom:10px; }
#form {width:90%; }
</style>
<!-- jquery include -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
<script>
// from https://developers.google.com/apps-script/guides/html/communication#forms
// Prevent forms from submitting so the page doesn't reload.
function preventFormSubmit() {
var forms = document.querySelectorAll('form');
for (var i = 0; i < forms.length; i++) {
forms[i].addEventListener('submit', function(event) {
event.preventDefault();
});
}
}
/* Class cancellation handler.
* param Object formObject
*/
function cancelClass(formObject) {
google.script.run.cancelRegistration(formObject);
// Reload the app to refresh the current registrations for the signed-in user.
window.location.href = "https://script.google.com/a/macros/elkhart.k12.in.us/s/AKfycby3BrJ2zIvH51edgSRNaVnv-NWdJW8aiUl_GTAoyDhlku54WRk/exec";
}
/* Registration form handler
* Pass the form object to the server, display toast
* @param Object formObject
*/
function handleFormSubmit(formObject) {
google.script.run.withSuccessHandler(displayResult).recordData(formObject);
$("input").attr("disabled","disabled");
$("#toast-wrap").toggleClass("hide");
}
// Callback function after server success, display confirmation message
function displayResult(e) {
$("input").attr("disabled",false);
$("#toast").empty().append("<p>Your registration has been received. Please refresh this page to see your updated registrations.</p><p>If you're done, you can close the window.</p>");
}
</script>
<script>
// Populate the school list select menu from an array
function buildSchoolList() {
var schools = [ "Beardsley", "Beck", "Bristol", "Cleveland", "Daly", "Eastwood", "Feeser", "Hawthorne", "Monger", "Osolo", "Pinewood", "Riverview", "Roosevelt", "Woodland", "North Side", "Pierre Moran", "West Side", "Elkhart Academy", "Central", "Memorial", "EACC", "Community Education", "PACE", "ESC", "Services", "Transportation" ];
var sorted = schools.sort();
var opt = '';
for(var i=0; i<sorted.length;i++) {
opt += '<option value="' + sorted[i] + '">' + sorted[i] + '</option>'
}
$("#bldg").append(opt);
}
</script>
</head>
<body>
<div id="body-wrap">
<div id="toast-wrap" class="hide">
<div id="toast"><p>Submitting...</p></div>
</div>
<div id="header-img"><img src="LOGO" /></div>
<div id="username">
<h1>Current User:</h1>
<!-- Because this is templated HTML, GAS allows you to use scriptlets in the front end.
More information on GAS web apps scriptlets: https://developers.google.com/apps-script/guides/html/templates
-->
<p><?= Session.getActiveUser().getEmail(); ?></p>
<!-- Display the current registrations -->
<div id="reg">
<h2>Your Registrations</h2>
<div id="courses">
<div class="row" id="titles"><span class="date">Date</span><span class="time">Start time</span><span class="title">Workshop</span></div>
</div>
</div>
</div>
<!-- Display all other workshops to the current user -->
<div id="workshops">
<h2>Upcoming Workshops</h2>
<p>To register for a workshop, fill in your name information and then click on the button next to each session title. Click <b>Register</b> when you're finished.</p>
<p><i>All workshops are held in the Professional Learning Center at the ECS Administration building unless otherwise noted.</i></p>
<div id="form">
<!-- Registration form for workshops.
A hidden field collects the user email address to guard against typos.
-->
<form id="foo" onsubmit="handleFormSubmit(this)">
<input id="email" name="email" type="hidden" value="<?= Session.getActiveUser().getEmail(); ?>" />
<label for="first">First Name</label>
<input id="first" name="first" type="text" onfocus="this.value=''" value="Required" required />
<label for="last">Last Name</label>
<input id="last" name="last" type="text" onfocus="this.value=''" value="Required" required />
<label for="bldg">Building</label>
<select id="bldg" name="building"></select>
<input type="submit" value="Register" />
<!-- Empty div to hold workshop options -->
<div id="list">
<div class="row" id="titles"><span class="date">Date</span><span class="time">Start time</span><span class="title">Workshop</span><span class="desc">Description</span><span class="seats">Seats available</span></div>
</div>
</form>
</div>
</div>
</div>
<script>
/* Populate the page based on the signed in user */
$(document).ready(function() {
// Immediately build the list of schools for the dropdown.
// Does this need to be a function...? Probably not.
buildSchoolList();
// Poll the server for current user registrations
google.script.run.withSuccessHandler(showClasses).availableWorkshops();
// Display the result
function showClasses(returnObj) {
var data = JSON.parse(returnObj);
if(data.usrRegs.length == 0) {
$("#courses").append("<div class='row'><p>No registrations</p></div>");
} else {
for(var i=0; i<data.usrRegs.length;i++) {
$("#courses").append("<div class='row'><span class='date'>" + data.usrRegs[i][0] + "</span><span class='time'>" + data.usrRegs[i][1] + "</span><span class='title'>" + data.usrRegs[i][3] + "</span><form id='cancel' onsubmit='cancelClass(this)'><input type='hidden' name='class " + i + "' value='" + data.usrRegs[i][0] + "' /><input type='submit' value='Cancel'></input></form></div>");
}
}
console.log(data.allClasses);
var today = new Date();
for(var j=0; j<data.allClasses.length;j++) {
$("#list").append("<div class='row' id='row" + j + "'><span class='date'>" + data.allClasses[j][0] + "</span><span class='time'>" + data.allClasses[j][1] + "</span><span class='title'><input type='checkbox' name='class " + j + "' value='" + data.allClasses[j][0] + "'>" + data.allClasses[j][2] + "</input></span><span class='desc'>" + data.allClasses[j][3] + "</span><span class='seats'>" + data.allClasses[j][4] + "</span></div>");
if(today.setHours(0,0,0,0) > new Date(data.allClasses[j][0]) && new Date(data.allClasses[j][0]) != today) {
$(":checkbox").attr("disabled", true);
$("#row" + j).addClass(" past");
}
}
preventFormSubmit();
}
})
</script>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment