Create your personal library with Google Sheets

List of books within Google Sheets

Are you someone who owns more than 5 books and would like to remember what books you do or do not have? Or maybe you’d like to remember who you lent your only copy of the Codex Leicester to? Well you’re in luck because you could be taking advantage of Google Sheets to create your own personal library directory and keep track of all your books.

For this project we will be using an external source to Google Sheets, the Open Library. This is a universal catalog for book metadata, which is how we will be able to retrieve book attributes such as title, author and even book cover. All of this data is retrievable via Open Library’s own API. Before we proceed I need to make myself clear that this is purely for educational purposes and the APIs are not intended to be used for bulk download. With that being said let’s get started.

Step 1: Create a sheet with the following 6 headers starting with cell A1:

• ISBN
• Cover
• Title
• Author
• Book Type
• Location

Creating the header columns

Step 2: Create the formula for Cover column

Notice how we are skipping the first column, ISBN and we’re moving straight to Cover. This is because the ISBN column is actually the last column we will be filling out with our ISBN data.

In cell B2 enter the following formula:

=IMAGE("https://covers.openlibrary.org/b/isbn/"&A2&".jpg",4,110,110)

The above formula is using the IMAGE function to retrieve the JPG from Open Library based on the isbn we provide in cell A2 (ISBN cell). Along with the retrieval of the image, we are also setting it to a custom size by first setting the mode to 4, which allows the specification of a custom size. Then we set both the height and width to 110. This allows us to to set the size of all book covers to be identical. Without this you could have a collage of book covers all ranging from thumbnails to supersized images.

Setting formula for Cover column

Step 3: Create the formula for Title column

For this step we will be using IMPORTXML to specify the XPath we want to pull content from.

=IFERROR(IMPORTXML("https://openlibrary.org/isbn/"&A2&"","//*[@id='contentBody']/div[1]/div[2]/h1"),"")

I wrapped the IMPORTXML function with IFERROR to display nothing if it presents any errors due to other cells being empty. This is purely cosmetic so it doesn’t throw errors while you’re compiling your list of books.

The IMPORTXML function is making use of Open Library’s ISBN API and then we’re querying the xpath to the element that contains a book’s title.

Setting formula for Title column

Step 4: Create the formula for Author column

Same as in Step 3, we will be making use of the IMPORTXML function. The only difference here is that we will be querying a different xpath that points to the element which contains a book’s author.

=IFERROR(IMPORTXML("https://openlibrary.org/isbn/"&A2&"","//*[@id='contentBody']/div[1]/div[2]/h2/a"),"")

Setting formula for Author column

Step 5: Create a list selection for Book Type column

There are a few ways of doing this but for this tutorial we will select cell E2 and go to Data, then select Data validation.

For Criteria, select List of items

Enter “Physical,Digital” in the input box. Make sure to have ‘Show dropdown list in cell’ selected and click ‘Save’.

You should now have a dropdown list in cell E2 to select between Physical or Digital. This is to differentiate whether the book you own is physical or an ebook.

Creating List of Items

Step 6: Prep the sheet for the amounts of books you have

For this step we will skip the last column; Location.

Select all the cells from B2 to F2 and drag the bottom right corner of F2 all the way down until you have reached the desired count of rows needed for your book collection. Once this is done you’re ready to start importing your books’ metadata.

Copying formulas to the rest of the rows

Step 7: Enter your books’ ISBN

This step may be daunting but the work will pay off in the form of having a nice structured list of books you own.

Start entering the ISBN for each book you own in the cells within column A (ISBN column). The Open Library supports valid ISBN 10 or 13.

As soon as you start entering your ISBNs you will start noticing that the data from the other columns (Cover, Title, Author) are automatically filling in. This is due to the formulas we created in the previous steps.

Make sure to also specify whether the book is a physical or digital book for your own sake.

Inputting ISBNs

Step 8: Resize the cells

This is a quick step to resize the cells to match the size of the imported images.

If you recall from Step 2, we imported the images with a set size of 110x110. So we can select all the rows, except for the headers, and right-click then select “Resize rows x — x”.

Set the value to 110.

You can also select the entire B column(minus the header) and select “Resize column”.

Set the value to 110

This should make the images within the Cover column to be perfectly displayed as squares measuring 110x110 pixels.

Resizing the cells

Step 9: Enter the location of your books

For easier book keeping you want to add a little note under the Location column to give you an idea where the book is currently at. Some good examples are:

• Bookshelf #1 top shelf
• Lent it to Bob
• Book bag
• Mom has it
• The URL of an ebook

This will allow you to quickly know wether the book is currently in your possession, and where exactly, or if someone has borrowed it. Having a good inventory of location may even encourage you into lending some of your books that haven’t been touched in such a long time. There’s nothing more sad than a book that’s confined by its own cover and its letters never seeing the light of day again.

Example use of the Location column

Well there you have it, a directory of all your books. I hope you find great use out of this sheet. I welcome you to add more columns to make it “complete” to your liking.