Custom course registration site with Google Apps Script

code.gs

// 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);
}

  

getUsrRegs.gs

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;
}

index.html

<!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>