LMS System
function onOpen(){ | |
SpreadsheetApp.getUi().createMenu('Register').addItem('Register', 'register').addToUi(); | |
} | |
function register(){ | |
var user = Session.getActiveUser(); | |
} | |
function newEntry(e) { | |
//get variables | |
Logger.log('Running') | |
var range = e.range; | |
var user = e.user; | |
var user = Session.getActiveUser(); | |
Logger.log(user); | |
var value = range.getValue(); | |
Logger.log(range); | |
var row = range.getRow(); | |
var amount = 1000; | |
var purchase = 'Lunch'; | |
//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); | |
value = '*'+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(); | |
//get person object data | |
var personObject = getInfoById(value, amount); | |
//place into sheet | |
Logger.log(personObject.newBalance); | |
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(personObject.newBalance); | |
thisSheet.getRange(row, 6).setValue(time); | |
thisSheet.getRange(row, 7).setValue(currentMS); | |
//if person was unknown, set bg to red and notify security | |
if (isNaN(personObject.newBalance)){ | |
Logger.log('Unauthorized Entry') | |
thisSheet.getRange(row, 1, 1, 7).setBackground('#FF6969'); | |
SpreadsheetApp.flush(); | |
} else { | |
//otherwise set bg to green | |
thisSheet.getRange(row, 1, 1, 7).setBackground('#DAF7A6'); | |
SpreadsheetApp.flush(); | |
//get the filename and get the file with that name if it exists, otherwise create it | |
var fileName = personObject.first+' '+personObject.last+' '+' Transaction History '+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('1kkt-WobX4hkJeZnOQ7cYGu-8LFDxrQoycbc6rNmDGYA'); | |
var folder = DriveApp.getFolderById('0B3QaE26f2r25dTZXeXlkRkVLTHM'); | |
var file = oldFile.makeCopy(fileName, folder); | |
var timeSS = SpreadsheetApp.open(file); | |
var databaseSheet = SpreadsheetApp.openById('1N5YoTJCcIWDS5Sg7AVoGfVJAlobZcgCV4N5rNkQeg8A').getSheetByName("Staff"); | |
databaseSheet.getRange(personObject.index, 9).setValue(file.getUrl()); | |
databaseSheet.getRange(personObject.index, 10).setValue(file.getId()); | |
} | |
//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 | |
} | |
catch(err){ | |
Logger.log(monthName+' not found'); | |
var timeSheet = timeSS.insertSheet(monthName, 0); | |
timeSheet.appendRow(['Date','Time','Purchase','Price','Balance After Purchase','Transaction ID']); | |
timeSheet.setFrozenRows(1); | |
timeSheet.activate(); | |
} | |
//append log to transaction history | |
timeSheet.appendRow([day,time, purchase, amount,personObject.newBalance, currentMS]); | |
//append logs to log sheets | |
var logSS = SpreadsheetApp.openById('1gXcnNi-IJAgS4bThVQ71mhbZNyo1QHy-2Gs-v77oQfs'); | |
var rowContents = [time,purchase, amount, personObject.first,personObject.last,personObject.role,user,currentMS] | |
var logSheetName = monthName+' '+day | |
try { | |
var logSheet = logSS.getSheetByName(logSheetName); | |
logSheet.appendRow(rowContents) | |
} | |
catch(e){ | |
var logSheet = logSS.insertSheet(logSheetName, 0); | |
logSheet.appendRow(['Time','Purchase','Amount', 'First Name', 'Last Name', 'Role','Cashier', 'Transaction ID']); | |
logSheet.setFrozenRows(1); | |
logSheet.appendRow(rowContents); | |
} | |
} | |
} | |
//find info from the the id and return an object, also updates the balance amount | |
function getInfoById(id, amount){ | |
var sheet = SpreadsheetApp.openById('1N5YoTJCcIWDS5Sg7AVoGfVJAlobZcgCV4N5rNkQeg8A').getSheetByName("Staff"); | |
var data = sheet.getDataRange().getValues(); | |
for (var i = 0; i < data.length; i++){ | |
var tableId = data[i][3]; | |
if (id == tableId){ | |
var personObject = {first:data[i][1], last:data[i][2],id:tableId, role:data[i][4], photo:data[i][5], balance:data[i][7], index:i+1}; | |
//if not authorized by a number on ss return unauthorized | |
if (isNaN(personObject.balance)){ | |
personObject.newBalance = 'Not Authorized'; | |
return personObject; | |
} | |
Logger.log("amount is") | |
Logger.log(amount) | |
Logger.log("Current Balance is") | |
Logger.log(personObject.balance); | |
personObject.newBalance = personObject.balance-amount; | |
sheet.getRange(i+1, 8).setValue(personObject.newBalance); | |
Logger.log('person found') | |
return personObject; | |
} | |
} | |
return {first:'unknown', last:'unknown', id:id, role:'unknown',photo:'unknown', balance:'Not Authorized',newBalance:'Not Authorized'}; | |
} | |
function testSubmit(){ | |
var e = {value: 'test', range: SpreadsheetApp.getActiveSheet().getRange(2, 1), user:'john.hinkle@aisabuja.com'}; | |
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','Balance','Time','Transaction Id']); | |
newSheet.setFrozenRows(1); | |
} | |
function deleteSheets(){ | |
var ss = SpreadsheetApp.getActive(); | |
var sheets = ss.getSheets(); | |
for (var i = 31; i < sheets.length; i++){ | |
ss.deleteSheet(sheets[i]) | |
} | |
} | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment