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

More

3 years 11 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

Please Log in to join the conversation.

Joffm
Away
LimeSurvey Community Team

More

3 years 11 months ago - 3 years 11 months ago #232793 by Joffm

Replied by Joffm on topic LIST_PARTICIPANTS IN EXCEL WITH VBA JSON

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
[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: 3 years 11 months ago by Joffm.

Please Log in to join the conversation.

jnieto-test
Topic Author
Offline
New Member

More

3 years 11 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:

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: https://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
Away
LimeSurvey Community Team

More

3 years 11 months ago #232797 by Joffm

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"

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

More

3 years 10 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?

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.