Library Sign in System
function onFormSubmit(){ | |
var form = FormApp.getActiveForm(); | |
var currentResponse = form.getResponses()[form.getResponses().length-1]; | |
var barCode = currentResponse.getItemResponses()[0].getResponse(); | |
try{ | |
var reason = currentResponse.getItemResponses()[1].getResponse(); | |
} | |
catch(err){ | |
var reason = 'None Given'; | |
Logger.log('No Reason Given'); | |
} | |
var result = '' | |
var info = findInfoFromBarCode(barCode); | |
if (info[1] == 'Unknown'){ | |
try{ | |
info[1] = currentResponse.getItemResponses()[2].getResponse(); | |
} | |
catch(err){ | |
Logger.log('Unknown Student'); | |
} | |
} | |
var name = info[0]; | |
var postion = info[1]; | |
var date = new Date(); | |
var month = date.getMonth()+1; | |
var day = date.getDate(); | |
var year = date.getFullYear(); | |
var hour = date.getHours(); | |
var myDay = month+'/'+day+'/'+year; | |
var minute = date.getMinutes()+1; | |
if (minute < 10){ | |
minute = '0'+minute; | |
} | |
var timestamp = hour+':'+minute; | |
var sheetName = month+'-'+year; | |
var spreadsheet = SpreadsheetApp.openById('1SNmUkStQF9Wihu-HT7FJPpOqQO-2pE2qgBS4bqJnvRc'); | |
try{ | |
var sheet = spreadsheet.getSheetByName(sheetName); | |
var lastRow = sheet.getLastRow(); | |
Logger.log('Hit'); | |
} | |
catch(err){ | |
var sheet = spreadsheet.insertSheet(sheetName); | |
sheet.appendRow(['Name','Time-In','Time-Out','Grade / Role','Date','Reason','Result','Minutes in Station','In Now()','Out Now()']) | |
Logger.log('New Sheet Inserted'); | |
} | |
var lastRow = sheet.getLastRow(); | |
Logger.log(lastRow); | |
var range = sheet.getRange(1, 1, lastRow, 4).getValues(); | |
for (var i = range.length-1; i >-1; i = i-1){ | |
Logger.log(range[i][0]); | |
if (name == range[i][0]){ | |
Logger.log('Found Name'); | |
var checkEntry = range[i][2].toString(); | |
if (checkEntry.length > 2){ | |
if (reason.length > 2 && result.length > 2){ | |
sheet.appendRow([name,timestamp,timestamp,postion,myDay,reason,result, 0 ,Date.now(), Date.now()]); | |
return; | |
} | |
Logger.log('Duplicate'); | |
sheet.appendRow([name,timestamp,'',postion,myDay,reason,'','',Date.now()]); | |
return | |
} | |
else{ | |
sheet.getRange(i+1, 3).setValue(timestamp); | |
var msTime = sheet.getRange(i+1, 9).getValue(); | |
var newNow = Date.now(); | |
var msInLibrary = newNow - msTime | |
var minInLibrary = Math.floor(msInLibrary / 60000); | |
Logger.log(minInLibrary); | |
sheet.getRange(i+1, 7).setValue(result); | |
sheet.getRange(i+1, 8).setValue(minInLibrary); | |
sheet.getRange(i+1,10).setValue(newNow); | |
return | |
} | |
} | |
} | |
if (reason.length > 2 && result.length > 2){ | |
sheet.appendRow([name,timestamp,timestamp,postion,myDay,reason,result, 0 ,Date.now(), Date.now()]); | |
return; | |
} | |
sheet.appendRow([name,timestamp,'',postion,myDay,reason,'','',Date.now()]); | |
} | |
function findInfoFromBarCode(barCode){ | |
var modCode = "*"+barCode+"*"; | |
var database = SpreadsheetApp.openById('1N5YoTJCcIWDS5Sg7AVoGfVJAlobZcgCV4N5rNkQeg8A'); | |
var subString = modCode.substring(1, 3); | |
Logger.log('Substring is: '+subString); | |
if (subString == '99'){ | |
var sheet = database.getSheetByName('parents'); | |
} | |
else if (subString == '10'){ | |
var sheet = database.getSheetByName('students'); | |
} | |
else{ | |
//Staff | |
var sheet = database.getSheetByName('staff'); | |
} | |
var lastRow = sheet.getLastRow(); | |
var dataRange = sheet.getRange(1, 1, lastRow, 6).getValues(); | |
for (var i = 1; i < lastRow; i++){ | |
if (dataRange[i][3] == modCode){ | |
var firstName = dataRange[i][1]; | |
var lastName = dataRange[i][2]; | |
var position = dataRange[i][4]; | |
Logger.log(firstName+' '+lastName+': '+position); | |
return [firstName+' '+lastName, position]; | |
} | |
} | |
return [barCode, 'Unknown']; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment