Google Sheets with SQL

The Google App Suite ( or G-Suite) of products is a powerful and free alternative to the traditional Office Suite and includes among others Docs, Sheets and Slides. Google Sheets offers full spreadsheet functionality and in this post and sample, we show how data stored in Google Sheets spreadsheets can be accessed with SQL. This post assumes that the reader is familar with standard spreadsheet terms like sheet, range and functions.

Google Script

The primary programming language used here is Google App Script, a javascript look alike that can be used to enhance and extend G-Suite products. Google script can also be interleaved with traditional HTML / CSS / JavaScript to create an elegant user interface as is shown in this example.
A Google Script project sits in the Google Drive just like any other Sheet, Doc or Slides and consists of code in a code.gs file along with html files for the interface. Google Script functionality can be built into other Google products but in this example, we build a web-app that can be called independently with its own URL or can be embedded into an iframe :

So how does one start with this new technology?

The developers guide to web apps is a good place to start and build a traditional Hello World application that creates and serves an HTML file. Then things turned a little difficult because it is not directly possible to make pure SQL calls from Google Script to Google Sheets.

A quick search through and post on Stackoverflow threw up two possible ways of addressing this problem and both these two approaches have been demonstrated in this application. However there were more challenges to overcome! How to navigate from one HTML page to another, how to store global variables that will carry data across functions and pages and how to embed the app inside an iframe. After sorting out all this the web app was up and running but it was looking rather clumsy and so there was a need for some cute CSS for the input form and the output report. But all this CSS had made the code almost unreadable and so there was a need to remove the CSS and Javascript code into external files and have them included in the HTML.

Before the app can be used it is nice to keep the following points in mind:

1. For the googleViz route, the underlying spreadsheet must have public access, though this does not seem to be necessary for the query formula route
2. For gViz, the name of the worksheet is adequate but for the query route the worksheet name followed by the cell range is necessary
3. The spreadsheet document needs to be identified by the document ID that is a part of the URL that leads to the sheet. However the URL to the sheet can also be used by calling a different function that was tried but then commented out.

The source code for this web app -- with all script code, html, javascript and CSS -- is available in G-drive. You would need to login with your Gmail id to inspect it and use it if you like this approach.

---

Some known Issues :

1. The worksheet names should not have spaces
2. Sometimes, the last displayed report shows up instead of the new one. Reloading the page, fixes the problem 
3. Multi-table joins are of course not supported! That is where you need a real RDBMS