It's a little known fact (is it?) that you can use a SQL like query language on Google Sheets.<p>You can query across different tabs on a single sheet, or query a totally different sheet!<p><a href="https://developers.google.com/chart/interactive/docs/querylanguage" rel="nofollow">https://developers.google.com/chart/interactive/docs/queryla...</a><p><a href="https://support.google.com/docs/answer/3093343?hl=en" rel="nofollow">https://support.google.com/docs/answer/3093343?hl=en</a>
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.<p>I showed this to my manager and he immediately demanded I explain to the rest of the team how this black magic worked…<p>[0]: <a href="https://support.office.com/en-gb/article/Overview-of-connecting-to-importing-data-c0ad7aec-ff1a-4e48-9c21-dc18a102433f" rel="nofollow">https://support.office.com/en-gb/article/Overview-of-connect...</a>
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.<p>At first I thought it was an actual spreadsheet application, or an Excel plugin or whatever.<p>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: <a href="http://sheets.alhur.es/" rel="nofollow">http://sheets.alhur.es/</a>), 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.
I created something simpler but based on Google Docs and just retrofitted promises into it: <a href="https://github.com/franciscop/drive-db" rel="nofollow">https://github.com/franciscop/drive-db</a>
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.<p>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:<p><pre><code> 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
</code></pre>
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.
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.<p>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.
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.
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.<p>Can you talk about the stack you are using ? really interested to hear about it.
Somewhat related. I've been using AirTable (<a href="https://airtable.com/" rel="nofollow">https://airtable.com/</a>) to manage my consulting startups clients and even track hours. Absolutely loving it.<p>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.<p>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.<p>[1] - <a href="https://github.com/airtable/airtable.js" rel="nofollow">https://github.com/airtable/airtable.js</a>
There's some related research out of UIUC that's pretty neat.
<a href="http://dataspread.github.io/" rel="nofollow">http://dataspread.github.io/</a>
Not sure what pain problems this solves. You can already programmatically access and manipulate Google Sheets.<p>And no, you are not going to run in to their upper bound limitations on rows and columns often.