Using the Qlik Sense REST Connector (with SurveyMonkey)

[jonvitale]

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.

[jonvitale]

First are you sure you actually need the information from "details", is everything not contained in "bulk"?

Assuming you do need both something I notice is that fields with the same name at different levels are automatically renamed. For example, in the code below from your "bulk" pull, many of the "id"-s are renamed to avoid a conflict. But, if you take a look at the "Details" script they are also renamed, but necessarily to the same name. For example, it looks like in "Bulk" the question id is just "id", but in the Details table, it is "id_1" (I think, it's a little hard to follow). 

So, what I'd suggest is going through your script and instead of letting Qlik rename your variables, you rename them in a way that makes sense, like page_id, question_id, answer_id, etc. That may help make it mesh together better

SQL SELECT 
	"per_page",
	"total",
	"__KEY_root",
	(SELECT 
		"total_time",
		"href",
		"ip_address",
		"id" AS "id_u1",
		"date_modified",
		...
		"__KEY_data",
		"__FK_data",
		(SELECT 
			"__FK_custom_variables"
		FROM "custom_variables" FK "__FK_custom_variables"),
		(SELECT 
			"__FK_logic_path"
		FROM "logic_path" FK "__FK_logic_path"),
		(SELECT 
			"id" AS "id_u0",
			"__KEY_pages",
			"__FK_pages",
			(SELECT 
				"id",
				"__KEY_questions",
				"__FK_questions",
				(SELECT 
					"choice_id",
					"row_id",
					"text",
					"__FK_answers"
				FROM "answers" FK "__FK_answers")
			FROM "questions" PK "__KEY_questions" FK "__FK_questions")
		FROM "pages" PK "__KEY_pages" FK "__FK_pages"),

[brian_magurn]

Awesome post, thank you so much!!! 

This gave me what I needed to get a REST connection working. 

The key for  me was where to put the authentication value in the REST connection settings. 

Although mine turned out to be a little different, as my authentication header was actually X-Samanage-Authorization
my curl command was as follows: curl -H "X-Samanage-Authorization: Bearer YOUR_ACCESS_TOKEN" -
So I just changed the Name field to "X-Samanage-Authorization" and bingo!

[Shiva123]

hello ,

                All how was the URL  mentioned when we have multiple surveys in the rest api connection 

https://api.surveymonkey.net/v3/surveys/details  is this correct ? 

[JonathanVitale]

@Shiva123 ,

Can you please elaborate on your question more? You can use https://api.surveymonkey.com/v3/surveys to get the ids for your surveys. But I believe that if you want any actual responses from your survey you'll have to use those survey ids to get the data, like https://api.surveymonkey.com/v3/surveys/SURVEY_ID/responses/bulk

where you replace SURVEY_ID with whatever survey you are trying to access. For multiple surveys you can do this by looping through your survey ids.

[Shiva123]

Hi,

             Jonathan Thank you for your reply . I agree with what ever you have mentioned , but in my connection string ( attached below) I have hardcoded the survey id to bring in the particular responses .Is there any to bring all the multiple survey responses in the connection string  and then do a loop in the script. My question was how should be the connection string when we have multiple surveys .

current connection string

[Jonathan_Alm]

@jonvitale 

Hi,

I have followed your guide but I am suspecting that maybe the pagination has changes since I am only able to get the first 100 records and nothing else. 
There should be 364 records since that is what is displayed when I run it with Postman:

These are my settings for the rest connector:

I have tried with different settings but none will make the fetching go past the first 100 records.

Have you seen any changes lately or have I missed any crucial setting?

Kind regards, Jonathan

[jonvitale]

@Jonathan_Alm 

I'm not sure I can help you, as I haven't really messed with this for a couple years.

But, can you also paste the code that actually goes into the load script? I think that you may need to make some manual edits to that.

jv

[Jonathan_Alm]

@jonvi

Hi,

I found the problem: It was the "Preload symbols count" that did not read the entire JSON format from source, and therefore the links wasn't in the script that was generated. I have seen this before when loading big structures and it is tough to identify and the setting is not so obvious in my opinion. 

Once I changed that to "unlimited" all the tables was loaded and the pagination worked. So your guide works! 😃