Automate Gmail Integration Tasks from PowerShell

Ready to get started?

Download for a free trial:

Download Now

Learn more:

Gmail ADO.NET Provider

The easiest way to integrate powerful Gmail send and receive capabilities with .NET applications. Send & Receive Email, manage Gmail folders & Messages, and more!



Are you looking for a quick and easy way to access Gmail data from PowerShell? We show how to use the Cmdlets for Gmail and the CData ADO.NET Provider for Gmail to connect to Gmail data and synchronize, automate, download, 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.

Cmdlets or ADO.NET?

The cmdlets are not only a PowerShell interface to the Gmail API, 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.

After obtaining the needed connection properties, accessing Gmail data in PowerShell consists of three basic 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

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='test@test.com' WHERE Id = @myId", $conn) $cmd.Parameters.Add(new 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 System.Data.CData.Gmail.GmailParameter("@myFrom","test@test.com")) $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 System.Data.CData.Gmail.GmailParameter("@myId","001d000000YBRseAAH")) $cmd.ExecuteNonQuery()