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!