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:
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!
កាស៊ីណូអនឡាញ
Post a Comment