Google Spreadsheet as backend

Discuss PivotX 2.0.x here. Non-PivotX related discussions go in The Drain.

Google Spreadsheet as backend

Postby ljhelbo » Fri Nov 25, 2011 11:48 am

I just want to share an idea, which for me has proved pretty useful. It is nothing really fancy but for me it turned out to be easy and helpful. I have a page for the local village showing activities in the school, church, sports club etc. An important part of this is listing the persons, who are in the board of each organization - with contact information like name, address, phone and email.

Right now it is a total of 140 persons. There are many changes over time and several are members in more than one place. I need some kind of administration, but I think a real database would be overkill. This gave me the idea to use Google Spreadsheet. This way I can easily share the spreadsheet with others, who will help updating the information. In the spreadsheet I write one person in each row. The columns contains name, address-1, address-2, zipcode, town, phone, cell phone, email etc. Only limitation is that it is not allowed to write any commas here.

Now I need to get the data from the spreadsheet to my site. First I go to my Gmail, open the spreadsheet and click on Files -> Download as -> CSV, and save the link to the file. Then i wrote the following procedure:

Code: Select all
function hent_personfil() {
   include("... base/folder ... /settings.php");
   global $personer;
   $i = 1;
   $fp = fopen( $person_csv, 'r' );
   while ( !feof ( $fp) ) {
           $personer[$i] = fgets( $fp );
           $i++;
       }
   fclose ($fp);
}


This is called from the header of the page via my init-extension. settings.php contains my global settings. In this case it holds the address of the spreadsheet in the variable $person_csv. The procedure reads the entire content of the spreadsheet and stores it in the global variable $personer - one row or person in each element of the array, as comma-separated text.

After this I can make extensions like this:

Code: Select all
function smarty_person_1($params) {
   global $PIVOTX ;
   global $personer;
   $nummer = $params['nr'];
   $denne_person = $personer[$nummer];
   $person_post = explode(",",$denne_person);
   $tekst = '<span style="font-size:11px;">'.$person_post[0].', '.$person_post[1];
   if ($person_post[2]!='') {$tekst = $tekst.', '.$person_post[2];}
   $tekst = $tekst.', '.$person_post[3].' '.$person_post[4];
   if ($person_post[5]!='') {$tekst = $tekst.', tlf.: '.$person_post[5];}
   if ($person_post[6]!='') {$tekst = $tekst.', mobil.: '.$person_post[6];}
   if ($person_post[7]!='') {$tekst = $tekst.', <a href="mailto:'.$person_post[7].'">e-mail</a>';}
   $tekst = $tekst.'</span>';
   return $tekst;
}


On the actual page, I will then write something like [[ person_1 nr="27" ]], where the number 27 is a reference to the line in the spreadsheet, where the person is listed. The extension will display a line with name, address, phone and email of the person in question.

The big advantage is of course, if a person is a member of several boards and therefore is listed at several places all over the site and this person gets a new phone number or email. In that case i only have to update me spreadsheet.

The global variable $personer is needed for reasons of speed. At first I was reading the data from the spreadsheet directly, but if you have 6 or 8 persons on one page (which is pretty normal), it will slow down the process way too much.
ljhelbo
 
Posts: 104
Joined: Sat Feb 26, 2011 2:58 pm
Location: Denmark

Re: Google Spreadsheet as backend

Postby Harm10 » Sat Nov 26, 2011 11:08 am

Looks to me as the start of an idea to create an extension for displaying csv file content in general within an entry or page.
If you need some pointers on how to start out creating an "official" extension there are some extensions around that are just created to be samples (Hello world (3x), Test (2x), The shouting hook. warning hook).
And of course if you need help you can also ask here.
Quality is in the detail of things............

Want to change or update your PivotX site? Mail or PM me!
I can also convert your site to a Wordpress site!
Harm10
Developer
 
Posts: 2011
Joined: Wed Jun 17, 2009 9:37 am
Location: Somewhere in The Netherlands (aka Holland)

Re: Google Spreadsheet as backend

Postby hansfn » Sat Nov 26, 2011 6:25 pm

Nice idea, Lars, but your code can be improved a lot:

1) Make $personer as static variable inside smarty_person_1
2) Replace your reading and parsing of the CSV file with the standard PHP function fgetcsv.
3) Move the initializing of the (now 2-dimensional) array $personer into smarty_person_1 - only read it if the static variable $personer isn't already set.

You gain three things with this: You aren't polluting the global name space, your are parsing the CVS only once and, most importantly, you aren't reading the CSV file if you aren't using the person_1 tag anywhere
hansfn
Developer
 
Posts: 3282
Joined: Sun Nov 25, 2007 7:48 pm
Location: Molde, Norway

Re: Google Spreadsheet as backend

Postby ljhelbo » Sat Nov 26, 2011 8:01 pm

Oh yes, that really makes sense - will test that out. :D

Code: Select all
function smarty_person_1($params) {
   include($_SERVER["DOCUMENT_ROOT"]."/inc/settings.php");
   global $PIVOTX ;
   static $personer;
   if (!isset($personer)) {
   $fp = fopen( $person_csv,'r' );
   $i = 0;
       while ( !feof ( $fp) ) {
           $personer[$i] = fgetcsv( $fp,0,"," );
           $i++;
          }
   }
   $nr = $params['nr']-1;
   $tekst = '<span style="font-size:11px;">'.$personer[$nr][0].', '.$personer[$nr][1];
   if ($personer[$nr][2]!='') {$tekst = $tekst.', '.$personer[$nr][2];}
   $tekst = $tekst.', '.$personer[$nr][3].' '.$personer[$nr][4];
   if ($personer[$nr][5]!='') {$tekst = $tekst.', tlf.: '.$personer[$nr][5];}
   if ($personer[$nr][6]!='') {$tekst = $tekst.', mobil.: '.$personer[$nr][6];}
   if ($personer[$nr][7]!='') {$tekst = $tekst.', <a href="mailto:'.$person_post[$nr][7].'">e-mail</a>';}
   $tekst = $tekst.'</span>';
   return $tekst;
}
ljhelbo
 
Posts: 104
Joined: Sat Feb 26, 2011 2:58 pm
Location: Denmark

Re: Google Spreadsheet as backend

Postby ljhelbo » Sun Nov 27, 2011 10:34 am

Harm10 wrote:Looks to me as the start of an idea to create an extension for displaying csv file content in general within an entry or page.


Just out of curiosity ;) I am thinking about which possible parameters such a procedure should have. My idea would be something like:

Origin of the data: CSV-Link to the spreadsheet, Starting row, Ending row, Starting column, Ending column
The way the data is displayed: Could be as a table or "inline" in the text. If its a table, you would need outside and inside border, maybe a background color or image. If it is inline, a sign of separation. In both cases some text style, font, size.

Now two questions: Would this be it, or am I missing something important? The CSV-link is usually very long, is it OK to integrate that as a parameter, or is there a better way to do it?
ljhelbo
 
Posts: 104
Joined: Sat Feb 26, 2011 2:58 pm
Location: Denmark

Re: Google Spreadsheet as backend

Postby Harm10 » Sun Nov 27, 2011 11:38 am

ljhelbo wrote:Now two questions: Would this be it, or am I missing something important?

The only thing I can think of right now are column headers and maybe even row titles to show what kind of data is in there.
Possibly something like max number of columns/row to limit the size of the display.
ljhelbo wrote:The CSV-link is usually very long, is it OK to integrate that as a parameter, or is there a better way to do it?

This I don't understand. Do you mean that there are potentially a lot of settings? If so you could work with a profile that contains a set of settings. Also an extension should always contain, in my view, defaults for settings that are not provided.
Quality is in the detail of things............

Want to change or update your PivotX site? Mail or PM me!
I can also convert your site to a Wordpress site!
Harm10
Developer
 
Posts: 2011
Joined: Wed Jun 17, 2009 9:37 am
Location: Somewhere in The Netherlands (aka Holland)

Re: Google Spreadsheet as backend

Postby hansfn » Sun Nov 27, 2011 9:34 pm

There is no length limit for parameters so long, ugly paths (or URLS) to a CSV file can be used.

If I would write a general extension to display CSV, it would take parameters "file", "start", "line_format", "end", and optionally "linenr" if you want to display just one line from the CSV file and maybe "select" to select just one line from the file based on some value matching a string. Some examples:

Display value 2 (which could be a name) and value 4 (which could be e-mail) from each line of the CSV file as a table:

Code: Select all
[[ display_csv
    file="path/to/file.csv"
    start="<table><tr><th>Name</th><th>E-mail</th></tr>"
    line_format="<tr><td>%2%</th><td>%4%</td></tr>"
    end="</table>" ]]

Display value 3 and value 5 from line nr 17 (as a paragraph):

Code: Select all
[[ display_csv
    file="path/to/file.csv"
    start="<p>"
    line_format="Some word %3% and some more words %5%."
    end="</p>"
    linenr=17 ]]

Display value 3 and value 5 from the line where value 3 is equal to some string (as a paragraph):

Code: Select all
[[ display_csv
    file="path/to/file.csv"
    start="<p>"
    line_format="Some word %3% and some more words %5%."
    end="</p>"
    select="%3%==some string" ]]
hansfn
Developer
 
Posts: 3282
Joined: Sun Nov 25, 2007 7:48 pm
Location: Molde, Norway


Return to 2.x Discussion

Who is online

Users browsing this forum: No registered users and 2 guests

cron