Automate Gmail Integration Tasks from PowerShell

The CData Cmdlets for Gmail 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 Gmail.

PowerShell Cmdlets or ADO.NET Provider?

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

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

There are two ways to authenticate to Gmail. Before selecting one, first ensure that you have enabled IMAP access in your Gmail account settings. See the "Connecting to Gmail" section under "Getting Started" in the installed documentation for a guide.

The User and Password properties, under the Authentication section, can be set to valid Gmail user credentials.

Alternatively, instead of providing the Password, you can use the OAuth authentication standard. To access Google APIs on behalf on individual users, you can use the embedded credentials or you can register your own OAuth app.

OAuth also enables you to use a service account to connect on behalf of users in a Google Apps domain. To authenticate with a service account, you will need to register an application to obtain the OAuth JWT values.

In addition to the OAuth values, you will need to provide the User. See the "Getting Started" chapter in the help documentation for a guide to using OAuth.

PowerShell

1. Install the module:

   Install-Module GmailCmdlets

2. Connect:

   $gmail = Connect-Gmail -User "$User" -Password "$Password"

3. Search for and retrieve data:

   $from = "test@test.com" $inbox = Select-Gmail -Connection $gmail -Table "Inbox" -Where "From = `'$From`'" $inbox

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

   $inbox = Invoke-Gmail -Connection $gmail -Query 'SELECT * FROM Inbox WHERE From = @From' -Params @{'@From'='test@test.com'}

ADO.NET

1. Load the provider's assembly:

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

2. Connect to Gmail:

   $conn= New-Object System.Data.CData.Gmail.GmailConnection("User=username;Password=password;") $conn.Open()

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

   $sql="SELECT Subject, Size from Inbox" $da= New-Object System.Data.CData.Gmail.GmailDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.subject $_.size }

Update Gmail Data

PowerShell

ADO.NET

Insert Gmail Data

PowerShell

ADO.NET

Delete Gmail Data

PowerShell

ADO.NET