Automate Google Directory Integration Tasks from PowerShell

The CData Cmdlets for Google Directory are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Google Directory.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to Google Directory, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete Google Directory data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for Google Directory. To access Google Directory data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Google Directory.

Once you have acquired the necessary connection properties, accessing Google Directory data in PowerShell can be enabled in three steps.

Google uses the OAuth authentication standard. You can authorize the data provider to access Google Spreadsheets as an individual user or with a Google Apps Domain service account. See the Getting Started section of the data provider help documentation for an authentication guide.

PowerShell

1. Install the module:

   Install-Module GoogleDirectoryCmdlets

2. Connect:

   $googledirectory = Connect-GoogleDirectory -OAuthClientId "$OAuthClientId" -OAuthClientSecret "$OAuthClientSecret" -CallbackURL "$CallbackURL"

3. Search for and retrieve data:

   $status = "confirmed" $mytable = Select-GoogleDirectory -Connection $googledirectory -Table "MyTable" -Where "Status = `'$Status`'" $mytable

   You can also use the Invoke-GoogleDirectory cmdlet to execute SQL commands:

   $mytable = Invoke-GoogleDirectory -Connection $googledirectory -Query 'SELECT * FROM MyTable WHERE Status = @Status' -Params @{'@Status'='confirmed'}

ADO.NET

1. Load the provider's assembly:

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

2. Connect to Google Directory:

   $conn= New-Object System.Data.CData.GoogleDirectory.GoogleDirectoryConnection("OAuthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;CallbackURL=http://localhost;InitiateOAuth=GETANDREFRESH") $conn.Open()

3. Instantiate the GoogleDirectoryDataAdapter, execute an SQL query, and output the results:

   $sql="SELECT Id, Description from MyTable" $da= New-Object System.Data.CData.GoogleDirectory.GoogleDirectoryDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.id $_.description }

Update Google Directory Data

PowerShell

ADO.NET

Insert Google Directory Data

PowerShell

ADO.NET

Delete Google Directory Data

PowerShell

ADO.NET