Simplify our Google Spreadsheet

[ACM Media Center]

Hi,

https://docs.google.com/spreadsheets/d/1rH490yUWLyFCgtXtpXAOa4hfFotkkL7ofpd9XZS1IrU/edit#gid=1038394822 

Attached is the Spreadsheet that I use for inventory and rental management at my job that I've been trying to develop since last year. The biggest problem that we've had is we have multiple classes with multiple students that are renting out multiple pieces of equipment, and we're using Google sheets to ensure we're not double booking. 

The problem is that I want to make this Spreadsheet as simple as possible, and I'm not very knowledgable on AppScript, I've basically been winging the whole Spreadsheet.

The way this works is that each tab is a class number, and you'd write down the student's name at the top and the check out date, then write in the quantities of the items that each student wants. Then, it returns back to the Total Stock (Our whole inventory) and subtracts it. There's a lot of connections between each of the class tabs that I really want simplified so it's impossible for other people to break the formulas.

There's also a tab to add new equipment based on its category that I just added. 

Any suggestions to simplify this Spreadsheet? Or if there's a way to create a check-in/check-out system tailored to each class which connects back to our Total Stock?

[ACM Media Center]

I also forgot to mention that I used Macros for some of the functions I made in Apps Script but below is the Function I used for adding a new item.

function AddEquipment() {

const ss = SpreadsheetApp.getActiveSpreadsheet()

const formWS = ss.getSheetByName("Add")

const settingsWS = ss.getSheetByName("Settings")

const tsWS = ss.getSheetByName("Total Stock")

const fieldRange = ["C3", "C5","F4"]

const fieldValues = fieldRange.map(f => formWS.getRange(f).getValue())

//console.log(fieldValues)

tsWS.appendRow(fieldValues)

ss.getRange("Total Stock!A3:E").activate()

.sort({column: 1, ascending: true});

var spreadsheet = SpreadsheetApp.getActive();

spreadsheet.getRange("Total Stock!D4:o4").activate();

var currentCell = spreadsheet.getCurrentCell();

spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();

currentCell.activateAsCurrentCell();

currentCell = spreadsheet.getCurrentCell();

spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();

currentCell.activateAsCurrentCell();

spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: false});

spreadsheet.getRange("Total Stock!D3:o3").activate();

spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

var spreadsheet = SpreadsheetApp.getActive();

spreadsheet.getRange("Total Stock!D4:o4").activate();

var currentCell = spreadsheet.getCurrentCell();

spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();

currentCell.activateAsCurrentCell();

currentCell = spreadsheet.getCurrentCell();

spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();

currentCell.activateAsCurrentCell();

spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: false});

spreadsheet.getRange("Total Stock!D3:o3").activate();

spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

}

And here are the functions of all my Macros. The Update button uses the SortInventory () function

function SortInventory() {

var spreadsheet = SpreadsheetApp.getActive();

spreadsheet.getRange("Total Stock!A3:e").activate()

.sort({column: 1, ascending: true});

var spreadsheet = SpreadsheetApp.getActive();

spreadsheet.getRange("Total Stock!D4:4").activate();

var currentCell = spreadsheet.getCurrentCell();

spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();

currentCell.activateAsCurrentCell();

currentCell = spreadsheet.getCurrentCell();

spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();

currentCell.activateAsCurrentCell();

spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: false});

spreadsheet.getRange("Total Stock!D3:3").activate();

spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

var spreadsheet = SpreadsheetApp.getActive();

spreadsheet.getRange("Total Stock!D4:4").activate();

var currentCell = spreadsheet.getCurrentCell();

spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();

currentCell.activateAsCurrentCell();

currentCell = spreadsheet.getCurrentCell();

spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();

currentCell.activateAsCurrentCell();

spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: false});

spreadsheet.getRange("Total Stock!D3:3").activate();

spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

};

function Clear() {

var spreadsheet = SpreadsheetApp.getActive();

spreadsheet.getRangeList(['C3', 'C5', 'F4']).activate()

.clear({contentsOnly: true, skipFilteredRows: true});

};

function Autofill() {

var spreadsheet = SpreadsheetApp.getActive();

spreadsheet.getRange('D3:e3').activate();

spreadsheet.getActiveRange().autoFillToNeighbor();

};

function ClearandAutofill() {

var spreadsheet = SpreadsheetApp.getActive();

spreadsheet.getRange("Total Stock!D4:o4").activate();

var currentCell = spreadsheet.getCurrentCell();

spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();

currentCell.activateAsCurrentCell();

currentCell = spreadsheet.getCurrentCell();

spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();

currentCell.activateAsCurrentCell();

spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: false});

spreadsheet.getRange("Total Stock!D3:o3").activate();

spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

}

[Laurie J. Nason]

Hi,

I’m not sure if anyone else has chimed in with this, but here’s probably the way I would work it:

1. Have a google form that the person requesting fills out which includes the item, the user, the number requested, (the date requested if different to the actual date they are filling it out), and the room number - if you are a gworkspace institution, then I would force recording of the users email address as well
2. This will then feed into a new tab which would then allow the calculations as to who and where the items are allocated - you can then have another manually filled column for the person checking things back in once they are returned.
3. Your total stock page would then be used to keep track using the tab above’s information
4. Each of the rooms tabs (if you wanted to continue to use them) could use a query and filter out only non-returned items in a list for easy display (use a template so that when you add rooms it’s easy to generate another tab)
5. If you wanted to fully automate the workflow - each time you add or remove an item of equipment from the inventory either checking in or out, a total stock number could be automatically updated against the item in the form - or if you wanted to be very fancy - remove the item so that it cannot be selected if none remain.
6. Each time you Add a new item or remove an item from the stock - you would run the update for the form.

If you pay for your Google Workspace - then you also could have the option to create an appSheet app for sign in / sign out and add/remove items (along with photos!) using barcodes too if you have them already.

Hope this makes sense - let me know if you need any clarification.

Laurie

Check me on Fiverr

------ Original Message ------

From "ACM Media Center" <ac...@hawaii.edu>

To "Google Apps Script Community" <google-apps-sc...@googlegroups.com>

Date 07/07/2022 03:25:03

Subject [Apps-Script] Re: Simplify our Google Spreadsheet

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/2ac0c92c-c851-4143-aa78-ebf7e051d3afn%40googlegroups.com.