Automate Google Sheets Integration Tasks from PowerShell

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

Cmdlets or ADO.NET?

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

After obtaining the needed connection properties, accessing Google Sheets data in PowerShell consists of three basic steps.

You can connect to a spreadsheet by providing authentication to Google and then setting the Spreadsheet connection property to the name or feed link of the spreadsheet. If you want to view a list of information about the spreadsheets in your Google Drive, execute a query to the Spreadsheets view after you authenticate.

ClientLogin (username/password authentication) has been officially deprecated since April 20, 2012 and is now no longer available. Instead, use the OAuth 2.0 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.

See the Getting Started chapter in the help documentation to connect to Google Sheets from different types of accounts: Google accounts, Google Apps accounts, and accounts using two-step verification.

PowerShell

1. Install the module:

   Install-Module GoogleSheetsCmdlets

2. Connect:

   $googlesheets = Connect-GSheets -Spreadsheet "$Spreadsheet"

3. Search for and retrieve data:

   $shipcity = "Madrid" $orders = Select-GSheets -Connection $googlesheets -Table "Orders" -Where "ShipCity = `'$ShipCity`'" $orders

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

   $orders = Invoke-GSheets -Connection $googlesheets -Query 'SELECT * FROM Orders WHERE ShipCity = @ShipCity' -Params @{'@ShipCity'='Madrid'}

ADO.NET

1. Load the provider's assembly:

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

2. Connect to Google Sheets:

   $conn= New-Object System.Data.CData.GoogleSheets.GoogleSheetsConnection("Spreadsheet=MySheet;InitiateOAuth=GETANDREFRESH") $conn.Open()

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

   $sql="SELECT Shipcountry, OrderPrice from Orders" $da= New-Object System.Data.CData.GoogleSheets.GoogleSheetsDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.shipcountry $_.orderprice }

Update Google Sheets Data

PowerShell

ADO.NET

Insert Google Sheets Data

PowerShell

ADO.NET

Delete Google Sheets Data

PowerShell

ADO.NET