Google Spreadsheet ORM

Lightweight Node.js library simplifying Google Sheets integration, offering a robust object-relational mapping (ORM) interface, following the data-mapper pattern. This library enables seamless CRUD operations, including batch operations, ensuring a strict Typescript typing.

Install

npm install google-spreadsheets-orm

Quickstart

Here's a quick example using CustomerModel interface as type for the customers sheet rows.

The example is using GoogleAuth from google-auth-library as authentication, but any other auth option from the auth library is available to use, more info on the Authentication options section.

import { GoogleAuth } from 'google-auth-library';
import { GoogleSpreadsheetOrm } from 'google-spreadsheets-orm';

interface CustomerModel {
  id: string;
  dateCreated: Date;
  name: string;
}

const myGoogleAuth = new GoogleAuth({
  scopes: 'https://www.googleapis.com/auth/spreadsheets',
});

const orm = new GoogleSpreadsheetOrm<CustomerModel>({
  spreadsheetId: 'my-spreadsheet-id',
  sheet: 'customers',
  auth: myGoogleAuth,
  castings: {
    dateCreated: FieldType.DATE,
  },
});

// returns all customers in customers sheet, as an array of `CustomerModel`
const sheetCustomers = await orm.all();

// Adds a new row to customers sheet
await orm.create({
  id: '1111-2222-3333-4444',
  dateCreated: new Date(),
  name: 'John Doe',
});

More info about available methods in Methods Overview section.

Configuration

Authentication Options

GoogleSpreadsheetORM supports various authentication options for interacting with Google Sheets API. You can provide authentication options through the auth or sheetClients properties in the Options configuration.

Auth

The auth property accepts either a single authentication option or an array of authentication options allowed in Google APIs libraries. These options are used to create a sheets_v4.Sheets instance for each authentication object provided. Operations are load-balanced across these clients, and quota retries for API rate limiting are automatically handled when multiple authentication options are provided.

Sheet Clients

Alternatively, you can directly provide an array of sheets_v4.Sheets client instances through the sheetClients property. GoogleSpreadsheetORM distributes operations among the provided clients for load balancing. Quota retries for API rate limiting are automatically handled when using multiple clients.

Cache

Google Spreadsheets API can usually have high latencies, so using a Cache can be a good way to work around that issue.

Enabling the cache is as simple as:

import { GoogleAuth } from 'google-auth-library';
import { GoogleSpreadsheetOrm } from 'google-spreadsheets-orm';

interface CustomerModel {
  id: string;
  dateCreated: Date;
  name: string;
}

const orm = new GoogleSpreadsheetOrm<CustomerModel>({
  spreadsheetId: 'my-spreadsheet-id',
  sheet: 'customers',
  auth: new GoogleAuth({
    scopes: 'https://www.googleapis.com/auth/spreadsheets',
  }),
  castings: {
    dateCreated: FieldType.DATE,
  },
  cacheEnabled: true, // Enabling Cache ✅
  cacheTtlSeconds: 60, // Data will be cached for one minute ⏱️
});

const firstCallResult = await orm.all(); // Data is fetched from spreadsheet and loaded into cache
const secondCallResult = await orm.all(); // Data is taken from cache 🏎️💨
const thirdCallResult = await orm.all(); // 🏎️💨
// more `all` calls...

// Any write operation will invalidate the cache
orm.create({
  id: '1111-2222-3333-4444',
  dateCreated: new Date(),
  name: 'John Doe',
});

await orm.all(); // Data is fetched from spreadsheet again

Cache Providers

By default, an in-memory implementation is used. However, that might not be enough for some situations. In those cases a custom implementation can be injected into the ORM, following the CacheProvider contract, example:

import { GoogleAuth } from 'google-auth-library';
import { GoogleSpreadsheetOrm, CacheProvider } from 'google-spreadsheets-orm';

class RedisCacheProvider implements CacheProvider {
  private dummyRedisClient;

  public async get<T>(key: string): Promise<T | undefined> {
    return this.dummyRedisClient.get(key);
  }

  public async set<T>(key: string, value: T): Promise<void> {
    this.dummyRedisClient.set(key, value);
  }

  public async invalidate(keys: string[]): Promise<void> {
    this.dummyRedisClient.del(keys);
  }
}

const orm = new GoogleSpreadsheetOrm<CustomerModel>({
  spreadsheetId: 'my-spreadsheet-id',
  sheet: 'customers',
  auth: new GoogleAuth({
    scopes: 'https://www.googleapis.com/auth/spreadsheets',
  }),
  castings: {
    dateCreated: FieldType.DATE,
  },
  cacheEnabled: true, // Enabling Cache ✅
  cacheProvider: new RedisCacheProvider(), // Using my custom provider 🤌
});

Methods Overview

GoogleSpreadsheetORM provides several methods for interacting with Google Sheets. Here's an overview of each method:

all()

Retrieves all entities from the specified sheet, parsing and serializing them according to the field types defined in the Castings configuration.

const myEntities = await orm.all();

• Returns: A Promise that resolves to an array of entities of type T, representing all rows retrieved from the sheet.

Filtering

all method also accepts an object to filter data from spreadsheet, example:

orm.all({
  filter: {
    // Filter by specific value
    id: '1111-2222-3333-4444',
    // Or... Filter by values contained in an array
    id: ['1111-1111-1111-1111', '2222-2222-2222-2222', '3333-3333-3333-3333', '4444-4444-4444-4444'],
  },
});

create(entity: T)

Creates a new row in the specified sheet with the provided entity data.

// Adds a new row to sheet
await orm.create({
  id: '1111-2222-3333-4444',
  dateCreated: new Date(),
  name: 'John Doe',
});

• Parameters:
  • entity: The entity object to persist as a new row in the sheet.
• Remarks:
  • It internally retrieves the headers of the sheet to ensure proper alignment of data.
  • Quota retries are automatically handled to manage API rate limits.

createAll(entities: T[])

Creates many new rows in the specified sheet with the provided entities.

// Adds 2 new rows to sheet
await orm.createAll([
  {
    id: '1111-2222-3333-4444',
    dateCreated: new Date(),
    name: 'John Doe',
  },
  {
    id: '5555-6666-7777-8888',
    dateCreated: new Date(),
    name: 'John Doe 2',
  },
]);

• Parameters:
  • entities: The entity array to persist as new rows in the sheet.
• Remarks:
  • It internally retrieves the headers of the sheet to ensure proper alignment of data.
  • Quota retries are automatically handled to manage API rate limits.

delete(entity: T) | deleteById(entityId: string)’

Deletes the entity provided from the spreadsheet.

const myEntities: YourEntity[] = await orm.all();

const entityToDelete: YourEntity = myEntities.find(e => e.id === '1111-2222-3333-4444');

await orm.delete(entityToDelete);

• Parameters:
  • entity: The entity object to delete in the sheet.
• Remarks:
  • This method deletes the row in which the entity was persisted.
  • It internally fetches the sheet data to find which row needs to delete.
  • Quota retries are automatically handled to manage API rate limits.

deleteAll(entities: T[]) | deleteAllByIdIn(entityIds: string[])

Deletes the provided entities from the spreadsheet.

const myEntities: YourEntity[] = await orm.all();

const entitiesToDelete: YourEntity[] = myEntities.filter(e => e.shouldBeDeleted());

await orm.delete(entitiesToDelete);

• Parameters:
  • entities: The entity array to delete in the sheet.
• Remarks:
  • It internally fetches the sheet data to find which row needs to delete.
  • Quota retries are automatically handled to manage API rate limits.

update(entity: T)

Updates the row in the specified sheet matching by id. All values are replaced with the ones in the entity param.

const myEntities: YourEntity[] = await orm.all();

const entityToUpdate: YourEntity = myEntities.find(e => e.id === '1111-2222-3333-4444');
entityToUpdate.name = 'Updated name';

await orm.update(entityToUpdate);

• Parameters:
  • entity: The entity object to update in the sheet.
• Remarks:
  • It internally retrieves sheet data to ensure proper alignment of data and checking which row needs to update.
  • Quota retries are automatically handled to manage API rate limits.

updateAll(entities: T[])

Updates the rows in the specified sheet matching by id. All values are replaced with the ones in the entities param.

const myEntities: YourEntity[] = await orm.all();

const entitiesToUpdate: YourEntity[] = myEntities.filter(e => e.shouldBeDeleted());

entitiesToUpdate.forEach(entity => {
  entity.name = 'Updated Name';
});

await orm.updateAll(entitiesToUpdate);

• Parameters:
  • entities: An array of entities objects to update in the sheet.
• Remarks:
  • It internally retrieves sheet data to ensure proper alignment of data and checking which row needs to update.
  • Quota retries are automatically handled to manage API rate limits.

metrics()

Returns an object that contains request latencies, grouped by type of request.

Example of method response:

{
  FETCH_SHEET_DATA: [432, 551, 901],
  SHEET_APPEND: [302, 104]
}

Check MetricOperations class to see possible keys.