# encoding: ascii
# api: powershell
# title: Get-GoogleSpreadsheets
# description: author: Doug Finke
# version: 0.1
# type: function
# author: halr9000
# license: CC0
# function: Get-GoogleSpreadSheets
# x-poshcode-id: 890
# x-archived: 2017-03-25T01:52:57
# x-published: 2009-02-23T09:56:00
#
# url: http://dougfinke.com/blog/index.php/2009/02/22/powershell-get-googlespreadsheets/
# Use PowerShell to access the content of Google spreadsheets with the Google Data API. The API let’s you request a list of spreadsheets, edit, delete or query the content in the form of Google Data API feeds.
#
#requires -version 2
Function Get-GoogleSpreadSheets {
param(
$userName = $(throw 'Please specify a user name'),
$password = $(throw 'Please specify a password')
)
Add-Type -Path "C:\Program Files\Google\Google Data API SDK\Redist\Google.GData.Client.dll"
Add-Type -Path "C:\Program Files\Google\Google Data API SDK\Redist\Google.GData.Extensions.dll"
Add-Type -Path "C:\Program Files\Google\Google Data API SDK\Redist\Google.GData.Spreadsheets.dll"
$service = New-Object Google.GData.Spreadsheets.SpreadsheetsService("TestGoogleDocs")
$service.setUserCredentials($userName, $password)
$query = New-Object Google.GData.Spreadsheets.SpreadsheetQuery
$feed = $service.Query($query)
$feed.Entries |
foreach {
$_.Title.Text
$_.Links |
? {$_.rel -eq "http://schemas.google.com/spreadsheets/2006#worksheetsfeed"} |
% {
$query = New-Object Google.GData.Spreadsheets.WorksheetQuery($_.Href)
$feed = $service.Query($query)
$feed.Entries |
% {
$_.Title.Text
$_.Links |
? { $_.rel -eq "http://schemas.google.com/spreadsheets/2006#listfeed"} |
% {
$listQuery = New-Object Google.GData.Spreadsheets.ListQuery($_.Href)
$feed = $service.Query($listQuery)
"Worksheet has $($feed.Entries.Count) rows:"
$feed.Entries |
% {
$_.Elements |
% {
Write-Host -NoNewline "$($_.value)`t"
}
Write-Host
}
}
}
}
}
}