Discover how a bimodal integration strategy can address the major data management challenges facing your organization today.
Get the Report →Automate Gmail Integration Tasks from PowerShell
Are you in search of a quick and easy way to access Gmail data from PowerShell? This article demonstrates how to utilize the Gmail Cmdlets for tasks like connecting to Gmail data, automating operations, downloading data, and more.
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
-
Install the module:
Install-Module GmailCmdlets
-
Connect:
$gmail = Connect-Gmail -User "$User" -Password "$Password"
-
Search for and retrieve data:
$from = "[email protected]" $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'='[email protected]'}
ADO.NET
-
Load the provider's assembly:
[Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Gmail\lib\System.Data.CData.Gmail.dll")
-
Connect to Gmail:
$conn= New-Object System.Data.CData.Gmail.GmailConnection("User=username;Password=password;") $conn.Open()
-
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
Update-Gmail -Connection $Gmail -Columns @('Subject','Size') -Values @('MySubject', 'MySize') -Table Inbox -Id "MyId"
ADO.NET
$cmd = New-Object System.Data.CData.Gmail.GmailCommand("UPDATE Inbox SET From='[email protected]' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Gmail.GmailParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()
Insert Gmail Data
PowerShell
Add-Gmail -Connection $Gmail -Table Inbox -Columns @("Subject", "Size") -Values @("MySubject", "MySize")
ADO.NET
$cmd = New-Object System.Data.CData.Gmail.GmailCommand("INSERT INTO Inbox (From) VALUES (@myFrom)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Gmail.GmailParameter("@myFrom","[email protected]")))
$cmd.ExecuteNonQuery()
Delete Gmail Data
PowerShell
Remove-Gmail -Connection $Gmail -Table "Inbox" -Id "MyId"
ADO.NET
$cmd = New-Object System.Data.CData.Gmail.GmailCommand("DELETE FROM Inbox WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Gmail.GmailParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()
CodeProject