Create HeatMap with Google Spreadsheets

The new gadget in SpreadSheets allows you to create heatmap, that is, a map showing how many users in different countries, for example, are searching for certain words in Google. GoogleShare evaluates how two words correspond to each other. For example, you took the number of indexed pages for the query "Beatles" and "Beatles John Lennon", found their relationship and got GoogleShare.


In order to use GoogleShare, you must have a list of countries that should look something like this.

Country name	Country code	Googleshare	PageCount for country name	PageCount for country name + keyword
China	CN
India	IN
USA	US
etc. ...	etc. ...

')

We collect information

Let's try to collect information about the request "habrahabr". For this we need to somehow get the number of pages. With this we will be helped by the importXml function, which has two input parameters: URL and XPath (query language for XML documents)

That is, in order to show how many pages are available for the query habrahabr, we write the following in the cell:
=importXml("http://www.google.com/search?hl=en&q=habrahabr", "//td/font/b[3]")

Add mobility

We need to collect information about how many indexed pages are available for Russia, Britain, etc. We drive in the “PageCount for country name” column the following code, which takes information from a cell and adds it to the request:
=importXml("http://www.google.com/search?hl=en&q=" & A2, "//td/font/b[3]")

More mobility - better results

In order not to write q = habrahabr all the time and to be more versatile, we place our query in a cell. Then go to the Formulas tab, and assign a variable to our cell (Formulas -> Range Names -> Define New).

We will rewrite our query, adding a country to it to determine how many pages Google has indexed for each country.

=importXml("http://www.google.com/search?hl=en&q=" & A2 & "+" & keyword, "//td/font/b[3]")
We stretch the contents of this formula to the whole column to get information for other countries.

Add a map

To add a map you need to find GoogleShare. Select the first cell in the corresponding column and insert the formula into it:
=E2 / D2 * 100
Which will calculate the ratio of the total number of pages for a country and the number of pages for a query, for example, habrahabr + Russia. We apply the formula for each country from the list and get the index we need.

Now everything is ready to add a gadget. Select the cells containing the short country code and the GoogleShare column. Select Insert -> Gadget in the panel, then in the opened dialog select Maps -> HeatMap. The map is ready! :) Now we sit and think, why do we need all this?

PS: This text is based on the article Creating a Googleshare Map With Google Spreadsheets