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];
$client.Headers.add("Cookie",$sid)
$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"
        $j=1
        foreach($line in $csv)
        {
                if($line.startsWith("Region`t"))
                        {$isData=$true}
                elseif($line.equals(""))
                        {$isData=$false}
                if($isData -and !$line.StartsWith("Region`t"))
                {
                        $country=$line.Split("`t")[0]
                        if($countries.contains($country))
                                {$countries[$country].set_item($i,$j)}
                        else
                                {$countries.Add($country, @{$i=$j})}
                        $j++
                }
        }
}
$Excel = New-Object -Com Excel.Application
$Excel.visible = $True
$Workbook = $Excel.Workbooks.Add()
$Sheet=$Workbook.Worksheets.Item("Sheet1")
for($i=0;$i -le 7;$i++)
{$Sheet.Cells.Item(1,$i+2)=2004+$i}
$Sheet.UsedRange.Font.Bold=$true
$xlRow=2
foreach($country in $countries.Keys)
{
        $Sheet.Cells.Item($xlRow,1)=$country
        for($i=0;$i -le 7;$i++)
                {$Sheet.Cells.Item($xlRow,$i+2)=$countries[$country][$i]}
        $xlRow++
}
$range=$Sheet.range("i1")
$Sheet.UsedRange.Sort($range,1,$null,$null,1,$null,1,1)
$chart=$Workbook.Charts.Add()
$chart.SetSourceData($Sheet.UsedRange)
$chart.Type=4
$chart.PlotBy=1
$chart.Axes(2).ReversePlotOrder=$true

1 comment:

Unknown said...

Thank you for all your efforts in this It is very interesting Blog ...
I believe there are many people who feel like I read this article!
I hope you continue to have articles like this to share with everyone!
កាស៊ីណូអនឡាញ