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.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to Google Sheets, 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 Spreadsheets. To access Google Sheets data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Google Spreadsheets.

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.

PowerShell

1. Install the module:

   Install-Module GoogleSheetsCmdlets

2. Connect:

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

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 Spreadsheets\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

Update-GSheets -Connection $GoogleSheets -Columns @('Shipcountry','OrderPrice') -Values @('MyShipcountry', 'MyOrderPrice') -Table Orders -Id "MyId"

ADO.NET

$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

PowerShell

Add-GSheets -Connection $GoogleSheets -Table Orders -Columns @("Shipcountry", "OrderPrice") -Values @("MyShipcountry", "MyOrderPrice") 

ADO.NET

$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

PowerShell

Remove-GSheets -Connection $GoogleSheets -Table "Orders" -Id "MyId"

ADO.NET

$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()