You can query across different tabs on a single sheet, or query a totally different sheet!
https://developers.google.com/chart/interactive/docs/queryla...
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.
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.
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
With urlFetchApp you can use it as an API runner: https://developers.google.com/apps-script/reference/url-fetc...
These functions were available since Excel 2.0 but many people don't know they exist
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
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...
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://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.
The linewrap goes away when I rotate to landscape mode.
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
Even 20 years ago good old Microsoft with their MSQuery capability in Excel stopped you from doing this every way they could.
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.
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.
Can you talk about the stack you are using ? really interested to hear about it.
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.
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.
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.
And no, you are not going to run in to their upper bound limitations on rows and columns often.