Automations are gifts you give yourself. They save you time, they let you focus on your real work, and they let you turn tedium into reward through the accomplishment you feel for solving a problem. But sometimes, you can just let it be about looking good. Automation can let you do things people thought were prohibitively time consuming.
A couple years ago, I was wrapping up on a project for the good folks at PERE at USC, a website for their Changing States report which laid out a framework for progressive governance across the US. We were discussing the credits page and this report had a lot of people to thank—at least 100.
“Here’s a list of all our strategic advisors, could you list them by state on the website?” They asked.
“Sure thing,” I said. “Any particular format?”
“I guess just their names, a comma, and their org. There’s a lot and I know you’re running short on time.”
“What about linking to everyone’s website? Makes it easier for people to visitors to go on a learning binge. Your partners will like it.”
“You can do that?” Incredulous. “Yeah that would be awesome if you can do it.”
Find 100 Webistes
I’m not going to go into how I made the code for credits page since that’s kinda trivial. Instead, I want to focus on how I automated the part of the task that would actually be time consuming without some creative means.
The sheet did not supply any URLs for the organizations, just their names. So if I wanted to get their websites, I would need to search for all 100 organizations and copy their web addresses.
That’s no problem. Just automate the process of performing a search.
How Do You Automate 100 Searches?
With as little effort as possible, that’s how! Google searches can be performed simply by entering a search URL, so we just need to create 100 URLs and open them all up at once.
This post will take you through:
- Defining a workflow so we know what we want to do 100 times
- Creating a single “Search URL” to learn how to search the web without doing
- Making 100 Search URLs at once using a spreadsheet
- Opening a dozen URLs at a time using a web service like OpenAllURLs.com
Define the Workflow to Be Automated
As with all automations, we need to figure out exactly what a single sucess looks like, so we can do it over and over. So what does it look like when you search for an organization and copy its web address?
I started by doing Google searches for the orgs’ names. Nine times out of then, the very first result for “Organization Name” would be the org’s website.
Internet veterans remember Google’s “I’m Feeling Lucky”. If you’ve forgotten what that is, it takes you directly to the first result of a Google search without having to see the search results. So doing “I’m Feeling Lucky” searches would shave off some time. All that’s left is just to copy the URL.
So we’ve got our action: Perform an “I’m Feeling Lucky” search and copy the address of the page you land on.
Crash Course on Search URLs
Doing I’m Feeling Lucky searches is faster than regular searches that take you to a results page, but it’s still too slow. We want a way to perform X numbe of “I’m Feeling Lucky” searches simultaneously as opposed to one after another.
A “Search URL” is a just a URL that initiates a web search. If you enter https://www.google.com/search?q=cheese into your address bar, then you will get the search page for cheese. It’s exactly the same as typing “cheese” into a search bar.
Anatomy of a Search URL
That bit after the equals sign? That’s the part we care about.
“search?q=cheese” is called a “Query String”. It passes information to a website. In this case, it’s passing our query to Google’s search.
Finding Search URLs and Identifying the Query
When finding the query in a search URL, you don’t really need to know what any of the computery gibble gabble means. You just need to find the query and replace it with your search. If your search has multiple words, separate them with a + sign.
Take a look at these search URLs and try to find and where the query is. I’ve put them in all caps for your convenience.
Website: Alternative To: Find similar software
|Query: unicorn horn
Website: Flat Icon: Icons and assets
|Query: angels landing zion
Website: Pixabay: No attribution images
|Query: underwater nuclear basket weaving
Website: JSTOR: god-awful academic search
|Query: charmander curve
Website: Wolfram Alpha: math homework cheating engine
Creating our URL List
Now that you know how to create a search URL, we just need to take our list of search terms and use them to create a list of search URLs.I prefer scripting, but using a spreadsheet is easiest for most people.
Build Your “I’m Feeling Lucky” Formula
Using Excel or Google Sheets, you can create a formula that will take the search terms from one column and use them to build your search URLs.
In column A of a spreadsheet, enter your search terms one per row. In column B, enter this formula:
Let’s breakdown the formula:
"https://google.com/search?btnI=&q="is the query string for “I’m Feeling Lucky”. Everything after the “=” is the search terms
SUBSTITUTE(A1," ","+")takes the contents of cell A1, which should be the organization name, and replaces the spaces with + signs
CONCATENATE("", "")takes the two things in double quotes and joins them together.
The end result will be a complete search query string.
Once you verify your first row is working right, then it’s just a matter of filling the formula down the rows so you get all your search terms.
Open All URLs
Websites like openallurls.com do just what they sound like. Give them a list of URLs, and they’ll open them in new tabs.
I prefer to open them in batches of 10-15 so I don’t get overwhelmed with tabs or crash my browser.
Just paste them in and open up!
NOTE: I’ve encountered issues with Safari on this site, so I suggest using Chrome or Firefox.
Copy the Discovered URLs
After letting Open All URLs do its thing and all the tabs finish loading, it’s time to verify that you found the correct website. For the job in question, I found that about 95% of the results were dead on.
For the direct hits, I copied the web address to a spreadsheet. The same one containing the search terms and search URLs actually.
For the misses, I re-did the searches manually. Most of these cases represented smaller organizations using a Facebook page as their digital home.
Copying the address and pasting it into the spreadsheet is itself tedious, so I suggest making a quick Keyboard Maestro macro or if you don’t have KM, BetterTouchTool. The accordion below has an example for each.
Armed with the URLs I was easily able to create the HTML code necessary for building the credits page I had promised.
Immediately I was hoisted aloft on their shoulders and showered with praise.
“Impossible!” They said. “Surely this Thomas is some manner of wizard!”
Wheth or not I’m embelishing, my original point stands. Automation expands the scope of things you can do in the amount of time you alotted and consequently you can do some stuff that seems impressive to others.
Other Uses for Batch Searching
Once I learned to do this, I kept finding new uses for it. Hear are three real examples of how I’ve found use for batch searches, utilizing this method of building search URLs and opening them with a web service.
Try and image how you could apply any of these to your own work.
Finding Staff Pages for Individuals in an Organization
At my previous work, we kept internal dossiers for university staff. One of the fields was the staff homepage. I basically did the exact search outlined in this post.
Finding Every Particular Office for Various Statewide Orgs
A colleague needed to find the address for every local office of various state-wide orgs—labor unions mostly—and asked me how I would go about it. Apparently these weren’t in their directories…I built a formula that would make a search URL for “Org Name” + “CA County Name” after copying a list of California counties. We then copied the addresses from the Google results.
Serving on the board of my alumni org, I need to track down lost alums. Armed with a list of names of around 450 people, I made a search URL that searched for people with these names who attended university. I went by cohort and was able to find around 150 new people.
Just like batch file operations, batch searches are massively scaleable and give you the opportunity to do things you might not have thought were on the table. Researchers especially would do well to add this to their toolkit. Think on how many times you’ve done what is essentially the same search over and over? Batch it. Do it 10 at a time instead of 1.
How do you do this in your own practice? Share it!