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:

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.

On the right-hand side, we have an area to enter a book number to delete. Plus, an area to add a new book.

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.

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

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.

=SORT(UNIQUE(Coursebooks!A2:A))

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.

=SORT(H2:H21)

---

THE CODE

There are four pieces of code here:

Code 1 – onOpen: To create the menus to run the scripts from, and to setup global variables

Code 2 – updateBookLocation: Function to update the locations of the books

Code 3 – Delete book: Function to delete a book from the inventory

Code 4 – Add new book: Function to add a new book to the inventory

---

Code 1 – Creating menu & setting up front page

First, let’s look at the onOpen script file, which creates the menus to run the scripts from, and also holds a few global variables.

I’ve set up these global variables as they are used in different functions, so it saves creating them repeatedly.

1. //Global variables
2. const SS = SpreadsheetApp.getActiveSpreadsheet(),
3.   SHFP = SS.getSheetByName("Front page"),
4.   SHINV = SS.getSheetByName("Inventory");

First, let’s set up some global variables which we will use in the different functions in the program.

L2: We get the spreadsheet and store it in SS. Note, the use of capitals to show it’s a global variable.

L3-4: Get the sheets “Front page” and “Inventory”.

Now, let’s look at the onOpen function which create the menus when the spreadsheet is opened.

6.	//Adds Books menu and activate cell B10
7.	function onOpen() {
8.	//Add BOOKS menu
9.	const ui = SpreadsheetApp.getUi();
10.	ui.createMenu("BOOKS")
11.	  .addItem("1-Update locations", "updateBookLocation")
12.	  .addSeparator()
13.	  .addItem("2-Delete book", "deleteBook")
14.	  .addSeparator()
15.	  .addItem("3-Add new book", "addNewBook")
16.	  .addToUi();

L7: Open the function.

L9: Get the UI of the spreadsheet to then be able to edit it.

L10: Create the menu called “BOOKS”.

L11: Add items to that menu, which will be specific functions we want to run. The first one will run the updateBookLocation function to update the book locations we’ve entered.

L12: To space the menu out a little, and to help prevent the wrong option being chosen, I usually add line separators in between the menu options.

L13-15: We do the same for the second function (delete a book) and the third one (add a book).

L16: Then add this to the UI.

Finally, I also want the ranges on the Front page sheet to be cleared , in case they contain information from the last time the sheet was used. Plus, I want to highlight cell B10, so it’s ready for the user to update the book locations, as this will probably be the most likely use of the file. It’s a simple thing, but saves them having to click on the cell.

//Clears cells and selects cell B10 on Front page
19.	 SHFP.getRange("B10:B20").clearContent();
20.	 SHFP.getRange("E5").clearContent();
21.	 SHFP.getRange("E11:E15").clearContent();
22.	 SHFP.setActiveRange(SHFP.getRange("B10"));
23.	 SS.toast("Ready!");
24.	}

L19: First, clear the contents of cells B10 to B20.

L20-21: Then, clear cell E5 and cells E11 to E15.

L22: Then, set cell B10 as the active range, i.e. the cell will be highlighted.

L23: Finally , give some feedback back to the user that everything is ready by uses a little toast message.

L24: Close the function.

When the file is opened the following menu will be created:

Plus, we can see that cell B10 is highlighted.

And once all done, the toast message appears.

Code 2 – Updating book locations

Next, let’s look at how we update the book locations.

The user enters the location of a book or a set of books in cell B10 on the Front page sheet. They then add the book numbers in the cells B11 to B20. They can add 1 to 10 books. They then select “1-Update locations” from the BOOKS menu to run the script.

Let’s see how the code works.

1.	//Rev 1
2.	function updateBookLocation() {
3.	//Get location and books entered on Front page
4.	const location = SHFP.getRange("B10").getValue(),
5.	      books = SHFP.getRange("B11:B20").getValues();

L2: Open the function updateBookLocation. This will be called from the “1-Update locations” menu.

L4: Get the location value in cell B10 on the Front page sheet.

L5: Get the book numbers in cells B11 to B20 on the same sheet.

7.	//Get all book numbers on Inventory sheet
8.	const  numRows = SHINV.getLastRow() - 1,
9.	       bookNumbers = SHINV.getRange(2, 4, numRows).getValues();

L8: Get the last row on the Inventory sheet and minus one, as we’ll be getting the book numbers, which don’t start until row 2, so one row less to get.

L9: We get the book numbers in column D, and get them from row 2 until the last row.

Next, we need to convert the array of book numbers. Why? Well, at the moment we have a 2D array, as we have a separate array per row, but later in this code, we want to find a specific book number to update and we will use the array method indexOf() and that only works with a single 1D array. So, we’ll need to convert it, which is easy to do with the map() method.

11.	//Change book numbers array from 2D array to 1D array
12.	const bookNumbers2 = bookNumbers.map((bookNumber) => {
13.	  return bookNumber[0];
14.	});

L12: Here we apply the map method to the bookNumbers array and store the result in bookNumbers2.

L13: As the map method loops through the array, it will return the book number in each row, which we get by getting the first position of the bookNumber array in each loop.

Note, bookNumber returns a value like this: [2.0], whereas, bookNumber[0] returns a value without the square bracket, i.e. not an array: 2.0.

L14: We close the map method and function.

Next, the book numbers entered on the Front page may include 1 to 10 numbers, so let’s remove any cells that are blank from the books array.

16.	//Remove empty cells from books array
17.	const filteredBooks = books.filter((bookNo) => {
18.	  return bookNo != "";
19.	});

L17: Similar to the map method above, the filter method will loop through the array and but this time will only return what you specify to the new variable.

L18: Here, it’s going to return a value if it’s not blank. So, of you only entered 2 book numbers to update, it will produce a new array with just those two numbers. This makes updating them quicker as there will be less to loop through in the next step.

L19: We close the filter method and function.

The final step, is to loop through the list of book numbers you have left, and to find them on the inventory book numbers list, then update the location associated with them.

21.	//Get row nos of books entered and update locations
22.	filteredBooks.forEach((book) => {
23.	  let rowNo = bookNumbers2.indexOf(book[0]) + 2;
24.	  if (rowNo > 1) {
25.	    SHINV.getRange(rowNo, 5).setValue(location);
26.	  }
27.	  else {
28.	   SpreadsheetApp.getUi().alert("Book " + book + " not found");
29.	  };
30.	});

L22: Here, we use the forEach() method to loop through the filteredBooks array.

L23: Next, the look for the position of the book number in the bookNumbers2 array. Note, we get the first position of book, just to get the number and not the number in an array. We add two to the position number to get the row number on the sheet.

L24: Here, I’ve added a check to make sure it’s found the number on the bookNumbers2 array, as if it hasn’t it will return the value -1. Then two is added, which would make 1, and so it would try to change the value in row one, when in reality it hasn’t found a matching book number.

So, a quick, easy way to check that is to check that the row number is more than one. If it is then it has matched a book number and will update it, if not it won’t update anything.

L25: If it’s found the book number, it gets the row it’s on and gets the cell next to it, which is in column E, and changes the value to the location value from the Front page.

L26: Close the if statement.

L27-29: If it doesn’t find the book number, it alerts the user to the fact the book wasn’t found, and states which book number it was. Once the alert is cleared by clicking OK, it continues looping through the array and updating other books.

32.	 SS.toast("Books updated!");
33.	}

L32: At the end of the process, the user sees a toast message informing them that the books have been updated.

The whole thing only takes a second.

---

Code 3 – Deleting a book

The second part of this project is to delete a book from the inventory.

The user will enter the book number to be removed in cell E5 on the Front page sheet and then select “2-Delete book” from the BOOKs menu. This will find the book on the Inventory list and delete the corresponding row from it.

1.	//Rev 1
2.	function deleteBook() {
3.	//Get book number to delete
4.	const bookToDeleteCell = SHFP.getRange("E5");
5.	const bookNumber = bookToDeleteCell.getValue();6.	

L2: Open the function deleteBook.

L4: Get the cell on the Front page sheet that the book number is in, i.e. cell E5. I’ve done this as this range will be used later on too, so it saves an extra call in the script, which spends things up a little.

L5: Get the book number from cell E5.

7.	//Find book number on inventory sheet
8.	const bookNumbers = SHINV.getRange(2, 4, SHINV.getLastRow() - 1).getValues();

L8: Get the book numbers on the Inventory sheet in column D.

10.	//Change book numbers array from 2D array to 1D array
11.	const bookNumbers2 = bookNumbers.map((bookNumber) => {
12.	 return bookNumber[0];
13.	});
14.	

L10-13: Like we saw in the updating code part, we need to convert the bookNumbers array from a 2D array to a 1D array, so we use the same map() method as before.

15.	//Get row nos of books entered and update locations
16.	let rowNo = bookNumbers2.indexOf(bookNumber) + 2;
17.	if (rowNo > 1) {
18.	 SHINV.deleteRow(rowNo);
19.	 bookToDeleteCell.clearContent();
20.	 SS.toast("Book deleted!");
21.	}

L16: Look for the book number in the bookNumbers2 array and add two to the position it finds to get the row number.

L17: We check if it’s found the number by checking it’s greater than 1 (i.e. -1 (not found) + 2 = 1).

L18: If it has been found, delete the book that holds the book details.

L19: Also, remove the book number from the Front page sheet cell E5, as it’s no longer needed.

L20: Advise the user it’s been deleted, by showing a toast message.

L21: Close the if statement.

22.	 else {
23.	  SpreadsheetApp.getUi().alert("Book " + bookNumber + " not found");
24.	 };
25.	}

L22-24: If it didn’t find the number, then alert the user by stating it hasn’t been found.

L25: Close the function.

---

Code 4 – Adding a new book

The final part of the project, allows the user to add a new book to the inventory list.

The user enters the book details on the Front page sheet in cells E11 to E14, then selects “3-Add new book” from the menu. This adds the book details at the bottom of the Inventory list and adds the new book number (which is the next sequential number to the last one on the list) to the Front page sheet in cell E15.

1.	//Rev 1
2.	function addNewBook() {
3.	//Get new book details
4.	const bookDetails = SHFP.getRange("E11:E14").getValues(),
5.	name = bookDetails[0][0],
6.	level = bookDetails[1][0],
7.	part = bookDetails[2][0],
8.	location = bookDetails[3][0];

L2: Open the addNewBook function.

L4: Get the book details (name, level, part, and current location) from the Front page sheet, cells E11 to E14.

L5-8: Assign the individual pieces of data to separate variables, so we can reconstruct them in a new array later on, so they can be added to the Inventory page.

10.	//Get last row, last number, and create new number
11.	const lastRow = SHINV.getLastRow(),
12.	lastNumber = SHINV.getRange(lastRow, 4).getValue(),
13.	newNumber = lastNumber + 1;

L11: Get the last row on the Inventory sheet.

L12: Get the last book number on the same sheet.

L13: Create the next sequential book number just by adding one to the last one.

15.	//Add book details to inventory
16.	SHINV.appendRow([name, level, part, newNumber, location]);
17.	SHINV.getRange(lastRow + 1, 4).setNumberFormat("0000");

L16: Append the new book details to the Inventory list, i.e. add a row after the last row. This also includes the book number just created.

L17: Just in case, the sheet isn’t formatted correctly, let’s format the book number cell, to make sure it’s a 4-digit number.

19.	//Add book number to Front page cell E15
20.	SHFP.getRange("E15").setValue(newNumber).setNumberFormat("0000");
21.	SS.toast("Book added!");
22.	}

L20:  Finally, add the new book number to the Front page and cell E15. Again, format it into a 4-digit number.

L21: Tell the user the book’s been added.

L22: Close the function.

The beauty of this is that the user never has to touch the Inventory sheet, and so won’t mess up the data on there. That page could be hidden, so users aren’t tempted to edit the data on there.

---

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

---

---