Build A Complete Back End In 20 Minutes With Google Sheets (A Hack For Beginners)
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.
Who This Is For:
- Beginner web developers that do not know any back-end languages yet, but would like to build dynamic websites without spending hundreds of hours learning a modern web development language + database.
- Lean startup practitioners that want to build a Minimum Viable Product (MVP) of their site
- Designers that want to rapidly prototype a new feature or mockup and test with users, and do not need a perfect back-end, just a functional back-end.
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
- Create a Google Account if you do not have one already.
- Open up Google Drive, and create a new Google Sheet. Give it a name related to the data you will be storing there. You may even want to create a new folder for sheets that are used as databases.
- In the top left of the sheet, there should be a “Share” button. Click that, then a dialogue will appear. Click “Get Shareable Link” in the top left, and set the permissions to “Anybody with the link can edit”
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.
Step 2: Use jQuery to interact with your Google Sheet
- Add some data to your Google Sheet that you would like to interact with. Make sure that the first row has your column labels. Also, the first row of data after that must have at least one number, for an unexplained reason. Blockspring has their own tutorial on querying a database using a card game. Here is their Google Sheet for an example.
- You are going to be using the Append Values to Sheet and Query Public Sheet functions. Let’s do Query Public Sheet first. On that page, on Step 1: Choose your tool, click Code on the far right. It should give you a code snippet.
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:
- This approach will not work for hundreds or thousands of users, so it is not a permanent solution if you are trying to gain a large user base.
- This approach is not very secure, and you should not store any information that needs to be protected. This includes things like passwords and payment info.
Best of luck with your projects!
Did this work out for you? Let me know in the comments!