Skip to content

Instantly share code, notes, and snippets.

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

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