Fetch Data For Your React App From Google Sheets Using Tabletop.js
If you want to rapidly prototype your next web app or give your client some dynamic data control in a simple React app without creating a backend to fetch data from, this is the article for you.
Recently I needed a really quick and simple way to let a client of mine display and edit structured data. The catch was that given the small amount of time and the fact that the data had to be updated frequently by a non-technical person led me to look for some solutions other than implementing a backend just to do CRUD on a list of stores.
While searching for a quick and practical solution that fitted my needs I stumbled on a library called Tabletop.js, the library lets you use a Google spreadsheet as your Database or if being more precise it completely replaced the need for any backend solutions (at least for the simple project I was intended to use it for).
Using Google spreadsheets as a database for web applications is not a mainstream solution, and it may or may not be suitable for your next project, But given the fact that you can have a free cloud database with a GUI editor up and running in less than 10 minutes, it was something worth sharing.
Without further ado Let’s Get Start 🎉.
Step One: Create a Google Spreadsheet with Data.
Go to Google Sheets, create a new file, and fill it with your Data.
Please Note: that the first row will be the keys we which will define the “structure” of our data, so all columns should be named in the first row and may not contain strange characters ($, #, &, % …).
Each row in the table represents one object and each tab represents the attribute value of the key mentioned above (first row), in case there is an empty value it will return an empty string (‘ ’).
One more thing Google assumes that an empty row is the end of the data so it doesn’t return any rows after the first fully empty row.
After creating our sheet we need to publish it to the web, and we do that in the following matter: click on → file → publish to the web → publish
(see the figures below for a visual example).
Let’s move to our React app now we are going to need one more thing from the spreadsheet later-on.
Step Two: Install TabletopJS
Use one of the following commands in order to install the package.
Step Three: Let’s get it up and running in our React App
First of all, we are going to need a key from the query string (URL) of our google sheet so let’s go and copy it, please see the figure below the orange section of the query string is the key we need, copy it.
(it is individual for each spreadsheet we use).
Now let's go into our react app and implement tabletop.
We’re initializing the Tabletop inside the useEffect hook. The init function takes an object as an argument. This is where we place the information about our Google sheets. Notice the key property (the value colored in orange is the same key we have copied from the query string mentioned above).
Once Tabletop is initialized it is going to fetch data from the google spreadsheet and it returns a promise with the data or with an error.
please see the example below, it is really simple we just console log the data but you can do whatever you like with the data returned by Tabletop.
Well Done We Did It!! 🤘🏻
A few things before we finish
- We Covered only one simple use case of the Tabletop library feel free to click here for the documentation in order to dig deeper into Tabletop.
- If you are planning to use it in a large traffic application/website I would highly recommend reading the terms and conditions and look at the Google Spreadsheet API Documentation, to see if there are any limits to the number of reads in any period of time.
- Note: This solution is suited only for certain use cases and it’s doesn’t give you the amount of functionality that a backend server can or a REST API can.
I hope you found this article useful, feel free to give it a clap if you liked it 👏🏻, and till next time 💃🏻.