4 ms·
I built the content for an entire news app with just Google Sheets: http://projects.propublica.org/sopa/ http://projects.propublica.org/sopa/ I imported the li
by danso 11y ago
I built the content for an entire news app with just Google Sheets: http://projects.propublica.org/sopa/ http://projects.propublica.org/sopa/
I imported the list of Congressmembers from Sunlight Foundation (which is itself a spreadsheet [1]...then I wrote a script to pull campaign finance data from OpenSecrets using the unique identifiers in the Sunlight sheet [2]. I believe I used the NYT's Congress API [3] to get term data and votes-with-party percentage...though at the time I hadn't known about how to get the data from www.govtrack.us (which has bulk downloads of bill/vote data via rsync)
Then for the rest of the data (news items that featured a given Congressmember), I just researched manually and entered it in by hand into another sheet. I eventually imported everything into a database to make it a Rails app because that's the only way I knew how to build an app at the time...but the laborious part, the research and data entry, was made possible through the use of a spreadsheet...I didn't have to spend time building an admin interface that, no matter how well designed, would have almost been certainly klunkier than using Google Sheets.
The tradeoff is that you have be disciplined in your data entry process...i.e. unique identifiers have to be spelled consistently, as you don't have the ability to enforce constraints or enumeration (well, not without writing a lot of custom JS to run inside of Google Sheets) the same way you do with databases. This isn't too hard if you're working by yourself and you're proficient with keyboard shortcuts (Cmd-C, Cmd-V, Cmd-Tab, particularly)...but it's not easy to bring other people into the project, ad-hoc.
[1] https://sunlightlabs.github.io/congress/#legislator-spreadsheet https://sunlightlabs.github.io/congress/#legislator-spreadsh...
[2] https://www.opensecrets.org/resources/create/apis.php https://www.opensecrets.org/resources/create/apis.php
[3] http://developer.nytimes.com/docs/congress_api http://developer.nytimes.com/docs/congress_api
Today I would most definitely not have moved it to Rails, as it was a relatively small dataset and the app didn't need anything besides static pages. I now do almost all my medium-to-small apps in Middleman, which is a slightly more complicated version of Jekyll (basically, you can execute Erb instead of being restricted to Liquid). Usually I start off with a spreadsheet, but if the dataset is small enough, I'll record the content in YAML.
https://middlemanapp.com/ https://middlemanapp.com/
edit: added mention of the NYT API
- devbug 11y agoHave you looked at Webhook? http://www.webhook.com/ http://www.webhook.com/
- kalven 11y ago"The tradeoff is that you have be disciplined in your data entry process...i.e. unique identifiers have to be spelled consistently, as you don't have the ability to enforce constraints or enumeration" This is a really good point and one I should have included (I wrote the piece above). There is no easy way to validate syntax and spelling in the spreadsheet and we would often find ourselves hunting around, trying to find the typo that was breaking a particular project. We obviously got better at avoiding this situation as time went on, but it made the learning curve all the more difficult for new users.
- aendruk 11y agoWhen I attempted a similar setup (Middleman + Google Sheets) I was quickly cornered into using fuzzy string matching to load data successfully. I ended up adding unplanned data validations that enabled the app to suggest corrections, but in retrospect I wish I'd done more validation in the spreadsheet itself for a tighter feedback loop early on.
- Someone 11y agoPivot tables can help there. If you pivot on color, you will more easily see that lone "grey" between the "gray"'s. You can also copy-paste-as-values a known-good one, and compare what you have know against what you had last week. And of course, you can enforce an enumeration in Excel. See https://support.office.com/en-us/article/Apply-data-validation-to-cells-c743a24a-bc48-41f1-bd92-95b6aeeb73c9 https://support.office.com/en-us/article/Apply-data-validati...
- epochwolf 11y agoLet me point out that Excel has had data validation in spreadsheets for years. It is trivial to enforce basic data validation without writing any code.