Inventory System for Google Sheets
function createSS() { | |
//get Variables | |
var user = Session.getActiveUser(); | |
var thisSheet = SpreadsheetApp.getActiveSheet(); | |
var data = thisSheet.getDataRange().getValues(); | |
var template = DriveApp.getFileById('1QuKvHEt85MivyCLHqMWz02kCTterqM-ed81PyiKD404'); | |
var myDate = getDate(); | |
var dateAndTime = myDate.dateAndTime; | |
var folder = DriveApp.getFolderById('0B3QaE26f2r25M0lUUjQ3V29HdG8'); | |
var addSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Added Log"); | |
//search through document for no id | |
Logger.log('Begin search for Id'); | |
for (var i = 0 ; i < data.length; i++){ | |
var documentId = data[i][8]; | |
var row = i+1; | |
if (documentId){ | |
continue; | |
} | |
var item = getInfoByIndex(i, data); | |
Logger.log(item); | |
if (!item.documentID){ | |
Logger.log("Need to Create Sheet"); | |
} | |
//creates a new sheet in the folder and updates the old sheet with the info | |
var newFile = template.makeCopy(item.brand+' '+item.type, folder) | |
thisSheet.getRange(row, 9).setValue(newFile.getId()); | |
thisSheet.getRange(row, 10).setValue(newFile.getUrl()); | |
thisSheet.getRange(row, 12).setValue(item.startingQuantity); | |
thisSheet.getRange(row, 6).setValue(item.startingQuantity); | |
var newSS = SpreadsheetApp.open(newFile); | |
var newSheet = newSS.getSheets()[0]; | |
newSheet.appendRow([dateAndTime, user, user, item.startingQuantity*-1, item.startingQuantity]); | |
//Log to added Sheet | |
var rowData = [myDate.dateAndTime,user,item.brand+' '+item.type,item.startingQuantity,item.uSD,item.startingQuantity*item.uSD] | |
addSheet.appendRow(rowData); | |
} | |
} | |
function getInfoById(id,type){ | |
Logger.log(id); | |
Logger.log(type); | |
//change vars depending on searched thing type | |
if (type == 'item'){ | |
var dataId = '10ZExgJ1SkUu1_eOPf1js2so3KOGZXrl3HA0Q_KIkS30'; | |
var dataSheetName = 'overview'; | |
var barRow = 0; | |
} else if (type == 'person'){ | |
var dataId = '1N5YoTJCcIWDS5Sg7AVoGfVJAlobZcgCV4N5rNkQeg8A'; | |
var dataSheetName = 'Staff'; | |
var id = '*'+id+'*'; | |
var barRow = 3; | |
} | |
//get ss | |
var data = SpreadsheetApp.openById(dataId).getSheetByName(dataSheetName).getDataRange().getValues(); | |
for (var i = 0; i < data.length; i++){ | |
//insert barcode check field here | |
var tableId = data[i][barRow]; | |
//If match, do___ | |
if (id == tableId){ | |
var myObject = getInfoByIndex(i, data) | |
myObject.id = id; | |
myObject.kind = type; | |
return myObject; | |
} | |
} | |
//Return Object with all values false | |
var myObject = {index:-1, id:id, kind:type}; | |
for (var j = 0; j < data[0].length; j++){ | |
var propertyName = camelize(data[0][j]); | |
myObject[propertyName] = false; | |
} | |
return myObject; | |
} | |
function getInfoByIndex(index, data){ | |
Logger.log('Getting Info by Index'); | |
var myObject = {index:index} | |
for (var j = 0; j < data[0].length; j++){ | |
Logger.log(data[0][j]) | |
var propertyName = camelize(data[0][j]); | |
myObject[propertyName] = data[index][j]; | |
} | |
return myObject; | |
} | |
function camelize(str) { | |
return str.replace(/(?:^\w|[A-Z]|\b\w|\s+)/g, function(match, index) { | |
if (+match === 0) return ""; // or if (/\s+/.test(match)) for white spaces | |
return index == 0 ? match.toLowerCase() : match.toUpperCase(); | |
}); | |
} | |
function checkout(){ | |
//get vars | |
var user = Session.getActiveUser(); | |
var thisSheet = SpreadsheetApp.getActive().getSheetByName('check out'); | |
var rawValues = thisSheet.getDataRange().getValues(); | |
Logger.log(rawValues); | |
var taker = rawValues[1][1]; | |
Logger.log(taker); | |
var department = rawValues[1][2]; | |
var deptName = 'Other' | |
for (var i = 0; i < rawValues.length; i++){ | |
if (department == rawValues[i][5]){ | |
var deptName = rawValues[i][6]; | |
Logger.log(deptName); | |
break; | |
} | |
} | |
//get the person | |
if (!taker || !department){ | |
SpreadsheetApp.getUi().alert('Incomplete info, checkout not run'); | |
return; | |
} else if (isNaN(taker)){ | |
var person = {first: taker, last: '(No ID)',dept: deptName}; | |
} else { | |
var person = getInfoById(taker,'person'); | |
person.dept = deptName; | |
Logger.log(person); | |
if (!person.first){ | |
SpreadsheetApp.getUi().alert('Unknown ID, checkout not run. If no id is present, just type the name in for person'); | |
return; | |
} | |
} | |
var items = []; | |
for (var i = 2; i < rawValues.length; i++){ | |
var barcode = rawValues[i][1]; | |
//if there is a item | |
Logger.log(barcode); | |
if (barcode){ | |
var amountTaken = rawValues[i][2]; | |
// if amount is missing, it is now 1 | |
if (!amountTaken){ | |
amountTaken = 1; | |
} | |
//update the sheets | |
items.push(updateSheets(barcode, person, amountTaken)); | |
} | |
} | |
//give prompt that action has been successful | |
var prompt = person.first+' '+person.last+' has taken:' | |
for (var i = 0; i < items.length; i++){ | |
prompt = prompt +'\n'+items[i]; | |
} | |
var values = [['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['','']] | |
thisSheet.getRange(2, 2, 20, 2).setValues(values); | |
SpreadsheetApp.getUi().alert(prompt); | |
} | |
function addStock(){ | |
//get vars | |
var user = Session.getActiveUser(); | |
var thisSheet = SpreadsheetApp.getActive().getSheetByName('add stock'); | |
var rawValues = thisSheet.getDataRange().getValues(); | |
var department = 'Added Items'; | |
var person = {first: user, last:' ', dept: department} | |
//get item data from barcode | |
var items = []; | |
for (var i = 1; i < rawValues.length; i++){ | |
var barcode = rawValues[i][1]; | |
//if there is a item | |
Logger.log(barcode); | |
if (barcode){ | |
var amountTaken = 0-rawValues[i][2]; | |
// if amount is missing, it is now 1 | |
if (!amountTaken){ | |
amountTaken = -1; | |
} | |
//update the sheets && find info | |
items.push(updateSheets(barcode, person, amountTaken,i)); | |
} | |
} | |
//create prompt if action has been succesful | |
var prompt = 'The following items have been added to the inventory:' | |
for (var i = 0; i < items.length; i++){ | |
prompt = prompt +'\n'+items[i]; | |
} | |
var values = [['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['','']] | |
thisSheet.getRange(2, 2, 30, 2).setValues(values); | |
SpreadsheetApp.getUi().alert(prompt); | |
} | |
function onOpen(){ | |
SpreadsheetApp.getUi().createMenu('Add Items').addItem('Add New Items', 'createSS').addItem('Add Stock', 'addStock').addToUi(); | |
SpreadsheetApp.getUi().createMenu('Checkout').addItem('Checkout', 'checkout').addToUi(); | |
} | |
function updateSheets(barcode, person, amountTaken){ | |
//get vars | |
var myDate = getDate(); | |
var user = Session.getActiveUser(); | |
//find item | |
var item = getInfoById(barcode, 'item'); | |
if (!item.type){ | |
return (barcode+' is unknown. Please update the inventory'); | |
} | |
item.taken = amountTaken; | |
Logger.log(item); | |
try{ | |
var itemSheet = SpreadsheetApp.openById(item.documentID); | |
} | |
catch(e){ | |
return (barcode+' is unknown. Please update the inventory'); | |
} | |
var itemName = itemSheet.getName(); | |
item.remaining = item.currentQuantity - item.taken; | |
itemSheet.appendRow([myDate.dateAndTime,person.first+' '+person.last,user,item.taken,item.remaining]); | |
//update info on overview sheet | |
SpreadsheetApp.getActive().getSheetByName('overview').getRange(item.index+1, 6).setValue(item.remaining); | |
//if items were added... | |
if (amountTaken <0){ | |
SpreadsheetApp.getActive().getSheetByName('overview').getRange(item.index+1, 4).setValue(item.startingQuantity - item.taken); | |
if (!item.totalAdded){ | |
item.totalAdded = 0; | |
} | |
SpreadsheetApp.getActive().getSheetByName('overview').getRange(item.index+1, 12).setValue(item.totalAdded - item.taken); | |
var rowData = [myDate.dateAndTime,person.first+' '+person.last,item.brand+' '+item.type,(item.taken*-1),item.uSD,item.taken*item.uSD*-1]; | |
SpreadsheetApp.getActive().getSheetByName('Added Log').appendRow(rowData); | |
} else { | |
//if items were removed... | |
if (!item.totalRemoved){ | |
item.totalRemoved = 0; | |
} | |
SpreadsheetApp.getActive().getSheetByName('overview').getRange(item.index+1, 11).setValue(item.totalRemoved + item.taken); | |
//get school year | |
if (myDate.monthNumber < 7){ | |
var year = myDate.year-1+'-'+myDate.year; | |
} else { | |
var year = myDate.year+'-'+myDate.year+1; | |
} | |
//if dept log exists, get it | |
var fileName = person.dept+' Inventory Checkout for '+year; | |
if (DriveApp.getFilesByName(fileName).hasNext()){ | |
var deptSS = SpreadsheetApp.open(DriveApp.getFilesByName(fileName).next()); | |
} else { | |
//otherwise make it | |
var folder = DriveApp.getFolderById('0B3QaE26f2r25aE1QSXVSY1Z6VHM'); | |
var template = DriveApp.getFileById('1vvZi_AJq0kH7FN9xCIHskAUkeZrRCmQdWqnyjfEwRP0') | |
var newFile = template.makeCopy(fileName, folder); | |
deptSS = SpreadsheetApp.open(newFile); | |
} | |
var deptSheet = deptSS.getSheetByName('Total Used'); | |
var dataArray = [myDate.dateAndTime,person.first+' '+person.last,item.brand+' '+item.type,item.taken,item.uSD,item.taken*item.uSD] | |
//and update | |
deptSheet.appendRow(dataArray); | |
var monthSheet = deptSS.getSheetByName(myDate.month); | |
monthSheet.appendRow(dataArray); | |
SpreadsheetApp.getActive().getSheetByName('Master Log').appendRow(dataArray); | |
} | |
Logger.log(item.remaining); | |
SpreadsheetApp.flush(); | |
var abs = Math.abs(item.taken) | |
var prompt = abs+' '+itemName; | |
Logger.log(prompt); | |
return (prompt); | |
} | |
function getDate(){ | |
//get date objects and variables | |
var myDate = {}; | |
var date = new Date(); | |
myDate.year = date.getFullYear(); | |
myDate.monthNumber = date.getMonth(); | |
var monthArray = new Array(); | |
monthArray[0] = "Jan"; | |
monthArray[1] = "Feb"; | |
monthArray[2] = "Mar"; | |
monthArray[3] = "Apr"; | |
monthArray[4] = "May"; | |
monthArray[5] = "June"; | |
monthArray[6] = "July"; | |
monthArray[7] = "Aug"; | |
monthArray[8] = "Sep"; | |
monthArray[9] = "Oct"; | |
monthArray[10] = "Nov"; | |
monthArray[11] = "Dec"; | |
var dayArray = ['Sun', 'Mon', 'Tue', 'Wed', 'Th', 'Fri', 'Sat'] | |
myDate.weekday = dayArray[date.getDay()]; | |
myDate.month = monthArray[myDate.monthNumber]; | |
myDate.day = dayArray[date.getDay()]+' '+date.getDate() | |
myDate.time = Utilities.formatDate(date, 'GMT+1:00', "HH:mm:ss"); | |
myDate.now = Date.now(); | |
myDate.dateAndTime = Utilities.formatDate(date, 'GMT+1:00', "MM/dd/yyyy HH:mm"); | |
myDate.origin = date; | |
return myDate; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment