Using the Qlik Sense REST Connector (with SurveyMonkey)

jonvitale · Feb 14, 2018 10:50:28 AM

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.

Darren_Ball · ‎2018-02-15

Hi,

Thanks for taking the time to write this excellent post, which I'm sure will help people regardless of the API that they need to connect to using the REST Connector.

Just so you are aware, there is a dedicated SurveyMonkey connector available in the Qlik Web Connectors suite for those that aren't comfortable taking the hands on approach.

Thanks,
Darren.

jonvitale · ‎2018-02-15

Darren,

According to the following page, "The built-in web connectors are only available on Qlik Sense Cloud with a Cloud Business subscription."

https://help.qlik.com/en-US/connectors/Subsystems/Integrated_Web_Connectors_help/Content/Introductio...

Since, I am using Qlik Sense Server, not Cloud, this does not apply in my case. Are there plans to expand the service to all Qlik users?

Jonathan

Darren_Ball · ‎2018-02-15

Hi,

To be fair the SurveyMonkey is a beta connector in the Qlik Web Connectors (which is for QlikView and Qlik Sense users like yourself), so isn't listed on it's official help system and not obvious:

https://help.qlik.com/en-US/connectors/Subsystems/Web_Connectors_help/Content/Data-Source-Connectors...

However, on the QWC's community page we do list the latest beta connectors:

Beta Connectors

That page also explains how to get access to the QWC beta connectors and how they expire.

Appreciate that this isn't clear, but they are there to be used.

Here's a blog post about the most recent release:

Qlik Web Connectors November 2017 Release (v2.28.2) Available

Hope this helps to clarify the location of the SurveyMonkey connector

Thanks,
Darren

lachlanwcast · ‎2018-05-02

Hi Jonathon,

I appreciate your time noting all this down - saves a lot of time and is working great for me on Qlik Sense February 2018 and a free account of SurveyMonkey & Public app (unsure if time limited etc.)

Just a few points which may may some lives easier...

I used the compiled cURL "SSL enabled SSH enabled" windows executable found here for token permanent generation: https://curl.haxx.se/dlwiz/?type=bin&os=Win32&flav=-

The exectuable file is located in the /src/ folder of the downloaded ZIP.

This required me to add the flag '--insecure' at the end of the curl request

Thanks again - have a great week

Lachlan

georgio_hb · ‎2019-04-05

Great Write up!
I'm having an issue while looping on the Survey Details. Gets me an error about invalid parameters.

But works fine with Survey Bulk, any idea about this?

jonvitale · ‎2019-04-08

Georgio,

I can't give you exact details because I only used this live connection to get - generally - metadata about the survey (how many people responded so far). But here are some thoughts about what you can try.

First, get the raw data using curl. That way you can see how your data is structured. Try the following with your personal token and the survey ID. I also needed to use a -k flag in front.

curl -i -X GET -H "Content-Type: application/json" -H "Authorization:bearer YOUR_AUTH_TOKEN" https://api.surveymonkey.net/v3/surveys/YOUR_SURVEY_ID/responses/bulk

If this works, you'll get a big box of text. Copy it and put in https://jsonlint.com/ to prettify it.

Next you'll need to build your app code to reflect the nested structure of the json output. For example, here's what my json code looks like:

{
	"per_page": 50,
	"total": 18690,
	"data": [
		{
		"total_time": 589,
		"href": "https:\/\/api.surveymonkey.net\/v3\/surveys\/REDACTED\/responses\/REDACTED",
		"custom_variables": {
			"student_id": "REDACTED",
			"ulcs_code": "REDACTED"
		},
		"ip_address": "REDACTED",
		"id": "REDACTED",
		"logic_path": {},
		"date_modified": "2019-01-25T20:42:31+00:00",
		"response_status": "completed",
		"custom_value": "",
		"analyze_url": "https:\/\/www.surveymonkey.com\/analyze\/browse\/REDACTED?respondent_id=REDACTED",
		"pages": [{
			"id": "REDACTED",
			"questions": [{
				"id": "REDACTED",
				"answers": [{
					"choice_id": "REDACTED"
				}]
			}]

Here's what my Qlik code looks like:

LIB CONNECT TO '$(smconnect)';
      RestConnectorMasterTable:

      SQL SELECT 
          "__KEY_root",
          (SELECT 
              "date_modified",
              "date_created",
              "__FK_data",
              "__KEY_data",
              (SELECT 
                  "student_id",
                  "ulcs_code",
                  "__FK_custom_variables"
              FROM "custom_variables" FK "__FK_custom_variables")
          FROM "data" PK "__KEY_data" FK "__FK_data")
      FROM JSON (wrap on) "root" PK "__KEY_root"
      WITH CONNECTION(
          Url "$(vURL)",
          QUERY "start_created_at" "$(vMaxDateCreated)"
      );

Notice that I have an object within my json called "data", which contains a bulk of the data. In the Qlik code I am pulling "date_modified" and "data_created" from the top level of "data".

Then, within data there is another object called "custom variables", we use a "From" statement to pull data at that level - that's where I get my variables "student_id" and "ulcs_code".

Presumably, if you want to get responses, you'll need to go at least one level deeper (probably more). So, selecting From "pages", "questions", and "answers". You'll need to play around with to get it right.

All the stuff with __FK in front is for Qlik, you won't see it in the SurveyMonkey data. So, I guess, try to mimic the structure you see here with that.

One more thing that might be helpful, instead of pulling all the data every single time (which can make you hit your limit), I am only collecting new data. I do this by first analyzing the data I already have and saving the maximum timestamp as a variable (vMaxDateCreated). I then use Query "start_created_at" to limit the data to only this time and later.

Good luck.

georgio_hb · ‎2019-04-09

Thanks a lot for your answer, you're helping a lot!

The connections are working properly now.

Since my aim is to link the Survey Bulk: (This is what survey monkey recommends)

https://api.surveymonkey.net/v3/surveys/YOUR_SURVEY_ID/responses/bulk

to the survey details and vice versa

https://api.surveymonkey.net/v3/surveys/YOUR_SURVEY_ID/details

If i select the data from the data connections as it is, we'd get those data models:

Bulk:

Details:

So linking those two is really challenging when it comes to pages and keys.

Any idea on how to use the keys together to make the proper links ?

Thanks !

jonvitale · ‎2019-04-09

Ciao Giorgio,

You got pretty far, so it looks like the connection script is doing its work correctly. Would you mind posting the Qlik script that you used to get the "bulk" data model (with sensitive information removed, of course)?

Now it seems like it's a matter of "de-normalizing" or "flattening" the data. It seems that the "data" table in the model is the highest level data structure. So, I would just start "left join"-ing the necessary child tables onto the data table until you get all the way down to the level that you need. So, left join "pages" onto "data", then "questions" onto that, and then "answers" on to that. That will be your main data table.

You can then have a "contacts" table. Start with the current contacts table and then left join onto that important other information like "first name" and "last name". Keep the metadata table as it is right now as a bridge between the flattened data table and contacts table.

By the way, it may all just work as it is right now - i.e., you might be able to do what need to do on the front-end without manually joining tables. Perhaps before trying to manipulate the data, see if you can make a front end table with the questions, answers, contacts, etc. It looks like you/Qlik did a good job of making a nice data model.

georgio_hb · ‎2019-04-09

Yes, we are trying to make the best out of it 🙂

Check the .qvf i sent you, first i am getting a list of all survey IDs, storing them and then looping over them to get all bulk and all details of all my surveys.

All the un-commented sections are working perfectly up until i reach those tables from the Bulk Connection:

-Pages

-Answers

-Questions

Since they are already coming from the details connection and the link is not clear at all, also note that there is no DATA table in the Details Connection!

Check the Picture attached you'd understand what's happening, the blue arrows show be linked to the other tables , but how ?

Making a table having questions and answers won't give correct results since they are not linked properly.

I do believe also the sequence ID you mentioned in your tutorial may confuse the data model, Once many Surveys are loaded all the links will be messed up.

georgio_hb · ‎2019-04-09

Link for .QVF