Cloudron Forum

Hello everyone,
FYI: We ended up using https://www.encuesta.biz/producto/limesurvey-plugin-google-sheets-connector/.
The guys are super responsive and very supportive.
It works like a charm.

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();

?>

@nebulon: Any updates if pretix is on your radar (or even roadmap)?

@marcusquinn Thanks for the tip. Not sure it is GDPR-compatible. There are other alternatives, but Pretix is our favourite.

@imc67 said in pretix - Ticketing software that cares about your event—all the way:

It's a pity that after more than 6 years on the Wishlist, high ranked, one of the best and complete Open Source Event Ticketing software, while there is no single equivalent in the App Store ... it's still not available

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!

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.

Thank you, @nebulon!!!

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.

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!

Since we are not in a particular hurry and Cloudron is just super comfortable to use, I guess we'll wait and see.

@scooke
The quote is from @andirahmat.

I represent a non-profit. We would like to start our own journal to quickly publish results from (our) European education projects.

Is this the dockerfile you mentioned?
https://github.com/marcbria/docker-pkp/blob/main/Dockerfile

Hello!

I believe the Cloudron documentation may be outdated:

Bitwarden now supports SSO: https://bitwarden.com/help/about-sso/

Can we expect SSO to be added to Vaultwarden?

It would be absolutely amazing to have publishing software from the Public Knowledge Project (PKP) on Cloudron:

• OJS: Open Journal Systems
• OPS: Open Preprint Systems
• OMP: Open Monograph Press

There are now docker images available for OJS and OMP:
https://forum.pkp.sfu.ca/t/docker-images-for-ojs-and-omp/83312

• https://github.com/pkp/docker-ojs/

I believe docker images would make packaging for Cloudron easier?

@girish Any chance we'll see OJS etc. on Cloudron in the future?

Hi,

I’m unsure if this issue is caused by the app itself or the Cloudron packaging.

We need the ability to set custom links for our privacy policy and terms. Currently, even after configuring the custom variables as instructed, the links still redirect to cal.com.

And mine are still there.

YEAH!!!!!!!

Thank you SO SO SO much, @girish.

Fun fact: the order of the menu buttons changed (for lack of a better word, it is also the word used in the kimai documentation) from CSV / Excel / Print / PDF to CSV / Excel / PDF / Print.

But there is now a fifth template in the PDF dropdown and it has the file name as a name.
Again, thank you so much!!!

Updating now.
Just to be sure: I delete the path I added to local.yaml, correct?

I also tried.

• 'app/data/export/'
• '/data/export/'
  I reloaded the cache every time. The additional templates do not appear.