Development

Quantified Anything: Using Google Sheets as a Database to Populate Your Website

September 30, 2022

Here at Zelolab we love spreadsheets, and we use Google Sheets to track everything from our personal eating habits, scheduling our product launches, to collecting product feedback.

One trick we have used over the years when creating quick proof of concept apps is to use Google Sheets to collect, store and supply the data to a front end app. This is certainly not recommended for apps that will have lots of users, large data requirements, or generally falls under any kind of “serious” website category, but it can save loads of time when trying to get something up for a hackathon.

Today we will lay out the groundwork on this technique and continue the fine tradition of using something on the internet for something other than its intended purpose.

Setup

First things first, create the spreadsheet that will drive our application.

New Google Sheet, Blank
New Google Sheet via my most commonly used bookmark: https://docs.google.com/spreadsheets/u/0/create

Data Input:

If you want to manually create columns and rows, start going wild. If you want to collect data from yourself or others in a structured way, the easiest thing to do is to setup a form which will populate the sheet. Create a new form by going to “Tools”->”Create a New Form”

Create a new form under the "Tools" menu of your sheet.
The “Create a new form” menu option

You can now create questions which will end up populating the columns and rows of your spreadsheet.

Once you are finished creating the questions you want to fill in, click the big “Send” button up top and grab the shareable link. You can share via email, a link, or even get an embed code to put on your website.

Share the form to via the generated link.
Share via link, shorten the url for your sanity.

Make sure you check the settings and configure if you want the form to be public or private before sharing as well.

*Side note: don’t accidentally share the editor link, this confuses the recipient and messes up your questions and data in a way that is impossible to untangle as the user will edit the question as their response. This lesson was learned the hard way, and is just one more reason why this whole endeavor is a bad idea.

If everything worked, you should now have a nice form for you or random strangers to fill out. Here is the link for the demo form https://forms.gle/RAroMgPgWDKHyHvC7, feel free to check it out and add some data.

What a beautiful form

Collect Some Data

Time to give our database some data, Data: it’s what the database wants.™

Filling in a few example response on our forms, we start to see the automatically generated “Form Responses 1” tab getting filled out. In addition the the question answers, we get a handy timestamp.

Some sample data, from someone (me) who is unlikely to follow the advice of not using this technique

If you are interested in other ways of sending data to a spreadsheet, check out the many integrations offered by zapier, or for the more technically inclined, check out the google-spreadsheet node package.

Pull The Data into a Website

Now for the fun part, pulling the data out of the Google Sheet, and displaying it on a site.

Publish

The first step is to make sure your sheet is public, and that it publishes changes to the web. To make the sheet public, click the large “Share” button up top, and set “General Access” to “Anyone with the link” to have “Viewer” access.

Sharing settings set to public viewing access.
Set the data free

The next step is to setup “Publish to the web”. Click the “File” menu, and select “Share”->”Publish to Web”

File->Share->Publish to the web

This will bring up a dialog box that allows you to pick which tab you want to publish (in our case, the form responses tab) and what format. Pick your tab and select “Comma-separated values” as the format.

Picking .csv on the publish dialog
.csv ftw

Click the “Publish” button and copy the link that they generate for you. If you did it right the link should end in “&output=csv”. Save this link for later.

Pull Data with Javascript

Now that the data lives somewhere public on the internet, we can use javascript to pull it into our application or webpage. At this point we are going to start doing some javascript, so if this doesn’t look like the matrix to you, then proceed, we are almost done!

var sheet_csv = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vQZBb0hdC5AnYmi445Ie0mDKuH6ipRjwmSZmEJppOLviHJnW1jLSow3jjwBO1QT-y_raxlEvzDC28b2/pub?gid=1752595225&single=true&output=csv';

We need to fetch the csv, and then parse it. A .csv is ultimately just a text file, where each row is separated by newlines, and each column is separated by commas. Rather than build some sort of hand rolled, string splitting, regex using, edge case handling, text file parsing function, we are going to use the excellent, mature, and well documented csv parsing library called PapaParse. So the plan is to fetch the .csv with ajax, then parse the code, then do something with the parsed data.

//fetch the data

//parse the data

//do something with it

First let’s add the data fetching.

//fetch the data
var sheet_csv = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vQZBb0hdC5AnYmi445Ie0mDKuH6ipRjwmSZmEJppOLviHJnW1jLSow3jjwBO1QT-y_raxlEvzDC28b2/pub?gid=1752595225&single=true&output=csv';

fetch(sheet_csv)
  .then(function(response){return response.text();})
  .then(function(data){pasrseData(data)});

Now create the data parsing function, note the “header:true” option, see the Papa Parse docs for more info.

//parse the data
var parseData = function(data){
  var gson = Papa.parse(data, {header:true}).data;
  renderData(gson);
};

The data will now be a nice array of objects.

An array of objects representing the data found in the google sheet.
A Google Sheet as JSON? GSON?

What you do with the data at this point is up to you, here is a full workable script to take your sharable csv link, and log the data into the console: https://gist.github.com/crismanNoble/9c8a477a97d4330be4c342c5c5e2ff6b

As a quick example, here is a jsfiddle that uses that same technique and creates a nice chart of the data. https://jsfiddle.net/Lpd5gb10/

Simple charting example
🤯

If you fill in the form, then refresh the fiddle, you should see new data.

Updating & Deleting Data

Any good database needs full CRUD support. We have already shown how you can Create, and Read the data. So how do we Update or Delete the data? To me this is the real advantage of using google sheets, no need to login to a database or use SQL commands, just open up your sheet and delete rows you don’t like! Edit any cell to your heart’s desire. Share edit access with your team and do it all in realtime! With a great power comes with no responsibility of maintaining database migrations or creating backup files, sounds pretty good for a hackathon to me.

Wait, Why?

I know that Google Sheets could create that chart natively, they even have a way to publish charts as embeddable widgets. But hopefully if you are reading this article you have some ideas of how you could get creative with pulling data out of a spreadsheet.

Here are a few ideas of how you could get creative:

  • Set up a shared form where all of your friends input their favorite GIF urls, your webpage could randomly cycle through them.
  • You could keep a list of all of the places you have visited, and what you did there. Use the MapBox api to display them as an interactive journaling map.
  • A list of books you have read displayed as an awesome timeline.

Will it Scale

No, this technique will probably not scale. It is fraught with problems, not the least of which is relying on Google to never shut down Google Sheets (RIP stadia) despite it not making any ad money. There is no sanitization of user inputs. If you delete your sheet, your data is gone forever, and your application will break. There could be undocumented reads per second limits on the csv endpoint, there are certainly write access limits, there could be a limit to how many rows it will return without any way of knowing there are more. For anything serious, you are going to want a real database on hand.

Get the latest news.

Sign up to our email list and be the first to know when we launch new tools and publish resources.