Automate SurveyMonkey Integration Tasks from PowerShell

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

PowerShell Cmdlets or ADO.NET Provider?

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

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

SurveyMonkey uses the OAuth 2 authentication standard. See the Getting Started section in the help documentation for a guide.

PowerShell

1. Install the module:

   Install-Module SurveyMonkeyCmdlets

2. Connect:

   $surveymonkey = Connect-SurveyMonkey -OAuthClientId "$OAuthClientId" -OAuthClientSecret "$OAuthClientSecret" -CallbackURL "$CallbackURL"

3. Search for and retrieve data:

   $choicetext = "blue" $mysurvey_responses = Select-SurveyMonkey -Connection $surveymonkey -Table "MySurvey_Responses" -Where "ChoiceText = `'$ChoiceText`'" $mysurvey_responses

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

   $mysurvey_responses = Invoke-SurveyMonkey -Connection $surveymonkey -Query 'SELECT * FROM MySurvey_Responses WHERE ChoiceText = @ChoiceText' -Params @{'@ChoiceText'='blue'}

ADO.NET

1. Load the provider's assembly:

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

2. Connect to SurveyMonkey:

   $conn= New-Object System.Data.CData.SurveyMonkey.SurveyMonkeyConnection("OAuthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;CallbackURL=http://localhost:portNumber;InitiateOAuth=GETANDREFRESH") $conn.Open()

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

   $sql="SELECT RespondentId, ChoiceId from MySurvey_Responses" $da= New-Object System.Data.CData.SurveyMonkey.SurveyMonkeyDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.respondentid $_.choiceid }