Book inventory

Here we’re going to make a simple book inventory, where we’ll be able to control the location of the books and also find out where a book is. This uses a mixture of GAS code and Sheets functions.

We’ve got 3 sheets:

book-inventory-4

Front page – This is what the user will see and use. In the yellow part, we’ll be able to find out the location of a book from its code. They enter the book number in cell B4 and the current location appears in B5. In the green part, we’ll be able to change the location of a book or in fact up to 10 books all in one go. The user adds the book numbers in cells B11 to B20 and enters the location in B10. Then from the menu they’ll run the program to update the list.

book-inventory-1

Coursebooks – On this page we have titles of the books, level, which part of the set it is (e.g. SB: student’s book), its individual reference number, and its current location. Columns D and E are what we’re going to use.

book-inventory-12

Ref – This page is used to create the drop down menus on the Coursebooks page using data validation.

book-inventory-11

In cell A2, I want to list the titles of the books we have on the Coursebooks page, but without duplicating any. So, I use the UNIQUE function and combine it with the SORT function to sort it into ascending order.

book-inventory-2

In column C I’ve listed the possible levels. In column E the possible type of book.

In column H I write the possible locations of the book, then to save me having to sort this list alphabetically every time I add a new person or location, I’ve added in cell I2 the SORT function below, to sort it alphabetically automatically and this is what the Coursebooks sheet will use.

book-inventory-3


THE CODE

There are two pieces of code here, one runs when the file is opened, and the second runs when the “Update locations” is selected from the “Books” menu.

CODE 1

It’s going to create a menu, clear any unwanted previous information on the Front sheet, highlight cell B10 to save having to click on it, and display a message when it’s finished.

book-inventory-31

Here’s the code step-by-step:

First, I start with a function and call it onOpen(). This tells the file to do something when the file is opened.

book-inventory-6

Line 3: Then I get the current spreadsheet and store it in the variable ss for later use in the code. Note, that I’ve added a comma at the end of lines 3 & 4, so that lines 4 and 5 are part of this.

Line 4-5: This states the name of the menu item and tells it which function to run, when someone clicks on it. This is stored in the variable menuItems and is stored as an array, so you need to make sure you enclose it all in square brackets and as these are menu properties, you’ll need to enclose them in curly brackets too.

If you use this code for your own projects, just copy lines 3 to 6 and change the text parts in inverted commas.

Line 6: This adds the menu to the spreadsheet. The first part “Books” is the name of the menu you will see on the menu bar, then the second part refers to the information in Lines 4-5, i.e. the menu item and associated function to run.

The rest of the code sets the sheet up ready to be used.

book-inventory-7

Line 9: When the user opens the file, I want them to start on the first page.  To select that specific sheet I want, I use the getSheets method and state that I want sheet 0. The getSheets method gets all the sheets in the spreadsheet and puts them in an array. This is why the first sheet is sheet 0, as arrays start with 0 (i.e. are zero-based). Now it’s got sheet 0 the setActiveSheet method makes the sheet I want the active one.

Next, I want to clear any book numbers or location that may have been previously entered in the range B10 to B20.

book-inventory-32

Line 12: First, I get the range I want using the getRange method, and here I enter the range between inverted commas.

Line 13: Then I get the range and use the clearContent method to clear it.

As the user more often than not, will want to fill out the new location in B10, I want the sheet to highlight that cell when they open the file, so they don’t have to click on it.

book-inventory-9

Line 16: First, I get the cell range using the getRange method and store it in the startCell variable.

Line 17: Then, I make that cell the active range, using the setActiveRange method and refer to the variable startCell where the range is stored.

Finally, just to tell the user the sheet’s ready to use, I want to display a little toast message, which is a pop-up message that appears in the bottom right-hand corner of the screen.

book-inventory-10

Line 20: This uses the toast method and has 3 parts to it in the brackets. The message you want to display, the title of the message, how many seconds you want the message to display.


CODE 2

Now let’s look at the second piece of code. We could add it at the bottom of the code we’ve just seen, but I often keep different functions separate just so it’s easier to read and to find the specific code you want.

First, let’s change the first one from “code” to “onOpen”. On the left of the screen you’ll see the list of script files you have for this particular project. To do so, just click on the little triangle next to the title and choose “Rename”, then type in a new title. You don’t need to add .gs at the end, it’s added automatically.

book-inventory-33

Now, let’s create a new Script file. Go to File>New>Script file.

book-inventory-30

Then, type in the title of the Script file and press OK.

book-inventory-34

You’ll see both script files on the left-hand side of the screen. Now, we’re going to work with the enterBookLocation one, so click on that file to open it up in the editor.

book-inventory-28

Here’s the complete code we’re going to use:

book-inventory-13

As before, let’s go through it step-by-step. First, start off with a new function called enterBookLocation and in line 3, let’s get the active spreadsheet.

book-inventory-14

Now, we want to get the details entered on the Front page, i.e. the location and the book numbers.

book-inventory-15

Line 6: We get the sheet called “Front page” using the getSheetName method. This is stored in the frontpage variable. Note, I could have used the getSheets ()[0] method as in the other code, but I just wanted to show an alternative method.

Line 7: Here we want to get the location entered, so we get the value in cell B10. Note, we’re using the getValue() method as it’s just one cell.

Line 8: Here we want to get the range of cells from B11 to B20, in case the user has entered up to 10 books. This time we use the getValues() method as there are 10 cells and this stores the values in an array variable, which we will call books. We will use this to find the books in the inventory.

Now, we need to get the list of books on the Coursebooks page.

book-inventory-16

Line 11: First, we get the sheet called “Coursebooks” with the getSheetByName method and store it in the coursebooks variable.

Line 12: Then we need to get the last row so we are able to get the list of books from the top to the last one on the list. To do this, I use the getLastRow() method.

Line 13: Now we can get the book numbers in column D. So, first we get the range using getRange and in the brackets we put the following information (starting row, starting column, number of rows, number of columns). So, here we start from row 2, column 4, down to the last row, and we only want this 1 column. Then we need to get the actual values in that range, using getValues(). So, now we have an array variable called bookNumbers which contains all the books.

Line 14: As we want to update the locations, we need to get the range of locations too. So, as before we use getRange and this time get column 5. We only need the ranges and not the values in those ranges, so we don’t need getValues(). We store this in the variable locationRows.

I want it to get a book number from the Front page then look it up on the inventory, and once found will get the corresponding location and change it to the one entered on the Front page. Then it will get the next book number and do the same until it comes across an empty cell on the Front page, when it will stop and go to the end of the code.

First, we need to set up a for loop which will go one by one down the list of book numbers on the Front page.

book-inventory-17

Line 19: We start with for and in the brackets we put 3 pieces of information:

(starting number of the variable; the condition which while it’s true will continue the loop; how much we’re going to increment the variable by each time it loops)

In this case, the variable bn starts at 0; it will continue while it is less than 10; every time it loops around the variable bn goes up by one (++ means +1). Then in curly brackets we put some kind of action. I’ve opened the brackets in line 19 and the closing bracket is on line 36. So, all the lines between 19 and 36 are included in this loop.

Now we create a second for loop to go down the book list and look for a matching number.

book-inventory-18

Line 22: The for loop is similar to the one above, but this time I use the variable RN (row number) and use the variable numRows as the condition, so that it will start at the top and go down the list one by one to the bottom. The open curly bracket is on line 22 and the closing one is on line 35.

Line 23: Every time it goes down the list we want it to check to see if the current book number on the list (stored in the bookNumbers variable) is the same as the book number on the Front page (stored in the books variable). To do this, we use an if statement. We put the condition we want to check in between brackets.

Here we get the value from the array variable bookNumbers and at position RN in our array. The zero is because our array is only one line of values, so the number in the second square brackets will always be 0. So, the first time it loops, it gets the value in the variable bookNumbers at [0][0], which is the first book on our list.

Then we use the triple equal signs to show we want the bookNumbers value to equal the books value. Now we need the books value at position bn, 0, which is the first book number, i.e. The value in cell B11.

Next I’ve add a second if statement to check to see if it has come to the end of the list of book numbers entered on the Front page, as this will speed up the program as it won’t continue to loop down the range B11 to B20 if it comes across an empty cell.

book-inventory-19

Line 26: Here it’s checking to see if the value in books is equal to “”, in other words, an empty cell.

Line 27: If it does, it “breaks” out of the loop, which means it doesn’t continue the loop and goes to the next part of the code after the loop (in this case line 37).

If there is a value and it the book number matches a number on the list, it performs the code in lines 31 to 33.

book-inventory-20

Line 31: Here we want to get the cell where the values match. This uses the getCell(row, column) method. For the row we use the variable RN and add 1 to convert it from its array position to its actual row on the sheet. The column is 1 as we are only using 1 column.

Line 32: Then we need to make that cell the active one and set its value with the new location. So, we get the sheet coursebooks, use the setActiveSelection method and use the location we just got in line 31. Then we set the value with the value from the location variable from the Front page.

Line 33: As it’s found a match, it doesn’t need to continue looking down the list, so we break out of the current loop by using {break}. Again this is to speed up the program.

Lines 34 to 36: These contain the closing curly brackets for the loops above.

The loop continues updating the location of each book until it comes across a blank cell in range B11 to B20.

Finally, once it’s updated the locations, I want it to return back to the Front page and highlight cell B10 again ready for future use, using similar code to what we saw in the onOpen function.

book-inventory-21

Line 38: This gets the first sheet.

Line 39: This gets the cell B10.

Line 40: This sets that cell as the active one.

Line 41: Closes the function.


Program in action

The user enters a new location (often a person) in cell B10. Then enters the books they want to link to that location. Here I’ve added two.

book-inventory-22

They click the Books menu, then Update Locations.

book-inventory-23

book-inventory-24

As we can see on the Coursebooks page, it’s updated book 0002 and 0005 to Paul. So, you can imagine with an inventory with hundreds or thousands of books, this will save the user a lot of time, as they can update up to 10 books in seconds, rather than scrolling up and down the list looking for the books to update.

book-inventory-25


Finding a location of a book

In the yellow part I’ve added a quick way to find out where a book is by entering the book number. This is particularly useful when a book is found and we want to find out whose it is.

The user types in the book number in cell B4 and the location appears in B5.

book-inventory-27

There’s no coding involved with this one, just the following VLOOKUP and IFERROR function. Using the VLOOKUP function, this looks up the number enters in B4 in column D on the Coursebooks page and gets the corresponding location from column E. If the book isn’t on the list, it comes back with the message “Book not found” by using the IFERROR function.

book-inventory-26


Here is a link to make a copy of the file, which contains the code above.

https://docs.google.com/spreadsheets/d/1vC6KDJXMmN1xwqS9sThUP8BbqyPJQ5TLyGl03XGFbvM/copy

I did think of writing a program to enter new books but to be honest we found it was just easier to add the book to the bottom of the list using the drop down menus to speed up the process.

If you want to know more about how if statements and for loops work I recommend reading the information on the W3Schools site:

For loop

If statement


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Follow my Google+ Learning Google Apps Script Collection

Baz Roberts (Flipboard / Twitter / Google+)


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s