7 ms·

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

144 points by Clex 9y ago

Mister_Snuggles 9y 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.
- cyberferret 9y 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_Snuggles 9y 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 9y 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/ 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 9y ago
  You might want to look at https://github.com/handsontable/handsontable https://github.com/handsontable/handsontable for the front end. You could probably mash it up with this to build what you describe.
codezero 9y 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/querylanguage https://developers.google.com/chart/interactive/docs/queryla... https://support.google.com/docs/answer/3093343?hl=en https://support.google.com/docs/answer/3093343?hl=en
- r00fus 9y ago
  Wow. This is what I've wanted for years. Amazing you can even write a query into a URL string.
  - emmelaich 9y ago
    You might like (love!) this: "Programming in the URL string" http://apenwarr.ca/log/?m=201212#18 http://apenwarr.ca/log/?m=201212#18 http://afterquery.appspot.com/help http://afterquery.appspot.com/help
- beering 9y 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 9y 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.
    - iheartmemcache 9y ago
SEJeff 9y ago
Aka Access?
- londons_explore 9y ago
  SSSh - we like reinventing the wheel in web based form!
- throwanem 9y ago
  Or Excel! It's been able to backend to various RDBMSes for at least twenty years now. Ripe for disruption, no doubt.
ukd1 9y ago
Cool idea, but that logo just reads as "preadsheet", which, is not exactly positive.
- Zombieball 9y 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.
  - Clex 9y ago
    Thanks for the feedback, that will get fixed soon.
    - cyberferret 9y ago
      Also, you mis-spelled "guarantee" under "99% uptime"...
      - Clex 9y ago
        Fixed, thank you.
sandGorgon 9y 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.
- Clex 9y 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 9y 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 9y ago
I created something simpler but based on Google Docs and just retrofitted promises into it: https://github.com/franciscop/drive-db https://github.com/franciscop/drive-db
eob 9y 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.
falsedan 9y 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-connecting-to-importing-data-c0ad7aec-ff1a-4e48-9c21-dc18a102433f https://support.office.com/en-gb/article/Overview-of-connect...
- lancewiggs 9y 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 http://archives.cpajournal.com/old/16531700.htm
- dizzystar 9y 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-windows-via-odbc/ 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 9y ago
Somewhat related. I've been using AirTable (https://airtable.com/ 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 https://github.com/airtable/airtable.js
sirrice 9y ago
There's some related research out of UIUC that's pretty neat. http://dataspread.github.io/ http://dataspread.github.io/
garyclarke27 9y 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 9y ago
  Any good resources you used to learn this (to replace pivot tables)?

144 points by Clex 9y 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.

cyberferret 9y 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_Snuggles 9y 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 9y 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/ 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 9y ago
You might want to look at https://github.com/handsontable/handsontable https://github.com/handsontable/handsontable for the front end. You could probably mash it up with this to build what you describe.

codezero 9y 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/querylanguage https://developers.google.com/chart/interactive/docs/queryla... https://support.google.com/docs/answer/3093343?hl=en https://support.google.com/docs/answer/3093343?hl=en

r00fus 9y ago
Wow. This is what I've wanted for years. Amazing you can even write a query into a URL string.
- emmelaich 9y ago
  You might like (love!) this: "Programming in the URL string" http://apenwarr.ca/log/?m=201212#18 http://apenwarr.ca/log/?m=201212#18 http://afterquery.appspot.com/help http://afterquery.appspot.com/help
beering 9y 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 9y 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.
  - iheartmemcache 9y ago

SEJeff 9y ago

Aka Access?

londons_explore 9y ago
SSSh - we like reinventing the wheel in web based form!
throwanem 9y ago
Or Excel! It's been able to backend to various RDBMSes for at least twenty years now. Ripe for disruption, no doubt.

ukd1 9y ago

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

Zombieball 9y 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.
- Clex 9y ago
  Thanks for the feedback, that will get fixed soon.
  - cyberferret 9y ago
    Also, you mis-spelled "guarantee" under "99% uptime"...
    - Clex 9y ago
      Fixed, thank you.

sandGorgon 9y 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.

Clex 9y 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 9y 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 9y ago

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

eob 9y 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.

falsedan 9y 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-connecting-to-importing-data-c0ad7aec-ff1a-4e48-9c21-dc18a102433f https://support.office.com/en-gb/article/Overview-of-connect...

lancewiggs 9y 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 http://archives.cpajournal.com/old/16531700.htm
dizzystar 9y 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-windows-via-odbc/ 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 9y ago

Somewhat related. I've been using AirTable (https://airtable.com/ 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 https://github.com/airtable/airtable.js

sirrice 9y ago

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

garyclarke27 9y 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 9y ago
Any good resources you used to learn this (to replace pivot tables)?