Find meanings of a bunch of words at once with Google Spreadsheet

A relative recently asked me if there was a quick way to find the meanings of a list of words all at once. I use & recommend WordWeb to look up difficult words but it takes one word/phrase a time.

I knew about the nifty GoogleLookup function in Google Spreadsheet that fetches "straightforward facts about specific things", but it does not fetch definitions from Google Dictionary as I had imagined. So, I turned to the ImportXML function to scrape the meaning of each word from Google Dictionary.


ImportXML() takes two input parameters URL & query. URL is the URL of the XML or HTML file from which information will be fetched while query represents the XPath query to run on the data given at the URL.


If you were manually looking for the meaning of pipsqueak on Google Dictionary, this is how the URL in your browser address-bar would look when you submit the request -
http://www.google.com/dictionary?q=pipsqueak&hl=en&langpair=en|en&spell=1&oi=spell


You'll notice that the word we submitted goes into a querystring value (q=pipsqueak). To automatically fetch definitions of multiple words at once, I placed the list of words in column A with the first one going into cell A2. In cell B2, I typed this formula -

=ImportXML("http://www.google.com/dictionary?aq=f&langpair=en|en&q="&A2&"&hl=en","//ul[@id='pr-root']")

If you look closely at that formula, you'll see that I inserted the cell number (A2) into the first argument of the function. Now, as we are interested in only the portion of the Google Dictionary page that shows the meaning of the word, we will grab only that content using a XPath query. The definition of the word is rendered in the HTML source code of the Google Dictionary page within an ordered list which has the value 'pr-root' for its ID attribute.

(click on image to enlarge)

After you finish typing the formula, press the Enter key & within a few seconds, the meaning of the word will get populated in the cell B2.

While the focus is on the cell B2, you can copy the formula to the rest of cells for which you want the definitions in column B by dragging at the edge of tiny blue box that appears at the bottom right corner of cell B2. This process will dynamically change the column A cell numbers in the formula & bingo......the meanings of the remaining words in column A will get populated in the column B.

(click on image to enlarge)