Skip to content

Instantly share code, notes, and snippets.

@hinklefoxmail hinklefoxmail/Library.gs Secret
Created Sep 3, 2017

Embed
What would you like to do?
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
You can’t perform that action at this time.