Use Google Sheets as a data source in the browser without OAuth
Use Google Sheets as a data source for your webapps. No need to use the Google API or OAuth.
Why write a CMS for small projects when you could just edit a spreadsheet?
Works in node and the browser. The ES5 browser build is ~1310 bytes minified/gzipped.
This library owes a lot to @jsoma and Tabletop.js.
Tabletop's implementation pointed me at the endpoints to hit, and saved me a lot of investigation into the specifics of how to fetch the data from Google Sheets.
Sheetsy is much simpler than Tabletop, by virtue of doing less, avoiding legacy IE and HTTP support, and being composed of pure functions, which allows this library to be more thoroughly tested.
That URL is the one you'll use to load content from your page.
This is what you'll do:
const sheetsy = require('sheetsy')
const { urlToKey, getWorkbook, getSheet } = sheetsy
key = urlToKey(url)
Given a url
string, returns a key
string. Throws an error if no key is found.
const key = urlToKey(
'https://docs.google.com/spreadsheets/d/14uk6kljx-tpGJeObmi22DkAyVRFK5Z1qKmSXy1ewuHs/pubhtml'
) // => '14uk6kljx-tpGJeObmi22DkAyVRFK5Z1qKmSXy1ewuHs'
promise = getWorkbook(key, [httpGet])
Given a key
string, returns a Promise that resolves to a object containing an object describing the workbook.
getWorkbook('14uk6kljx-tpGJeObmi22DkAyVRFK5Z1qKmSXy1ewuHs').then(workbook => {
workbook // => {
// name: 'Sheetsy test',
// authors: [
// {
// name: 'joshduffman',
// email: '[email protected]',
// }
// ],
// updated: '2017-07-14T04:59:24.123Z',
// sheets: [
// { name: 'Herp', id: 'od6', updated: '2017-07-14T04:59:24.123Z' },
// { name: 'Derp', id: 'of6b9b5', updated: '2017-07-14T04:59:24.123Z' }
// ]
// }
})
promise = getSheet(key, id, [httpGet])
Given a key
string and an id
string from the workbook results, returns a Promise that resolves to a sheets object:
getSheet('14uk6kljx-tpGJeObmi22DkAyVRFK5Z1qKmSXy1ewuHs', '0d6').then(sheet => {
sheet // => {
// name: 'Herp,'
// updated: '2017-07-14T04:59:24.123Z',
// authors: [
// {
// name: 'joshduffman',
// email: '[email protected]',
// }
// ],
// rows: [
// rowArray({
// firstheader: `Something's up`,
// secondheader: `That's "cool"`,
// }),
// rowArray({
// firstheader: 'a3',
// secondheader: 'b3',
// }, [ 'a3', 'b3', 'c3' ])
// ]
// }
})
Rows are arrays, like [ 'Something\'s up', 'That\'s "cool"' ]
and [ 'a3', 'b3', 'c3' ]
, but you can also access values in a row by using header names as properties.
In this example, the header name (defined by the value in cell A1) is "First header":
getSheet('14uk6kljx-tpGJeObmi22DkAyVRFK5Z1qKmSXy1ewuHs', '0d6').then(sheet => {
const firstRow = sheet.rows[0]
firstRow.firstheader // => `Something's up`
})
See below to figure out the property names for a column.
The column names are generated by Google Sheets, not by this library.
Whether you format the first row as a header or not, Google Sheets will treat it as if it is the header row of your sheet, and will use the values in those columns to generate the names of all of the column values in all the rows following.
It appears to strip spaces and lowercase your text.
httpGet
The getSheet
and getWorkbook
functions take as an optional argument a function that takes a url and return a promise that resolves to the response body.
This defaults to a function backed by XMLHttpRequest
in the browser, or the got
module in node.
You can pass in your own function if you want to try to get it working with old IE or restrictive CORS settings or what-have-you.
Your browser bundler will need to respect the browser
field in package.json
. Given that, all you need to do is import sheetsy from 'sheetsy'
or const sheetsy = require('sheetsy')
.