Is using Google Sheets as a database really so crazy?

I have become very interested in the idea of the 'digital backpack' as a method to store and share pupil achievements. I was inspired by the way Code Academy and Treehouse use badges as awards for achieving learning goals.

Examples of badges on Treehouse

My first attempt at implementing a similar process in school was with Mozilla Open Badges using their digital backpack, however the process threw up two problems: pupils had to sign up for a Mozilla account and check their emails regularly enough to be aware of and collect badges; teachers needed to be familiar with using the site I had made. The PHP/MySQL approach I had taken was too cumbersome and so, looking for a simpler approach, I turned to Google Sheets.

I'm quite accustomed to using Sheets for general record keeping, and with a quick 'publish to the web' such records can make up the database of a web application.

Publish to the web option

With a bit of research, I discovered that a Sheet can be interpreted by PHP like any other CSV file. With a few lines of code the whole thing can be stored in an array.

Create an array[row][column] from the Google Sheet:

$file = fopen("http://doc.google... etc","r");
$data = array();

while(! feof($file))
  {
    array_push($data, fgetcsv($file));
  }
fclose($file);

Data can then be 'queried' - well read - in much the same way as a MySQL database.

Function to find pupil row:

function ReturnRowPosition($data, $needle){
   $i = 0;
   foreach($data as $arrays){
       if($arrays['0']==$needle){
       return $i;
       break;
       }
      $i++;
    }
}

My first meaningful go at using Sheets in this way worked by awarding digital badges simply through a teacher adding a date in the pupil row and under a column with the badge name. This eliminated the need for a teacher to login to the site, find the badge, select pupils etc. Pupils needed only to bookmark the site and recall simple picture based log-in details.

Pupil login process

This process was beginning to work well, but I changed jobs and let the school specific domain name lapse. Thoughts of 'Badges' fell away in the new job haze and I began to become curious about other web related things, notably AngularJS and JSON, learning as I built Slide and Tell.

At my new school I resumed my Google Apps workflows and soon had copious amounts of assessment information in the form of Sheets and Form data. As a result, the idea of a 'digital backpack' evolved into a question of how best to share the teacher's mark book with pupils and parents directly. The first challenge was to draw all the data together which I achieved with a Sheet that became titled 'One sheet to rule them all'.

Google Sheets becomes a bit relational

My use of formulas has never got much beyond =sum(A1:A10) and basic maths, but to link and organise multiple files I began exploring the docs.

Linking to another sheet, say results of a Google Forms quiz:

=importrange("link-address","grades!A1:D5")

Filtering out data relating to a matching username, joining multiples scores with a comma and clearing the % sign from a score:

=SUBSTITUTE(JOIN(",",(TRANSPOSE(FILTER(IMPORTRANGE($E$5,"Grades!D:D"), IMPORTRANGE($E$5,"Grades!D:D"),IMPORTRANGE($E$5,"Grades!B:B") = $A11)))),"%","")

One Sheet to rule them all

The resultant Sheet references data across multiple files through formulas and hyperlinks. Quiz titles and scores are taken directly from the Sheet, while question level data is referenced via hyperlink to another Sheet's published CSV location and AngularJS handles the http requests:

$scope.showAnswers = function(level,attempt,score) {
    $scope.scoreImg = "inc/load.gif";
    $http.get('inc/data.php?v=scores').then(function(response) {
        $scope.scoreInfo = response.data.records;
        $scope.scoreImg = "";
    }); 
};
What the pupils/parents see

Links to data contained on the same 'One sheet to rule them all' the teacher uses:

One sheet menu

Friendly links to categories:

Links to categories

Overview of results for easy identification of strengths and weaknesses

Overview of strengths and weaknesses

Ajax request via AngularJS to the Sheet containing question level data:

Question level detail

What the teacher sees

Menu items including links to display login details (stored in a Google Sheet of course)

Teachers menu

Available tests displayed using AngularJS's filters and ng-repeat methods:

Available assessments for searching

Distribution of pupil scores, here at the start of a Science unit (names obscured obviously):

Distribution of pupil scores

Distribution of question accuracy informing lesson planning - extra time on molluscs, less on birds and mammals:

Distribution of question accuracy

Click a question for indication of intervention is needed:

Intervention

Although a proper database like MySQL will be a lot faster than a Google Sheet CSV, it still has its trade-offs in that staff can input data as they would naturally anyway and it serves adequately as a database for giving pupils and parents a window to the data which would otherwise be kept private.