I spent a lot of time building tools at work. There were things we needed to automate, but I didn’t have a server or any kind of programming platform to put things on. Because of that I discovered you could do a lot just using Google Sheets.

I built a tool that let you scan in ISBNs and get the title and author of a book. I built another that let you put in the title of a movie and get a plot summary and list of actors. I designed another one that let you scan in a CD and get recommended genres and sub-genres. (That one I had to hire someone to program as the implementation was a bit beyond me.) There’s no telling how much time those tools saved me at work, and they left me with a slight mania for making useful spreadsheets, though it’s not really one of my job functions anymore.

Last week I got an e-mail from a reader named Joe asking for help researching a topic he didn’t know a lot about. Then at about the same time, I read about an add-on that would let you import Wikipedia data into Google Sheets. And I thought, “Hey, this might be a nifty way to quickly build Google search queries when you don’t have a lot of information.”

And so the Web Search Query Builder 5 Million Google Sheet was born. (The “5 Million” part is in honor of the Hydraulic Press Channel, which seems to name all its original tools the dup-de-dup 5 Million. Visit YouTube to see the Rock Maker 6 Million (it’s an upgrade) turn hair into powder.)

You can try the sheet at https://docs.google.com/spreadsheets/d/1hl0Ku9eqLIcr3piiz5DI_bbeZnQhZP-4eMLRBFLWJ44/edit?usp=sharing . I encourage you to make a copy of it for yourself and play with it. I’ve found it’s useful for getting a quick overview when I run into an unknown concept or name while doing ResearchBuzz; it’s much faster than searching for it “cold,” with no contextual hints. (And since I run into topics and names I don’t know constantly, I’m hoping this will save me some considerable time.)

PLEASE NOTE VERY IMPORTANT: If you do make a copy of it and (and as I said before, please feel free) you will need the Wikipedia and Wikidata Tools add-on for Google Sheets. It’s free and you can find it here: https://chrome.google.com/webstore/detail/wikipedia-and-wikidata-to/aiilcelhmpllcgkhhpifagfehbddkdfp?hl=en 

In this article I’ll give you an overview of the sheet and explain how the tabs work.

What the Heck Is This

What this the heck is, is a sheet that uses the Wikipedia and Wikidata Tools add on to draw data from Wikipedia in response to a query and then build from that data a set of Google search queries which are tweaked slightly by domain limitations and thematic keywords. In the case of this particular sheet, I’m including limitations to the .edu and .gov domains, and adding additional keywords with the hope of finding more teacher-friendly resources. The tool is set up in a series of five tabs.

Tab 1 – Queries

The first tab is for entering your search term. General searches like “cow” will get you so many results as to be useless. I find that names and events and somewhat obscure concepts are the most fruitful; these searches for example brought good results:

Great Patriotic War
Bangladesh Liberation War
Mzwakhe Mbuli
EMDR

Underneath the query box you’ll see count results for your query; enter your search term, and these boxes will briefly reset to 1 and then update with the number of queries returned, thesaurus lookup results, outbound link topic results, and category results. There’s a maximum of 500 items per line. If you find that you’re getting 500s or 100+ for everything, your search may be too broad. I do find, though, that popular culture figures tend to get very high numbers no matter what because they’re linked to so many things.

Once you enter a search term, all the other tabs will update.

Tab 2 – Wikipedia Data

This tab displays all the results that are pulled from Wikipedia and put in this sheet. The data here will be used in building search query URLs in the following sheets. I find that glancing at just this sheet can give me a general overview (very general) of a name or concept.

Tab 3 – Search Engine queries

This tab presents you with a number of Google search URLs. The first set uses the first five lookup keywords to create general Google searches. The second set uses three keywords at a time to create a more specific query. (It works like this: the first URL incorporates the 1st, 2nd, and 3rd row of Wikipedia query results, the second incorporates the 2nd, 3rd, and 4th row, etc.)

The third set uses five keywords at a time. That one can be so specific as to give you no results!

Hold your mouse pointer over a URL and you’ll get a little pop-up box with the option to open the URL (you’ll see what that looks like in the screen shot.) Unfortunately the URLs aren’t really directly clickable; that’s a Google interface decision and not something I can change.

(Also note that if you run a search that has a limited number of results from Wikipedia, all of the search URLs won’t populate with keywords. They’ll still be on the page, they’ll just be empty.)

Tab 4 – Search Engine TLD-Domain Restricted Links

Tab 4 takes the same kind of searches done in tab 3, but adds domain limiters so the first set queries is restricted to .edu domains only and the second set is restricted to .gov. There are simple and medium queries here, but the “deep dive” queries of five keywords at a time are removed. I found doing searches that large AND restricting by top-level domain tended to produce few to no results.

Tab 5 – Google Queries for Finding Learning/Reference

This set of Google queries removes the domain restriction but adds a couple of keywords designed to get the results to go in a more scholarly/learning direction. This isn’t perfect and the kind of result you’ll get will depend a lot on your initial search.

This Could Go a Lot Further

When I initially developed this, I didn’t know how useful I would find it. But I showed it to my friend Kathy Jacobs, who looked at it and very helpfully broke it in a couple of places (when you make things like this, it’s invaluable to have someone technical enough to poke at it, break it, and then explain clearly to you how they broke it) and she liked it a lot. So maybe you will too.

Obviously this could go a lot further. I could add an option for specifying domains (top level or otherwise) that you want to restrict your generated searches to. You could specify your own keywords. Maybe customizable country restrictions for your searches.

Let me know if you find this useful and would like to see some upgrades. I’ve only done a little spreadsheet work since my job changed, and I realize I miss it!

Thanks to my great patrons at Patreon who are supporting ResearchBuzz and affording me time to write these articles.