Automate Google Sheets Integration Tasks from PowerShell

Ready to get started?

Download for a free trial:

Download Now

Learn more:

Google Sheets ADO.NET Provider

Easily connect .NET applications with real-time data from spreadsheets stored in Google Docs. Use Google Sheets to manage the data that powers your applications.



Are you looking for a quick and easy way to access Google Sheets data from PowerShell? We show how to use the Cmdlets for Google Sheets and the CData ADO.NET Provider for Google Sheets to connect to Google Sheets data and synchronize, automate, download, and more.

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

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 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 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 System.Data.CData.GoogleSheets.GoogleSheetsParameter("@myId","https://spreadsheets.google.com/feeds/list/tv3a8NcMPGTad5D56PRtuVQ/oda/private/full/cokwr")) $cmd.ExecuteNonQuery()