Experiences using LS with Google® drive™ spreadsheets©

More
2 years 6 months ago - 2 years 6 months ago #132260 by Ben_V
Ben_V created the topic: Experiences using LS with Google® drive™ spreadsheets©
Dear All,

There is an existing old post about this topic but I prefer start a new one.

I personally use to read directly ( using "IMPORTXML" function) some data collected with LS in a GG spreadsheet. Its very easy; No required use neither of LS nor GG APIs. Note that new survey responses are not exactly transmited in real-time, because there is usually a 15 to 30 minutes delay with the spreadsheet update, but it's ok :) .

Some interessant features are:
  • updated reports without LS admin login (for people who know the url)
  • very large library of powerful functions, well documented for calculation, charts, terrific mailing, etc.
  • possibility to build reports merging several surveys
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 just would like to know if someone else is also playing with this, to have & share more ideas about what is possible, etc.

Thank you for your answers...

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: 2 years 6 months ago by Ben_V.
The following user(s) said Thank You: DenisChenu, holch, tpartner, gabrieljenik, mprados, abudzarr

Please Log in or Create an account to join the conversation.

More
2 years 6 months ago #132261 by gabrieljenik
gabrieljenik replied the topic: Experiences using LS with Google® drive™ spreadsheets©
Hi Ben,

Very interesting... I didn't know that function.
I am interested about
- very large library of powerful functions, well documented for calculation, charts, terrific mailing, etc.
- possibility to build reports merging several surveys

Can you detail a little bit? What do you have in mind?

Thanks!

Cheers,
Gabriel - www.Encuesta.Biz - Authorized Partner

Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.
Assistance on LimeSurvey forum and LimeSurvey core developpement are on my free time (Say thanks ?)....[img]

Please Log in or Create an account to join the conversation.

More
2 years 6 months ago #132265 by holch
holch replied the topic: Experiences using LS with Google® drive™ spreadsheets©
Nice, looks good!

I usually like to avoid using Google services with sensible data, but it is very interesting to see what is possible.

Will have to play around a bit.

I'm not a LimeSurvey GmbH member. I answer at the LimeSurvey forum in my spare time. No support via private message.
Some helpful links: Manual (EN) | Question Types | Workarounds

Please Log in or Create an account to join the conversation.

More
2 years 6 months ago - 2 years 6 months ago #132267 by Ben_V
Ben_V replied the topic: Experiences using LS with Google® drive™ spreadsheets©
Gabriel, to have an idea of some possibilities you have visit the following sites & pages:
developers.google.com/apps-script/articles/sending_emails
support.google.com/docs/table/25273?hl=en

There are a lot of automated e-mailing possibilities ("triggers")
For example if a respondant answers that he could be interested by a service in the next two months, you can send him/her an automated (tailored) email around this date without having to switch your PC on. Obviously you will need a gmail (or better a gg apps pro) account.

About "sensible data"matter I agree with Holch. For example in USA, GG drive is not considered HIPAA compliant out of the box;
This said drive spreadsheets are not public by default (html view) and you have to choose your collaborators to share the main (editable) document (need gmail account too).

My main idea is that some tasks are a lot of easier this way than handling external scripts (php, json...)

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: 2 years 6 months ago by Ben_V.

Please Log in or Create an account to join the conversation.

More
2 years 6 months ago - 2 years 6 months ago #132268 by holch
holch replied the topic: Experiences using LS with Google® drive™ spreadsheets©
And for example in Europe, especially in Germany, saving business data in any Google service can be considered to be against dataprotection laws, as Google transfers information to the US, and with the safe harbor agreement not in place anymore, it is quite problematic.

And I totally agree, the ease of use with an implementation into Google Spreadsheets is definitely nice.

I'm not a LimeSurvey GmbH member. I answer at the LimeSurvey forum in my spare time. No support via private message.
Some helpful links: Manual (EN) | Question Types | Workarounds
Last Edit: 2 years 6 months ago by holch.
The following user(s) said Thank You: Ben_V

Please Log in or Create an account to join the conversation.

More
2 years 6 months ago #132275 by Ben_V
Ben_V replied the topic: Experiences using LS with Google® drive™ spreadsheets©
Another interessant built-in feature is the access to history versions (with possibility to restore the sheet state at a previous date if saved)... For surveys where respondants can edit their answers, you will be able to track some changes ;)

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)

Please Log in or Create an account to join the conversation.

More
2 years 6 months ago #132359 by Ben_V
Ben_V replied the topic: Experiences using LS with Google® drive™ spreadsheets©
Added to the doc as a workaround


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:
The following user(s) said Thank You: tpartner

Please Log in or Create an account to join the conversation.

More
2 years 6 months ago #132408 by DenisChenu
DenisChenu replied the topic: Experiences using LS with Google® drive™ spreadsheets©
Hi,

Seems very interesting, except the google part. I look for framacalc (ethercalc) system if we can import an XML file.

Maybe update RC to have this XML type can be a good idea ? exportResponse as XML ?
But i think it can be done in a plugin. If i found a ethercalc import XML : i like to do a plugin : export XML . BHere : we need a protection system .....
Denis

Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand (or search sondages pro).
An error happen ? Before make a new topic : remind the Debug mode .

Please Log in or Create an account to join the conversation.

More
2 years 6 months ago - 2 years 6 months ago #132409 by Ben_V
Ben_V replied the topic: Experiences using LS with Google® drive™ spreadsheets©

DenisChenu wrote: Seems very interesting, except the google part. I look for framacalc (ethercalc) system if we can import an XML file.



Is it possible to associate scripts with an ethercalc sheet as GG spreadsheets do (=> Aps script ) ?



BTW I paste here for testing purposes, the most simple code to generate a valid XML file listing all templates available in a LS install . Only needs to edit db credentials.

Rows can be displayed in a gg drive spreadsheet using the following formula in a cell:
=IMPORTXML("http://example.org/myxmlfilename.php", "//folder")

myxmlfilename.php
<?php
 
/* 
* LIST OF AVAILABLE THEMES IN A LS INSTALLATION 
* - - - - - - - - - - - - - - - - - - - - - - - - - - - 
* GGsheet formula (have to be pasted in a cell):
* =IMPORTXML("http://example.org/myxmlfilename.php", "//folder")
*/
 
header('Content-type: application/xml; charset=utf-8');
header ('X-Robots-Tag: none', true);
 
print "<?xml version='1.0' encoding='utf-8'?>"; 
print "<answers>";
 
// Fetch database credentials (better outside web www root directory)
 
$dblocation =   ' ';
$dbname =  ' ';
$dbuser =    '  ';
$dbpwd =   ' ';
 
 
// Create connection
$conn = new mysqli($dblocation, $dbuser, $dbpwd, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
 
$sql = "SELECT `folder` FROM `lime_templates`";
$result = $conn->query($sql);
 
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
 
    print "<item>\n";
    print "<folder>".$row["folder"]."</folder>\n";
    print "</item>\n";
    }
} else {
    print "big error";
}
 
$conn->close();
 
print "</answers>";
 
?>

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: 2 years 6 months ago by Ben_V.

Please Log in or Create an account to join the conversation.

More
2 years 6 months ago #132437 by DenisChenu
DenisChenu replied the topic: Experiences using LS with Google® drive™ spreadsheets©

Ben_V wrote: ...
Is it possible to associate scripts with an ethercalc sheet as GG spreadsheets do (=> Aps script ) ?
...

Don't knwo actually, asking on frmasoft forum :)

Denis

Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand (or search sondages pro).
An error happen ? Before make a new topic : remind the Debug mode .

Please Log in or Create an account to join the conversation.

More
2 years 6 months ago #132936 by Ben_V
Ben_V replied the topic: Experiences using LS with Google® drive™ spreadsheets©

Ben_V wrote: For example in USA, GG drive is not considered HIPAA compliant out of the box;

For whom is interested, more info is available here

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)

Please Log in or Create an account to join the conversation.

More
2 years 6 months ago #133005 by Ben_V
Ben_V replied the topic: Experiences using LS with Google® drive™ spreadsheets©

DenisChenu wrote: I look for framacalc (ethercalc) system if we can import an XML file.


FYI, after reading this stackoverflow post , this sounds yet feasible with recent Excel versions.... maybe only Office 365, I don't know.
I can't test right now, because I've no recent Excel release installed, but if someone wants to put the hands into this, there are lots of xml valid files waiting for testing at xmlfiles.com .

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)

Please Log in or Create an account to join the conversation.

More
2 years 6 months ago #133038 by DenisChenu
DenisChenu replied the topic: Experiences using LS with Google® drive™ spreadsheets©
Excel : what it is ?

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 (or search sondages pro).
An error happen ? Before make a new topic : remind the Debug mode .
The following user(s) said Thank You: Ben_V

Please Log in or Create an account to join the conversation.

More
2 years 5 months ago #133560 by Qsgt
Qsgt replied the 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

Please Log in or Create an account to join the conversation.

More
2 years 5 months ago #133564 by Ben_V
Ben_V replied the 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)

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)

Please Log in or Create an account to join the conversation.

Start now!

Just create your account and start using Limesurvey today.

Register now
Join our Newsletter!