Skip to content

Instantly share code, notes, and snippets.

@hinklefoxmail hinklefoxmail/ Secret
Created Sep 3, 2017

What would you like to do?
LMS System
function onOpen(){
SpreadsheetApp.getUi().createMenu('Register').addItem('Register', 'register').addToUi();
function register(){
var user = Session.getActiveUser();
function newEntry(e) {
//get variables
var range = e.range;
var user = e.user;
var user = Session.getActiveUser();
var value = range.getValue();
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){
//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 =;
//get person object data
var personObject = getInfoById(value, amount);
//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(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');
} else {
//otherwise set bg to green
thisSheet.getRange(row, 1, 1, 7).setBackground('#DAF7A6');
//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
var filesIt = DriveApp.getFilesByName(fileName);
if (filesIt.hasNext()){
Logger.log('File Found');
var file =;
var timeSS =;
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 =;
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(monthName+' found');
//find the row that corrisponds to the day
Logger.log(monthName+' not found');
var timeSheet = timeSS.insertSheet(monthName, 0);
timeSheet.appendRow(['Date','Time','Purchase','Price','Balance After Purchase','Transaction ID']);
//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);
var logSheet = logSS.insertSheet(logSheetName, 0);
logSheet.appendRow(['Time','Purchase','Amount', 'First Name', 'Last Name', 'Role','Cashier', 'Transaction ID']);
//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("Current Balance is")
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:''};
//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']);
function deleteSheets(){
var ss = SpreadsheetApp.getActive();
var sheets = ss.getSheets();
for (var i = 31; i < sheets.length; i++){
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.