Automate Google Sheets Integration Tasks from PowerShell

The CData ADO.NET Provider for Google Sheets is a standard ADO.NET Provider that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Google Sheets.

ADO.NET Provider

The ADO.NET Provider provides a SQL interface for Google Sheets; this tutorial shows how to use the Provider to create, retrieve, update, and delete Google Sheets data.

Once you have acquired the necessary connection properties, accessing Google Sheets data in PowerShell can be enabled in three 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, 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.

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

$cmd =  New-Object System.Data.CData.GoogleSheets.GoogleSheetsCommand("UPDATE Orders SET ShipCity='Madrid' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.GoogleSheets.GoogleSheetsParameter("@myId","https://spreadsheets.google.com/feeds/list/tv3a8NcMPGTad5D56PRtuVQ/oda/private/full/cokwr")))
$cmd.ExecuteNonQuery()

Insert Google Sheets Data

$cmd =  New-Object System.Data.CData.GoogleSheets.GoogleSheetsCommand("INSERT INTO Orders (ShipCity) VALUES (@myShipCity)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.GoogleSheets.GoogleSheetsParameter("@myShipCity","Madrid")))
$cmd.ExecuteNonQuery()

Delete Google Sheets Data

$cmd =  New-Object System.Data.CData.GoogleSheets.GoogleSheetsCommand("DELETE FROM Orders WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.GoogleSheets.GoogleSheetsParameter("@myId","https://spreadsheets.google.com/feeds/list/tv3a8NcMPGTad5D56PRtuVQ/oda/private/full/cokwr")))
$cmd.ExecuteNonQuery()