Survey Monkey API Query Parameters not working on Excel VBA

Question

I have a wrapper function that calls Survey Monkey methods on Excel VBA:

Function SM_Method(https As String, method As String, query As Variant) As String
    Dim objSM As Object
    Set objSM = CreateObject("MSXML2.XMLHTTP.6.0")

    With objSM
        .Open https, charSMAPI & method & "?api_key=" & charAPIKey
        .setRequestHeader "Authorization", "Bearer " & charToken
        .setRequestHeader "Content-Type", "application/json"
        .Send query
        SM_Method = .responseText
    End With
End Function

I call this function as below which specifies the API query to print out certain fields:

Dim Request As Variant
Request = "{""fields"":[""per_page""]" & "}"
MsgBox (SM_Method("GET", "/surveys", Request))

But the above message box also prints out other fields such as "total", "data", "page", "links" etc...

I would love to get the query parameters working and was wondering what was wrong with my code above. Your help is greatly appreciated!

General Kandalaft · Accepted Answer · 2016-10-18T17:51:13.233

1

Is the request you're trying to do:

/v3/surveys?api_key=<key>&fields=per_page

And expecting the fields returned to only be per_page (no data, or anything else)?

I don't think that'll work, I think the fields url parameter only works for a specific resource, not a resource list.

So if you wanted to filter out a specific survey, this would work:

/v3/surveys/<survey_id>?api_key=<key>&fields=title

But not for a resource list, it'll always return the total amount of items available, which page you're currently on, how many items per page, the links for pagination, and all the resource items under data.

EDIT: Explaining how to get survey responses

The docs for fetching survey responses are here. What you'd want to do based on your comment is:

GET /v3/surveys/<id>/responses?api_key=<key>&email=<email>&start_created_at=<begin_range>&end_created_at=<end_range>

This will get you all the responses (by ID) for a respondent with the provided email, and within the date range specified by and .

You can then fetch all the details of that response (every answer for every page) here:

GET /v3/surveys/<id>/responses/<response_id>?api_key=<key>

Given that you are using an email collector (that's how you have their email) and there should really only be one response ID returned in the list for that survey, unless they answered the survey more than once. If that's the case then you can do

GET /v3/surveys/<id>/responses/bulk?api_key=<key>&email=<email>&start_created_at=<begin_range>&end_created_at=<end_range>

Only, which with one request you can get all the responses including the answers for that given email in one request.

edited Oct 18 '16 at 17:51

answered Oct 18 '16 at 16:44

General Kandalaft

2,215
2
18
25

Thanks for the response. This [link](http://stackoverflow.com/questions/29366206/accessing-surveymonkey-api-from-vba) **bold** somehow suggests – Neil Cho Oct 18 '16 at 17:35
Ah that link is for V2 of the API, which works a bit differently. In that case, _only_ the fields specified in `fields` paramater show up. In V3 of the API, `fields` filters out everything but the ones specified, if you don't include it you get everything. You don't need to use that URL parameter in V3, I would just ignore it. – General Kandalaft Oct 18 '16 at 17:38
Sorry I meant to say that I tried doing something like: Dim vRequestBody As String and assign vRequestBody = "{""survey_id:""86003167"",""fields"":[""title""]" & "}" and pass this input as part of the send method in the VBA function defined above. Did not have much luck in subsetting the title only. Seems like this [link](http://stackoverflow.com/questions/29366206/accessing-surveymonkey-api-from-vba) suggests that this would be the right way to approach but doesn't seem to work well with me... Thanks a lot for you help again! – Neil Cho Oct 18 '16 at 17:39
I think what I would like to eventually achieve is to retrieve survey responses for a specific user(potentially identified by email) and/or for a specific time period. Also, if the responses are across multiple pages, I'd like to access other pages a well by querying with the API. It's kindda difficult to get that query condition to get working haha – Neil Cho Oct 18 '16 at 17:43
@NeilCho I edited the answer, hopefully that helps you out a bit with what you're trying to do – General Kandalaft Oct 18 '16 at 17:51
1

Ah! This works great. It seems like all the queries can be done on the http statements. Thanks so much! – Neil Cho Oct 18 '16 at 18:52

score 0 · Answer 2 · answered Oct 19 '16 at 07:06

I have code like vRequestBody = "{""survey_id"":" & Quoted(sSurveyID) _ & ", ""fields"":[""collector_id"", ""url"", ""open"", ""type"", ""name"", ""date_created"", ""date_modified""]" _ & "}"

or

vRequestBody = "{"_ & JKeyValue("fields", Array("title", "date_created", "date_modified", "num_responses", _ "language_id", "question_count", "preview_url", "analysis_url")) & "}"

Where JKeyValue uses the json class https://code.google.com/archive/p/vba-json/

Function Quoted(var As Variant) As String Quoted = Chr(34) & Replace(var, Chr(34), "''") & Chr(34) ' "str" with embedded double quotes replaced by pairs of single quotes End Function

Function JKeyValue(sKey As String, vValues As Variant) As String Dim jLib As New ClsJSONLib JKeyValue = jLib.toString(sKey) & ":" & jLib.toString(vValues) Set jLib = Nothing End Function

Survey Monkey API Query Parameters not working on Excel VBA

2 Answers2