Sync LimeSurvey results to Google Sheets using importxml for Dummies?

[marylou]

Hello everyone,

I would be super grateful if someone could help me set up an importxml connection from Google Sheets to Limesurvey.

More specifically, this is what I would like to achieve:

---

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
  (https://forums.limesurvey.org/forum/can-i-do-this-with-limesurvey/103747-experiences-using-ls-with-google-drive-spreadsheets#132260)

---

I am trying to follow this manual.

I suppose this is what I need to do:

1. Create the php.file:

Is the dblocation https://mylimesurveysite.com/admin/index.php?
What is the dbname?
dbuser and dbpwd are the LimeSurvey user credentials?
(No questions about sid :-)).

2. Save it at http://mylimesurveysite.com/test.php
   Here is where I run into difficulties since we run LS on Cloudron.

3. Paste the following formula in Google Sheets:
   =IMPORTXML("http://mylimesurveysite.com/test.php?refresh=&GoogleClock()", "//mytaglabel")

Can you help me figure out how to use it?
It would be awesome to get it to work.

Thanks!

[marylou]

I believe I am one step closer to what I need. If I manage to locate the LimeSurvey config.php, I will know dblocation, dbname, dbuser and dbpwd. It should be easy from there.

In self-hosted LimeSurvey environments, it seems to be located here:
[limesurvey_installation]/application/config/config.php.

The question now is where is the LimeSurvey config.php stored on Cloudron?

Unfortunately, config.json does not seem to provide the answer.

[nebulon]

To get the database credentials, you can open a webterminal into the app and then run the env command. There you should see variables starting with CLOUDRON_MYSQL_

[marylou]

Thank you, @nebulon!!!

[marylou]

Unfortunately, I am still failing to connect Sheets to LimeSurvey.

$dblocation = CLOUDRON_MYSQL_URL ?
$dbname = CLOUDRON_MYSQL_DATABASE ?
$dbuser = CLOUDRON_MYSQL_USERNAME ?
$dbpwd = CLOUDRON_MYSQL_PASSWORD ?

CLOUDRON_MYSQL_DATABASE and CLOUDRON_MYSQL_USERNAME
have identical values.

CLOUDRON_MYSQL_URL starts with "mysql://..."
Not sure how to translate this into a URL.

[joseph]

@marylou the mysql is not exposed to the world. It is internal. By Sheets, do you mean the SaaS product ? (or is this some plugin or something in LimeSurvey?).

Does Google Sheets support connecting via ssh tunnel? See https://docs.cloudron.io/guides/connect-mysql/

[jdaviescoates]

Just to mention, it's not quite the same as what you're trying to do, but if you can't get this working Nextcloud Form data can be automatically stored into a Nextcloud spreadsheet these days (much like how Google Forms works with Google Sheets)

[marylou]

Hi, Google Sheets, yes.
This description makes it sound really easy.
But... dblocation, dbname, dbuser, dbpwd.
Nextcloud is not an option at this time unfortunately.
I will find out if Google Sheets supports connecting vis ssh tunnel.
Thanks for your comments and help!

[marylou]

Hi @joseph,
Google Sheets (Google version of MS Excel) does in fact support connecting via ssh tunnel and we can get it done.
But I still don't know how to adapt the variables in the script.

This is the description in the LimeSurvey Manual:

Import & show data into a Google Drive Spreadsheet
Tested with: LS >= 2.05

You can import any data (e.g. list of surveys or users, selected answers) stored in your limesurvey database using the "IMPORTXML" function.

Create a XML file to structure and print needed data (you should access it at http://mysite.com/myxml.php )
In your spreadsheet use the following format (pasting in a cell):

=IMPORTXML("http://mysite.com/myxml.php?refresh=&GoogleClock()", "//mytaglabel")

To protect your XML file(s) from direct access you can put them in a dedicated folder and use a .htacess file to limit the access exclusively to Google Apps web crawlers filtered by ip ranges

order allow, deny
deny from all
# allow from your_ip
allow from 66.249.64.0/19 # Used by User-Agents 'apps-spreadsheets' & 'Feedfetcher-Google'
allow from 66.249.80.0/20 # Used by User-Agents 'apps-spreadsheets' & 'Feedfetcher-Google'
allow from 64.233.160.0/19 # Used by  'GoogleApps' UA
# Uncomment if necessary following ther IP ranges commonly used by Google INC.
# allow from 46.108.0.0/18 64.18.0.0/20 66.249.80.0/20 72.14.192.0/18 74.125.0.0/16 173.194.0.0/16 207.126.144.0/20 209.85.128.0/17 216.239.32.0/19

PHP (XML output) file sample.

The following php file will generate an XML view printing ID, STARTDATE and STARTLANGUAGE values of a survey.

<?php

/*
 - This php file will generate an XML file showing ID, STARTDATE and STARTLANGUAGE values of a survey.
 - Please edit variables for db credentials and survey id ($sid)
*/
    header('Content-type: application/xml; charset=utf-8');
    header ('X-Robots-Tag: none', true);

// Fetch database credentials (much better outside the wwww root directory)
// ************************
    $dblocation =   ' ';
    $dbname =  ' ';
    $dbuser =    '  ';
    $dbpwd =   ' ';
// ************************

// Create db connection
    $conn = new mysqli($dblocation, $dbuser, $dbpwd, $dbname);
// Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 


// Start XML output

    echo "<?xml version='1.0' encoding='utf-8'?>"; 
    echo "<answers>";

// Define survey ID
// ************************
    $sid = "1234";
// ************************

    $sql = "SELECT id, startdate, startlanguage FROM lime_survey_".$sid." ORDER BY 'id' LIMIT 100";
//  $sql = "SELECT 1234X56X789, id, startdate, startlanguage FROM lime_survey_".$sid." ORDER BY 'id' LIMIT 100";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {

    echo "<item>\n";
    echo "<id>".$row["id"]."</id>\n";  
    echo "<startdate>".$row["startdate"]."</startdate>\n";
    echo "<startlanguage>".$row["startlanguage"]."</startlanguage>\n";
//  echo "<anylabel>".$row["1234X56X789"]."</anylabel>\n";
    echo "</item>\n";
    }
    } else {
        echo "something is happening here but you don't know what it is";
    }

    echo "</answers>";

// Close db connection
    $conn->close();

?>