Background image
Snippets

Save to Google Sheets with Nodejs/Nextjs

blog main image

Imagine a tool everyone already knows - even non-developers and offering: advanced security, role-based access, version history, and more. And you get started with a single click: just head to sheets.new. It also has an API that we can use, almost free.

In the following snippets, we will show the minimal steps required to implement CRUD data directly with Google Sheets through API.

Installation

Requirements:

  • Google Sheet -> https://sheets.new/. Add example headers: Email, Date, Notes (first row)
  • GSheet API Auth keys

To get GSheet API Keys, we need:

Install dependencies:

pnpm add google-auth-library google-spreadsheet dotenv

Full Code

.env
GSHEET_API_PRIVATE_KEY='-----BEGIN PRIVATE KEY...'
GSHEET_API_CLIENT_EMAIL='example@endless-beach-46301.iam.gserviceaccount.com'
gsheet.mjs
import {JWT} from 'google-auth-library'
import {GoogleSpreadsheet} from 'google-spreadsheet'
import 'dotenv/config' // don't forget to create .env file

const GSHEET_API_CLIENT_EMAIL = process.env.GSHEET_API_CLIENT_EMAIL // example: ...@...iam.gserviceaccount.com
const GSHEET_API_PRIVATE_KEY = process.env.GSHEET_API_PRIVATE_KEY // example: -----BEGIN PRIVATE KEY...

if (!GSHEET_API_CLIENT_EMAIL || !GSHEET_API_PRIVATE_KEY) {
    throw new Error('Provide GSHEET_API_CLIENT_EMAIL and GSHEET_API_PRIVATE_KEY')
}

async function getDocument(sheetId) {
    const serviceAccountAuth = new JWT({
        email: GSHEET_API_CLIENT_EMAIL,
        key: GSHEET_API_PRIVATE_KEY,
        scopes: ['https://www.googleapis.com/auth/spreadsheets']
    })
    const doc = new GoogleSpreadsheet(sheetId, serviceAccountAuth)

    await doc.loadInfo()

    return doc
}

// sheet ID could found in the URL: https://docs.google.com/spreadsheets/d/<SHEET_ID>/edit?gid=0#gid=0\
// example: 1-HlukgKZtVA-Qjkb3v96JYasdgqkdZBCJ1o7z-aEDZPk
const SHEET_ID = 'sheet_id'

const getSheet = async () => {
    const doc = await getDocument(SHEET_ID)
    return await doc.sheetsByIndex[0]
}

async function getRows() {
    const sheet = await getSheet()
    return sheet.getRows()
}

getRows().then(e => console.log(e.toObject()))

Execute:

node ./gsheet.mjs

You should now see your data in the console ✅

CRUD

Create

async function addRow(item) {
    const sheet = await getSheet()
    return sheet.addRow(item)
}

addRow({Email: `example${new Date().getDate()}@example.com`, Date: '01-01-1971', Note: 'Note'})

Delete

async function deleteRow(index) {
    const rows = await getRows()
    return rows[index].delete()
}

deleteRow(0)

Update

async function updateRow(index, patch) {
    const rows = await getRows()
    const item = rows[index]
    Object.entries(patch).map(([key, value]) => {
        item.set(key, value)
    })
    return item.save()
}

updateRow(0, {Email: 'NEW@gmail.com'})

Nextjs Version

Now let's implement the Next.js API routes:

api.ts
import {NextResponse} from 'next/server'
import {getRows, addRow, deleteRow, updateRow} from './gsheet.ts'

export async function GET() {
    const rows = await getRows()
    return NextResponse.json(rows.map(r => r.toObject()))
}

export async function POST(req: Request) {
    const body = await req.json()
    const row = await addRow(body)
    return NextResponse.json(row.toObject())
}

export async function DELETE(req: Request) {
    const body = await req.json()
    await deleteRow(body.index)
    return NextResponse.json({success: true})
}

export async function PUT(req: Request) {
    const body = await req.json()
    const row = await updateRow(body.index, body.patch)
    return NextResponse.json(row.toObject())
}

Summary

Overall, using Google Sheets as a temporary database offers several benefits:

  • Can be set up quickly – ideal for POCs or prototypes
  • Almost free – service accounts have usage limits before incurring charges, but in my experience, I have never reached that point
  • Includes built-in permissions and version history
  • Most people are familiar with Google Sheets or Excel

But overall, you should still treat it as technical debt and remember that it is obviously not scalable. So, if you have time or long-term plans for the project, start with tools designed for that purpose.

nodejs
gsheet
nextjs
crud