Create HeatMap with Google Spreadsheets

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


In order to use GoogleShare, you must have a list of countries, which 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 query "habrahabr". To do this, we need to somehow get the number of pages. The importXml function, which has two input parameters, will help us with this: URL and XPath (the query language for XML documents)

That is, in order to show how many pages are available for the habrahabr request, we will write the following into 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 at the request of Russia, Britain, etc. We drive the following code into the “PageCount for country name” column, which takes information from the 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 constantly write q = habrahabr and be more universal, we place our request in some cell. Then go to the Formulas tab, and assign a variable to our cell (Formulas -> Range Names -> Define New).

We rewrite our query by 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]")
Stretch the contents of this formula to the entire 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 in it:
=E2 / D2 * 100
Which calculates the ratio of the total number of pages for the country and the number of pages for the request, 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 on the panel, then in the dialog that opens, select Maps -> HeatMap. The card 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