Do a Google search from an Excel formula and put the result in a cell

Issue

Before

After (Addresses Added)

I have a spreadsheet with names of venues and I would like to add the addresses of those venues in the next cell using a formula in Google Sheets. I would like to do this using a formula to do a Google Search for the results because I want to scrape many other values as well and quickly change the sheet on my phone as my needs change. Thus I would like to do this without using any outside coding language and not even google apps scripts if I can avoid it.

Google Search for data

I found some information on how to do something similar with an excel formula here:

Using Google Sheets as a basic web scraper

That site shows how to do a google search for an author name of an article with an excel formula with a format like this:

=INDEX(IMPORTXML(A1,"//span[@itemprop='name']"),1)

Can you help me modify this to return the result of a google search into an excel cell?

Solution

The index function could probably be eliminated by someone with more knowledge of XPath, but nonetheless the below seems to work reliably. The following would be pasted into B2 of your sample sheet:

=index(IMPORTXML("http://www.google.com/search?&q="&"Address+of+"&A2&"+in+british+columbia"&"&oq="&"Address+of+"&A2&"+in+british+columbia","//span"), 15, 1)

This is based upon the assumption that all of the venues are located in British Colombia. You could parameterize the Google search section to add a wider array of possible search locations.

Answered By - J. Spain