Sheets as a Database

Read my motivation on why using Sheets-as-a-Database (SaaDB) app in Coding part below. As a start, let's say I just want to be unique and MySQL is too school for my cool.

Enable Google Sheets API

  1. Go to the Google APIs Console
  2. Create a new project
  3. Click Enable API. Search for and enable the Google Drive API
  4. Create credentials for a Web Server to access Application Data
  5. Name the service account and grant it a Project Role of Editor
  6. Download the JSON file
  7. Copy the JSON file to your app directory and rename it to sheet_secret.json

Keep this JSON file secret and do not share it with anyone except yourself. This is to authenticate us to connect the Sheets to the API client.

Create a new Sheet

Go to Google Sheets and create a new Sheet. We will be using a sample sheet for this demonstration.

Enable sharing for this Sheet

  1. Open sheet_secret.json and find client_email. Copy the value.
  2. Go to your Google Drive and find the Sheet that you have just created.
  3. Right-click the Sheet and click Share...
  4. Paste the email from the JSON file into the People field. Keep other settings as it is.

Now this Sheet will only be shared/exposed to the email belonging to the Service Account we've created earlier.

Composer

Because I'm too lazy to write my own client, I'll just use an open-source Sheets API PHP client by ttskch on Github. Let's pray that this client library is not malicious.

$ composer require ttskch/google-sheets-api-php-client:@dev

This will download all required libraries needed to create a working Sheet API client.

Coding

As you might have noticed, we've skipped a lot of vanilla php from here since we are using an open-source library for our API client. I guess that's fine because what I'm trying to achieve here is to make Sheets available on the cloud to act as my database, so that anyone can contribute on one interface and the output can be done separately as many things imaginable.

An example of a case, I wanted to build a calendar interface which feeds data from a source. I don't want the data source to be closed and too technical for the non-technical person, and the most important part is I want to code it fast. If I'm writing a CRUD application for this matter, it's gonna take some days to finish let alone having to clean up the CRUD panel interface using CSS (being honest here, I'm lazy). With only Sheets as our CRUD platform, I already have a working cloud system with user-friendly interface for everyone to use for data input.

The only thing that's left is the backend architecture which will connect our Sheet (Database/CRUD) and my calendar front-end. Here I give you a tiny chart for your ant brain to imagine how all this is gonna work.

mytinychart

<?php

require __DIR__ . '/vendor/autoload.php';

$googleClient = \Ttskch\GoogleSheetsApi\Factory\GoogleClientFactory::createServiceAccountClient(__DIR__ . 'sheet_secret.json');

$api = \Ttskch\GoogleSheetsApi\Factory\ApiClientFactory::create($googleClient);

$spreadsheetId = 'SHEET_ID';
$range = 'Sheet1!A:Z';

$response = $api->getGoogleService()->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();

echo '<pre>';
echo json_encode($values, JSON_PRETTY_PRINT | true);
echo '</pre>';

// from here, you can design however the fuck you want the JSON response to look like.

Several things need to be done before executing this code.

  1. Change the relative file path for the secret JSON file. In this case, I put it in the same directory as index.php file of this code.

  2. Change SHEET_ID to your own Sheet ID. Sheet ID can be found in the URL of your newly created Sheet.

    eg. https://docs.google.com/spreadsheets/d/1DlFP2sh0JAubooZo5n2ldPGl3OzDQDc1GkMBhPrF-DA/edit#gid=0

Run $ php -S localhost:8888 in your working directory to execute this file in your browser. Here is the result:

[
    [
        "Name",
        "Age",
        "Phone No"
    ],
    [
        "Alpha",
        "23",
        "2384523"
    ],
    [
        "Beta",
        "43",
        "4563235"
    ],
    [
        "Charlie",
        "21",
        "2323452"
    ]
]

See sample sheet for comparison.

Sure, the JSON looked ugly as hell. All you need to do now is to modify the response to fit your JSON model.

And bam, you have your own backend microservice which interacts with just a Google m***cking Sheets. Tell your staff to just use the Sheets to update data and let your front-end guy deal with the JSON response to feed it into his interface design.

Advanced usage

We have coded a very basic reading client from a spreadsheet. Basically, we just use GET method to read stuff from a sheet. There are other methods available on Sheets API which you can use to leverage your app to become more sophisticated.

Developing an admin panel front-end to store, update or create data to a sheet is recommended, if not, necessary to avoid direct editing from the sheet itself.

Pros and cons using SaaDB

Pros:

  • Don't have to code for admin panel (CRUD platform)
  • Cloud-enabled (lol) so every one can contribute to data entry
  • Plugins integrated -- Google Forms etc.
  • Using internal calculations using spreadsheet functions
  • For those lazy wanker

Cons:

  • Cannot do any relational database stuff like JOINing table and shit
  • Unless you have an admin panel front-end, direct data entry could be a problem as someone could destroy it by just changing the cell order making our code above unusable (have to be corrected when someone mess with the cells)
  • Only up to 2 million cells in a sheet
  • Data has to be publicly accessible (unless you keep the Sheet shared URL to yourself)
Show Comments

Get the latest posts delivered right to your inbox.