Hi Reader,

Welcome to the Google Sheets Tips newsletter #239!

One downside of working from home is that I don't have colleagues I bump into at work.

I don't have those "water-cooler" chats, casual conversations about life's minutiae.

So instead, I chat (via Zoom or Meet) with other folks in the same boat as myself: running small education businesses.

Recently, one friend asked for some book recommendations, so I dug through my book list to share some recent favorites. And I thought, why not share them here too.

So here they are, some of more memorable books I've read in the past few years:

Novels

• A Gentleman in Moscow - Amor Towles
• The Paris Architect - Charles Belfoure
• The Silent Patient - Alex Michaelides
• Seveneves - Neal Stephenson
• The Goldfinch - Donna Tartt

Non-fiction

• Midnight in Chernobyl - Adam Higginbottom
• The Wright Brothers - David McCullough
• Shadow Divers - Robert Kurson

Business

• Range - David Epstein
• Atomic Habits - James Clear
• Company of One - Paul Jarvis

What are your thoughts? Do you have any book recommendations?

_______

➜ Google Sheets Tip #239: Google Search Formulas in Sheets

Here is your Google Sheets tip, which you can think of as your Monday morning espresso, in spreadsheet form.

Sticking with the theme of books, let's see how to build a Google Search formula inside our Google Sheets.

Suppose we have a list of book recommendations in column A:

Let's add a hyperlink formula in column B.

Google Search Formula

First, we need to use the ENCODEURL function to convert these titles into web-friendly titles by removing the spaces.

Then, we add the Google search URL to the front:

Note the "?q=" parameter to specify the search parameter.

Finally, we wrap the result with the HYPERLINK function, to turn it into a clickable link.

The result looks like this:

Clicking any of the links will take you to the Google search results for this product.

Bonus: Image Formula

If you like formulas, then I have a treat for you...

By modifying our search URL to specify an image search, we can actually display the book covers in our Sheet, via formulas:

So first, with our formula in column C, we modify the search URL to search images:

Next, we use the IMPORTXML function to retrieve the image URLs from the search results:

(See here for more examples of web scraping with IMPORT functions.)

Then we select one of these images, using the INDEX function:

And finally, we wrap it with the IMAGE function to show the book cover:

Amazing!

It doesn't just work for books of course... here are some bike images I pulled with these formulas:

_______

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. Beautiful maps of the world's river basins, from the Visual Capitalist.

And another from this site, the world's top 50 most visited websites.