Spreadsheets,
Forms and Forks
Jessica Lord — Render 2017 — @jllord
Slide 2
Slide 2 text
Hello!
@jllord
Slide 3
Slide 3 text
No content
Slide 4
Slide 4 text
Spreadsheets, using tools so that making for the web is
more approachable, easier, maybe fun?
Let’s talk about
Slide 5
Slide 5 text
Code for America
Boston City Hall
Slide 6
Slide 6 text
Yes to DIY, No to Bottlenecks
Making better use of existing tools so that cities can get things done
and maintain sites themselves, avoiding IT Department bottlenecks.
Slide 7
Slide 7 text
Magic WordPress Theme
Open source, could be used by other cities and talked to a Google
Spreadsheet. Staff in the budget department just needed to update
the spreadsheet.
Slide 8
Slide 8 text
:( sad face
The project’s fate: the county rebuilt the site in .net, closed the source
code and access to the raw data.
Slide 9
Slide 9 text
:) happy face
There was still a good ending: got a Mozilla OpenNews Code Sprint
Grant to pull out the JavaScript from the project and make a
standalone library. That became sheetsee.js!
Slide 10
Slide 10 text
Hooked on
spreadsheets.
Slide 11
Slide 11 text
Spectrum of websites
single static page,
no data
massive web app,
lots of secure data
SO MUCH SPACE
Slide 12
Slide 12 text
Despite the tricks, there are hurdles
— Hosting $
— Hosting service
— Database
— Settings
— Collaborators
— Non developers
Slide 13
Slide 13 text
Spectrum of Websites
single static page,
no data
massive web app,
lots of secure data
SPREADSHEETS
Slide 14
Slide 14 text
Spreadsheet is a
database, already set up
and with an interface
people already know.
Slide 15
Slide 15 text
Spreadsheets!
Spreadsheets are free, no install, easy to use, easy to share.
Yes, some limits: cells/column limits, Google bandwidth.
Slide 16
Slide 16 text
No deploys, just edit sheet
Slide 17
Slide 17 text
Years have gone by.
What does it matter now?
Slide 18
Slide 18 text
Dang, srsly?
Slide 19
Slide 19 text
Sheetsee.js
A little client-side library to visualize spreadsheet data—seeing your
spreadsheet in a new way.
And I’ve re-written it!
Slide 20
Slide 20 text
Sheetsee
Slide 21
Slide 21 text
No content
Slide 22
Slide 22 text
Visualizing
In maps or sortable and
searchable tables.
Slide 23
Slide 23 text
Spreadsheet data
Nice JSON you get from using Tabletop.js.
Fruit Color
Apple Red
[
{
“Fruit”: “Apple”,
“Color”: “Red”
}
]
Slide 24
Slide 24 text
Public Spreadsheet
You do need to publish your spreadsheet so that it can be reached,
but you don’t have to make it editable. Each spreadsheet has a
unique KEY, Tabletop uses this to fetch the contents.
Slide 25
Slide 25 text
Sheetsee Modules
sheetsee
sheetsee-core
sheetsee-tables
sheetsee-maps
sheetsee.js
command line tool to build sheetsee.js
in every build, basic data functions
build sortable, searchable table
make a map with popups
full library and documentation site
Slide 26
Slide 26 text
Sheetsee CLI
npm install -g sheetsee
cd my-cool-site
sheetsee -t --save
Bundles sheetsee-core, sheetsee-tables and mustache.js (and sheetsee-
maps and leaflet.js if including maps).
Slide 27
Slide 27 text
Tables+
More than just tables!
Slide 28
Slide 28 text
Tables+
More than just tables!
Slide 29
Slide 29 text
Maps
Pretty much maps, but
there is clustering!
Slide 30
Slide 30 text
Sheetsee Charts?
No more!
Remove features = happier maintainer.
Slide 31
Slide 31 text
Using Sheetsee
Slide 32
Slide 32 text
Use Tabletop.js
Use Tabletop to send your spreadsheet data to a function:
document.addEventListener('DOMContentLoaded', function() {
var KEY = 'YOURSPREADSHEETSKEYHERE'
Tabletop.init({key: KEY, callback: doStuff, simpleSheet: true})
})
function doStuff (data) {
// Use Sheetsee!
}
Slide 33
Slide 33 text
Create a placeholder
With both maps and tables you’ll create a placeholder div in your HTML.
Slide 34
Slide 34 text
Sheetsee Maps
One of *two* methods. Use this if you want geoJSON (and certain
data from your spreadsheet in it), you don’t have to.
var optionsJSON = ['name', 'breed', 'cuddlability']
var geoJSON = Sheetsee.createGeoJSON(data, optionsJSON)
Sheetsee Tables
Also just*two* methods!
Sheetsee.initiateTableFilter(tableOptions)
Slide 38
Slide 38 text
Sheetsee Tables
You’ll need to make a template for how you want it to display.
<table>
<tr><th class="tHeader">City</th><th class="tHeader">Place Name</th></tr>
{{#rows}}
<tr><td>{{City}}</td><td>{{PlaceName}}</td></tr>
{{/rows}}
</table>
Slide 39
Slide 39 text
Sheetsee Tables
Also! Add column sorting to your table with .theader classes in
your table header cells.
Google Forms
Also generate spreadsheets!
Yes, way No, way
Slide 48
Slide 48 text
Use existing
resources to reduce
barriers to the web.
Slide 49
Slide 49 text
GitHub Pages
— Free hosting of static or Jekyll sites.
— Can now be hosted from any branch!
— Supports HTTPS!
— A forked site needs *one* commit before it’s hosted—make that
changing a spreadsheet key, editor in browser (no dev env!).
Slide 50
Slide 50 text
Fork-n-Go
Slide 51
Slide 51 text
Glitch
Slide 52
Slide 52 text
Glitch
— Free hosting and servers for Node.js apps
— In browser editor, collaborative editing!
— Glitch Remix is like a GitHub Fork (it can also connect to GH)
— FUN!
Slide 53
Slide 53 text
Doing Good
Slide 54
Slide 54 text
sheetsee.glitch.me
Slide 55
Slide 55 text
spreadsheet.glitch.me
Slide 56
Slide 56 text
spreadsheet.glitch.me
API for getting your spreadsheet as JSON (uses Tabletop.js).
spreadsheet.glitch.me/?key=YOURSPREADSHEETKEY
You can also paste key into the site’s form.
Slide 57
Slide 57 text
spreadsheet.glitch.me
spreadsheet.glitch.me
Slide 58
Slide 58 text
Spreadsheets,
Forms (and other
stuff) and Forks and
Glitches