How to use Google Sheets As Your Website Database
To have a CMS you’ll need to have a database. If you’ve never used a database than using something like Wordpress or another CMS platform will have a learning curve and could be daunting to figure out how to use them. In addition, platforms like Wordpress have many moving parts and are more susceptible to breaking. WordPress just requires more maintenance.
On the contrary, something very familiar like Google Sheets, which is only a spreadsheet, is something most people already know how to use and it nowhere near intimidating to learn compared to Wordpress.
This method I will go over is free and will help you save money, but it’s not the best solution for websites that need advanced security protection. However, it is perfect for a designer or developer that wants to display their work or portfolio.
How do you use Google spreadsheets to act as your database though?
First, you’ll have to have a Google account and create a new spreadsheet.
If you don’t have a Google account, you can go here.
Under the Start a new spreadsheet option, click on the option that says blank. View the image below for reference.
When you create a new sheet it should look something like this:
The first row will be important. It serves as the titles to the data information we will input.
For example if you want column B to have data of dates, then in the first row of column B you will have the word date. In the following rows you will input the various dates you need.
The column titles should only be one word, no uppercase. If you need to use multiple words for the column title then insert a hyphen between the words. You can view the image below for reference.
Now that your Google Sheet is setup, you’ll need to link the spreadsheet to your website. To do that we’ll use an open source javascript called Tabletop. I recommend this because it’s free and easy to setup.
You’ll need to download the Tabletop javascript. It can be found here.
There are detailed instructions to setting it up on the download page, but I’ll give a brief walkthrough.
- Copy and Paste the below code into a javascript file for your website:
function init() {
Tabletop.init( { key: ‘https://docs.google.com/spreadsheets/d/0AmYzu_s7QHsmdDNZUzRlYldnWTZCLXdrMXlYQzVxSFE/pubhtml',
callback: function(data, tabletop) {
console.log(data)
},
simpleSheet: true } )
}
window.addEventListener(‘DOMContentLoaded’, init)
2. Substitute the link where it says ‘key’ with your Google spreadsheet key.
To find your spreadsheet key, go back to your Google Sheet.
The key is in the URL and is after “https://docs.google.com/spreadsheets/d/ “ and before the last “/”.
The key will be letters and numbers and possibly have hyphens or underscores.
When you run this on your website, it will have a problem generating your data and that’s because you need to publish your spreadsheet.
Go back to your Google Sheet and go to File > Publish to the web
You will get this dialog menu.
Make sure you have entire document selected in the dropdown option. You can choose to automatically publish changes by checking the feature on the bottom. If you don’t do this, you will have to publish the spreadsheet every time you make a change.
By using Tabletop.js you can connect your Google Sheet to your website. Using Google Sheets as your database allows you to avoid learning a new CMS platform, but if you already know something like Wordpress, you can still use Google Sheets as it doesn’t require as much maintenance. Plus coupled with the insights I provided on how to get free hosting, this way will save you money.
With that said, this is a easy and free way to get a CMS up and running relative to other options. Stay tuned for next week as I show you how to get your website to loop through your database data.
