geepers v0.0.3
geepers
A simple Node.js library for accessing Google Spreadsheets. It provides an API similar to that of MongoDB for CRUD operations making it familiar to many developers.
Uses the node-google-spreadsheet module for accessing Goole Spreadsheets, and provides thin Mongo like wrapper around it's operations.
If you're looking for a speedy database, this module is not it. Operations that alter the data are slooooowwwww. Operations data read operations are faster but don't expect to break any records.
Installation
npm install geepers
Getting Started
Create a Spreadsheet
Manually create a spreadsheet that you want to access with geepers. Within that spreadsheet,
you can optionally create new sheet with a meaningful name, or feel free to stick with "Sheet1".
Make a note of the id of the spreadsheet, this is found in the URL. For example, the spreadsheet
id in the URL https://docs.google.com/spreadsheets/d/abc1234567/edit#gid=0
is "abc1234567".
You'll need that id shortly.
Google Spreadsheet Authentication
geepers only supports authenticated access to Google Spreadsheets using service accounts. Setting a spreadsheet up for this is a few steps described in the node-google-spreadsheet docs.
geepers Configuration
Once you've followed the steps to enable API access via a service account, you'll need to update the provided JSON key file, adding the id of your shared spreadsheet.
Open the JSON key file and add a new key geepers_sheet_id
with the value set to the
id of the spreadsheet you noted earlier. It should end up looking something like below.
{
"geepers_sheet_id": "your google sheet id",
"private_key_id": "key id ...",
"private_key": "key ...",
"client_email": "your_service_account@developer.gserviceaccount.com",
"client_id": "your_service_account.apps.googleusercontent.com",
"type": "service_account"
}
Example Usage
var Geepers = require('geepers');
var geepersConfig = require('./gsheetsauth.json');
var geepers = new Geepers();
geepers.connect(geepersConfig, function (err, db) {
// Collection name maps to spreadsheet sheet name
db.collection('Sheet1').insertMany([
{ name: 'Superman', alignment: 'Hero', comic: 'DC' },
{ name: 'Batman', alignment: 'Hero', comic: 'DC' },
{ name: 'Joker', alignment: 'Villian', comic: 'DC' },
{ name: 'Iron Man', alignment: 'Hero', comic: 'Marvel' },
{ name: 'Thor', alignment: 'Hero', comic: 'Marvel' },
{ name: 'Jean Grey', alignment: 'Hero', comic: 'Marvel' },
{ name: 'Ultron', alignment: 'Villian', comic: 'Marvel' }],
function (err, insertedRecs) {
// find all the DC heros
db.collection('Sheet1').find(
{alignment: 'Hero', comic: 'DC'},
{gid:1, name:1},
function (err, dcHeros) {
var hero = {};
// delete all the DC heros
while (dcHeros.hasNext()) {
hero = dcHeros.next();
db.collection('Sheet1').deleteMany({gid:hero.gid},
function(err, result) {
if (err || !result) {
console.log('Delete error for - ', hero);
}
}
);
}
}
);
// Update a record
db.collection('Sheet1').find(
{ name: 'Jean Grey' },
function (err, jeanGreys) {
if (jeanGreys.count() === 1) {
var jean = jeanGreys.toArray()[0];
// Jean Grey goes bad
db.collection('Sheet1').update(
{gid:jean.gid},
{name: 'Dark Phoenix', alignment: 'Villian'},
function (err) {
if (err) {
console.log('Error while updating - ', jean);
}
}
)
}
}
);
});
});
Take a look at tests/test.js to see more examples.
Query Options
Query selectors can use a subset of MongoDB query operators. The default action is to and the listed selection properties. Aside from and, the following operators are supported.
$or
- or$gt
and$gte
- greater than and greater than or equal to$lt
and$lte
- less than and less than or equal to$ne
- not equal to
You can test a query against a specific collection (a sheet) using the collection's
filterQuery()
function. This returns a string representation of the query.
Find Projections
Projections specify which properties are going to be returned in the found records.
A projection object is either inclusive or exclusive, except for the gid
property
which can be excluded from an inclusive projection.
The gid
property is always returned unless explicitly excluded
A projection object can be supplied as the second arguement to the find()
function, it's
an optional arguement, if it's not provided all fields are returned.
Examples
{a:1, b:1}
- returns propertiesa
,b
, andgid
{gid:-1, a:1, b:1}
- returns onlya
andb
{a:-1}
- returns all properties excepta
Running Tests
Establish a spreadsheet and set it up for geepers access. Tests require the JSON key file
be named gsheetsauth.json
and located in the root directory of the project.
Create a new empty sheet called functionTests
.
npm test
Links
- https://developers.google.com/google-apps/spreadsheets/
- https://github.com/theoephraim/node-google-spreadsheet/
Todo
- Better documentation of geepers API
- Regex string matches in queries
- Spreadsheet maintenance, create, sheets
- Speed