Using Google Sheets as a database?

Question

I would like to create a very simple PHP page for a site, which would show a timetable / calendar like data, where each slot would be either free or would have some appointment in it.

Because all the data is actually just one table, something like {month, day, hour, talk_name, talk_description}, I thought why not use a Google Sheets Spreadsheet as the database. OK, the main reason is that I'm just reading books about how to use MySQL in PHP, so I'm definitely not on a level to:

• create a nice admin interface for managing the events
• make the whole thing safe (I mean all my idea about safety is to use .htaccess for an admin folder and make the site read-only elsewhere).

On the other hand everyone could use Google spreadsheets for editing the table, so this way both the security aspects and the UI aspects would be solved.

My question is that how would you recommend me to do that? Google Sheets can both publish in XML and CSV formats. Can I just use fgetcsv to get the data? Can you give me some simple examples how to parse the csv, and if it would be efficient (ok, it will be less than 50 views a day), if I would do something like this (sorry for the abstract syntax)?

$source_csv = fgetcsv(...);

get_talk_name(x,y,z) {
  for all rows in $source_csv {
    if (month == x && day == y && hour == z) return talk_name
  }
}

get_talk_desc(x,y,z) {
  for all rows in $source_csv {
    if (month == x && day == y && hour == z) return talk_name
  }
}

Heteroousian

i would not recommend it, if you ever want to do it properly may as well start learning now. – Megaera

@zsero does it have to be php, because you can actually use JavaScript and YQL to achieve this and its pretty straight forward. – Pero

tutorialzine.com/2010/08/dynamic-faq-jquery-yql-google-docs – Pero

@kjy112 i would definately not like to do it in JS and YQL, as I prefer doing everything on the server side and it's PHP which I'm practising most these days. But thanks for the article! – Heteroousian

@zsero np...just double checking because i recently used this method to build a small business site, and so far no downtime or lag at all. – Pero

@kjy112 I know that this method can do some really serious stuff, for example I've been emailing with the guy who does gpsvisualizer.com and the Atlas part is entirely Javascript driven, so its fetching all the data from Google Docs on the client side: gpsvisualizer.com/atlas.html I'm using it to create an always up to date map for a moving geocache. – Heteroousian

@zsero you might want to take a look at YQL its basically doing the database communication that PHP does. If user can not disabled JavaScript, then JS + YQL can be very awesome! – Pero

Answer 1

So, while it might not be wise or scalable, yes, you can do it. Try this code:

<?php
$url = "https://spreadsheets.google.com/pub?hl=en&hl=en&key=0AupgXsRU8E9UdC1DY0toUUJLV0M0THM4cGJTSkNSUnc&output=csv";
$row=0;

if (($handle = fopen($url, "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        echo "<p> $num fields in line $row: <br /></p>\n";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo $data[$c] . "<br />\n";
        }
    }
    fclose($handle);
}

Basically, publish the spreadsheet as public, change output to csv (from the default HTML) by manually editing the URL (ie, output=csv), fetch it, and then iterate over it line by line using fgetcsv.

If the spreadsheet looks like this:

This will output the following for the csv in question:

array(2) {
  [0]=>
  string(4) "Name"
  [1]=>
  string(5) "Value"
}
array(2) {
  [0]=>
  string(3) "Foo"
  [1]=>
  string(5) "13123"
}
array(2) {
  [0]=>
  string(3) "Bar"
  [1]=>
  string(3) "331"
}

Ammonite

WOW, such a nice solution! Thank for the solution and the detailed explanation! For escaping the commas, I can just use the function SUBSTITUTE(text, search_text, new text, occurrence) in Google Docs. +Reference the original sheet to a new "clean" sheet and export only the "clean" one. – Heteroousian

look out for apostrophes! I am using fgetcsv to import data, and apostrophes are breaking, regardless whether I escape or htmlspecialchars. – Sparerib

This is no longer possible. There's no option to publish spreadsheet as '.csv' file. – Hachmin

@Hachmin still works for me. You publish the file, and then manually edit the output parameter as described in the post. – Ammonite

Answer 2

You could try this just for fun. But if you just need a communal calendar, use something like Google Calendar. The Google Calendar API enables you to update your calendar from a program. And you can embed a calendar in your website using the Google Embeddable Calendar Helper.

Not as much fun as programming it from scratch though ... ;-)

Qualitative

It's not really a calendar, and more importantly I think it's really fun to solve this problem while learning some bit of PHP. – Heteroousian

Answer 3

Check out this page for a pretty straightforward approach to using a GDocs spreadsheet as a CRUD DB. Usage follows this pattern, but you need to download a Zend class, and a GDocs/PHP file from github first...

<?php            // Zend library include path
set_include_path(get_include_path() . PATH_SEPARATOR . "$_SERVER[DOCUMENT_ROOT]/ZendGdata-1.8.1/library");
include_once("Google_Spreadsheet.php");
    $u = "[email protected]";
    $p = "password";
    $ss = new Google_Spreadsheet($u,$p);
    $ss->useSpreadsheet("My Spreadsheet");
                // if not setting worksheet, "Sheet1" is assumed
                // $ss->useWorksheet("worksheetName");
$row = array 
    ( "name" => "John Doe", "email" => "[email protected]", "comments" => "Hello world" );
if ($ss->addRow($row)) echo "Form data successfully stored using Google Spreadsheet";
    else echo "Error, unable to store spreadsheet data";
?>

Limburger

Answer 4

I don`t have enough rep to add a comment above, but the new method of exporting a sheet to CSV does work.

Your url as (publicly) shared from sheets:

https://docs.google.com/spreadsheets/d/9999999999999/edit?usp=sharing

Change the end /edit?usp=sharing to /export?format=csv

Like:

https://docs.google.com/spreadsheets/d/99999999999999/export?format=csv

Source: https://productforums.google.com/d/msg/docs/An-nZtjaupU/llWy4eYFywcJ

Bayou