Secure Sign in System
function newEntry(e) { | |
//get variables | |
var securityPhone = '+2348099828250' | |
Logger.log('Running') | |
var range = e.range; | |
var value = range.getValue(); | |
Logger.log(range); | |
Logger.log(value); | |
var row = range.getRow(); | |
//if change is not in first column or is in the first row, end. | |
if (row == 1 || range.getColumn() !== 1){ | |
return; | |
} | |
//add * to value to match Id database | |
Logger.log('Value is: '+value); | |
//get date objects and variables | |
var date = new Date(); | |
var year = date.getYear(); | |
var monthNumber = date.getMonth(); | |
var month = new Array(); | |
month[0] = "January"; | |
month[1] = "February"; | |
month[2] = "March"; | |
month[3] = "April"; | |
month[4] = "May"; | |
month[5] = "June"; | |
month[6] = "July"; | |
month[7] = "August"; | |
month[8] = "September"; | |
month[9] = "October"; | |
month[10] = "November"; | |
month[11] = "December"; | |
var dayArray = ['Sun', 'Mon', 'Tue', 'Wed', 'Th', 'Fri', 'Sat'] | |
var day = dayArray[date.getDay()]+' '+date.getDate() | |
var time = Utilities.formatDate(date, 'GMT+1:00', "HH:mm:ss"); | |
var thisSheet = SpreadsheetApp.getActiveSheet(); | |
var currentMS = Date.now(); | |
//check if is string | |
if (isNaN(value)){ | |
var personObject = getInfoByIndex(value); | |
}else{ | |
value = '*'+value+'*'; | |
//get person object data | |
var personObject = getInfoById(value); | |
} | |
//checks to see visitor is allowed on campus | |
if (personObject.type == 'visitor'){ | |
if (personObject.startValid > currentMS){ | |
personObject.role = 'void'; | |
personObject.last = 'Too early'; | |
var alert = 'ID is not set up for this day: Too Early!'; | |
} else if (personObject.endValid < currentMS) { | |
personObject.last = 'ID Voided'; | |
personObject.role = 'void'; | |
var alert = 'This ID is old and has been voided!'; | |
} | |
} | |
//place into sheet | |
thisSheet.getRange(row, 2).setValue(personObject.first); | |
thisSheet.getRange(row, 3).setValue(personObject.last); | |
thisSheet.getRange(row, 4).setValue(personObject.role); | |
thisSheet.getRange(row, 5).setValue(time); | |
thisSheet.getRange(row, 6).setValue(currentMS); | |
/* | |
//if photo was found, place it in the sheet | |
if (personObject.photo && personObject.photo !== 'unknown'){ | |
thisSheet.setRowHeight(row, 85); | |
var blob = DriveApp.getFileById(personObject.photo).getAs('image/jpeg'); | |
thisSheet.insertImage(blob, 7, row); | |
} | |
*/ | |
//if person was unknown, set bg to red and notify security | |
if (personObject.first == 'unknown'){ | |
Logger.log('Unknown Entry') | |
thisSheet.getRange(row, 1, 1, 7).setBackground('#ff9900'); | |
SpreadsheetApp.flush(); | |
var gateName = thisSheet.getParent().getName(); | |
var message = 'Unknown at '+gateName; | |
//sendSms(securityPhone, message); | |
//MailApp.sendEmail('douglas.black@aisabuja.com', message, message); | |
} else if (personObject.role == "void"){ | |
Logger.log('Unauthorized Entry') | |
thisSheet.getRange(row, 1, 1, 7).setBackground('#ff1a1a'); | |
SpreadsheetApp.flush(); | |
var gateName = thisSheet.getParent().getName(); | |
var message = 'Unauthorized Entry at '+gateName; | |
//sendSms(securityPhone, message); | |
//MailApp.sendEmail('douglas.black@aisabuja.com', message, personObject.first+' '+personObject.last+' has tried to enter campus at '+gateName+' with a voided ID card'); | |
} else { | |
//otherwise set bg to green | |
thisSheet.getRange(row, 1, 1, 7).setBackground('#66ccff'); | |
SpreadsheetApp.flush(); | |
//get the filename and get the file with that name if it exists, otherwise create it | |
var fileName = personObject.first+' '+personObject.last+' '+' Time Card '+year | |
Logger.log(fileName); | |
var filesIt = DriveApp.getFilesByName(fileName); | |
if (filesIt.hasNext()){ | |
Logger.log('File Found'); | |
var file = filesIt.next(); | |
var timeSS = SpreadsheetApp.open(file); | |
}else{ | |
Logger.log('File Not Found'); | |
var oldFile = DriveApp.getFileById('1-s7W5No-xTMkS3KJMVrQuoK0pFjxYSlu8GHRpEJaEEQ'); | |
var folder = DriveApp.getFolderById('0B3QaE26f2r25WDVVaV9KWi1DZ2M'); | |
var file = oldFile.makeCopy(fileName, folder); | |
var timeSS = SpreadsheetApp.open(file); | |
} | |
//get the sheet with the month name, otherwise create it | |
var monthName = month[monthNumber]; | |
try { | |
var timeSheet = timeSS.getSheetByName(monthName); | |
var timeValues = timeSheet.getDataRange().getValues(); | |
Logger.log(timeValues[0][1]); | |
Logger.log(monthName+' found'); | |
//find the row that corrisponds to the day | |
var lastEntryRow = findTime(timeValues, day); | |
} | |
catch(err){ | |
Logger.log(monthName+' not found'); | |
var templateSheet = timeSS.getSheetByName('Template Sheet'); | |
var timeSheet = templateSheet.copyTo(timeSS); | |
timeSheet.setName(monthName); | |
var lastEntryRow = -1 | |
} | |
//append logs to log sheets | |
var logSS = SpreadsheetApp.openById('1fPIdPqXqW5g0p3gQnRRQt7eTEttYu0SHcA_JigzYLMs'); | |
var rowContents = [time+' '+day,personObject.first,personObject.last,personObject.role,currentMS,SpreadsheetApp.getActive().getName()] | |
var logSheetName = 'Current' | |
try { | |
var logSheet = logSS.getSheetByName(logSheetName); | |
logSheet.appendRow(rowContents) | |
} | |
catch(e){ | |
var logSheet = logSS.insertSheet(logSheetName, 0); | |
logSheet.appendRow(['Time', 'First Name', 'Last Name', 'Role', 'Unix Timestamp','Gate']); | |
logSheet.setFrozenRows(1); | |
logSheet.appendRow(rowContents); | |
} | |
//if the person has not signed in before on that day, create a new record their spreadsheet | |
if (lastEntryRow === -1){ | |
timeSheet.appendRow([day,time,'','',Date.now(),'','In']); | |
if (day !== 6 && day !== 0){ | |
var hour = date.getHours(); | |
var minute = date.getMinutes(); | |
if ((hour >= 7 && minute > 30) || (hour > 7)){ | |
var daysLate = timeSheet.getRange(2, 8).getValue(); | |
daysLate++; | |
timeSheet.getRange(2, 8).setValue(daysLate); | |
} | |
} | |
}else{ | |
//otherwise update the sign out time on the spreadsheet | |
var eTimeOut = Date.now(); | |
var eTimeIn = timeSheet.getRange(lastEntryRow, 5).getValue(); | |
var minWorked = Math.floor( (eTimeOut - eTimeIn)/(1000*60)); | |
timeSheet.getRange(lastEntryRow, 4).setValue(minWorked); | |
timeSheet.getRange(lastEntryRow, 3).setValue(time); | |
timeSheet.getRange(lastEntryRow, 6).setValue(eTimeOut); | |
var status = timeSheet.getRange(lastEntryRow, 7).getValue(); | |
if (status === 'In'){ | |
timeSheet.getRange(lastEntryRow, 7).setValue('Out'); | |
timeSheet.getRange(lastEntryRow, 7).setBackground('blue'); | |
} else { | |
timeSheet.getRange(lastEntryRow, 7).setValue('In'); | |
timeSheet.getRange(lastEntryRow, 7).setBackground('red'); | |
} | |
} | |
} | |
} | |
//find info from the the id and return an object | |
function getInfoById(id){ | |
var idDatabase = SpreadsheetApp.openById('1N5YoTJCcIWDS5Sg7AVoGfVJAlobZcgCV4N5rNkQeg8A'); | |
var firstDigits = id.slice(1, 3); | |
if (firstDigits=='97'){ | |
var data = idDatabase.getSheetByName('visitor').getDataRange().getValues(); | |
var type = 'visitor'; | |
} else if (firstDigits=='99') { | |
var data = idDatabase.getSheetByName('parents').getDataRange().getValues(); | |
var type = 'parent'; | |
} else if (firstDigits=='10') { | |
var data = idDatabase.getSheetByName('students').getDataRange().getValues(); | |
var type = 'student'; | |
} else{ | |
var data = idDatabase.getSheetByName('staff').getDataRange().getValues(); | |
var type = 'staff'; | |
} | |
for (var i = 0; i < data.length; i++){ | |
var tableId = data[i][3]; | |
if (id == tableId){ | |
if (type == 'visitor'){ | |
var personObject = {first:data[i][1], last:data[i][2],id:tableId, role:data[i][4], photo:data[i][5], startValid:data[i][6], endValid:data[i][7], type:type}; | |
}else{ | |
var personObject = {first:data[i][1], last:data[i][2],id:tableId, role:data[i][4], photo:data[i][5], type:type}; | |
} | |
Logger.log('person found') | |
return personObject; | |
} | |
} | |
return {first:'unknown', last:'unknown', id:id, role:'unknown',photo:'unknown'}; | |
} | |
//finds the time time the person signed in, or submits -1 | |
function findTime(table, day){ | |
for (var i = 1; i < table.length; i++){ | |
var tableDay = table[i][0]; | |
if (tableDay == day){ | |
return i+1; | |
} | |
} | |
return -1; | |
} | |
function testSubmit(){ | |
var e = {value: 'test', range: SpreadsheetApp.getActiveSheet().getRange(2, 1)}; | |
newEntry(e); | |
} | |
//Creates a new sheet for the day and places it first, triggered daily | |
function newSheet(){ | |
var date = new Date(); | |
var sheetName = Utilities.formatDate(date, 'UTC+1', 'yyyy-MM-dd'); | |
var newSheet = SpreadsheetApp.getActive().insertSheet(sheetName, 0); | |
newSheet.appendRow(['ID','First','Last','Role','Date','MS Since Epoch']); | |
newSheet.setFrozenRows(1); | |
} | |
function testSms(){ | |
sendSms('+2349087177882','Test Message. Send John an email if you get this') | |
} | |
function sendSms(number, message) { | |
// Get account SID and auth token here: | |
// https://www.twilio.com/user/account | |
////////////////////////////////////////// | |
return; | |
////////////////////////////////////////// | |
var accountSid = "ACe774d203ea3cd77da45cd4f1b7afacec"; | |
var authToken = "91f32de51945e39247fa7d2a6b9e3ba8"; | |
var url = "https://api.twilio.com/2010-04-01/Accounts/" + accountSid + "/SMS/Messages.json"; | |
var options = { | |
method: "post", | |
headers: { | |
Authorization: "Basic " + Utilities.base64Encode(accountSid + ":" + authToken) | |
}, | |
payload: { | |
// From is one of your Twilio phone numbers | |
From: "+18647194988", | |
To: number, | |
Body: message | |
} | |
}; | |
var response = UrlFetchApp.fetch(url, options); | |
Logger.log(response); | |
} | |
function nuke(){ | |
var ss = SpreadsheetApp.getActive(); | |
var sheets = ss.getSheets(); | |
sheets[0].clear(); | |
for (var i = 1; i < sheets.length; i++){ | |
ss.deleteSheet(sheets[i]); | |
} | |
} | |
function deleteSheets(){ | |
var ss = SpreadsheetApp.getActive(); | |
var sheets = ss.getSheets(); | |
for (var i = 31; i < sheets.length; i++){ | |
ss.deleteSheet(sheets[i]) | |
} | |
} | |
function getInfoByIndex(value){ | |
//if so check first letter | |
var i = 0; | |
var firstLetter = value.charAt(0); | |
try { | |
var indexVar = parseInt(value.slice(1)); | |
Logger.log(indexVar); | |
} | |
//try to convert remaining into a number | |
catch (e){ | |
Logger.log(e); | |
return {first:'unknown', last:'unknown', id:value, role:'unknown',photo:'unknown'}; | |
} | |
if (firstLetter == 'f'){ | |
var sheetName = 'staff' | |
} else if (firstLetter == 'T'){ | |
var sheetName = 'tmpParent'; | |
} else if (firstLetter == 'p' || firstLetter == 'P'){ | |
var sheetName = 'parentsP'; | |
} | |
if (indexVar == 1){ | |
indexVar = 600; | |
} | |
try{ | |
var data = SpreadsheetApp.openById('1N5YoTJCcIWDS5Sg7AVoGfVJAlobZcgCV4N5rNkQeg8A').getSheetByName(sheetName).getRange(indexVar, 1, 1, 10).getValues(); | |
Logger.log(data); | |
var personObject = {first:data[i][1], last:data[i][2],id:data[i][3], role:data[i][4], photo:'unknown', type:sheetName}; | |
Logger.log(personObject); | |
return personObject; | |
} | |
catch(e){ | |
return {first:'unknown', last:'unknown', id:value, role:'unknown',photo:'unknown',type:'unknown'}; | |
} | |
} | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment