Snippets

Save to Google Sheets with Nodejs/Nextjs

blog main image

When discussing easy-to-set-up, cloud-based databases, platforms like Supabase often come to mind.

However, if you're looking for something that can be used even by non-developers, something nearly everyone is familiar with: Google Sheets is likely the first tool you'll think of šŸ„‡

In the following snippet, I'll show the minimal steps required to implement CRUD data directly to/from Google Sheets.

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:

Setup

Install dependencies:

Terminal
Copied!
pnpm add google-auth-library google-spreadsheet dotenv

Code

.env
Copied!
GSHEET_API_PRIVATE_KEY='-----BEGIN PRIVATE KEY...'
GSHEET_API_CLIENT_EMAIL='[email protected]'
gsheet.mjs
Copied!
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: [email protected]
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:

Terminal
Copied!
node ./gsheet.mjs

You should now see your data in the console āœ…

Create

Copied!
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

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

deleteRow(0)

Update

Copied!
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: '[email protected]'})

Result

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.

Nextjs

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

api.ts
Copied!
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())
}
nodejs
gsheet
nextjs
crud