Automate Greenhouse Integration Tasks from PowerShell

The CData Cmdlets for Greenhouse are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time access to Greenhouse.

PowerShell Cmdlets or ADO.NET Provider?

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

Once you have acquired the necessary connection properties, accessing Greenhouse data in PowerShell can be enabled in three steps.

You need an API key to connect to Greenhouse. To create an API key, follow the steps below:

1. Click the Configure icon in the navigation bar and locate Dev Center on the left.
2. Select API Credential Management.
3. Click Create New API Key.
• Set "API Type" to Harvest.
• Set "Partner" to custom.
• Optionally, provide a description.
4. Proceed to Manage permissions and select the appropriate permissions based on the resources you want to access through the driver.
5. Copy the created key and set APIKey to that value.

PowerShell

1. Install the module:

   Install-Module GreenhouseCmdlets

2. Connect:

   $greenhouse = Connect-Greenhouse -APIKey "$APIKey"
   

3. Search for and retrieve data:

   $status = "Active"
   $applications = Select-Greenhouse -Connection $greenhouse -Table "Applications" -Where "Status = `'$Status`'"
   $applications
   

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

   $applications = Invoke-Greenhouse -Connection $greenhouse -Query 'SELECT * FROM Applications WHERE Status = @Status' -Params @{'@Status'='Active'}
   

ADO.NET

1. Load the provider's assembly:

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

2. Connect to Greenhouse:

    
   $conn= New-Object System.Data.CData.Greenhouse.GreenhouseConnection("APIKey=YourAPIKey;")
   $conn.Open()
   

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

   $sql="SELECT Id, CandidateId from Applications"
   
   $da= New-Object System.Data.CData.Greenhouse.GreenhouseDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.id $_.candidateid
   }