Render 2017

Slides from my talk at Render Conf in Oxford, UK in March 2017. I talk about Sheetsee.js, spreadsheets, and Glitch!

Transcript

1. Spreadsheets, Forms and Forks Jessica Lord — Render 2017 — @jllord
2. Hello! @jllord
4. Spreadsheets, using tools so that making for the web is 
 more approachable, easier, maybe fun? Let’s talk about
5. Code for America Boston City Hall
6. 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.
7. 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.
8. :( sad face The project’s fate: the county rebuilt the site in .net, closed the source code and access to the raw data.
9. :) 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!
10. Hooked on spreadsheets.
11. Spectrum of websites single static page,
 no data massive web app,
 lots of secure data SO MUCH SPACE
12. Despite the tricks, there are hurdles — Hosting $ — Hosting service — Database — Settings — Collaborators — Non developers
13. Spectrum of Websites single static page,
 no data massive web app,
 lots of secure data SPREADSHEETS
14. Spreadsheet is a database, already set up and with an interface people already know.
15. Spreadsheets! Spreadsheets are free, no install, easy to use, easy to share. Yes, some limits: cells/column limits, Google bandwidth.
16. No deploys, just edit sheet
17. Years have gone by. What does it matter now?
18. Dang, srsly?
19. Sheetsee.js A little client-side library to visualize spreadsheet data—seeing your spreadsheet in a new way. And I’ve re-written it!
20. Sheetsee
22. Visualizing In maps or sortable and 
 searchable tables.
23. Spreadsheet data Nice JSON you get from using Tabletop.js. Fruit Color Apple Red [ { “Fruit”: “Apple”, “Color”: “Red” } ]
24. 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.
25. 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
26. 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).
27. Tables+ More than just tables!
28. Tables+ More than just tables!
29. Maps Pretty much maps, but there is clustering!
30. Sheetsee Charts? No more! Remove features = happier maintainer.
31. Using Sheetsee
32. 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! }
33. Create a placeholder With both maps and tables you’ll create a placeholder div in your HTML. <div id=“mymap”></div> <div id=“mytable”></div>
34. 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)
35. Sheetsee Maps var mapOptions = { data: data, mapDiv: 'map', geoJSONincludes: ['Name', 'Animal', 'Rating'], template: '<p>{{Name}}—{{Animal}}—{{Rating}}</p>', cluster: true, hexcolor: '#e91e63' } Sheetsee.loadMap(mapOptions)
36. Sheetsee Tables var tableOptions = { data: data, pagination: 10, tableDiv: "#fullTable", filterDiv: "#fullTableFilter", templateID: "fullTable_template" } Sheetsee.makeTable(tableOptions)
37. Sheetsee Tables Also just*two* methods! Sheetsee.initiateTableFilter(tableOptions)
38. Sheetsee Tables You’ll need to make a template for how you want it to display. <script id="fullTable_template" type="text/html"> <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> </script>
39. Sheetsee Tables Also! Add column sorting to your table with .theader classes in your table header cells.
40. Sheetsee Core Sheetsee.getKeywordCount(data, keyword) Sheetsee.getKeyword(data, keyword) Sheetsee.getColumnTotal(data, column) Sheetsee.getColumnAverage(data, column) Sheetsee.getMin(data, column) Sheetsee.getMax(data, column) Sheetsee.getMatches(data, filter, column) Sheetsee.getOccurance(data, column)
41. Build things!
42. Beyond Basics You can use your spreadsheet data in other ways besides just spitting it out into a table.
43. Query Strings https://maps.google.com/maps?q={{address}},{{city}},{{state}}
44. Math(s)! data.length()
45. IFTTT Do what you
 want with your
 data!
47. Google Forms Also generate spreadsheets! Yes, way No, way
48. Use existing resources to reduce barriers to the web.
49. 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!).
50. Fork-n-Go
51. Glitch
52. 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!
53. Doing Good
54. sheetsee.glitch.me
55. spreadsheet.glitch.me
56. 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.
57. spreadsheet.glitch.me spreadsheet.glitch.me
58. Spreadsheets, Forms (and other stuff) and Forks and Glitches
59. Scale down, people up.
60. Thanks! speakerdeck.com/jllord/render-2017