google-spreadsheets-orm
v1.0.3
Published
Production-ready ORM applying data-mapper pattern to consume google spreadsheets.
Downloads
61
Maintainers
Readme
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.