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!)

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:

Cons Of This Approach:

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

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.

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.

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!

Founder of CodeAnalogies (www.codeanalogies.com). Self-taught web developer. Passionate about not making same mistakes twice. Only new mistakes!

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