Use Google Sheets as a data source for your webapps. No need to use the mail Google API or OAuth.
- Create a spreadsheet
- Create a webapp that points at that spreadsheet
- Update the spreadsheet, watch your app update automatically
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.
Relationship to Tabletop.js
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.
How to set up your Google Spreadsheet
- Create a spreadsheet with Google Sheets
- Navigate to the "File" menu
- Click "Publish to the Web"
- It should default to "Entire Document" + "Web page", which is what you want.
- The defaults should be fine for "Published content & settings" as well - for maximum easiness, you'll want to "Automatically republish when changes are made".
- Click "Publish"
- Close the "Publish to the web" dialog
- Click the blue "Share" button at the top-right
- If you want to make it slightly harder for people to find your spreadsheet:
- Click "advanced" at the bottom-right of the "Share" dialog
- Under "Who has access", click "Change"
- Select "On - Anyone with the link"
- Click "Save"
- Copy the "Link to share"
That URL is the one you'll use to load content from your page.
Using a published Google Spreadsheet as a data source
This is what you'll do:
- Turn the URL into a key
- Using the key, fetch the top-level workbook containing a list of sheets
- With the original key and a sheet id from the list, fetch one of the sheets to access its rows
API
const 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 = // => '14uk6kljx-tpGJeObmi22DkAyVRFK5Z1qKmSXy1ewuHs'
promise = getWorkbook(key, [httpGet])
Given a key
string, returns a Promise that resolves to a object containing an object describing the workbook.
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:
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":
See below to figure out the property names for a column.
Column names/property names
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
Optional argument: 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 from with old IE or restrictive CORS settings or what-have-you.
How to use in the browser or node
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')
.