5 ms·
Just something I discovered recently that entirely takes care of the problem if you have ONE PERSON who knows how to code: Google Sheets with the IMPORTHTML() f
by fragsworth 9y ago
Just something I discovered recently that entirely takes care of the problem if you have ONE PERSON who knows how to code: Google Sheets with the IMPORTHTML() function.
You host a web server that runs Python or PHP or whatever and it queries and pulls data from any number of places, and dumps the information to CSV. Then in the Google Doc, you request the data from your server with the IMPORTHTML function. It populates your spreadsheet automatically, and then you can show graphs and other various things. And it will update on its own!
There you have it! Single source of truth! And you still get to use a spreadsheet!
It's a fucking beautiful, elegant solution to the entire "Excel problem" that I don't see enough people using.
Instead they're going with enterprise 3rd party "solutions", like Anaplan. Who knows how much money they're blowing because the execs don't know tech...
- wiradikusuma 9y agoYou mean the function inside your Doc(ument) will periodically fetch the http://myweb.com/periodically_generated.csv http://myweb.com/periodically_generated.csv? Can't you just "push" to the target Doc(ument) instead?
- fragsworth 9y agoYes, IMPORTHTML fetches your csv, but you can have your CSV populated by your server dynamically on each request. You can't push to a Google Sheet as far as I know. But polling is just as good for these purposes, your data will be up to a few minutes old. If you want true live data then you should use something else, but that's not what the finance chiefs are complaining about. They want people to stop manually importing CSV data because it's really slow, and causes lots of errors. EDIT: I was going off the assumption that there was actually a database somewhere. Turns out they're using Excel as the database. I'd still argue that one person who codes can fix it. Take your Excel sheets, put them into an SQL database, and dump to CSV for importing to Google Docs for graph/display purposes.
- roadnottaken 9y agoUnless I'm missing something, this only provides a way to display data from a database. But users cannot write to the database via the Google Sheet, and it thus doesn't solve the problem...
- fragsworth 9y agoOh god you're right, I just re-read the article and realized what they're doing now. It didn't even occur to me that anyone would use Excel as the database. Holy hell. Nevermind. Still, it shouldn't be hard to convert Excel to an SQL database. Then you dump to CSV, and use ImportHTML to have a spreadsheet be your source of information.
- Amezarak 9y agoExcel can use databases as a data source, there's no need for all your extra steps.
- ZenoArrow 9y agoJust FYI, Excel has that too. Check the 'Get Data' options under the Data tab.