Using Google Sheets as a Database

In Snapstream Searcher, a wep application I'm developing, I want to enable the user to search for specific lists of programs. Thus, this would require creating a database where users could manage and edit these lists. Rather than creating a nice RESTful API to manage the reading and editing of this database, I naturally decided to be lazy and just have users use Google Sheets instead.

Doing this wasn't actually as straightforward as I thought it would be, so I'm writing some notes to myself on the process. There are several moving parts here:

the Google sheet itself
App Script to create an API to get the sheet data
Google Developer Console to create an OAuth token, so you can use the API
client-side JavaScript to call the API

First, here's a working example. You can edit this sheet. When you edit it, you will see the changes reflected here, JSFiddle. Here's the API that I created in this app script. You'll probably need to authorize yourself with a Google account and refresh the page for it to work.

Directions

Create a Google Sheet in your Google Drive and make it editable by users with a link by clicking share in the upper right.

Go to the Script Editor and make your API to get the sheet data. You can find a detailed reference here, Spreadsheet Service. The API seemed pretty intuitive. You can copy and paste this code in and change the URL to your document's:

 function getData() {
     // link to your google doc
     var spreadSheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/<YOUR DOCUMENT>/edit');
     var sheet = spreadSheet.getActiveSheet();
     var dataRange = sheet.getDataRange();
     var values = dataRange.getValues();
     return values;
 }

Now, follow Step 1 of this guide to make an OAuth token: JavaScript Quickstart
Then, deploy as an API according to this guide: Using the Execution API

Now, on the client-side create an HTML file, something like this, where you substitute your project key and client id. If you need other scopes, check Project Properties in the Script Editor.

 <!DOCTYPE html>
 <html>
   <head>
     <style type="text/css">
      table {
        border-collapse: collapse;
      }
      td, th {
        padding: 10px;       
        border: 1px solid gray;
      }
     </style>
     <script>
      var CLIENT_ID = '<CLIENT_ID>';
      var SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
      /**
       * Check if current user has authorized this application.
       */
      function checkAuth() {
        gapi.auth.authorize(
          {
            'client_id': CLIENT_ID,
            'scope': SCOPES.join(' '),
            'immediate': true
          }, handleAuthResult);
      }
      /**
       * Handle response from authorization server.
       *
       * @param {Object} authResult Authorization result.
       */
      function handleAuthResult(authResult) {
        var authorizeDiv = document.getElementById('authorize-div');
        if (authResult && !authResult.error) {
          // Hide auth UI, then load client library.
          authorizeDiv.style.display = 'none';
          callScriptFunction();
        } else {
          // Show auth UI, allowing the user to initiate authorization by
          // clicking authorize button.
          authorizeDiv.style.display = 'inline';
        }
      }

      /**
       * Initiate auth flow in response to user clicking authorize button.
       *
       * @param {Event} event Button click event.
       */
      function handleAuthClick(event) {
        gapi.auth.authorize(
          {client_id: CLIENT_ID, scope: SCOPES, immediate: false},
          handleAuthResult);
        return false;
      }

      function callScriptFunction() {
        var scriptId = "<PROJECT_KEY>"; // aka Project key
        // Create an execution request object.
        var request = {
          'function': 'getData'
        };

        var op = gapi.client.request({
          'root': 'https://script.googleapis.com',
          'path': 'v1/scripts/' + scriptId + ':run',
          'method': 'POST',
          'body': request
        });

        op.execute(function(res) {
          var htmlTable = '<table>';
          res.response.result.forEach(function(row, idx, rows) {         
            if (idx == 0) {
              htmlTable += '<tr><th>' + row.join('</th><th>') + '</th></tr>';
            } else {
              htmlTable += '<tr><td>' + row.join('</td><td>') + '</td></tr>';
            }
          });
          htmlTable += '</table>';
          document.getElementById('output').innerHTML = htmlTable;
        });
      }
     </script>    
     <script src="https://apis.google.com/js/client.js?onload=checkAuth">     
     </script>
   </head>
   <body>
     <div id="authorize-div" style="display: none">
       <span>Authorize access to Google Apps Script Execution API</span>
       <!--Button for the user to click to initiate auth sequence -->
       <button id="authorize-button" onclick="handleAuthClick(event)">
         Authorize
       </button>
     </div>
     <div id="output"> 
     </div>
   </body>
 </html>

To test it out, you can run run a local server with python -m http.server 8000. Make sure you've added http://localhost:8000 to your Authorized JavaScript origins when creating the OAuth token.
You should see your sheet in the form of a HTML table.

Tags: javascript, google

New Comment

Comments

No comments have been posted yet. You can be the first!

About Me

Using Google Sheets as a Database

Directions

New Comment

Comments