Overview

google-sheets-automation is a Node.js package for programmatically managing Google Sheets. Easily add, update, and read rows using service account credentials. Designed for backend, CLI, and serverless environments.

Features

• Add rows with optional header mapping
• Update cells in bulk (A1 notation)
• Read sheet data as objects
• Batch operations
• Promise-based API
• Secure authentication via Google service accounts

Installation

npm install google-sheets-automation

Quick Start

import { GoogleSheetProvider } from 'google-sheets-automation';

const provider = new GoogleSheetProvider({
  client_email: '[email protected]',
  private_key: '-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n',
  sheetId: 'your-google-sheet-id',
});

const headerMap = {
  Name: 'name',
  Email: 'email',
  Message: 'message',
};

const rows = [
  { name: 'Alice', email: '[email protected]', message: 'Hello!' },
  { name: 'Bob', email: '[email protected]', message: 'Hi!' },
];

await provider.addRows({ spreadsheetId: 'your-google-sheet-id', sheetName: 'Sheet1', headerMap }, rows);

React/NextJS Serverside form example

import type { NextApiRequest, NextApiResponse } from 'next';
import { GoogleSheetProvider } from 'google-sheets-automation';

export default async function handler(req: NextApiRequest, res: NextApiResponse) {
  if (req.method !== 'POST') return res.status(405).end();

  const { name, email, message } = req.body;
  if (!name || !email || !message) {
    return res.status(400).json({ error: 'Missing required fields' });
  }

  const credentials = {
    private_key: process.env.GOOGLE_PRIVATE_KEY?.replace(/\\n/g, '\n'),
    client_email: process.env.GOOGLE_CLIENT_EMAIL,
  };

  const client = new GoogleSheetProvider(credentials);

  const options = {
    spreadsheetId: process.env.GOOGLE_SHEET_ID,
    sheetName: 'Sheet1',
    headerMap: { name: 'Name', email: 'Email', message: 'Message' }
  };
  const rows = [{ name, email, message }];

  try {
    await client.addRows(options, rows);
    res.status(200).json({ success: true });
  } catch (err: any) {
    res.status(500).json({ error: err.message });
  }
}

Environment Setup

1. Create a Google Cloud project and enable the Google Sheets API.
2. Create a service account and download the JSON key.
3. Share your target Google Sheet with the service account email.
4. Store credentials in your .env:

   GOOGLE_SERVICE_ACCOUNT_EMAIL=your-service-account-email@project.iam.gserviceaccount.com
   GOOGLE_PRIVATE_KEY="-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n"
   GOOGLE_SHEET_ID=your-google-sheet-id

API Reference

GoogleSheetProvider

addRows(options, rows)

Add rows to a sheet. If headerMap is provided and the sheet is empty, headers are added automatically.

updateCells(options)

Update cells in a sheet using A1 notation and a 2D array of values.

readData(options)

Read all data from a sheet, returned as an array of objects (first row is used as keys).

deleteRows(options)

Delete rows by index (not yet implemented).

createSheet(options)

Create a new sheet (not yet implemented).

Types

• AddRowsOptions: { spreadsheetId, sheetName, headerMap? }
• UpdateCellsOptions: { spreadsheetId, sheetName, range?, values }
• ReadDataOptions: { spreadsheetId, sheetName, range? }
• DeleteRowsOptions: { spreadsheetId, sheetName, rowIndexes }
• CreateSheetOptions: { spreadsheetId, sheetName }

Advanced Usage

Update Cells Example

await provider.updateCells({
  spreadsheetId: 'your-google-sheet-id',
  sheetName: 'Sheet1',
  range: 'Sheet1!A2:B2',
  values: [['Alice', '[email protected]']]
});

Read Data Example

const data = await provider.readData({
  spreadsheetId: 'your-google-sheet-id',
  sheetName: 'Sheet1'
});
console.log(data);

Testing

Unit Tests

Run all unit tests (mocks Google API calls):

npm run test

Integration Tests

Run integration tests against the real Google Sheets API (requires valid .env and sheet setup):

npm run test test/providers/googleProvider.integration.test.ts

Contributing

Pull requests and issues are welcome! Please open an issue for feature requests or bugs.

License

MIT

Testing

Unit tests are written with Jest and mock all Google API calls, so no credentials are required:

npm run test

Integration Testing

To run integration tests against the real Google Sheets API, you must:

• Set up a .env file with valid GOOGLE_SERVICE_ACCOUNT_EMAIL, GOOGLE_PRIVATE_KEY, and GOOGLE_SHEET_ID.
• Ensure the target sheet (e.g. IntegrationTestSheet) exists in your Google Spreadsheet before running the test.
• Run:

npm run test test/providers/googleProvider.integration.test.ts

Integration tests will use your credentials and make real API calls. Do not run these against production data.

Providers

• GoogleSheetProvider (currently available)
  • Usage: new GoogleSheetProvider({ ...credentials }) -(Planned) ExcelSheetProvider, AirtableSheetProvider, etc.

API Functions (ISheetProvider)

All providers implement the following methods:

addRows(options, rows) — Add rows to the sheet, optionally mapping headers. updateCells(options) — Update cells in the sheet (A1 range, 2D values). readData(options) — Read data from the sheet (returns array of objects). deleteRows(options) — Delete rows by index (not yet implemented). createSheet(options) — Create a new sheet (not yet implemented).

Types

AddRowsOptions: { spreadsheetId, sheetName, headerMap? } UpdateCellsOptions: { spreadsheetId, sheetName, range?, values } ReadDataOptions: { spreadsheetId, sheetName, range? } DeleteRowsOptions: { spreadsheetId, sheetName, rowIndexes } CreateSheetOptions: { spreadsheetId, sheetName }

Roadmap

• Support for additional sheet providers (Excel, Airtable, etc.) planned for future releases.

License

MIT