Instantly share code, notes, and snippets.

Embed
What would you like to do?
Event manager Apps Script webapp
var ss = SpreadsheetApp.getActiveSpreadsheet();
/**
* doGet - Return webapp
*
* @param {String} e query string to serve different pages
* @returns {String} rendered HTML to the browser
*/
function doGet(e) {
if (!e.parameter.page) {
// When no specific page requested, return "home page"
return HtmlService.createTemplateFromFile('webapp').evaluate().setTitle("Prom Manager").setSandboxMode(HtmlService.SandboxMode.IFRAME).setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
// else, use page parameter to pick an html file from the script
return HtmlService.createTemplateFromFile(e.parameter['page']).evaluate().setTitle(e.parameter['page']);
}
/**
* include - include separate HTML files to cut down on redundancy
*
* @param {String} filename Another file in the Apps Script project to include in the rendered template
* @returns {String} rendered HTML to the template
*/
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
/**
* lookup - Find the student name from the master spreadsheet by ID
*
* @param {Number} id ID number of the student signing up for the event
* @returns {String} String name of the student
*/
function lookup(id) {
var lookup = SpreadsheetApp.openById("LOOKUP_DATABASE_ID");
var data = lookup.getDataRange().getValues();
for(var i=0; i<data.length; i++) {
if(data[i][0] == id) {
return data[i][1];
}
}
}
/**
* getCurrentSales - Get all sales registered in the spreadsheet
*
* @returns {Object[]} All sales data sorted ascending by ID number
*/
function getCurrentSales() {
var sheet = ss.getSheetByName("sales");
var data = sheet.getRange(2,2,sheet.getLastRow()-1, 2).getValues();
return data.sort();
}
/**
* writeToSheet - Write registration information to the sales spreadsheet
*
* @param {Object} formObj Form data from the web app
* @returns {Object[]} Student ID and data displayed in the sales list on the dashboard
*/
function writeToSheet(formObj) {
var sheet = ss.getSheetByName("sales");
var data = sheet.getDataRange().getValues();
var isNewSale = true;
// Double check that the student hasn't already bought a ticket. If they have, set false
for(var i=0; i<data.length; i++) {
if(data[i][1].toString() == formObj.id) {
isNewSale = false;
}
}
// Throw an error if it is not a new sale
if(!isNewSale) {
throw new Error("They already bought a ticket")
} else {
// Write data to the spreadsheet.
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow()+1;
sheet.getRange(nextRow,1,1,6).setValues([ [ new Date(), formObj[headers[1]], formObj[headers[2]], formObj[headers[3]], formObj[headers[4]], formObj[headers[5]] ] ]);
return [ [formObj[headers[1]], formObj[headers[2]] ]];
}
}
/**
* getCard - Return student purcahse data to the checkin manager
*
* @param {Number} id ID of student checking in to the event
* @returns {Object} Object containing student data
*/
function getCard(id) {
var data = ss.getSheetByName("sales").getDataRange().getValues();
for(var i=0; i<data.length; i++) {
if(data[i][1].toString() == id) {
return JSON.stringify(data[i])
}
}
}
/**
* setTime - Add a timestamp to the check in or check out column from the checkin manager
*
* @param {String} el div id attribute displayed on the manager
* @param {String} id Student ID number to lookup
* @param {String} method log the student in or out of the event, maps to the correct column in the sheet
* @returns {Object} Date object and element target to update the manager dashboard
*/
function setTime(el, id, method) {
var sheet = ss.getSheetByName("sales");
var tickets = sheet.getDataRange().getValues();
var time = new Date();
var col;
if(method == "in") { col = 7 } else { col = 8 }
for(var i=0; i<tickets.length; i++) {
if(tickets[i][1] == id) {
sheet.getRange((i+1), col).setValue(time);
}
}
return JSON.stringify({ "time": time, "el": el });
}
<!DOCTYPE html>
<html>
<head>
<style>
html, body {
height:100%;
width:100%;
margin:0;
padding:0;;
}
#container {
display: grid;
grid-template-columns: 45% auto;
grid-gap: 15px;
grid-template-areas: "left right";
width: 100%;
height:100%;
margin: 0 auto;
}
#topnav {
display:block;
width:inherit;
height:auto;
padding:5px;
}
#topnav ul { list-style-type: none; margin-right: 10px; width: 80%; text-align:center; }
#topnav ul li { font-size:14px; display:inline; margin-right: 22px; }
#left { grid-area: left; background-color: #efefef; height:100%; }
#right { grid-area: right }
#form { width: 90%; margin: 0 auto }
#sale input[type=text], input[type=number] { display: block; margin: 10px 0; width:75%; font-size:18px; }
#sale h3 { display: inline-block; margin-right: 10px;}
#sale input[name=guest] { display: inline-block; }
#toast { margin-top: 15px; font-size:14px; font-weight: bold; color:rgb(280, 0,0); }
.card {
display:grid;
grid-template-columns: 20% auto;
grid-template-areas: "name id";
grid-gap:5px;
width:80%;
font-size: 18px;
margin: 5px auto;
padding: 5px;
}
.card .id { grid-area: id }
.card .name { grid-area:name }
</style>
<base target="_top">
<!-- apps script styles -->
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha256-3edrmyuQ0w65f8gfBsqowzjJe2iM6n0nKciPUp8y+7E=" crossorigin="anonymous"></script>
</head>
<body>
<div id="container">
<div id="left">
<div id="form">
<h2>New ticket</h2>
<form id="sale">
<input type="number" name="id" placeholder="ID number" value="" />
<input type="text" name="name" value="" required/>
<h3>Guest</h3>
<label><input type="radio" name="guest" value="true" id="radio_yes" />Yes</label>
<label><input type="radio" name="guest" value="false" id="radio_no" checked />No</label>
<input type="text" name="gname" value="" placeholder="Guest name" style="display:none"/>
<input type="text" name="sold" placeholder="Sold by" value="" />
<button type="submit" id="submit" class="create" onclick="event.preventDefault(); $('#toast').empty(); validate(this.parentNode)">Submit</button>
</form>
<div id="toast"></div>
</div>
<div id="topnav">
<ul>
<li><a href="YOUR_INDEX_FILE">sale</a></li>
<li><a href="YOUR_MANAGER_PAGE">manager</a></li>
</ul>
</div>
</div>
<div id="right">
<div id="results"></div>
</div>
</div>
<script type="text/javascript">
$(document).ready(function() {
// Pull all current sales data into the dashboard
google.script.run.withSuccessHandler(onSuccess).getCurrentSales();
// Show or hide the Guest input based on whether or not they're bringing someone
$("input[name=guest]").on('change', function() {
if($("#radio_no").is(":checked")) {
$("input[name=gname]").hide();
} else {
$("input[name=gname]").show();
}
});
// Submit the ID number to the lookup function when blurred
$("input[name=id]").on("blur", function() {
var id=$(this).val();
google.script.run.withSuccessHandler(fillName).lookup(id);
})
})
/**
* validate - Validate the form input before submitting
*
* @param {Object} formObject Check required form elements for successful sheet write
* @returns {String} Inform the user of errors or submit and get a confirmation message
*/
function validate(formObject) {
var inputs = $("#sale").find(":input");
var guest;
var id = inputs[0].value;
var name = inputs[1].value;
var seller = inputs[5].value;
if($("input[name=guest]").is(":checked")) {
guest = $("input[name=gname]").val();
}
if(!id || !name|| !seller) {
$("#toast").text("Please complete the entire form");
return;
}
google.script.run.withFailureHandler(onFailure).withSuccessHandler(onSuccess).writeToSheet(formObject);
}
/**
* fillName - fill in the registration form from the lookup database
*
* @param {String} name Student name returned from the lookup GAS function
*/
function fillName(name) {
if(name == undefined) { $("input[name=name]").val("ID not matched!") }
else { $("input[name=name]").val(name); }
}
/**
* onFailure - register a failed Apps Script function
*
* @param {Object} error Error to display to the user
*/
function onFailure(error) {
$("#toast").text(error);
}
/**
* onSuccess - Register a successful Apps Script function
*
* @param {Object[]} data Student data to add to the dashboard
*/
function onSuccess(data) {
for(var i=0; i<data.length; i++) {
var container = document.createElement("div");
container.innerHTML = Card.TEMPLATE;
$("#results").append(container);
var div = container.children[0];
div.querySelector(".name").textContent = data[i][0]
div.querySelector(".id").textContent = data[i][1]
}
}
Card.TEMPLATE =
'<div class="card">' +
'<div class="name"></div>' +
'<div class="id"></div>' +
'</div>';
</script>
</body>
</html>
<!DOCTYPE html>
<html>
<head>
<style>
html, body {
height:100%;
width:100%;
margin:0;
padding:0;
}
#container {
display: grid;
grid-template-columns: 35% auto;
grid-gap: 15px;
grid-template-areas: "left right";
width: 100%;
height:100%;
margin: 0 auto;
}
#topnav {
display:block;
width:inherit;
height:auto;
padding:5px;
}
#topnav ul { list-style-type: none; margin-right: 10px; width: 80%; text-align:center; }
#topnav ul li { font-size:14px; display:inline; margin-right: 22px; }
#left {
grid-area: left;
background-color: #efefef;
height:100%;
}
#right { grid-area: right }
#form { width: 90%; margin: 0 auto }
#search input[type=text] { display: block; margin: 10px auto; width:75%; font-size:18px; }
#checkin h3 { display: inline-block; margin-right: 10px;}
#toast { margin-top: 15px; font-size:14px; font-weight: bold; color:rgb(280, 0,0); }
.card {
display:grid;
grid-template-columns: 20% auto;
grid-template-areas: "id name";
grid-gap:5px;
width:80%;
font-size: 18px;
margin: 5px auto;
padding: 5px;
}
.card .id { grid-area: id }
.card .name { grid-area:name }
#left { grid-area: left }
#right { grid-area: right }
.bigCard {
display:grid;
grid-template-columns: auto 100px auto;
grid-template-rows:35% 35% 1fr 1fr;
grid-template-areas: "id name name"
"guest gname gname"
"sold soldBy soldBy"
"checkin checkout checkout";
grid-gap:10px;
height:auto;
width:80%;
border: 1px solid red;
padding: 25px;
font-size:18px;
margin: 20px auto;
}
.bigCard span {
background-color: #efefef;
display:block;
padding:5px;
margin-top: 5px;
}
.bigCard .name { grid-area: name; }
.bigCard .id { grid-area: id; }
.bigCard .guest { grid-area: guest; }
.bigCard .gname { grid-area: gname; }
.bigCard .sold { grid-area: sold; }
.bigCard .soldBy { grid-area: soldBy }
.bigCard .checkin { grid-area : checkin }
.bigCard .checkout { grid-area: checkout }
</style>
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha256-3edrmyuQ0w65f8gfBsqowzjJe2iM6n0nKciPUp8y+7E=" crossorigin="anonymous"></script>
</head>
<body>
<div id="container">
<div id="left">
<div id="topnav">
<ul>
<li><a href="YOUR APP URL">sale</a></li>
<li><a href="YOUR MANAGER URL">manager</a></li>
</ul>
</div>
<div id="results">
<div id="search">
<input type="text" placeholder="Search ID" />
</div>
</div>
</div>
<div id="right"></div>
</div>
<script>
$(document).ready(function() {
// Get the current sales and append them to the left sidebar
google.script.run.withSuccessHandler(onSuccess).getCurrentSales();
$("input[name=guest]").on('change', function() {
if($("#radio_no").is(":checked")) {
$("input[name=gname]").hide();
} else {
$("input[name=gname]").show();
}
});
});
/**
* checkX - Worker function to check students in or out
*
* @param {object} obj Data returned from the backend to append to the student card
* @param obj.time Timestamp from the Spreadsheet
* @param obj.el Targeted element to append timestamp
*/
function checkX(obj) {
obj = JSON.parse(obj);
var time = new Date(obj.time);
time = time.getHours() + ":" + time.getMinutes() + ":" + time.getSeconds();
document.getElementById(obj.el).textContent = time;
}
/**
* showCard - Build and display a student information card on the manager dashboard
*
* @param {Object[]} data Student ticket sale data pulled fromt he backend
*/
function showCard(data) {
$("#right").empty();
var data = JSON.parse(data);
var card = document.createElement('div');
card.innerHTML = TEMPLATE;
card.setAttribute("class", "bigCard");
// Check for data to avoid null error
if(data[6]) {
var checkin = new Date(data[6]);
checkin = checkin.getHours() + ":" + checkin.getMinutes() + ":" + checkin.getSeconds();
}
// Check for data to avoid null error
if(data[7]) {
var checkout = new Date(data[7]);
checkout = checkout.getHours() + ":" + checkout.getMinutes() + ":" + checkout.getSeconds();
}
var sold = new Date(data[0]);
sold = sold.getDate() + "/" + sold.getMonth()+1 + "/" + sold.getFullYear() + " " + sold.getHours() + ":" + sold.getMinutes();
$("#right").append(card);
card.querySelector(".id").getElementsByTagName('span')[0].textContent = data[1];
card.querySelector(".name").getElementsByTagName('span')[0].textContent = data[2];
card.querySelector(".guest").getElementsByTagName('span')[0].textContent = data[3];
card.querySelector(".gname").getElementsByTagName('span')[0].textContent = data[4];
card.querySelector(".sold").getElementsByTagName('span')[0].textContent = sold;
card.querySelector(".soldBy").getElementsByTagName('span')[0].textContent = data[5];
card.querySelector("#in").textContent = checkin;
card.querySelector("#out").textContent = checkout;
}
/**
* onSuccess - Successful operation helper
*
* @param {Object[]} data Student data from the backend
*/
function onSuccess(data) {
for(var i=0; i<data.length; i++) {
var container = document.createElement("div");
container.innerHTML = Card.TEMPLATE;
$("#results").append(container);
var div = container.children[0];
div.querySelector(".id").textContent = data[i][0];
div.querySelector(".name").textContent = data[i][1];
div.querySelector(".id").setAttribute("id", data[i][0]);
}
}
function Card() {
this.submitQuery = $("#submit");
}
Card.TEMPLATE =
'<div class="card">' +
'<div class="name"></div>' +
'<a href="#" id="" class="id" onclick="google.script.run.withSuccessHandler(showCard).getCard(this.id)"></a>' +
'</div>';
var TEMPLATE =
'<div class="name">Name: <span></span></div>'+
'<div class="id">ID: <span></span></div>'+
'<div class="guest">Guest: <span></span></div>'+
'<div class="gname">Guest name: <span></span></div>' +
'<div class="sold">Sold on: <span></span></div>' +
'<div class="soldBy">Sold By: <span></span></div>' +
'<div class="checkin"><button onclick="google.script.run.withSuccessHandler(checkX).setTime(this.nextSibling.id, $(this).parents().eq(1).children()[1].lastChild.textContent, \'in\');">Check In</button><span id="in"></span></div>' +
'<div class="checkout"><button onclick="google.script.run.withSuccessHandler(checkX).setTime(this.nextSibling.id, $(this).parents().eq(1).children()[1].lastChild.textContent, \'out\');">Check Out</button><span id="out"></span></div>';
// Search for student ID on checkin to prevent excessive scrolling
$("#search input").on('keyup',function() {
$(".card").hide();
var input = $(this).val();
$(".id").each(function() {
var id = $(this).attr("id");
if(id.indexOf(input) > -1) {
$(this).parents(".card").show();
}
});
});
</script>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment