Using Google Sheets as a database for simple web applications

data
web development
Published

January 31, 2024

Often when creating simple web apps you want a small and simple database for the data. It is also often a requirement to give someone else access to the database, and to allow them to make additions and updates.

To set up a database and admin system on a web server is a fairly big task, and then that system requires long-term updating and maintenance. Using Google Sheets gets around that. Although not a perfect solution (Google could always close down Sheets one day, although that seems unlikely), it has a lot of advantages.

To be able to access Google Sheets data is relatively simple.

Import data into Google Sheets

Firstly, let’s export the data in JSON format from our existing database and import it into Google Sheets. Import a JSON feed into Google Sheets is actually more complex that I had imagined - I thought it would be a single Sheets function (I’m sure it used to be, perhaps I imagined that!?). Here’s the process:

  1. Open Google Apps Script Open Google Apps Script by clicking Extensions in the main menu, then selecting Apps Script.

  2. Copy this script into the Apps Script box: https://gist.github.com/paulgambill/cacd19da95a1421d3164

  3. Then you can use the function in a cell in a Google Sheet.

=importjson(...

Get the data from the sheet

Once imported, we can copy the values and then paste them again so we just have the values in the sheet. The sheet can be exported in JSONP format as follows:

  1. Make the Sheet accessible to anyone with the URL.

  2. Get the URL.

  3. Change /edit#gid=0 at the end to /gviz/tq?tqx=out:json&tq&gid=0

The JSONP format actually isn’t very nice for simple applications - it names the data by rows and columns rather than using the column headers. The CSV format may be better. Just change json in 3 above to csv.

References