Query Google Calendars, Contacts, and Documents from PowerShell

Are you looking for a quick and easy way to access Google Drive from PowerShell? The CData Google Apps Data Provider provides an easy-to-use ADO.NET interface that you can take advantage of from PowerShell scripts. Simply use the included .NET objects in your PowerShell scripts to connect to Google Apps and synchronize, automate, download, and more!

Connect to Google Apps

To connect to any CData ADO.NET Provider from Powershell, first load the provider assembly, located in the lib subfolder in the installation directory:

[Reflection.Assembly]::LoadFile("C:\Program Files\\CData\\CData ADO.NET Provider for Google Apps\lib\System.Data.CData.GoogleApps.dll")

In PowerShell, you can set connection properties with the following code:

$constr = "OAuthClientId=username;OAuthClientSecret=password;InitiateOAuth=true;"
$conn= New-Object System.Data.CData.GoogleApps.GoogleAppsConnection($constr)
$conn.Open()

Instead of a username and password, Google Apps use the OAuth authentication standard. See the "Getting Started" guide in the help documentation for a guide to obtain the OAuth authentication values.

Create, Retrieve, Update, and Delete from PowerShell

The CData Data Providers enable you to execute standard SQL queries from PowerShell. You can use the code below to execute SQL commands with any of the CData ADO.NET Providers.

You can use the GoogleAppsDataAdapter to execute any CRUD (create, read, update, and delete) query to Google Apps. The following example uses a SELECT query to list information about documents and folders in Google Drive. The Fill method of the GoogleAppsDataAdapter is used to populate a DataTable with the results.

$sql="SELECT Title, Format, Extension, Viewed, Modified from Documents"

$da= New-Object System.Data.CData.GoogleApps.GoogleAppsDataAdapter($sql, $conn)
$dt= New-Object System.Data.DataTable
$da.Fill($dt)

$dt.Rows | foreach {
  Write-Host $_.updated $_.title
}

Other Google Apps Actions

You can use the Google Apps Data Provider to easily upload and download documents, get directions from Google Maps, and more. The following code downloads a document in Google Drive:

$cmd= New-Object System.Data.CData.Google.GoogleCommand("DownloadDocument", $conn)
$cmd.CommandType= [System.Data.CommandType]'StoredProcedure'
$cmd.Parameters.Add( (New-Object System.Data.CData.Google.GoogleParameter("@DocumentType", "TXT")) ) 
$cmd.Parameters.Add( (New-Object System.Data.CData.Google.GoogleParameter("@Title", "myfile")) ) 
$cmd.Parameters.Add( (New-Object System.Data.CData.Google.GoogleParameter("@LocalFile", "d:\myfile.txt")) ) 
$reader = $cmd.ExecuteReader()

Stored procedures surface the capabilities of the underlying Google API that cannot be represented as SELECT, INSERT, UDPATE and DELETE statements: for example, sending IMs and searching. See the "Using ADO.NET" chapter in the help documentation for example queries and for more information on stored procedures. See the "Data Model" chapter for the available stored procedures and their inputs.