Image for post
Image for post

Build A Complete Back End In 20 Minutes With Google Sheets (A Hack For Beginners)

Only front-end skills required. Learn how to set up a full stack web app + database with just HTML, CSS, JavaScript and jQuery (and a Google account!)

Kevin Kononenko
Apr 18, 2016 · 7 min read

Want to learn how to develop dynamic web sites and mockups, but don’t have any back-end skills like Node.js, Ruby or Python (yet)? Then this guide is for you! I am going to show you how to use two quick Blockspring jQuery functions (Append Values to Sheet and Query Public Sheet) and Google Sheets to bring full-stack capabilities to your front-end.

What You Will Be Able To Build: A dynamic website that lets you read and write to the database.

The Skills You Need To Know: HTML, CSS, JavaScript, jQuery, Ajax basics

Who This Is For:

Example: You can check out my example site here. It uses two separate Google Sheets to give users dynamic results based on their answer to a quiz.

Pros Of This Approach:

  • It can be set up nearly instantly after you create your accounts
  • Google Sheets is a performant database that supports both reading and writing.
  • The familiar Google Sheets interface makes the process very transparent, and eliminates any confusion associated with learning a completely new language.
  • The interface can be shared with teammates of all technical abilities so that they can contribute to the project via the database.
  • Google’s Query Language is easy to learn.

Cons Of This Approach:

  • It can be a little slow i.e. 3 seconds to complete a GET request.
  • There is no way to update or delete entries via code, although you can always manually do this within Google Sheets.
  • It is not a secure approach and should not be deployed at scale if you are looking to permanently build your back-end with this method.
  • Google’s Query Language is a little limited and cannot do table joins like SQL.

Cost: The Blockspring API is a key component of this approach. It is free for the first 14 days, but costs $7 a month after that. You can easily build your first prototype in those first 14 days to test it out. You can also deploy it for free with Github Pages.

Words of Caution/Disclaimer: This is NOT a secure approach, so please do not ask users for any data that might be sensitive like payment info or passwords. I will add an optional step for including some security measures, but I still would not recommend asking for sensitive info in that case. It is far too easy to download. Google Sheets includes version control, so you can always see the history of the database and revert back to a previous version.

Step 1: Setup

Image for post
Image for post

4. Now you need to add the Blockspring plugin. Click “Add-ons” in the top bar, then “Get add-ons”. In the search bar, type “blockspring”. Click the Add button for the official Blockspring add-on.

5. Go to the Blockspring site and create an account with the same Google Account that has the Google Sheet.

6. Add the following code snippet to the HTML file of the project you will be using. This allows you to use Blockspring in your code. It is dependent on jQuery.

<script src="https://code.jquery.com/jquery-1.10.1.min.js"></script>
<script src="https://cdn.blockspring.com/blockspring.js"></script>

Step 2: Use jQuery to interact with your Google Sheet

For reference throughout this section, you can check out my index.js that works with my example site.

Image for post
Image for post

3. Here is a line-by-line breakdown of that code. Google uses a SQL-like syntax, documented here. If you do not have any SQL experience, it might be useful to learn the basics. This Codecademy course will cover that. You will be using a limited number of operations that are very similar to Excel.

You can apply this function on any data you want to use from the spreadsheet. Line 6 contains the key part- the data you want to select. The results are lined 14–15 in res.params.data.

4. Let’s move on to Append Values to Sheet so you can add items to your database with your code. Again, choose “Code” from the tools. Here is the relevant code.

The Append Values to Sheet page will have the relevant API key on line 16. There are two easy places to get tripped up here. First, the file_id is not the same as the URL- it is just the middle parameter. See line 9 for instructions. Second, the data structure. Blockspring requires you to use nested arrays. Everything that you pass in will be added to the sheet, so you cannot have column names in your array. It is somewhat similar to SQL’s INSERT INTO syntax. Each array must have one row of data, and be in the same order you want to insert. All rows will be added at the end of the existing values. Like this if you have a two column sheet:

[[row1val1, row1val2],[row2val1, row2val2],[row3val1, row3val2]…]

And that is pretty much it! Again, you can check out my index.js for an example.

Optional: A More Secure Approach

Keep in mind that the method above will require you to expose your Google Spreadsheet. So if anyone inspects your file, they can find the sheet and mess around with it. If you are planning to share your project with users beyond your friends and people to test with, it might be wise to conceal the spreadsheet. I wrote a quick fix to alleviate this.

I wrote a quick function on Blockspring called “Universal Get/Post” which will allow you to keep your spreadsheet links hidden. You can check it out here. To use it on your own site, all you need to do is click Publish in the bottom left and you will get access with your own API key. Make sure to make it “closed source” as well.

Image for post
Image for post

All you need to do to use it properly is change lines 11–18. Lines 12–13 are examples of GET requests with this API. Your function name from the front-end needs to match one of the keys, in this case, “getTitles” or “getDetails”. The value is the URL of the corresponding sheet.

If you want to do a POST request, or add data to your sheet, check out lines 14–17. Line 14 must match the function name from the front-end. Lines 15–16 should include the file_id and worksheet number. This approach scales infinitely. If you want to add more functions, just add more key/value pairs to the URL hash object. The rest of the file should work without touching it.

This function will replace the functions above. Here is how you call it on the front end.

You can also check out line 45 and below of my other index.js to see this approach in action

Step 3: How to Use It/Conclusion

And now you can build your fully featured web app! Just use the two methods to get and post data. It will work on both your local system and deployed live. You can use GitHub Pages if you want to quickly get it online.

Each Google Sheet basically serves the same purpose as a table in SQL. If you want to develop more complicated services, you may need more than one sheet. This approach also supports multiple sheets. If you need to grab data from two different sheets, just nest one of the Blockspring calls within the callback of the first. Check out line 57 of my index.js file if you want to see this approach in action. If you do not know callbacks very well yet, I wrote this guide to explain them.

Again, a couple warnings:

Best of luck with your projects!

Did this work out for you? Let me know in the comments!

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store