Tuesday, December 13, 2011

I've got the Power(Shell) - how to connect Google Insights and Excel

It is almost certain that you will find the below crushingly uninteresting. In an earlier post I used a PowerShell script to pull the year on year stats for certain search terms, add the results to a spreadsheet, then draw a line graph to show change over time.

Here is the script. I'm a believer in 'good enough' coding. This was written for a short-term project (it assumes, for instance, that the year will always be 2011) for my own use only. It works, but there are areas in which it could be improved. Feel free to use it and ask questions in the comments, but please don't assume it's my finest work.

$client = new-object System.Net.WebClient
$response = $client.DownloadString("https://www.google.com/accounts/ClientLogin?accountType=GOOGLE&Email=YOUREMAILADDRESS@gmail.com&Passwd=YOURPASSWORD&service=trendspro&source=test-test-v1");
$sid = $response.Split("`n")[0];
$countries= @{}
for($i=0;$i -le 7;$i++)
        $year = 2004+$i
        $url = "http://www.google.com/insights/search/overviewReport?q=YOUR%20SEARCH%20TERM&date=1%2F$year%2012m&cmpt=q&content=1&export=2"
        $doc = $client.DownloadFile($url,"c:\temp\temp$i.csv")
        $csv = Get-Content "c:\Temp\temp$i.csv"
        foreach($line in $csv)
                if($isData -and !$line.StartsWith("Region`t"))
                                {$countries.Add($country, @{$i=$j})}
$Excel = New-Object -Com Excel.Application
$Excel.visible = $True
$Workbook = $Excel.Workbooks.Add()
for($i=0;$i -le 7;$i++)
foreach($country in $countries.Keys)
        for($i=0;$i -le 7;$i++)

No comments: