How to Use Google Spreadsheets as a Database (Free Blog Hosting Included)

RedaCodes — Fri, 20 May 2022 08:35:23 +0000

Thanks to Papa Parse & GitHub you can host the cheapest simple static blog with a database completely for free.

We all have that one friend who has their own tiny super niched blog but most of the time that blog is either down or super slow.

The problem here is that most of these people know little to nothing about managing a website and they often ask us for advice, which most of the time will be “go with WordPress”.

But sometimes even WordPress can be a pain to manage due to its size, cost and support if you want to have a solid blog.

To solve this issue I came up with the cheapest, easiest plan to host a simple static blog with a database completely for free (Excluding the cost of registering a domain which can be as low as 0.99$ for your first year).

Now let’s create our new blog, I’ll call it The Cats Blog because why not.

For the blog template I’ll be using React to keep it simple I’ll create a single component app that manages everything.

For the database go to google spreadsheets and create a table with all the data that you want to import.

In my case, I created a dummy table that contains Id, Title, Body, and Image.

Importing the data to our website

To import the data we will be using a tool called Papa Parse.

On your project folder terminal run:

npm i papaparse

Voila! You have the tool let's get us some data!

const Papa = require(“papaparse”);
const filePrasing = () => new Promise((resolve)=> {
  Papa.parse(“YOUR GOOGLE SPREADSHEETS LINK", {
   download: true,
   header: true,
   newline: “”,
   complete: function(results, file) {resolve(results.data);}
  })
});

const getData = async() => {
  const data = await filePrasing();
  return data
}

Note: In order for Papa Parse to parse your sheet it must be publicly shared as a CSV.

Now after calling getdata() Papa Parse will get the data from our spreadsheet and returns a promise with all our Data inside one single object.

[
 {
   “ID”: “1”,
   “title”: “ARTICLE TITLE”,
   “body”: “<p>ARTICLE BODY IN HTML</p>”,
   “image”: “IMAGE LINK"
 },
 {
   “ID”: “2”,
   “title”: “ARTICLE TITLE”,
   “body”: “<p>ARTICLE BODY IN HTML</p>”,
   “image”: “IMAGE LINK”
 }
]

To do this using react I’ll be using the react hook useState to manage our state and useEffect to update the state when the website renders.

/* our data variable */
const [data, setData] = useState([])
useEffect(() => {
  getData().then((res)=> setData([…res]))
}, []);
/* our data variable */

Blog Layout

For the blog layout, our component maps over our state and displays the data on the front end. Also to keep it simple, when the user clicks on an article it’ll be shown on a popup that contains the article’s body due to react being a Single Page Application Framework.

/* Article popup */
  const loadArticle = (id) => {
    document.getElementById('article').innerHTML= data[id-1].body //adds the article body to the articleParagraph/article <p>
    document.getElementById('articles').style.filter = 'blur(10px)' //adds the blur effect to the background
    document.getElementById('articleParagraph').style.visibility = 'visible' //makes the articleParagraph <div> visible
    document.querySelector('body').style.overflow ='hidden' //Disables the website scrolling feature
  }
/* Article popup */

/* Article close toggle */
  const hide = () => {
    document.getElementById('articles').style.filter = 'blur(0px)' //Removes the blur effect
    document.getElementById('articleParagraph').style.visibility = 'hidden' //hides the articleParagraph <div>
    document.querySelector('body').style.overflow ='scroll' //enables the website scrolling feature
  }
/* Article close toggle */

  return (
    <div className="App">
      <div className='header'>
        <h1 className='logo'>The Cats Blog</h1>
      </div>
      <div id='articleParagraph' >
        <p id='article'></p>
        <img src="../close.svg" className='closeIcon' onClick={()=>hide()} />
      </div>
      <div className='articlesContainer'>
        <div id='articles'>
          {
            data.map(
              (el)=>(
                <div className='block' key={el.ID} >
                  <h1 className='articleTitle' onClick={()=>loadArticle(el.ID)}>{el.title}</h1>
                  <img src={el.image} className='image' onClick={()=>loadArticle(el.ID)}/>
                </div>
              )
            )
          }
        </div>
          <div className='footer'>
            <p className='footerText'>This web site was created by Reda-codes</p>
            <ul>
              <li><a href="https://github.com/Reda-codes" target="_blank">GitHub</a></li>
              <li><a href="https://www.linkedin.com/in/reda-med/" target="_blank">LinkedIn</a></li>
            </ul>
          </div>
      </div>
    </div>
  );
}

Note: To get All the code above please go to the project repository on GitHub.

The end Result

Finally, our blog is done and functional the only thing left is to host it.

Free Blog hosting

Due to our blog being a static web page that fetches data from our Google Spreadsheets on every load we can host it on GitHub pages for free and in case you want to have a custom domain for your blog, GitHub allows that for free.

This project was just a demonstration of how you can use Google Spreadsheets as a database and not a react tutorial, you can build a more complex blog if you use other frameworks that require a server.

To get all the code for this project head to the project repository on GitHub.

DEV Community: RedaCodes

How to Use Google Spreadsheets as a Database (Free Blog Hosting Included)

Importing the data to our website

Blog Layout

The end Result

Free Blog hosting