Wardrobe Organizer

Whether it's shopping for clothes or always being asked to borrow any item, there are times you wish you could peep into your closet from anywhere to see if you have something similar.
The Wardrobe Organizer does just that AND MORE!

This is a one stop shop and is expansive for many other purposes. My Wardrobe Organizer is a combination of Google Sheets as an SQL database, Google Scripts for handling the data, and Google WebApp for an online portal to this data. The end user can see all the items, filter for something specific, mark items as out on loan, manage their laundry, and stop mom from buying you the same shirt for Christmas every year*.

(*No Guarantee. Mothers will buy what they want whether you need it or not)

Taking a quick glance at the website design in the image above, one might recognize a familiar layout. The Wardrobe Organizer is set up like any ordinary apparel website. Broken up by departments up top and filters provided on the side, this interface brings familiarity with functionality to the casual user. AND it is simple to use.

Let 's start by creating your own copy of this project.

Google Drive

Click on the above Link to take you to my current version of this application.

You will see 3 items in this folder: A Google Form, a Google Sheet, and a Folder.

Right Click on the Google Sheet and click Make A Copy.

Set the Location of this copy to your own Drive.

After copying this document, the Google Form will be automatically generated in the same folder you moved the Google Sheet.

To create the Folder (this is necessary for collecting uploads of the item pictures), click the copied Google Form and a prompt will appear asking you to restore the folder location for uploads.

You now have a copy of this document to work on for yourself!

Now that you have your own version of this application, let's take a look around.

Your Google Form is set up to accept many different types of items. However shirts, pants, dresses, and shoes all have different sizing limitations. Therefore a different section of this form will be filled out based on the department you file your item under. In my (Male Article's) template I have created 5 different sizing categories. (For Women's Articles, click here, there are many more).

Under each sizing section, I established a unique title for each parameter I will be collecting. We do not want to have multiple columns in our database with the name "Size" or we wouldn't be able to determine which type of clothes that size applies to.

At the end of each section, the user is directed to the final portion of this form: Location. I personally chose to add Location to determine items at the Dry Cleaners, in the laundry, in their place or those items in which I have let a friend borrow. This allows me to be organized and never feel like I am missing a piece of clothing somewhere.

As I mentioned from the beginning, this project can be expanded in a million different ways. You can use it for inventory, a more precise organization tool, or for strictly borrowing clothes. The fields and sections you can add are endless so don't feel limited to what is in my form. (For the Women's Articles click here)

Before you go uploading a few of your own items let's move on to the next step to ensure proper submission.

Clicking into the Google Sheets document, you will see many columns of data (and some rows, left in for demonstration). During form submission some sections are skipped, this is evident from the missing data in some columns. But additional columns such as ID, Default Location, Who, and Updated have been added to better track the edits of these items.

An ID field has been created when you submitted the form to allow for an unique identifier when traversing this database. To create this field, we will take a look at the Script Editor by clicking on Tools>Script Editor.

With the Script Editor open, you will notice 8 documents in the sidebar of this new window. These documents help control back-end process, front-end displays, and front-end functionality. We will jump into each one (if you stick around) but right now click on Server Code.

In the Server Code.gs file there are many functions:

onSubmit(e) , onOpen() , doGet(), include(fileName), openApplication(), openLaundryApp() , changeValueOnSubmit(e) ,setIDOnSubmit(e)

onSubmit(e) - This function will be configured as the first function to run when a Google Form submits. You can place other functions inside this function to allow many different processes to happen.

onOpen(e) - This function is called when Google Sheets is opened. It populates a new menu option to allow quick access to the application links and views.

doGet()- This function is called on the Web App address call. When a user browses to the published Web App this code will tell that page what to display. In this case, it is the document Application.html.

include(fileName)- This function is used inside of HTML pages to read other document and insert their contents into a proper HTML format within another page. We use it for our CSS.html and JS.html files.

openApplication() and openLaundryApp()- These functions contain the code to run when a user clicks on the menu buttons added to the Google Sheet toolbar.

changeValueOnSubmit(e) and setIDOnSubmit(e)- These are the functions we will look into for now. They are responsible for updating certain fields with default values when the form is initially submitted.

These two functions, changeValueOnSubmit(e) and setIDOnSubmit(e), need to be connected to the user action of submitting a form. To do so we need to enable a Trigger.

We enable the trigger by clicking Edit > Current project's triggers. This opens the Google Developer Hub.

In the bottom right hand corner of the trigger dashboard is a button Add a trigger. Click here.

We will now set up the function to run when a form is submitted. In our case I have multiple functions (changeValueOnSubmit(e) and setIDOnSubmit(e) ) that I put inside of a onSubmit() function so I only have to set up 1 trigger. Therefore we will select onSubmit() and set this trigger to run On form submit.

We now have a working form that will populate a Google Sheet with unique identifiers and set default values.

You can now upload your own items using the Google Form. (This isn't necessary to continue as there are already demo values in). We will now dive into the user interface.

Back in the Google Script page. Click on Publish > Deploy as web app...

A popup modal will appear.

Step 1) Name this new version of the application as Version 1.0. If you choose to make update in the future use this field as a way to describe what changes you made so you can go back to a working version if the new code fails.

Step 2) Set the End User as the executioner of this application. This means the user will have to login with their email address to access your website and if they attempt to make changes the code will prevent them.

Step 3) Copy the Web URL in the top field. (You'll need this to get to your website.)

Click done and your first version of this application has been successfully published.

Back in the Google Scripts interface, navigate to Redirect.html page.

Using the address you copied in step 3, replace the current address (href) in the tags.

This page is a simple HTML page with a button that links you to your published website. This page is deployed as a popup modal in the Google Sheet by clicking on the toolbar option App View > Get Link.

A prompt will appear making the user aware that authorization is needed to run these scripts.

The second page is a disclosure agreement that Google is not responsible for the following code as it was developed and created by me. If you trust me you can click Advanced and the Go to Closet Organization Code.

LASTLY this page lists the services Google Scripts MIGHT use, in my case I am only accessing the google sheet we created, the form we created, and I am creating and running HTML code for an interface.

Finally a modal will appear with a link inside. Clicking on this link will send you to your own personal Wardrobe Website....so let's take a look.

WELCOME! We have finally reached the part you came for, the User Interface!!!!

On first look, there is nothing here. We haven't made any calls yet. To load the page faster I decided not to plague the first page with ALL your items and allow you to click what you want to see faster. Since this is the case there are no items in the main content field and no filters in the sidebar. Let's click on All to see what is in our database.

We have now loaded every item in our database into the main content field. You will see pictures, ID numbers, color, sizes, and locations. The location field can be updated right here! If you decide to loan out the item you can select that option, you can place it in your closet, dresser, or laundry.

And in our sidebar we have every possible field for every clothing item in our new query. Just imagine having 20 different sizing options on this sidebar, it wouldn't be very effective, so let's narrow our search by clicking Accessories.

Now that we have loaded Accessories, take a look at the sidebar. It has adjusted to only 3 fields, as these are the parameters that apply to every item in this query. I am going to do a sort by color. By clicking on color, a drop down box appears. Here I can either type in the color I want and then select it, or if I see my option right away I'll just click it. I selected Red for this demonstration. Click Apply Filter at the bottom of this sidebar and the main content will refresh showing you the items that have the color Red set as their color parameter.

I previously mentioned this database helps me manage my items out on loan and in my laundry. To make it a little easier, instead of manually clicking every dropdown location in this main page I created the Laundry Mode. Return to the Google Sheet page and under App View you will see Laundry Mode. This option will pull up a smaller modal that shows only items with the location of Laundry. I can now mark all of these items as Default which will place them back in their locations they normally are stored in.

CONGRATS!

For those of you that just want a working database to manage your items, welcome to your Online Organizer. For those curious minds interested in the code behind this application. Stick around as I break it down.

*You are free to delete the test items AFTER you enter at least one of your own items into the database. (I'll explain later if you stick around).

Earlier we opened up the Server Code.gs file and I gave a quick run down of each of the functions as their purpose was to serve each of the items you just set up but now we will break them down some of the functionality and utlities called to make this code a success.

1) Table traversing:

<em>var ss = SpreadsheetApp.getActiveSpreadsheet();<br>var sheet = ss.getSheetByName("Form Responses 1"); <br>var range = sheet.getRange(1,1, sheet.getMaxRows()); <br>var rowNum = range.getLastRow();</em>

• This code is a basis for traversing a Google Sheet. I call the sheet by name rather than number so that if sheets are deleted or rearranged by function can still operate properly.
• In this code I am collecting just the Range for all the data in the table.

2) Assigning an ID:

var LastID =range.getCell(rowNum-1, 1);<br>var CellValue = Number(LastID.getValue());<br>var ColA = 1;   <br>var max =15; var min=5;   <br>CellValue = CellValue+ Math.round((Math.random()* (max - min) + min));     e.source.getActiveSheet() <br>       .getRange(range.getLastRow(), ColA) <br>       .setValue(CellValue);   <br>changeValueOnSubmit(e);

• I previously asked that the demo values be left in the table until the user has submitted at least one value for themselves. This is because the Auto ID generator relies on the last value in the database to populate.
• I fetch the last 2nd to last row because the last row is our new value and the 1st column for the ID value.
• I then randomly generate a number between 5 and 15 and add it to the last value. *
• Finally I place this value in the ID column of the last row.
• Next we call the changeValueOnSubmit(e) function.

* I chose 5-15 to allow for future labeling and Google Home integration so that the numbers will not be close enough to cause confusion on hangers or clothing tags or barcodes.

3) Changing URL Value:

var DataChange = e.namedValues["Item Picture"];<br>var DefaultLocation = e.namedValues["Where are you keeping this article of clothing?"];   <br>var ColD = ColumnID_("Item Picture") +1; <br>var ColLoc = ColumnID_("Default Location")+1;<br>DataChange = DataChange.toString().replace("open?", "uc?export=view&"); <br>    e.source.getActiveSheet()<br>         .getRange(e.range.rowStart, ColD)<br>         .setValue(DataChange);<br>       e.source.getActiveSheet()<br>         .getRange(e.range.rowStart, ColLoc)<br>         .setValue(DefaultLocation);

• When submitting a photo through a Google Form the URL inserted into Google Sheets is a link to the actual document. In our case, as we are creating a HTML page we want the link to be just the image.
• By changing the "open?" portion of the URL to "uc?export=view&" we have created a link to the image instead.
• We will again place this new value in the location of the current Item Picture link.
• I am also setting the "Default Location" and "Current Location" of the item to the same thing in the database. This will be helping when trying to use my Laundry Mode.
• We will dive into it on the next page but this is our first glance at the ColumnID_() function I created.
  • This function uses Column Names to translate it into the column integer which is helpful for Range calling that requires a column number rather than name.

4) SpreadsheetApp.getUI()

• In the second image you can see the use of the SpreadsheetApp.getUI() as it used to create a Toolbar Menu addition to the Google Sheet.
• The .getUI() function also helps create a modal popup which is used for Laundry mode and as a quick link to the website interface.

5) HTMLService

• There are two types of HTMLServices used in this code: Template and HTMLOutput
• Template allows for code to be inserted inside of the HTML code so information coming from a server can be populated when the page is called.
• HTML Output displays simple HTML pages.
• We also have the includes() method which allows for us to create multiple HTML files and combine them in one templated HTML file by returning the contents of the file in an HTML format rather than a string.

I have attached a document set up like Google App Scripts Documentation to bring a familiarity to how source code and functionality is explained within Google Apps.

Now we have enter the Server Calls.gs. These functions are used primarily in the HTML JavaScript so they have been separated from code that is primarily used in the back end that are located in Server Code.gs.

Picture 1) Global Variables:

Picture 2) fetching Items:

Picture 3) fetchItemsQry

Picture 4) filterItems

Picture 5) fetchFiltersWithQry

Picture 6) ColumnID, and CacheCalls

There is so much to talk about with each of these. And in order to break the code down and explain what is going on I needed a little more typing space. Attached is a document for the code breakdown of the ServerCalls.gs

This document is set up like Google App Scripts Documentation and even makes links to similar objects.

HTML code get very unhappy within an Instructable's dialog box. So please follow along with the pictures above.

1) In the header of the Application.html page we establish a title and call our CSS.html page to be loaded.

*Being a templated HTML page, we can add more code to this document without cluttering the current screen by using the previously mentioned include(pageName) method found in Server Code.gs

The main header box is also found in this picture. You can change the header here and enter "[Your Name]'s Wardrobe" or whatever else you would like to recognize this page as.

2) Just below the header is our top navigation bar.

This navigation bar includes all the articles types as listed on the Article sheet inside of our Google Sheets.

An inline function is called to fetch an array of these items. Then a loop is ran to include each of these options as a menu button, complete with an action code so when a user click on the menu button, the respective items will appear in the body area.

3) The main body.

There are 4 portions to this part. A text output, the sidebar filter, the main body images, and the JS includes.

The text output allows for the user to see a quick text view for what type of items they are currently looking at instead of referring to the menu option they selected.

The sidebar filter contains the many filter available for the type of item a user has selected. These filters reflect all the options available for this category as well as how many items fall under that category value. This sidebar is populated with JavaScript code (which will be discussed next).

The main body is currently empty, but just like the filters it will be filled with item boxes detailing the Item's ID, Color, Sizing, and Location with an image included once the user selects a category and the JavaScript Code populates this area.

Finally the includes(JS), let's take a look at this on the next step.

If you thought the Server Code was a heavy section get a load of this.

Here we combine our HTML and SeverCode with user interactions. Any item that is clicked must be processed here to get the proper data and return it in a readable format. So let's take a look at our first calls:

The script calls: I am using 3 different libraries for this project; jquery, bootstrap, and a special bootstrap-select add-on. These libraries allow for the formatting of objects and easier calls to the elements within the HTML code.

<p><script<br>src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></p><p>  <script
src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></p><p>  <script
src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-select/1.13.1/js/bootstrap-select.min.js"></p>

My next important line of JavaScript is below:

<p>$(document).keypress(       </p><p>	function(event){<br>         if (event.which == '13') {
           event.preventDefault();
         }
     });</p>

Here I am disabling the enter key from triggering any of the forms. As in this case Google Web Apps are only assigned their one page address. An enter press would add data to the HTML address and try to redirect the user. By disabling this, you allow your JavaScript code to do all the work.

<p>function removeFilters(){<br>     google.script.run.withSuccessHandler(updateItems).withFailureHandler(onFailure).ServerRemoveFilters(); 
   }</p><p>function updateDBlocation(id, value){<br>     google.script.run.withSuccessHandler(allGood).withFailureHandler(FailDBUpdate).updateLocation(id, value);
   }</p>

Here are two functions that are making calls to the Server Code.gs file. The line:

<p>google.script.run.withSuccessHandler(updateItems).withFailureHandler(onFailure).ServerRemoveFilters();</p>

has many working part to it but the skeleton is rooted from "google.script.run" which tells the HTML page the following function is on the server.

• The last bit of this code is the function to run. In this example ServerRemoveFilter()
• By adding a withSuccessHandler() the HTML page now knows what to do with the data that is returned, and this is to run the function with the parenthesis.
• The same applies to the withFailureHandler()

Now that we have broken down the Server Code call, let's take a quick glance at what happens when these server call succeed and fail.

<p>function allGood(e){<br>     console.log("Success on server");
   }
function onFailure(error){
       $("#message-box").html("<p style='color: red'> Unable to Fetch Clothing Items at this time. ERROR: " + error.message + "</p>");
    }
    
    function FailDBUpdate(error){
      $("#message-box").html("<h3 style='color: red'> You do not have access to modify the location. ERROR: " + error.message + "</h3>");
      $(".location-selects").prop('disabled', 'disabled');
    }</p>

I created a very simple console log to signify success when the location function is run which you can see as allGood().

When handling the errors, these two functions output the error message where the user can see by using a jQuery call to the HTML object with an ID of "message-box".

Now let's get down to the gritty work -->

The top menu bar has options for each article type. The function they run on click is:

<p>function filterType(article, id){<br>      $("ul.navbar-nav li.active").removeClass("active");
      $("#currentArticle").html( " //HTML CODE HERE");</p><p>      updateSideBar = true;
      google.script.run.withSuccessHandler(updateItems).withFailureHandler(onFailure).fetchItems("Articles", article); 
   
      var newSelect = "#type-"+id;
      $(newSelect).addClass("active");
      $("#myNavbar").removeClass("in");
    }</p>

We can see in this code we have a google.script.run which calls to the Server to retrieve information. The success function for this call is updateItems().

PICTURE 1 (with the heavy HTML code within this function is it difficult to copy strictly the code, without appearing a mess in this box)

In the updateItems() code, we have a lot of things happening. Once again we must traverse through the Object[][] that was returned to us and add each item to our main body page.

The HTML code is added in as Arrays to break up the code and make it easier to read and see where itemData is being inserted.

In the loop of each item, I am removing fields that I do no want to see in the description such as Default, timestamp, and picture URL. I remove picture URL from the description because it is being added as the href to an tag instead. Once this information is assembled it is sent to the main body using the jQuery .append() function.

After all the items have been added to the page, this query of items is sent to the Server Code again to sort and return the filter options as seen in Picture 2.

PICTURE 2 (updating the SideBar)

Very similar to the updateItems() function, we once again have arrays of HTML code and a loop for all the filter options. The only noticeable change is the jQuery .selectpicker('refresh'). This function comes from the script library we included in the last step. It allows for the programmer to write a simple select HTML and let the library update it to include searchable function as well as the CSS code.

PICTURE 3 (filtering with the sidebar)

Lastly we have the updateFilter(formData) function. This is used when a form is submitted from the sidebar. We start by using a jQuery function .serializeArray() this reads the HTML code of the element defined in our case a form, and returns the values in a string to be sent to the server. And we start the process from Picture 1 all over again.

Well there you have it; a full and thorough explanation to help you set up your own online wardrobe, or utilize the functionality created in my Google Scripts to expand your own project.

It's been a journey coding this project (and documenting through this Instructable) but I have enjoyed the process and hope you will enjoy the product. I would love to hear back from anyone that makes adjustments as Michael Jordan says "The ceiling is the roof" and I agree this application has no limits.