Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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.
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:
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).
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.
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.
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.
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!)
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.
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.
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.
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"),
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!
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 ?
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.
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
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
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
@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! 😃