gsheetobj v1.0.6
Google Sheets Simplifier
const gsobj = require('gsheetobj')
const data = gsobj.fetch({
spreadsheetId: <YOURSHEETID>,
key: <YOURAPIKEY>,
}).then( ... )
const data = gsobj.save({ ... }).then( ... )
const data = gsobj.fetchMeta({ ... }).then( ... )
const data = gsobj.fetchTable({ ... },'mysheet').then( ... )
You know all that Google API stuff? Awesome, right? Well this makes it into what you really want, JavaScript objects implied from the sheet itself.
Created initially to pull down the data in a friendlier (albeit larger)
way for populating Mustache templates in custom static, serverless
JAMstack site generation using only Gulp (which
is much simpler than setting up GraphQL as Gatsby requires). In fact,
you can just include the data.json
in your static site as a poor-man's
read API for simple, smaller data-driven web sites.
Algorithm
The sheet meta data is first requested to get the list of tables/sheets
and the timeZone
. This can be done explicitly by calling
fetchMeta(params)
.
Schema
The sheet name is required and becomes the effective table name. This means only one range per sheet.
A range is assumed to represent a collection of one-dimensional objects always with headers as property names. The default return type for any range is therefore a list of objects with properties.
If the first row of the first column is blank then the first column is assumed to be key name instead giving the object two dimensions of values, the primary from the column header, and secondary from the first column values.
Obviously headers and first-rows have to contain only unique values.
Names
Names are kept as they are allowing developers to decide to use
long-hand indexing (with []
) or stick with dotted notation legal
names.
The only reserved name is the spreadsheet/table name meta
which is
used to contain information about all of the parsed data as detected.
Just do not name any individual sheet in your spreadsheet meta
and you
are good.
Dates
By default all dates are returned as strings they way they are formatted. Usually these are going to be directly included in other output views. If conversion is needed be sure to pick a format that can be easily parsed.
This keeps strings like 7-12
from being interpreted as dates even if
the field type of the spreadsheet column is not explicitly set. This
simply saves time for most practical applications, which is the point of
this module.
Data Preparation Suggestions
Remove any rows or columns that are not being used to avoid blanks being returned.
Although you can have as many columns as Google allows, you should limit the number of columns to the letters of the alphabet (27). This is plenty and will encourage you to keep your data at least somewhat normalized.
Hidden rows and columns are not hidden. Data will have to either be deleted from the sheet or filtered after the data is fetched and returned by the caller. This encourages you to create an aging data strategy.
Testing
Since this requires setting up a Google Sheets spreadsheet with an API key testing has been commented out by default but left in to demonstrate. (If you have a good suggestions on how that could be automated without blowing up the quota let me know.)