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

  1. 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.
  2. Lean startup practitioners that want to build a Minimum Viable Product (MVP) of their site
  3. 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.
  • 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.
  • 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.

Step 1: Setup

  1. Create a Google Account if you do not have one already.
  2. 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.
  3. 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”
<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

  1. 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.
  2. 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.

Optional: A More Secure Approach

Step 3: How to Use It/Conclusion

  1. 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.
  2. 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.

--

--

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
Kevin Kononenko

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