Google Sheets as a Database with Node.js and Google APIs

Google Sheets can be a simple and effective way to store structured data. In this guide, you'll learn how to integrate Google Sheets with a Node.js (TypeScript) application using the googleapis package.

🚀 Step 1: Create a New Google Cloud Project

1. Go to Google Cloud Console
2. Click New Project

🔍 Step 2: Enable Google Sheets API

• Search for Google Sheets API

• Click Enable

🔑 Step 3: Create Credentials

• Navigate to the Credentials tab
• Click Create Credentials → Service Account

• Select Application Data → Click Next

• Click Create and Continue, then Done

🔐 Step 4: Generate JSON Key

• Go to Credentials → Select the service account you created

• Navigate to Keys → Click Add Key → Create New Key

• A JSON file will be downloaded. Keep it safe!

📄 Step 5: Share Google Sheet Access

• Create a new Google Sheet
• Click Share

• Open the downloaded JSON file and find client_email

• Paste the copied email in the Share section and give Editor access.

⚙️ Step 6: Set Up Node.js Project

1️⃣ Install Dependencies

npm init -y
npm install googleapis dotenv

2️⃣ Write Code to Save Data to Google Sheets

import { google } from "googleapis";
import fs from "fs";
import dotenv from "dotenv";

dotenv.config();

const credentials = JSON.parse(fs.readFileSync("path-to-downloaded-json-key.json", "utf8"));

const auth = new google.auth.GoogleAuth({
  credentials,
  scopes: ["https://www.googleapis.com/auth/spreadsheets"],
});

async function saveToGoogleSheet(data: any[]) {
  const sheets = google.sheets({ version: "v4", auth });

  const values = data.map((item) => [
    item.firstName,
    item.lastName,
    item.email,
    item.phone,
    item.plan,
    item.premium_amount,
    item.start_policy_date,
    item.end_policy_date,
  ]);

  await sheets.spreadsheets.values.append({
    spreadsheetId: process.env.GOOGLE_SHEET_ID!,
    range: "Sheet1!A2", // Adjust based on your sheet structure
    valueInputOption: "RAW",
    requestBody: { values },
  });

  console.log("Data saved successfully!");
}

const exampleData = [
  {
    firstName: "John",
    lastName: "Doe",
    email: "johndoe@example.com",
    phone: "+1234567890",
    plan: "Gold Plan",
    premium_amount: 50000,
    start_policy_date: "2025-01-01",
    end_policy_date: "2026-01-01",
  },
];

saveToGoogleSheet(exampleData).catch(console.error);

🔍 Step 7: Get Your Google Sheet ID

To find your Sheet ID, look at the URL of your Google Sheet.

Copy this ID and set it in your .env file:

GOOGLE_SHEET_ID=your-google-sheet-id

✅ Step 8: Test Your Code

Run your script and check your Google Sheet. You should see something like this:

🎯 Conclusion

You’ve successfully integrated Google Sheets with Node.js using the Google Sheets API! 🎉

This method is great for small projects, logging data, or quickly storing structured records without setting up a database. If you need more complex features, consider using a dedicated database like Firebase, PostgreSQL, or MongoDB.

Got questions? Drop them in the comments below! 🚀