Tutorial: Google Sheets as a Database with Node.js

When you want to build an IT product, often you need a storage layer. The most common approach is to resort to SQL or NoSQL relational databases. But, are there other alternatives?

In general, any database will work fine. In my case, after having tried several of them, I feel very comfortable working with both MySQL and SQLite. If needed, I can choose and try other databases. As I always say, it will depend on the needs of the problem to be solved, and I have a lot of content to share about databases.

If we compare two projects, one with a storage layer and one without, we will all agree that managing the project without a storage layer is much simpler: less cost, less maintenance or management time, and fewer issues.

That's why I increasingly like to start the products I create in the simplest way possible. A few weeks ago, I started building a data visualization tool, for which I used Next.js, and I'm really enjoying building it.

My intention is to be able to iterate on it quickly and agilely by myself. The problem arises when the need to manage users and permissions arises. The most obvious thing in these cases is to decide to use a relational database, as I mentioned at the beginning of the post.

Sometimes we don't stop to think about the consequences of adding complexity to our projects. For example, adding a database requires:

• Managing schemas.
• Managing migrations.
• Managing the database itself (although you can always use some cloud system that manages the database for you and you don't have to worry about some things).
• Giving access to third parties, who may not know about IT, to manage permissions, requires some kind of tool no matter how simple it may be.

In the end, being a small product with which I can experiment, being developed by myself, and wanting to build it quickly, I decided not to use this database system and try something new.

I have created a session system for the authentication layer, which checks if the user exists in a Google Sheets spreadsheet. This way, I don't have to manage databases, and anyone with access can modify the data. Although managing a database is fun, it takes time, and time is limited. When you do one thing, you stop doing others, this is what is known as opportunity cost.

Google Sheets is a cloud-based spreadsheet application developed by Google, which also offers a free API through Google Cloud Platform to access and modify data. This is a very interesting tool when you want to evolve a product quickly and don't want to maintain a database, so you can focus on, first, building the product itself and then if needed, you can always add a database and all the complexity your project requires.

The only thing you have to be careful with is the API limits, which are generally quite lax, and with a cache layer in your application, you can bypass them.

Overall, I am very happy with this decision. Besides, having a small product to experiment with, to learn, explore new options, and discover the advantages and disadvantages of each option is something I am passionate about.

Next, I'm going to explain how to access Google Sheet data using Node.js and the Google API. In this post, I'm not going to talk about sessions, caches, or other topics, so if you're interested, remember to subscribe to the newsletter. You can leave a comment on the YouTube video or contact me.

Tutorial: Using Google Sheets API with Node

First, we have to set up and access our Google Cloud Platform account to configure this part:

• First, we create an account on Google Cloud Console.
• We create a new project.
• We search for the "Google Sheets API" product and activate it.
• We search for the "Service Accounts" product within "IAM" and create a new service account.
• When created, it will provide us with an email that we must note down. We will use it later.
• We enter the service account, and in the "Keys" menu, we create a new key of type json that we will download.

Then, with your Gmail account, you must perform the following steps: - We create a Google Sheets spreadsheet with your email account. - We invite the email account we have generated in Google Cloud Platform. - Here you can give it read-only permissions or read and write permissions. In my case, I used read-only since accesses to the tool are manual. - We will note down the ID of the sheet, which you can find in the browser's URL.

Now we have the preparations ready. Now all that's left is to go to your application, using your preferred language. In my case, I'm using Node.js, so the steps are as follows:

We install the following dependencies:

npm i google-auth-library googleapis

In the code, we create a function that will connect to the Google API, and we have to pass it the ID of the spreadsheet we created. The process is as follows:

• We authenticate with GoogleAuth and our service account.
• We connect to the Google Sheets API using the generated credentials, and with this, we already have access to the data.

An example function in node would be like this:

import { GoogleAuth } from "google-auth-library";
import { sheets } from "googleapis/build/src/apis/sheets";

export async function getGoogleSheetByID(sheetId: string): Promise<Data> {
    const auth = new GoogleAuth({
        keyFile: "crendentials.json",
        scopes: "https://www.googleapis.com/auth/spreadsheets.readonly"
    });

    const mysheet = sheets({ version: "v4", auth });

    try {
        const response = await mysheet.spreadsheet.values.get({
            spreadsheetId: sheetId,
            range: "Accounts"
        });

        const rows = response.data.values;

        // your logic

    } catch (e) {
        // Handle error
    }
}

With this code, we already have the information in the rows variable so we can iterate over it and access the data.