LIST_PARTICIPANTS IN EXCEL WITH VBA JSON

More
3 months 2 weeks ago #232791 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

Please Log in to join the conversation.

More
3 months 2 weeks ago - 3 months 2 weeks ago #232793 by Joffm
Hi,
why didn't you answer the initial questions?

recently I use a code that I found in the community of this forum

It would be great if you showed us which code you are talking about.

But you may read this
forums.limesurvey.org/forum/design-issue...he-user-email#230812

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: 3 months 2 weeks ago by Joffm.

Please Log in to join the conversation.

More
3 months 2 weeks ago #232795 by jnieto-test
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:
' -----------------------------------------------------------------------
' 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.

More
3 months 2 weeks ago #232797 by Joffm
Did you read the documentation of the API?
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.
$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)
$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.

More
3 months 1 week ago #232902 by jnieto-test
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?
'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.

Start now!

Just create your account and start using Limesurvey today.

Register now