google-sheetjs

Google SheetJS

Lightweight NPM package to interact with Google Sheets using Node.js and Google Sheets API. google-sheetjs simplifies reading and writing data to Google Sheets from your Node.js applications.

Features

• Read and write data to Google Sheets.
• Create columns and column groups.
• Add validation with dropdown options.
• Supports both reading and writing data in different ranges.
• Protect columns

Installation

To install the package, run the following command:

npm install google-sheetjs

Prerequisites

To be able to manage spreasheet using this library you need the following creds from your google cloud project:

1. Service account email id
2. Service account private key

Follow the guide to generate above two required inputs:

1. Google Cloud Project: You need to create a Google Cloud project, follow this link to create a project.
2. Enable the Google Sheets API in your google cloud project, follow this link.
3. Google Service Account: Generate credentials for a service account with appropriate access to your Google Sheets, follow this link to create a service account.

Usage

1. Install the package:

npm install google-sheetjs

2. Initialize the spreadsheet to create new or read existing

import { Spreadsheet } from "google-sheetjs";

const spreadsheet = new Spreadsheet("My title", "1.2.0", {
  serviceAccountEmail: process.env.GOOGLE_ACCOUNT_SERVICE_EMAIL,
  privateKey: process.env.GOOGLE_ACCOUNT_PRIVATE_KEY,
});

const sheet1 = spreasheet.addSheet("Sheet1"); // at least 1
sheet is required to add

sheet1.addColumn("col_1"); // add column to the sheet

await spreadsheet.create(); // This will create a sheet and update the sheet id

console.log(spreadsheet.spreadsheetId);

Access the sheet:

https://docs.google.com/spreadsheets/d/{sheetId}/edit

Example

1. Simple sheet with basic dropdown

import { Spreadsheet } from "google-sheetjs";

const spreadsheet = new Spreadsheet("My title", "1.2.0", {
  serviceAccountEmail: process.env.GOOGLE_ACCOUNT_SERVICE_EMAIL,
  privateKey: process.env.GOOGLE_ACCOUNT_PRIVATE_KEY,
});

spreadsheet.addSheet("Sheet1");
spreadsheet.sheets[0].addColumns([
  new Column("name"),
  new Column("age"),
  new Column("employee_type").setValidation(
    new DataValidation(ValidationType.ONE_OF_LIST, ["FULL_TIME", "PART_TIME"])
  ),
]);
await spreadsheet.create();

The above will generate the following sheet:

2. Simple sheet column grouping

// ...
// ...
// setup spreadsheet with auth info
spreadsheet.addSheet("Sheet1");
spreadsheet.sheets[0].addColumnGroups([
  new ColumnGroup("Basic Information", [new Column("name"), new Column("age")]),
  new ColumnGroup("Address", [
    new Column("city"),
    new Column("country").setValidation(
      new DataValidation(ValidationType.ONE_OF_LIST, [
        "India",
        "Germany",
        "UK",
        "USA",
      ])
    ),
  ]),
  new ColumnGroup("Contact", [new Column("email"), new Column("phone")]),
]);
await spreadsheet.create();

The above will generate the following sheet:

Entities

The following is the high level relationship between the entities to write the data, or read the data in google sheet.

Spreadsheet

This is the root entity, which allows all the operations to manage the sheet, be it adding array of sheets, adding columns, or column group, adding validations to a columns, or protecting the columns to disable edit.

Functions:

1. loadProperties()
2. addSheet()
3. create()

Sheet

The Sheet represents one individual Sheet in google-sheet, which contains the cells and data.

Spreadsheet contains more than one Sheet Functions:

1. addColumn()
2. addColumns()
3. addColumnGroup()
4. appendRows()
5. getRow()
6. getCell()

Column

1. setValidation()
2. protect()