Using Google Sheets as a Makeshift Database for React Apps
We would like a custom-made website and a little birdie told us React is what the cool kids are going for these days — like, it’s faster, scalable, and easy to update compared to Wordpress? But we want to be able to update the content ourselves. Like, everything. Oh, Post…what? Postgres? What’s that? No, we don’t know MySQL either. Firebase? I feel attacked by yet another tech jargon! Why can’t we just use Google Sheets?! — Well, yes, now you can.
I do apologize for comparing React to Wordpress. They are completely distinct beasts and serve different purposes — React is a Javascript framework and Wordpress is a Content Management System (CMS). One can tie Wordpress to a chair and go all Hostel (all three parts) on it, or praise it like the Patriots fans worship Tom Brady (sorry, I am an Eagles fan always and forever). But I, like most of the other folks I know, have moved on from Wordpress. For varying reasons — reasons best suited for Reddit, I think. Okay, back to React things.
Background
I ran the FAWN Initiative (Free and Accessible Websites for Non-profits) a couple of years ago as part of my volunteer startup EvoXLabs. Mostly, these were informational websites, which would need updates every so often. However, updates weren’t part of the deal as the idea behind the initiative was to promote and advocate for accessibility in websites. This is also one of the reasons why React was the choice — not because it magically made websites accessible but because it didn’t comprise of a bunch of visually and functionally appealing widgets that were made with absolutely zero accessibility in mind. Yeah, Angular and Vue would have been almost the same for that matter, but React was the poison. Anyway, updates. For updates, there were two ways: either the organization’s staff/volunteers were comfortable with React or they were able to navigate their way through traditional databases. This, very quickly, became an issue and together with a lack of resources to keep up with the demand, FAWN Initiative became inactive and has been since.
Enters Google Sheets
About a year ago, I started thinking of other ways I, as a React developer, could contribute to the causes I cared about. Could I still make websites for non-profits without being the bottleneck for them to add/remove/modify content themselves? So, I started asking around what’s the most common tool on the Web that people use, generally, to store and fetch information. In the tech world, the answer to that is a list that would be a book of its own. Outside of the tech world, there’s only one answer that significantly emerged — Google Sheets. Excel/CSV files were the second on that list but people were hesitant about the whole process of making changes and deploying files. Fortunately, Google Sheets, like most other Google products, has a pretty awesome API. And where there’s an API, there’s light.
Google Sheets has no comparison to a traditional database and is by no way a replacement for traditional databases. But one place where Google Sheets as a makeshift database could be really beneficial is its ability to track history out-of-the-box, which in traditional databases is handled via additional means.
Let’s Get Technical
Okay, so we have an API. React is a Javascript framework. So, fetch calls, right? Or axios if you prefer that. Should be simple, yeah? Well, not really.
One of the best things about React is it’s routing. You don’t need to reload the entire page when navigating through pages and only subcomponents. That’s why it’s fast. So, let’s say you have several components (in this context, synonymous to pages) and each of them has a fetch call. What happens? Each time the component is loaded (depending on where the fetch call is placed), there’ll be a network call. And network calls could substantially decrease performance. But that’s not all. Every time a Google Sheets API call is made, it is counted against the quota of calls. From the Usage Limits section of the Google Sheets API v4:
This version of the Google Sheets API has a limit of 500 requests per 100 seconds per project, and 100 requests per 100 seconds per user. Limits for reads and writes are tracked separately. There is no daily usage limit.
Long story short, given these constraints, what we’d really like is to limit the network calls as much as possible. This makes our website faster and we make the most use of the quota. If your expected traffic is beyond the usage limit, traditional databases might be best suited for your needs. A later section on “When To Not Use Google Sheets” covers such scenarios.
Introducing Metis
Metis is a Higher-Order Component (HOC) that allows React Apps to use Google Sheets as a Makeshift Database. And in case you were wondering, Metis is named after the Greek Goddess of Wisdom. There are two main components of Metis: GoogleSheetsProvider and withGoogleSheets.
GoogleSheetsProvider
GoogleSheetsProvider is very similar to React’s Context.Provider. It wraps your React App, fetches all the sheets from your Google Sheets Doc, converts the raw results from the API into a readable key/value (kv) store, and publishes this kv store to the context. This allows for the kv store to be accessible from anywhere in your component tree.
withGoogleSheets
withGoogleSheets is a Higher-Order Component that makes it super easy to fetch the data from a specific sheet(s) from the context (done via the GoogleSheetsProvider) and make it available for a component via props.
By default, withGoogleSheets is shipped with a very basic component to render in case fetching the data from Google Sheets API was unsuccessful (incorrect permissions, quota reached, etc.). This HOC allows for this default component to be customized as well as replaced by an entirely different custom component.
withGoogleSheets is designed to take either a single sheet name or an array of sheet names as an argument to fetch the data from. Fetching all the sheets without specifying each individually is also supported and well-documented with examples.
Basic Example
Installation
npm install react-db-google-sheets --save.env
REACT_APP_GOOGLE_SHEETS_API_KEY=[YOUR-API-KEY]
REACT_APP_GOOGLE_SHEETS_DOC_ID=[YOUR-DOC-ID]App.js
import React from 'react';
import GoogleSheetsProvider from 'react-db-google-sheets';const App = () => (
<GoogleSheetsProvider>
<MyApp />
</GoogleSheetsProvider>
);export default App;
Component (say Hello.js)
import React from 'react';
import { withGoogleSheets } from 'react-db-google-sheets';const Hello = props => (
<div>
{props.db.sheet1.map(data => (
<span>{data.id}</span>
))}
</div>
);export default withGoogleSheets('sheet1')(Hello);
More examples are mentioned in the documentation and under the examples subfolder on the GitHub Repo.
When To Not Use Google Sheets
Hopefully, by now, you’re at least intrigued by the idea of using Google Sheets as a makeshift database for your React Apps. But you might also be thinking of several scenarios where this just isn’t a good solution. That’s true — there are several such cases. Like literally everything in the programming world, the usage of a tool depends solely on the needs and Metis is in no way an exception to that rule.
First, as mentioned earlier, if you expect heavy traffic coming into your website, even with the usage of GoogleSheetsProvider to limit one network call per the initial website load, you should either use a traditional database or consider the paid plans for the Google Sheets API usage. Second, in order to use Google Sheets as a makeshift database, the permissions on the doc would need to be set to publicly viewable. So, if your data contains passwords or sensitive information that warrants for user access privileges, you should most definitely use a traditional database and implement appropriate security protocols. Metis and the use of Google Sheets as a makeshift database are intended for data that is publicly viewable such as in informational or resume/portfolio websites.
Finally, if you require foreign keys (columns that act as a cross-reference between tables — sheets in Google Sheets terminology) then too, you’re better off using a relational database. Metis would be providing helper functions and guidelines on how to achieve that in the future using Google Sheets but evaluating your needs and deciding what’s the best tool for your use case is strongly recommended.
Updated: After conversing with a few folks, it’s worth reasserting that Google Sheets is not a database, in itself. It doesn’t scale, it utilizes no benefits of a traditional database, because it just simply isn’t one. But given very specific use cases where scalability isn’t a concern, where building a CMS is impractical, and where the overhead of training a new tool to the non-tech-savvy maintainers of the content is just too high, Metis could be beneficial. Evaluating your needs and use cases, and then selecting the appropriate tool for the purpose is inherently critical — and arguably, should be the initial deciding factor between using Metis or not.
Final Thoughts
Metis is intended as a tool to add/modify/delete content on your website with the ease of using Google Sheets without having to worry about code changes and deployment processes, and whatnot. You can even manage the design and layout of the website by specifying, for example, the colors, font sizes, and other styling components, in a sheet on your Google Sheets Doc. The possibilities are endless and the ease-of-use is tremendously sexy. But again, you should evaluate your needs and pick the tool that works the best for your requirements.
Give Metis a go. Feedback, contributions, questions — all welcome! Feel free to file issues on the GitHub repo or send me an email or Tweet at me with your questions/feedback!
Happy React-ing? Happy Google-Sheet-ing? Sigh, I try.
