sheetsy

Use Google Sheets as a data source for your webapps. No need to use the mail Google API or OAuth.

1. Create a spreadsheet
2. Create a webapp that points at that spreadsheet
3. 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

1. Create a spreadsheet with Google Sheets
2. Navigate to the "File" menu
3. 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".
4. Click "Publish"
5. Close the "Publish to the web" dialog
6. Click the blue "Share" button at the top-right
7. If you want to make it slightly harder for people to find your spreadsheet:
   1. Click "advanced" at the bottom-right of the "Share" dialog
   2. Under "Who has access", click "Change"
   3. Select "On - Anyone with the link"
   4. Click "Save"
8. 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:

1. Turn the URL into a key
2. Using the key, fetch the top-level workbook containing a list of sheets
3. With the original key and a sheet id from the list, fetch one of the sheets to access its rows

API

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: '[e-mail]',
//			}
//		],
//		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: '[e-mail]',
//			}
//		],
//		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.

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.

Optional argument: 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 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').

License

WTFPL