Using a Google Sheet as Database for Web Page
I recently had a request to create dynamic content that could be easily controlled via a google docs account. After a little research and a small proof of concept I found that there are some very simple great ways to utilize google sheets as a database to drive dynamic content on a webpage. Since I ended up piecing together what I learned from many different sites and tutorials I decided to consolidate the various approaches here for an overview of the options available (I’m certain this isn’t comprehensive).
If your Google Sheet is large (or will become large over time) you may want to consider leveraging the Google Visualization API in order to reduce the data being returned from the Google Sheet. This will offload processing from the “Apps Script Execution”, “Server” or “Client Browser” depending on your implementation.
Google Sheet as JSON
- Very Simple to Implement
- A simple get request to google sheets retrieves the sheet as a JSON object
- Doesn’t rely on your web server, distributes load to the client browser and google docs service
- Reed Only
- There isn’t a way to control access to the Google Sheet using this method without making the document editable by anyone – this isn’t generally a practical solution.
- Public Sheet
- Requires the sheet you are using to be publically readable and published
- Exposes Sheet URL
- Since this is implemented on the webpage it exposes the URL to a publically available sheet within the webpage source code
Client Side Google Apps Script Execution (Public/Private Sheet Access)
The Google Apps Script Execution service allows you to create publish a web service that you have written which can access google sheets. The main reason for utilization the Script Execution service is that you can request access to any individuals google docs via their google credentials (leveraging OAuth) without having to get into implementing understanding the authentication details.
Although I haven’t implemented a solution which allows for anonymous access to a Public Sheet, the POC I’d like to do is to utilize a key to do authentication to the Apps Script Execution which gives access to a specific sheet. This exposes the web service you implement in the Apps Script Execution service since the key would be in the webpage, but it does create a barrier to the public for directly accessing the google sheet.
- Hide Google Sheet URL
- By including the URL in the Google Script Engine and not the webpage source, users can’t simply view the page source to get the URL. The access to the sheet data is limited to what the webs service exposes.
- Read/Write Potential
- You could potentially implement authentication in the Apps Script Execution web service to allow writing to the google sheet from the web via the web service endpoint behind authentication.
- Your web server is not going to become a bottleneck, distributes load to the Google Script Engine and google docs service.
- Expose Server Key
- Not sure what the real downside to this is…
- More complicated to implement
- Google Sheet must be publically accessible
- Although the google sheet URL isn’t available in the webpage source, you still have to create a publicly accessible sheet
Server Side Google Apps Script Execution API (Private Sheet Access)
If you create a service from a web server that calls the Google Apps Script Execution from the server directly you can hide the key required to authenticate (since it will reside on the server). You could implement some server side authentication for write access to the sheet.
I’m not entirely sure what the advantage to doing this would be over putting the key in the web page source code, since you should be able to implement any logic you would need the the Google Apps Script Execution service… maybe giving up the server key for authentication could allow someone to abuse the number of calls to your service, but I imagine you’d have the same issue if folks are hitting your server side implemented service first any way.
- Hide the URL to the Google Sheet
- By including the URL in the Google Script Engine and not the webpage source, users can’t simply view the page source to get the URL
- Read/Write Potential
- You could potentially implement authentication in either the web server service or the Google Apps Script Execution web service to allow writing to the google sheet from the web via the web service endpoint behind authentication.
- Hide the server Key
- Google Sheet can be Private
- You can setup a key on the web server to allow access to a private google sheet, this keeps your google sheet from being accessed by anyone who hasn’t been given permission
- Complicated to Implement
- Call from web page to your web server
- Call from web server to google script engine (provides support for authentication to private sheet) which contains key for authentication
- Call from google script engine to google sheet
- Limited Scalability
- Since your web server is a part of the chain it can become a bottle neck in the process
- Decreased Performance (Latency)
- There are more calls involved here, so there will certainly be a performance impact
Googe Data API’s – Spreadsheet Samples (Depricated)
Simple example of accessing a Google Sheet as a json object
Simple Google Sheets API
This is the most current (as of Dev 2015) API which is much more powerful than the “Google Apps > Sheets API” and can be accessed via the Apps Script Execution REST API.
Google’s documentation for the Apps Script platform, this allows you to create a web service api endpoint that can interact with google Sheets, Docs or Forms.
If you are working with a large data source you can query a Google Spreadsheet like a Database with Google Visualization API Query Language.