- Posts: 13597
- Thank you received: 2487
Welcome to the LimeSurvey Community Forum
Ask the community, share ideas, and connect with other LimeSurvey users!
Experiences using LS with Google® drive™ spreadsheets©
- DenisChenu
- Offline
- LimeSurvey Community Team
Less
More
8 years 1 month ago #133038
by DenisChenu
Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand , plugin development .
I don't answer to private message.
Replied by DenisChenu on topic Experiences using LS with Google® drive™ spreadsheets©
Excel : what it is ?
Else : another tool to considerate : docs.datawrapper.de/automatic-data-upload/
Else : another tool to considerate : docs.datawrapper.de/automatic-data-upload/
Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand , plugin development .
I don't answer to private message.
The following user(s) said Thank You: Ben_V
The topic has been locked.
- Qsgt
- Offline
- New Member
Less
More
- Posts: 19
- Thank you received: 0
8 years 3 weeks ago #133560
by Qsgt
Replied by Qsgt on topic Experiences using LS with Google® drive™ spreadsheets©
This sound like something that I would be interested in however, I do have some questions.
1. Is it possible to define which column a response goes into i.e. is it possible to skip some columns when importing responses so as to have field that utilise a gg formula in the same row as the reposes?
2. Is making the gg sheet publicly viewable as a published html document the only way to have it update or will it still update if I have a private document that I just chare with the necessary people?
Thanks,
qsgt
1. Is it possible to define which column a response goes into i.e. is it possible to skip some columns when importing responses so as to have field that utilise a gg formula in the same row as the reposes?
2. Is making the gg sheet publicly viewable as a published html document the only way to have it update or will it still update if I have a private document that I just chare with the necessary people?
Thanks,
qsgt
The topic has been locked.
- Ben_V
- Topic Author
- Offline
- Platinum Member
Less
More
- Posts: 1128
- Thank you received: 329
8 years 3 weeks ago #133564
by Ben_V
Benoît
EM Variables => bit.ly/1TKQyNu | EM Roadmap => bit.ly/1UTrOB4
Last Releases => 2.6x.x goo.gl/ztWfIV | 2.06/2.6.x => bit.ly/1Qv44A1
Demo Surveys => goo.gl/HuR6Xe (already included in /docs/demosurveys)
Replied by Ben_V on topic Experiences using LS with Google® drive™ spreadsheets©
Hi..very quickly:
1) yes (it's the standard method)
2) no (you can share the original sheet with only some persons, with or without rights to edit the document)
1) yes (it's the standard method)
2) no (you can share the original sheet with only some persons, with or without rights to edit the document)
Benoît
EM Variables => bit.ly/1TKQyNu | EM Roadmap => bit.ly/1UTrOB4
Last Releases => 2.6x.x goo.gl/ztWfIV | 2.06/2.6.x => bit.ly/1Qv44A1
Demo Surveys => goo.gl/HuR6Xe (already included in /docs/demosurveys)
The topic has been locked.
- Qsgt
- Offline
- New Member
Less
More
- Posts: 19
- Thank you received: 0
8 years 3 weeks ago #133565
by Qsgt
Replied by Qsgt on topic Experiences using LS with Google® drive™ spreadsheets©
OK. Thanks for the quick reply but I have some follow up questions:
1. I'm relatively new to XML where would I go to find the standard method?
2. So, if I share the sheet like I would a regular Google Document, it will still update?
3. New Question, is it possible to only export a response to the sheet if there is a value in the response? If so, where do I go to find out how?
Thanks for the help,
qsgt
1. I'm relatively new to XML where would I go to find the standard method?
2. So, if I share the sheet like I would a regular Google Document, it will still update?
3. New Question, is it possible to only export a response to the sheet if there is a value in the response? If so, where do I go to find out how?
Thanks for the help,
qsgt
The topic has been locked.
- Mazi
- Offline
- Official LimeSurvey Partner
8 years 3 weeks ago #133574
by Mazi
Best regards/Beste Grüße,
Dr. Marcel Minke
Need Help? We offer professional Limesurvey support: survey-consulting.com
Contact: marcel.minke(at)survey-consulting.com
Replied by Mazi on topic Experiences using LS with Google® drive™ spreadsheets©
This is a very interesting approach! Thanks for
documenting it at the workarounds
.
Note that for sending emails to a predefined address if question X was answered "Y" is something that can easily be done from within Limesurvey. That's nothing I'd recommend to use the Google service for.
Note that for sending emails to a predefined address if question X was answered "Y" is something that can easily be done from within Limesurvey. That's nothing I'd recommend to use the Google service for.
Best regards/Beste Grüße,
Dr. Marcel Minke
Need Help? We offer professional Limesurvey support: survey-consulting.com
Contact: marcel.minke(at)survey-consulting.com
The topic has been locked.
- Ben_V
- Topic Author
- Offline
- Platinum Member
Less
More
- Posts: 1128
- Thank you received: 329
8 years 2 weeks ago - 8 years 2 weeks ago #133670
by Ben_V
....
For example it's quiet easy to handle:
- Automating a Help Desk Workflow
- blacklists of email addresses or domains (eg. yopmail.com).
- Delayed emails for example x days or months after submission.
Benoît
EM Variables => bit.ly/1TKQyNu | EM Roadmap => bit.ly/1UTrOB4
Last Releases => 2.6x.x goo.gl/ztWfIV | 2.06/2.6.x => bit.ly/1Qv44A1
Demo Surveys => goo.gl/HuR6Xe (already included in /docs/demosurveys)
Replied by Ben_V on topic Experiences using LS with Google® drive™ spreadsheets©
(re)read this blog entry from the begining; there are some links to the manual describing the whole process and other pieces of code.qsgt wrote: 1. I'm relatively new to XML where would I go to find the standard method?
Yes of course. My experience is that all documents update at least 2 or 3 times hourly.qsgt wrote: 2. So, if I share the sheet like I would a regular Google Document, it will still update?
Sorry. I don't understand fully the question, but remember that sheets content is linked to the query presented in your xml file... For example if you query included a statement like "where submitdate is not null", incomplete records won't be returned.qsgt wrote: 3. ...is it possible to only export a response to the sheet if there is a value in the response?
....
Yes... but for more advanced needs, it's hard to imagine easier and more complete possibilities than those managed with apps script.Mazi wrote: Note that for sending emails to a predefined address if question X was answered "Y" is something that can easily be done from within Limesurvey
For example it's quiet easy to handle:
- Automating a Help Desk Workflow
- blacklists of email addresses or domains (eg. yopmail.com).
- Delayed emails for example x days or months after submission.
Benoît
EM Variables => bit.ly/1TKQyNu | EM Roadmap => bit.ly/1UTrOB4
Last Releases => 2.6x.x goo.gl/ztWfIV | 2.06/2.6.x => bit.ly/1Qv44A1
Demo Surveys => goo.gl/HuR6Xe (already included in /docs/demosurveys)
Last edit: 8 years 2 weeks ago by Ben_V.
The topic has been locked.
- mprados
- Offline
- New Member
Less
More
- Posts: 3
- Thank you received: 0
7 years 10 months ago #137690
by mprados
Replied by mprados on topic Experiences using LS with Google® drive™ spreadsheets©
"I made this morning a 2 questions survey sample with the link to the spreadsheet living in the completed message…
I can explain the process later if needed."
I think the link is no longer available, thanks for the follow up!
I can explain the process later if needed."
I think the link is no longer available, thanks for the follow up!
The topic has been locked.
- Ben_V
- Topic Author
- Offline
- Platinum Member
Less
More
- Posts: 1128
- Thank you received: 329
7 years 10 months ago - 7 years 10 months ago #137701
by Ben_V
Hi @mprados... I will replace it when I'll have more time.
This said this demo was only made to show that this is fully possible & quiet easy.
So I suggest you to start directly your own test based on what I wrote on the doc.
For users starting with this method, I think the best is first to work with a static xml file...
For example if you want to import the data from:
www.xmlfiles.com/examples/plant_catalog.xml
That's all ...your sheet will now look like:
Benoît
EM Variables => bit.ly/1TKQyNu | EM Roadmap => bit.ly/1UTrOB4
Last Releases => 2.6x.x goo.gl/ztWfIV | 2.06/2.6.x => bit.ly/1Qv44A1
Demo Surveys => goo.gl/HuR6Xe (already included in /docs/demosurveys)
Replied by Ben_V on topic Experiences using LS with Google® drive™ spreadsheets©
mprados wrote: I think the link is no longer available, thanks for the follow up!
Hi @mprados... I will replace it when I'll have more time.
This said this demo was only made to show that this is fully possible & quiet easy.
So I suggest you to start directly your own test based on what I wrote on the doc.
For users starting with this method, I think the best is first to work with a static xml file...
For example if you want to import the data from:
www.xmlfiles.com/examples/plant_catalog.xml
- Create a new GG sheet called "Sheet_1"
- Create a new script (Tools => Script editor )
- Paste the following script, save and run ( ►)
Code:
function getData() { // related with Sheet "Sheet_1" // 1. Create headers // https://zapier.com/blog/google-apps-script-tutorial/ // * * * * * * * * * * * * * * * * * * * * * // Create headers var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('Sheet_1'); // Set the headers values var values = [ ["COMMON", "BOTANICAL", "ZONE", "LIGHT", "PRICE", "AVAILABILITY"] ]; // Set the range of cells var range = sheet.getRange("A1:F1"); // Call the setValues method on range and pass in our values range.setValues(values); // 2. Import data // * * * * * * * * * * * * * * * * * * * // If .XML file => use semicolon -> plant_catalog.xml"; "//COMMON" // If xml generated with .PHP file => use comma -> plant_catalog.xml", "//COMMON" // * * * * * * * * * * * * * * * * * * * var importCommon = '=IMPORTXML("http://www.xmlfiles.com/examples/plant_catalog.xml"; "//COMMON")'; var importBotanical = '=IMPORTXML("http://www.xmlfiles.com/examples/plant_catalog.xml"; "//BOTANICAL")'; var importZone = '=IMPORTXML("http://www.xmlfiles.com/examples/plant_catalog.xml"; "//ZONE")'; var importLight = '=IMPORTXML("http://www.xmlfiles.com/examples/plant_catalog.xml"; "//LIGHT")'; var importPrice = '=IMPORTXML("http://www.xmlfiles.com/examples/plant_catalog.xml"; "//PRICE")'; var importAvailability = '=IMPORTXML("http://www.xmlfiles.com/examples/plant_catalog.xml"; "//AVAILABILITY")'; SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet_1').getRange('A2').setValue(importCommon); SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet_1').getRange('B2').setValue(importBotanical); SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet_1').getRange('C2').setValue(importZone); SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet_1').getRange('D2').setValue(importLight); SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet_1').getRange('E2').setValue(importPrice); SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet_1').getRange('F2').setValue(importAvailability); }
That's all ...your sheet will now look like:
Benoît
EM Variables => bit.ly/1TKQyNu | EM Roadmap => bit.ly/1UTrOB4
Last Releases => 2.6x.x goo.gl/ztWfIV | 2.06/2.6.x => bit.ly/1Qv44A1
Demo Surveys => goo.gl/HuR6Xe (already included in /docs/demosurveys)
Attachments:
Last edit: 7 years 10 months ago by Ben_V.
The following user(s) said Thank You: abudzarr
The topic has been locked.
- abudzarr
- Offline
- New Member
Less
More
- Posts: 6
- Thank you received: 0
6 years 2 months ago #163659
by abudzarr
Replied by abudzarr on topic Experiences using LS with Google® drive™ spreadsheets©
I have followed the above prescription, and I could transfer the limesurvey result to googlesheet.
However I end up with an issue. As I have set my questions non-mandatory, some of the responses are 'NULL'. The responses were correctly displayed in the XML file. But IMPORTXML apparently ignores the NULL values, leading to wrong order.
For example the xml codes shown here snag.gy/mGbTYJ.jpg was read into a googlesheet.
The second rater Zul Amali did not rate on REL;
In the google drive, snag.gy/WL3xHT.jpg ,
As you can see the column REL for Zul Amali which should have been empty, is taken up by the next REL value which is 3 in the next node.
I have tried changing xmls:xsi value from default to true, but that did not bring any effect.
I am loss here, any help is much appreciated.
However I end up with an issue. As I have set my questions non-mandatory, some of the responses are 'NULL'. The responses were correctly displayed in the XML file. But IMPORTXML apparently ignores the NULL values, leading to wrong order.
For example the xml codes shown here snag.gy/mGbTYJ.jpg was read into a googlesheet.
The second rater Zul Amali did not rate on REL;
In the google drive, snag.gy/WL3xHT.jpg ,
As you can see the column REL for Zul Amali which should have been empty, is taken up by the next REL value which is 3 in the next node.
I have tried changing xmls:xsi value from default to true, but that did not bring any effect.
I am loss here, any help is much appreciated.
The topic has been locked.
- Ben_V
- Topic Author
- Offline
- Platinum Member
Less
More
- Posts: 1128
- Thank you received: 329
6 years 2 months ago #163661
by Ben_V
Benoît
EM Variables => bit.ly/1TKQyNu | EM Roadmap => bit.ly/1UTrOB4
Last Releases => 2.6x.x goo.gl/ztWfIV | 2.06/2.6.x => bit.ly/1Qv44A1
Demo Surveys => goo.gl/HuR6Xe (already included in /docs/demosurveys)
Replied by Ben_V on topic Experiences using LS with Google® drive™ spreadsheets©
Hi @abudzarr,
Your xml file is generated by a php file, right?
In this case you can try to add some php code to replace all null values by a dummy value (like "999")
(it's quiet hard for me to help you without making some huge work to reproduce your issue...)
Ben-
Your xml file is generated by a php file, right?
In this case you can try to add some php code to replace all null values by a dummy value (like "999")
(it's quiet hard for me to help you without making some huge work to reproduce your issue...)
Ben-
Benoît
EM Variables => bit.ly/1TKQyNu | EM Roadmap => bit.ly/1UTrOB4
Last Releases => 2.6x.x goo.gl/ztWfIV | 2.06/2.6.x => bit.ly/1Qv44A1
Demo Surveys => goo.gl/HuR6Xe (already included in /docs/demosurveys)
The topic has been locked.
- abudzarr
- Offline
- New Member
Less
More
- Posts: 6
- Thank you received: 0
6 years 2 months ago #163737
by abudzarr
Replied by abudzarr on topic Experiences using LS with Google® drive™ spreadsheets©
Thanks Ben. I did not like '6' to appear in my list of responses, but I guess, having the show 'no answer' option ON would have solved the whole problem. It would relieved me the learning curve to get that php snippets you are talking about.
Regards.
Regards.
The topic has been locked.
- Ben_V
- Topic Author
- Offline
- Platinum Member
Less
More
- Posts: 1128
- Thank you received: 329
6 years 2 months ago - 6 years 2 months ago #163740
by Ben_V
Benoît
EM Variables => bit.ly/1TKQyNu | EM Roadmap => bit.ly/1UTrOB4
Last Releases => 2.6x.x goo.gl/ztWfIV | 2.06/2.6.x => bit.ly/1Qv44A1
Demo Surveys => goo.gl/HuR6Xe (already included in /docs/demosurveys)
Replied by Ben_V on topic Experiences using LS with Google® drive™ spreadsheets©
How many "not mandatory" questions (included in xml file) do you have ?
Benoît
EM Variables => bit.ly/1TKQyNu | EM Roadmap => bit.ly/1UTrOB4
Last Releases => 2.6x.x goo.gl/ztWfIV | 2.06/2.6.x => bit.ly/1Qv44A1
Demo Surveys => goo.gl/HuR6Xe (already included in /docs/demosurveys)
Last edit: 6 years 2 months ago by Ben_V.
The topic has been locked.