Using the Qlik Sense REST Connector (with SurveyMonkey)

    Over the last couple days I've been using the Qlik Sense REST connector to get survey data from SurveyMonkey. As far as I can tell, our organization does not have access to the dedicated SurveyMonkey connector, so REST needed to work. I found the process tricky, so I'm documenting the steps I took to get this working. Some of this material is specific for SurveyMonkey, but I hope that there may be some clues that are relevant for other connections.

     

    Authentication

    Whatever service you are using, you will need some kind of authentication token for your final connection. For SurveyMonkey this involves "creating an app". I'm not really making a SurveyMonkey app (whatever that is), but this process provides access to a token. In my case, because I have a paid subscription to SurveyMonkey, I can create a private app and get an authentication token immediately. If you have a free plan, you'll need to go through a more cumbersome process to get the token. Because reading is hard, I inadvertently went through the entire process for a public app - only to realize that it gave me back the SAME authentication token that I already had access to ! So, the good news is that I've seen this process work.

     

    API Docs | SurveyMonkey API Developer Portal (Authentication)

     

    Talking to SurveyMonkey: The Tools (cURL)

    To make this work you'll need to make GET and POST requests to the SurveyMonkey API. There are a few tools for this, but if you have Windows, I recommend using the command line cURL tool. Download here. https://curl.haxx.se/download.html

     

    You can unpack the downloaded zip file in your Program Files (or wherever) and then look for the executable file. Run a command prompt from that directory (you can also put it in your path if you'll use it multiple times). You're now ready to talk to SurveyMonkey.

     

    Getting a Token

    First you'll need to create your app. Go to https://developer.surveymonkey.com/apps/ and "Add a New App". If you can make it Private, do it because you'll get your authentication token right away, if not Public works. When you've created the app, go to settings and you'll find a "Client ID", a "Secret", a "OAuth Redirect URL" and, if private, an "Access Token". You'll need this information. You should also set the Scopes, in my case I needed View Response Details (and some others), so I set this to "Optional".

     

    Now, following the API documentation from SurveyMonkey, you'll do the following:

     

    1. Follow the link to get "short-lived" code.

    Here you'll enter a link in the browser. Replace the colored text with the appropriate values found on the App settings:

    https://api.surveymonkey.com/oauth/authorize?response_type=code&redirect_uri=YOUR_REDIRECT_URI&client_id=YOUR_CLIENT_ID

     

    When the browser redirects you it will update the URL. In the URL you should see ...?code=AUTH_CODE

    The short-lived code (which is a long alphanumeric string) will only be good for a few minutes, so copy it and get cracking... (you can always do this again to get another code, so no big deal).

     

    2. Get Access Token

    Now it's time to use cURL. Open up the command prompt and make sure you have access to the curl executable. Enter the following with all the information you've gathered (you may need to enter it line by line in the console). I suggest creating the full command in a text document, that way if you need to go through this process again (with a new AUTH_CODE), you'll be ready to go.

    curl -i -X POST https://api.surveymonkey.com/oauth/token -d \
      "client_secret=YOUR_CLIENT_SECRET \
      &code=AUTH_CODE \

      &redirect_uri=YOUR_REDIRECT_URI \

      &client_id=YOUR_CLIENT_ID \

      &grant_type=authorization_code"


    Assuming you did this in time (your AUTH_CODE didn't expire), in the returned message from this post you'll get a response with a "YOUR_ACCESS_TOKEN" and an "access_url". I didn't actually use the access_url, but you might as well keep track of it.


    Creating a Qlik Rest Connection

    Now that you have your YOUR_ACCESS_TOKEN you can use it to establish the REST connection. Assuming that you want to get all of the responses for one or more surveys you'll need to find the ids for your surveys, set up a connection, and get the data.


    1. Get Survey IDs.

    The SurveyMonkey API allows you to access the ids of the survey with a GET call. You could do this in Qlik, which is nice because you could save all your survey ids in a table. A more straightforward way is to make the request in cURL and then manually create a record of these ids. This is what I did with curl:


    curl -i -X GET -H "Authorization:bearer YOUR_ACCESS_TOKEN" -H "Content-Type": "application/json" https://api.surveymonkey.com/v3/surveys


    You'll get back a crazy json text string. Copy it and then enter it at this website to make it look pretty: https://jsonlint.com/


    Now you should see a "data" array that has a list of survey names and ids. Copy down these IDs. Personally, I made a table with the name of the surveys I needed and their associated IDs. This can come in handy later for the app.


    2. Make the REST connection

    In the "data load editor" of your Qlik App, on the right hand side click, "Create new connection" and choose "Qlik REST Connector". 


    Let's first assume that you want to create a connection to a single survey to get its data. In this case use one of the IDs you collected in the the previous step. The URL you use should be the same as step one, but with the survey id and "response/bulk" added, for example, "https://api.surveymonkey.com/v3/surveys/SURVEY_ID/responses/bulk"


    For the other parameters, here's what I used (do I understand all of this? no):

    Method: GET

    Auto detect response type: check

    Key generation strategy: Sequence ID

    Authentication Schema: Anonymous

    Skip server certificate validation: check

    Use certificate: No


    The Query headers tell the SurveyMonkey API service that you have valid access. So, you'll need to provide the access token (you don't need to do "Content-Type").

    Name: Authorization    

    Value: bearer YOUR_ACCESS_TOKEN


    The Query parameters provide additional information to the API about what information you want. In our case SurveyMonkey defaults to providing 50 records per "page" of data. You can increase this by changing the "per_page" value to 100 (the max I believe).

    Name: per_page

    Value: 100


    I also checked, "Add missing query parameters to final request", because (I believe), when we use pagination this setting makes sure that the per_page setting goes to every subsequent page call. May be unnecessary.


    Finally, for Pagination type, I used "Next URL". This tells Qlik how to make a request for more data because SurveyMonkey limits how much it will send at one time. (Note, there is a limit on API calls, so if you have crazy big data, this could be a problem).

    "Next url' path: links/next


    The SurveyMonkey documentation tells you to get this next page address at "links.next", but that's not how Qlik does it, you need the forward-slash. (This took forever to figure out!)


    3. Create the Script

    Now that you have the connection, you can click "Select Data" icon to determine what data you want. The response type should be set as JSON. Because JSON is a nested structure you'll need to un-collapse the root and other nodes there within to find the data you are looking for. The first time you may want to try gathering everything, and then look at the data model to see what you really need.


    Click "Insert script" to put the code into the scripting area. You can run it now and see what you get.


    4. Multiple Surveys

    What if you don't want to connect to the same survey every time, but rather want to adapt to the ids that you collected in the first step of this section? In step 2, you hard coded the ID into the URL, but there is a workaround.


    Edit your REST connector. Instead of using a URL with a hard-coded ID, just use "https://api.surveymonkey.com/v3/surveys"


    In fact, I don't think it matters what we put here because we will override this value.


    Under the SQL code you received upon inserting script, you'll add a "WITH CONNECTION ()" command. It will look something like this:

    LIB CONNECT TO 'SurveyMonkey REST connector'

    RestConnectorMasterTable:

    SQL SELECT

    "per_page",

    "total",

    "page",

    "__KEY_root"

    FROM JSON (wrap on) "root" PK "__KEY_root"

    WITH CONNECTION(

    Url "https://api.surveymonkey.com/v3/surveys/SURVEY_ID/responses/bulk"

             );

     

    The Url in WITH CONNECTION tells Qlik to look at this URL instead of the one defined in the REST connector. What's nice about putting it here is that the URL text string can be a variable. This way you can dynamically load a SURVEY_ID from a table. So for example, I created a variable called vURL that dollar-expands a variable storing the ID of the survey I'm currently interested in.

    let vURL = 'https://api.surveymonkey.com/v3/surveys/$(vID)/responses/bulk';

    So in WITH CONNECTION I used:

    Url "$(vURL)"


    If you have these survey IDs stored in a table you can loop through the table (for.. next), Peek at the id in the current row, set it to vID and then run the REST connector with each of the surveys.


    Summary


    At the end of the day, this process was cumbersome but I learned a lot about connecting to APIs. Hopefully, the SurveyMonkey dedicated connector will be available for others, but this approach seems to work. Perhaps much of this is applicable to other connectors.


    Please feel free to give me feedback and comments. There are a few steps of this process that I don't fully understand, so if you have a different approach or can provide more detail, I'd appreciate it.