Google Sheet CRUD

Overview

google-sheet-crud is a lightweight and easy-to-use Node.js package that simplifies CRUD (Create, Read, Update, Delete) operations on Google Sheets. It provides an abstraction over Google Sheets API, making it convenient for developers to interact with spreadsheets without dealing with low-level API details. This package is particularly useful for applications that require simple database-like operations with the flexibility of a spreadsheet.

Features

  • Simple and Intuitive API: Provides easy-to-use methods for CRUD operations.
  • Google Sheets Integration: Connects seamlessly with Google Sheets API.
  • Asynchronous Operations: Supports async/await for better performance.
  • Minimal Configuration: Requires only essential authentication setup.
  • Batch Operations: Efficiently handles multiple row updates at once.

Requirements

Before installing the package, ensure you have the following:

  • Node.js (version 18 or later)
  • Google Cloud Project with Google Sheets API enabled
  • Service Account JSON credentials from Google Cloud Console
  • A Google Sheet shared with the service account email

Installation

Perform the installation through the terminal from your project directory. Choose the package manager you are using with the following command:

npm install google-sheet-crud
Bash

Class: GoogleSheetService

Constructor

constructor(credentials: { client_email: string; private_key: string })
Bash
  • Initializes the GoogleSheetService instance with Google API credentials.

Methods

1. Create

async create({ sheetId, range, data }: CreateProps): Promise<any>
TypeScript

Parameters:

  • sheetId: The ID of the Google Sheet.
  • range: The range where data will be added (e.g., Sheet1!A1:D1).
  • data: An object containing key-value pairs of data.

Usage Example:

import { GoogleSheetService } from 'google-sheet-crud';

const googleSheetService = new GoogleSheetService({
  client_email: 'your-service-account-email',
  private_key: 'your-private-key'
});

await googleSheetService.create({
  sheetId: 'your-sheet-id',
  range: 'Sheet1!A1:D1',
  data: { id: 1, name: 'John Doe', age: 30, active: true }
});
TypeScript

2. bulkCreate

async bulkCreate({ sheetId, range, data }: bulkCreateProps): Promise<any>
TypeScript

Parameters:

  • sheetId: The ID of the Google Sheet.
  • range: The range where data will be added.
  • data: An array of objects containing key-value pairs.

Usage Example:

await googleSheetService.bulkCreate({
  sheetId: 'your-sheet-id',
  range: 'Sheet1!A1:D1',
  data: [
    { id: 1, name: 'John Doe', age: 30, active: true },
    { id: 2, name: 'Jane Doe', age: 25, active: false }
  ]
});
TypeScript

3. update

async update({ sheetId, range, id, data }: UpdateProps): Promise<any>
TypeScript

Parameters:

  • sheetId: The ID of the Google Sheet.
  • range: The range where data exists.
  • id: The ID of the row to update.
  • data: An object containing the updated key-value pairs.

Usage Example:

await googleSheetService.update({
  sheetId: 'your-sheet-id',
  range: 'Sheet1!A1:D1',
  id: 1,
  data: { name: 'John Updated', age: 31 }
});
TypeScript

4. get

async get({ sheetId, range }: GetProps): Promise<any>
TypeScript

Parameters:

  • sheetId: The ID of the Google Sheet.
  • range: The range to retrieve data from.

Usage Example:

const data = await googleSheetService.get({
  sheetId: 'your-sheet-id',
  range: 'Sheet1!A1:D10'
});
console.log(data);
TypeScript

5. delete

async delete({ sheetId, range, id }: RemoveProps): Promise<any>
TypeScript

Parameters:

  • sheetId: The ID of the Google Sheet.
  • range: The range where data exists.
  • id: The ID of the row to delete.

Usage Example:

await googleSheetService.delete({
  sheetId: 'your-sheet-id',
  range: 'Sheet1!A1:D1',
  id: 1
});
TypeScript

Additional Information