- Posts: 3
- Thank you received: 0
Welcome to the LimeSurvey Community Forum
Ask the community, share ideas, and connect with other LimeSurvey users!
LIST_PARTICIPANTS IN EXCEL WITH VBA JSON
- jnieto-test
- Topic Author
- Offline
- New Member
Less
More
1 year 6 months ago #232791
by jnieto-test
LIST_PARTICIPANTS IN EXCEL WITH VBA JSON was created by jnieto-test
Hello, recently I use a code that I found in the community of this forum to be able to apply JSON-RCP to Excel using VBA. What this code does is download responses from Lime Survey using the remote
The issue is that when exporting responses, I do not receive the emails from the participants. I have seen that there is an option for the latter but I don't know how to apply it to VBA... can someone who understands how guide me? Thanks in advance
The issue is that when exporting responses, I do not receive the emails from the participants. I have seen that there is an option for the latter but I don't know how to apply it to VBA... can someone who understands how guide me? Thanks in advance
Please Log in to join the conversation.
- Joffm
- Offline
- LimeSurvey Community Team
Less
More
- Posts: 12930
- Thank you received: 3978
1 year 6 months ago - 1 year 6 months ago #232793
by Joffm
Volunteers are not paid.
Not because they are worthless, but because they are priceless
Replied by Joffm on topic LIST_PARTICIPANTS IN EXCEL WITH VBA JSON
Hi,
why didn't you answer the initial questions?
But you may read this
[url] forums.limesurvey.org/forum/design-issue...he-user-email#230812 [/url]
It explains that with the setting "all" the email is not exported, but with the individual fields it is.
Joffm
But one thing is confusing.
Your topc is about "list participants", but then you talk about "exporting responses".
So, what do you want to export?
why didn't you answer the initial questions?
It would be great if you showed us which code you are talking about.recently I use a code that I found in the community of this forum
But you may read this
[url] forums.limesurvey.org/forum/design-issue...he-user-email#230812 [/url]
It explains that with the setting "all" the email is not exported, but with the individual fields it is.
Joffm
But one thing is confusing.
Your topc is about "list participants", but then you talk about "exporting responses".
So, what do you want to export?
Volunteers are not paid.
Not because they are worthless, but because they are priceless
Last edit: 1 year 6 months ago by Joffm.
Please Log in to join the conversation.
- jnieto-test
- Topic Author
- Offline
- New Member
Less
More
- Posts: 3
- Thank you received: 0
1 year 6 months ago #232795
by jnieto-test
Replied by jnieto-test on topic LIST_PARTICIPANTS IN EXCEL WITH VBA JSON
Oh, sorry for not answering the main questions. I did not know very well that she had to answer them.
Yes, I copy the code below:
I'll tell you what I'm really looking for...
I know that in LimeSurvey graphic mode I can export answers with the mail of the participants. I would like to be able to do this through the remote system... but when I use this code it does not export the mail information. Doing some research I read in this forum that you could do this from the mail with an option to list the participants. So I guess it would be a good idea to combine "export responses" with "list participants" to get what I want. Although I don't know if it is possible to do this with a single option as I do in graphical mode in lime
I'm a bit of a newbie at this, thanks for replying.
Yes, I copy the code below:
Code:
' ----------------------------------------------------------------------- ' ESTABLECER CONEXIÓN Y DESCARGAR ENCUESTA ESPECÍFICA ' ----------------------------------------------------------------------- ' Con el siguiente código podemos establecer acceso por JSON-RCP a Lime Survey ' y utilizar códificador en Base64 para poder tratarlo con VBA para EXCEL ' ' SE NECESITA INTRODUCIR ID ENCUESTA DE FORMA MANUAL EN EL CÓDIGO ' ' Se utiliza diferentes módulos: ' VBA JSON tools: https://github.com/VBA-tools/VBA-JSON ' Base64 Deconding: http://www.vbforums.com/showthread.php?379072-VB-Fast-Base64-Encoding-and-Decoding&p=2539878&viewfull=1#post2539878 ' ' Para más documentación: ' https://www.limesurvey.org/community/forums/can-i-do-this-with-limesurvey/114846-export-answers-to-excel-in-semi-realtime ' Sub export_limesurvey() Dim key As String Dim limeuser As String, limepass As String, limeurl As String, URL As String Dim jsonText As String, jsonObject As Object Dim SurveyID As String, DocumentType As String Dim export64 As String, export64Decoded As String limeurl = "XXXXXX" limeuser = "XXXXXXX" limepass = "XXXXX" SurveyID = Worksheets("Hoja2").Range("A6").Value DocumentType = "csv" 'Limpiar página excel Cells.Clear 'Inicialización Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1") URL = limeurl + "/admin/remotecontrol" objHTTP.Open "POST", URL, False objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)" objHTTP.setRequestHeader "Content-type", "application/json" 'Establecer acceso (get_session_key) sendtext = "{""method"":""get_session_key"",""params"": [""" + limeuser + """,""" + limepass + """],""id"": 1}" objHTTP.Send (sendtext) jsonText = objHTTP.responseText Set jsonObject = JsonConverter.ParseJson(jsonText) key = jsonObject("result") 'Exportar respuestas (export_responses) sendtext = "{""method"":""export_responses"",""params"": [""" + key + """,""" + SurveyID + """,""" + DocumentType + """],""id"": 1}" objHTTP.Send (sendtext) jsonText = objHTTP.responseText Set jsonObject = JsonConverter.ParseJson(jsonText) export64 = jsonObject("result") 'Codificar respuestas exportadas export64Decoded = Decode64(export64) 'Cerrar sesión sendtext = "{""method"":""release_session_key"",""params"": [""" + key + """],""id"": 1}" objHTTP.Send (sendtext) 'Divide las respuesta en varias líneas, de lo contrario, todo está en una celda s = export64Decoded i = 0 While Split(s, Chr(13) + Chr(10))(i) <> "" Cells(i + 1, 1) = Split(s, Chr(13) + Chr(10))(i) i = i + 1 Wend 'Conversión a CSV Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=True, Comma:=False, Space:=False, Other:=False, TrailingMinusNumbers:=True Cells.WrapText = False End Sub 'SI OCURRE ALGUN CAMBIO LLAMA LA FUNCIÓN... 'Private Sub Worksheet_Change(ByVal Target As Range) 'If Not Intersect(Target, Range("A1:Z10000")) Is Nothing Then 'Call copiar_celda 'End If 'End Sub
I'll tell you what I'm really looking for...
I know that in LimeSurvey graphic mode I can export answers with the mail of the participants. I would like to be able to do this through the remote system... but when I use this code it does not export the mail information. Doing some research I read in this forum that you could do this from the mail with an option to list the participants. So I guess it would be a good idea to combine "export responses" with "list participants" to get what I want. Although I don't know if it is possible to do this with a single option as I do in graphical mode in lime
I'm a bit of a newbie at this, thanks for replying.
Please Log in to join the conversation.
- Joffm
- Offline
- LimeSurvey Community Team
Less
More
- Posts: 12930
- Thank you received: 3978
1 year 6 months ago #232797
by Joffm
Volunteers are not paid.
Not because they are worthless, but because they are priceless
Replied by Joffm on topic LIST_PARTICIPANTS IN EXCEL WITH VBA JSON
Did you read the documentation of the API?
Here you find in "export responses"
You use only the basic call with default settings after the document type.
But here is a call with specified fields to export (you have to use the SGQA code of fields)
These two examples of a call in php.
But you see the important things.
Maybe this helps.
Joffm
Here you find in "export responses"
export_responses(string $sSessionKey,integer $iSurveyID,string $sDocumentType,string $sLanguageCode = null,string $sCompletionStatus = 'all',string $sHeadingType = 'code',string $sResponseType = 'short',integer $iFromResponseID = null,integer $iToResponseID = null,array $aFields = null): array|string
You use only the basic call with default settings after the document type.
Code:
$response = $myJSONRPCClient->export_responses( $sessionKey, $survey_id, 'json', // Document type : pdf,csv,xls,doc,json null, // Language code : null : default from survey 'all', // Stautus complete|incomplete|all null, // Heading : code|full|abbreviated : question text, default code null // answer : short|long , default : long );
But here is a call with specified fields to export (you have to use the SGQA code of fields)
Code:
$surveyResponses = $myJSONRPCClient->export_responses( $sessionKey, $survey_id, 'json', // Document type : pdf,csv,xls,doc,json null, // Language code : null : default from survey 'complete', // Stautus complete|incomplete|all NULL, // Heading : code|full|abbreviated : question text, default code NULL, // answer : short|long , default : long 1, // From Response ID 3000, // To Response ID ['id','token','submitdate','lastpage','interviewtime','email','159677X490time','159677X487time','159677X490X9219SQ001','159677X490X9221SQ001'] );
These two examples of a call in php.
But you see the important things.
Maybe this helps.
Joffm
Volunteers are not paid.
Not because they are worthless, but because they are priceless
Please Log in to join the conversation.
- jnieto-test
- Topic Author
- Offline
- New Member
Less
More
- Posts: 3
- Thank you received: 0
1 year 6 months ago #232902
by jnieto-test
Replied by jnieto-test on topic LIST_PARTICIPANTS IN EXCEL WITH VBA JSON
Sorry for the delay in responding.
I have seen what you tell me, and in your example... I see it clearly. I can understand what each thing is and what it is for. But it is very difficult for me to implement it in my VBA code, it is an example that I collected from the community of this forum
Next I show how my code is, would you know how to implement more parameters?
I have seen what you tell me, and in your example... I see it clearly. I can understand what each thing is and what it is for. But it is very difficult for me to implement it in my VBA code, it is an example that I collected from the community of this forum
Next I show how my code is, would you know how to implement more parameters?
Code:
'Exportar respuestas (export_responses)
sendtext = "{""method"":""export_responses"",""params"": [""" + key + """,""" + SurveyID + """,""" + DocumentType + """],""id"": 1}"
objHTTP.Send (sendtext)
jsonText = objHTTP.responseText
Set jsonObject = JsonConverter.ParseJson(jsonText)
export64 = jsonObject("result")
Please Log in to join the conversation.