DEV Community

Cover image for Google Sheets as a Database with Node.js and Google APIs
DrSimple
DrSimple

Posted on

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

#google #sheet

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

Create a new project

New project page

🔍 Step 2: Enable Google Sheets API

  • Search for Google Sheets API

Search API

  • Click Enable

Enable API

🔑 Step 3: Create Credentials

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

Create Credentials

  • Select Application Data → Click Next

Application Data

  • Click Create and Continue, then Done

Redirected Page

Click Done

🔐 Step 4: Generate JSON Key

  • Go to Credentials → Select the service account you created

Service Account

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

Add Key

Choose JSON

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

Download JSON

📄 Step 5: Share Google Sheet Access

  • Create a new Google Sheet
  • Click Share

Share Sheet

  • Open the downloaded JSON file and find client_email

Find client email

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

Paste Email

⚙️ Step 6: Set Up Node.js Project

1️⃣ Install Dependencies 

npm init -y npm install googleapis dotenv
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

🔍 Step 7: Get Your Google Sheet ID

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

Find Sheet ID

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

GOOGLE_SHEET_ID=your-google-sheet-id
Enter fullscreen mode Exit fullscreen mode

✅ Step 8: Test Your Code

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

Google Sheet Data

🎯 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! 🚀

Top comments (1)

Collapse
 
fredabod profile image
FredAbod •

Amazing