Google sheets as data source
- Using with JS: https://developers.google.com/sheets/api/quickstart/js
- https://support.google.com/datastudio/answer/6295223?hl=en#zippy=%2Cin-this-article
- https://www.brainlabsdigital.com/blog/google-data-studio-using-google-sheets-as-a-data-source/
- https://developers.google.com/sheets/api/guides/connected-sheet
> Google broke some MORE stuff, so while this will work you’ll need to – jump through a few hoops(https://github.com/jsoma/tabletop/issues/189).
One important note is **this won’t work with Publish to Web if you have an organizational account.** Like if I use my `@columbia.edu` account Google pretends I’m a terrible criminal and won’t give me access via Papa Parse.
This also won’t work if you’re opening up an html file on your computer like a Normal Person, it requires you to either put it on the internet or run a local server. There are worse things, I guess.
But hey, as of September 2020 it’s this way or the highway!
> Google broke some MORE stuff, so you just plan might not be able to do this any more. If it’s a PRIVATE project, you can get an API key and [jump through a few hoops](https://github.com/jsoma/tabletop/issues/189). But beware that this [exposes your API key to anyone visiting the site](https://github.com/jsoma/tabletop/issues/187#issuecomment-650942492), which allows people to do whatever they want with your data and run up bills on your end, so you probably don’t want this on something facing the world.
https://medium.com/@jaejohns/how-to-use-google-sheets-as-your-website-database-b0f2f13d0396
How to use Google Sheets As Your Website Database
Setup Free Database with Google Sheets
Table of Contents:
- The Intro — Click Here for Article
- Setup Free Hosting and Domain — Click Here for Article
- Setup Free Database with Google Sheets
- Link Google Sheet Database to Website
- Add Javascript to Get Database Data to Loop Click Here for Article
- Add Search Function to Website Click Here for Article
- Add Non Php reliant Contact Form Click Here for Article
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.
Get more insights like this on my blog, jaejohns.com/blog