Show HN: SpreadsheetDB – A database that you can query with spreadsheets

https://www.spreadsheetdb.io/

by Clex • 9 years ago

144 42 9 years ago

I'd love to see this concept taken a step further and be able to write back changes (within reason, of course) to a SQL database.

The piece I'd most love to see is some form of editable Pivot Table, where the changes get written back to the appropriate place in the database. For example:

    CREATE TABLE Balances(Account varchar, Scenario varchar, Amount numeric, primary key(Account,Scenario));
    -- Pretend that this is pre-populated with all Account and Scenario combinations

Using this schema, I would want to get a Pivot Table out of this where Account makes up the row labels, Scenario forms the column labels, and the Amount values are the data. Where the magic happens is that I want to be able to make changes to the amounts and have it write those back into the database with the appropriate UPDATE statements.

cyberferret2• 9 years ago

Be prepared for a bill from your DBA for his/her blood pressure medication then... or the accounts team when they realise that a missing conversion of a string to a numeric caused you to zero out every customer's account balance... ;) (Based on real world experience with my clients over 3 decades)

Even 20 years ago good old Microsoft with their MSQuery capability in Excel stopped you from doing this every way they could.

Mister_Snuggles2• 9 years ago

Depending on how it's done it can be perfectly safe.

I've actually built a thing, in Lazarus of all things, to do this very thing. Unfortunately it's very specific to my use-case, but it is "safe" in the sense that it doesn't screw things up too badly. My particular use case is actually very spreadsheet-like, but I also want some things that are a lot easier to do in SQL than in Excel.

My use case is to have a tool to help me evaluate monthly financial statements for a smallish organization, enter my own adjustments, and come up with a final statement.

It's spreadsheet-like in the sense that I enter everything into a grid of rows and columns. The rows are accounts (in the income statement sense, not customer accounts), columns are a "type" (e.g., actuals, my adjustments, adjustments made by the auditor, adjusted actuals, budget, budget vs actuals, etc). Some accounts and some types are calculated, thus read-only in the table. There's also a drop-down at the top to select a period.

Basically, it's everything that a basic Excel Pivot Table does, but database-backed.

My Lazarus program lets me fill in that table however I want, when I hit save it persists it all to the database and runs a stored procedure to recalculate all of the calculated values. The database makes sure that I don't try to put a string in a numeric field, and it (PostgreSQL) is smart enough not to convert strings to zeroes.

The thing that makes this better than a spreadsheet is that I can just add a new account/scenario/whatever and run a stored procedure to insert the missing rows in the data table. When I did a similar thing with Excel, it was easy enough to insert rows. The hard part was inserting all of the rows everywhere they needed to go (I had a tab for budget, a tab for actuals, a tab for adjustments, etc) and updating all of the formulas.

fiatjaf• 9 years ago

The idea is very nice, but I don't like the fact that it is just an API for calculations. That kind of defeats the purpose of a spreadsheet format.

At first I thought it was an actual spreadsheet application, or an Excel plugin or whatever.

I tried multiple times to create a spreadsheet app that would speak to JSON APIs easily from within cells. The problem is that writing a spreadsheet app from scratch is not easy (one of my tries: http://sheets.alhur.es/), but when that is done it is not hard to integrate it directly with PouchDB, CouchDB -- or any other database or application, actually, with a custom integration.

tekromancr• 9 years ago

You might want to look at https://github.com/handsontable/handsontable for the front end. You could probably mash it up with this to build what you describe.

codezero2• 9 years ago

It's a little known fact (is it?) that you can use a SQL like query language on Google Sheets.

You can query across different tabs on a single sheet, or query a totally different sheet!

https://developers.google.com/chart/interactive/docs/queryla...

https://support.google.com/docs/answer/3093343?hl=en

r00fus• 9 years ago

Wow. This is what I've wanted for years. Amazing you can even write a query into a URL string.

emmelaich• 9 years ago

You might like (love!) this: "Programming in the URL string"

http://apenwarr.ca/log/?m=201212#18

http://afterquery.appspot.com/help

beering• 9 years ago

This feature makes me think, "When all you have is SQL, everything looks like a relational database."

I recently confused some co-workers when they examined my Google Sheets query - not knowing Excel very well, I just wrote SQL-ish. Now they have to live with my SQL-ish spreadsheet or figure out how to redo it with Excel functions.

andrewstuart2• 9 years ago

How about "when all data is fundamentally structured, a Structured Query Language will be highly useful in many cases?"

Seriously, we live in a universe where everything is composed of lots of smaller things that exist in relation to each other. Relational Data isn't so much a database technology as it is fundamental information theory.

tensor• 9 years ago

Relational calculus is pretty fundamental. A better quote might be "Every application that uses complex data has buggy hand coded relational database plans it in."

galdosdi• 9 years ago

If you work with data, you should learn basic SQL. It's a lot more useful and safe than a tangle of Excel functions.

The relational model was a real breakthrough. It really does describe, generically, any data structure you may have. Folks who adopted NoSQL solutions are now learning this the hard way

TAForObvReasons• 9 years ago

It's a little known fact (is it?) that there are database functions in Excel! You can calculate simple aggregates like count and sum of records subject to some constraints. Example: https://support.office.com/en-us/article/DAVERAGE-function-a...

These functions were available since Excel 2.0 but many people don't know they exist

infogulch• 9 years ago

Excel's filtering syntax (e.g. SUMIF) and this function are obtuse, difficult to use, and very simplistic just compared to a WHERE clause on a sql statement. And this ignores other very powerful sql features like pivot and group by that are exceedingly difficult for excel to produce.

codezero2• 9 years ago

High five to you for adopting (did you?) my (is it?) quasi-half-assertion.

mwexler• 9 years ago

You could also query Excel with SQL; the ODBC driver is part of Windows. http://stackoverflow.com/questions/15844633/using-excel-as-a... gives an example of how to use. Not the best database in the world, but sometimes "SQL can save ya".

macNchz• 9 years ago

I often wind up needing kind of the opposite of this: a spreadsheet as a view on a complex SQL query I've written, so that folks can manipulate the results themselves.

A quick way I've set this up with Google Sheets is via IMPORTDATA(), combined with a url that dumps a csv of my query results. That way people can do all sorts of analysis and manipulation in a different tab while being able to refresh the underlying data. https://support.google.com/docs/answer/3093335?hl=en

pbreit• 9 years ago

Also little known, extremely powerful feature of Google Apps is full-on scripting: https://developers.google.com/apps-script/overview

With urlFetchApp you can use it as an API runner: https://developers.google.com/apps-script/reference/url-fetc...

vgt• 9 years ago

And BigQuery!

SEJeff• 9 years ago

Aka Access?

londons_explore• 9 years ago

SSSh - we like reinventing the wheel in web based form!

throwanem• 9 years ago

Or Excel! It's been able to backend to various RDBMSes for at least twenty years now. Ripe for disruption, no doubt.

ukd1• 9 years ago

Cool idea, but that logo just reads as "preadsheet", which, is not exactly positive.

Zombieball• 9 years ago

Good call out. I just want to add that the issue is exacerbated on a mobile phone. iPhone 6, portrait mode, the "S" and "preadsheet" are even on different lines!

The linewrap goes away when I rotate to landscape mode.

Clex3• 9 years ago

Thanks for the feedback, that will get fixed soon.

sandGorgon• 9 years ago

pretty cool - I have been a fan of tools like Airtable.com (IMHO the best cloud spreadsheet I have seen). In my own startup, I have hacked together a version of a cloud spreadsheet using Handsontable + reactjs + flask api.

Can you talk about the stack you are using ? really interested to hear about it.

Clex3• 9 years ago

Thank you for the kind words!

Airtable is really neat indeed. But SpreadsheetDB aims to be more a database with an alternative paradigm than an actual spreadsheet editor.

Regarding the stack, it is made with Go and Postgres.

brilliantcode• 9 years ago

Not sure what pain problems this solves. You can already programmatically access and manipulate Google Sheets.

And no, you are not going to run in to their upper bound limitations on rows and columns often.

franciscop• 9 years ago

I created something simpler but based on Google Docs and just retrofitted promises into it: https://github.com/franciscop/drive-db

eob• 9 years ago

Cool idea. We've built a spreadsheet-powered web platform (Cloudstitch - YC S15) that lets you publish web forms and widgets that rely on spreadsheets as a backend.

Several folks use us just for the spreadsheet API -- as this service seems oriented. We provide a simple, common API that can talk to both GSheets and MS Excel 365, support various forms of row-level access control, and Ruby on Rails-like join syntax. Would love to hear feedback.

falsedan2• 9 years ago

Other posters have mentioned Microsoft's Excel 20-year-old ODBC support [0]: you can connect any database as a source of data, then create pivot tables for live ad-hoc transforms and summarizations of a database. This is a separate feature to connecting to an Excel spreadsheet using ODBC.

I showed this to my manager and he immediately demanded I explain to the rest of the team how this black magic worked…

[0] https://support.office.com/en-gb/article/Overview-of-connect...

lancewiggs• 9 years ago

ODBC is over 20 years old.

I was using ODBC from MS Access to query Oracle databases in the early 90s - both ODBC and (preferably) pass-through queries. The entire system included then generating (recursive) reports in Access, passing the data to Excel to integrate with other data and input, opening and updating a Powerpoint file with linked tables, saving the PPT and returning to Excel for the next in the series.

All driven by VBA. All in 1995, maybe 1994 too.

I had to restart Windows after each run of 20 or so, and had two computers as one was always tied up running jobs. Kludgy but at least I wan't writing COBOL.

Here is some 1994 PR: http://archives.cpajournal.com/old/16531700.htm

dizzystar• 9 years ago

Was going to post about this. The ODBC support is actually pretty awesome, but it is a pain in the ass to get working on some databases. If you want to connect to PostgreSQL, for example, go through these steps and enter the bowels of Windows:

http://www.dashbay.com/2011/03/working-with-postgres-on-wind...

You can also use the ODBC in LibreOffice Calc to similar effect. You can also connect to the database with MS Access and LibreOffice Base. In my opinion, it is much easier to make an intuitive interface for a non-tech user. SpreadsheetDB seems to want curl and JSON requests, which is never going to be used by a non-tech.

nodesocket• 9 years ago

Somewhat related. I've been using AirTable (https://airtable.com/) to manage my consulting startups clients and even track hours. Absolutely loving it.

You define tables and scheme in their beautiful excel like web interface. AirTable then automagically generates a RESTful API you can build on-top of. They even have a great Node.js ORM[1] library. Finally, you can export public embeddable forms for inserting data into tables.

I'd love to see somebody create a React based front-end that pulls data and schema from AirTable and automatically creates a CRUD interface like Rails does. All inclusive authentication (Twitter, Facebook, GitHub) and user level permissions.

[1] https://github.com/airtable/airtable.js

sirrice• 9 years ago

There's some related research out of UIUC that's pretty neat. http://dataspread.github.io/

garyclarke272• 9 years ago

I was an Excel expert before becoming a sql expert. Following this I changed the way I created large excel models, now 90% of my data is in Excel tables. The main benefit of these is they are much easier to use and manage than range names and the ranges automatically change their size to contain the data. Wish ms would add unique row ids (as we'll as the unique column ids they have) is a bit cumbersome having to setup index match everywhere to link rows in different tables. It's amazing what you can do with sumifs and countifs these are far more powerful then the sumif - very similar to sql where clause, with multiple criteria such as <= , works with text and numbers. I used to use tons of pivot tables but no longer, they are too unstable and confusing and very hard to format nicely.

sidegrid• 9 years ago

Any good resources you used to learn this (to replace pivot tables)?

Show HN: SpreadsheetDB – A database that you can query with spreadsheets

Related Stories