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.

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”

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.

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.

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.

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.

The next step is to setup “Publish to the web”. Click the “File” menu, and select “Share”->”Publish to 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.

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.

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/

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.