One solution you could do is use the Zapier webhook trigger to push data to google docs. Then write an application that iterates over the database table and fires a webhook with the JSON to that endpoint on Zapier.
Should be a piece of cake, but you'd definitely want to throttle the rate though to prevent yourself from being throttled. :-)
Hi, I'm a cofounder of Airtable. We have an alpha version of a Zapier integration, and also an API to directly push your order data into Airtable. The benefit of using Airtable over Google Sheets is that you get true database features without losing the speed and flexibility of a spreadsheet interface: foreign key linking, rich field types, multiple views, better collaboration and mobile access, etc.
If your database is an MSSql server, you can connect to it through "data connections". Sort of works on a Mac, but only really on Windows (Might've improved with the latest version of Office for Mac though)
In that past I've used postgres' ODBC driver to connect to my database from Excel. I've heard of (but never tried) Power Query, which may be a better way of directly doing that sort of thing without using Access to wire postgres to excel.
Kind of ... I think that instant tabulR data of the sort you find in an HDF5 hooked up to distributed backend so my changes and yours are merged managed or thrown seems the likely "future" solution. If that's anything like aorta me please shout details
I can totally see this, as it is our analysts use Toad for Oracle and directly edit tables through that. There is a "data view" that looks quite similar to excel.
It's a very useful article, and well-argued, but I agree, the format jarred. I'm not sure however that it's worse in substance than many: just that he's been rather unsubtle a bout it
Hi, author of the post here. Apologies if the format of the article was confusing or misleading. I will note that this article was posted under InfoWorld's "New Tech Forum", which is specifically for authors from industry to write guest posts.
I agree with this. My group uses google sheets to manage a very complex collaborative process which is highly record oriented and requires relational semantics and database-like integrity controls, but which also needs spreadsheet-like sorting, grouping, formula work, rapid data entry, pivot tables, and so on.
We kind of work around spreadsheet limitations now by having a separate google sheet hold source data in various tabs (one for each relational table), then importing data from that sheet into the working spreadsheets (where we do enrichment and analysis) using various lookup and query functions. It's precisely as fragile as the attention span of the most easily distracted team member.
Oh wow, that could be scary. How is data entered or modified?Is the schema changing too much to put this in an actual relational DB? Are you using versioning or backups?
Each instance is a short-term, tactical thing, so the fallout from any particular error is at least localized. Everything is entered manually. Schema is fixed at the core, with small numbers of custom columns needed every now and then. A relational DB would be a better tool for storing the core data, but the tooling itself is wrong: we'd have to build a UI to front it, whereas using google sheets gives a free UI that everyone knows how to use (and supports all the various analysis cases I mentioned above). Version control is just that provided by google sheets (irritatingly granular, with no ability to create a marked checkpoint version - I hate it), plus whatever naming convention discipline any team comes up with. It's not great.
I can't really be more specific unfortunately - NDAs etc.
I've been hearing versions of this, with slightly different justifications and details for why databases would replace spreadsheets, for, well, almost as long as there have been desktop databases.
Really, I think it misses the point. Databases are about storing data, and admit a wide array of front ends, and there are plenty of tools now to use spreadsheets as a front-end for databases (most spreadsheets have basic tools for this built in, and more advanced ones are available.)
Spreadsheets are about viewing/manipulating data, and can both natively store data (in what is, necessarily, a database, though perhaps not a relational one) or connect to foreign sources for it.
"The next generation of spreadsheets will be a database" is a silly statement, for that reason: its possible the next generation of spreadsheets may have a particular kind of database (say, relational) as a native, internal storage system and expose tools for working with it. Or the next popular database front end -- the next Access -- may be a general purpose spreadsheet with the right tools.
And maybe spreadsheets, as a viewing and interaction tool, will be replaced with something with a different more mobile/touch-friendly UI paradigm, as the article seems to suggest (but doesn't really present much of a case for -- making vague suggestions about how spreadsheets don't work on touch [that remind me of the kind of hyperbolic descriptions of why current solutions don't work typical of infomercials] without any suggestion of what concrete elements would work better is not making that case.)
I agree. The next generation of spreadsheets, will be a more powerful spreadsheet. The ease of use and widespread familiarity with spreadsheets are going to make them very difficult to ever displace.
That said, airtable does look pretty cool. As someone terminally addicted to Excel, and looking at Microsoft's recent responsiveness to market dynamics, it makes me wonder whether if this was a major market opportunity, they could create a special "realtional DB tab" in excel and capture 90% of the functionality. Interesting to watch.
Have you tried PowerPivot? Fellow Excel addict here :)
Agreed with you on the next gen of spreadsheets, being more powerful spreadsheets. The OP is playing their book: sure, Airtable can do list-taking - and I use Evernote for that - but it sure ain't going to replace Excel.
"What was I talking about? Oh yeah... most people just used Excel to make lists. Suddenly we understood why Lotus Improv, which was this fancy futuristic spreadsheet that was going to make Excel obsolete, had failed completely: because it was great at calculations, but terrible at creating tables, and everyone was using Excel for tables, not calculations."
On the other end I work in a company where excel is often used as a "development" platform, internal tools, VBA codebases etc etc. It's terrifying to see what a well intentioned mathematician can do with Excel, VBA, and 50meg of data. (Mostly just create a spreadsheet that crashes a lot tbh).
Excel is used for a great many things, lists, calculations, analysis, charts, storage, and all of the possible mixtures of the two.
You're both correct and slightly missing the point.
Normal people use spreadsheets. These spreadsheets then grow into monsters.
What's needed is an invisible migration path so that people use what they understand but have somewhere to grow when they start abusing formulae to do what a real RDMS would do naturally.
It's essentially a UX problem - to all extents and purposes a database == a spreadsheet with multiple sheets - as soon as you start defining relationships between the sheets.
This article kind of misses the forest for the trees. The reason why spreadsheets took over for databases is because the spread sheet was easy and put less restrictions in the way.
So creating a spreadsheet that is not as easy in order to support real database like functionality is going to end up the same way...
But consider this: if they hadn't mistimed their IPO to be the day after the Crash of 1987, they could easily have been the dominant paradigm in spreadsheets and small databases.
Interesting, thanks. However, I'm not sure most end users were going to figure this out any sooner than a relational database:
Unlike models in a spreadsheet, Javelin models are built on objects called variables, not on data in cells of a report. For example, a time series, or any variable, is an object in itself, not a collection of cells which happen to appear in a row or column. Variables have many attributes, including complete awareness of their connections to all other variables, data references, and text and image notes. Calculations are performed on these objects, as opposed to a range of cells, so adding two time series automatically aligns them in calendar time, or in a user-defined time frame.
Maybe it's just not well-explained by the Wikipedia authors, or it makes more sense if you see it ...
When this says generation, what time frame are they referring to? I've been reading about this sort of thing for the last twenty years. Hasn't happened. What has happened is people asking for more spreadsheet integrations.
I love Excel - I wonder how it was possible to analyse a business before it came along - and the primary reason is pivot tables. Pivot tables are an awesome tool.
It should be possible to replicate pivot table functionality using SQL semantics.
The majority of end users have the ability to understand a list in the style of a spreadsheet, which then becomes a simple and effective tool. But sometimes there need goes beyond the spreadsheet.
As soon as you add relationships, such as 1-to-many, the average end user is lost. Not just struggling a little, but completely bamboozled. As programmers we often forget that even something as simple as that is a road block for almost all end users. Teaching users about relationships is a dead end, about 90% will never get it. Especially when they are trying to learn about it from a website product.
Instead I think 'airtable' would be better placed marketing to web developers and IT departments. Every company I have worked at has requests from other departments wanting a simple database. Most companies are full of these simple internal databases. A tool that allows us to quickly (~1 day) setup a solution and then get rid of the request would be a boon. You should be targeting web developers and IT professionals with your solution.
I've wanted something like this for years. So many times I've tried to use a Google spreadsheet to store data, because I needed the web-based UI and mobile access... but it never works out well, because it's just not a relational database. Airtable seems to get it right.
Also, the API docs being automatically customised per-table is awesome.
I'm in the database camp but I can see the use case for spreadsheets.
The big problem is unless you are very disciplined it is easy for a large spreadsheet to become unmaintanable. I've come across this a lot when I've been approached by people in my work place asking me to automate a spreadsheet for them. Inevitably someone will insert, delete or just move a column around and the whole fragile mess will come crashing down.
I've found when someone comes to me and says "I spend X amount of time every morning updating this spreadsheet can you automate it for me." The answer is almost always to migrate to a database.
Spreadsheets also seems to encourage people to lazily paste data in without much thought. I've seen some real nightmares - spreadsheets that are 100's of mb in size and contain almost a complete copy of portions of the company's database inside them, which take minutes to open and load.
Pasting data has some other disadvantages as well over time minor changes sneak in due to the mutable nature of cells in excel. Which leads to phone calls like "Can you check the figures for last FY Mike our Accountant is telling me X while Bill our Engineer is convinced it should be Y" eventually you find out Mike is getting his data out of a giant spreadsheet and one of the cells has 'inadvertantly' been modified so it no longer agrees with the source database. Eventually you learn to head off phone calls like this with "The number in the database is correct if you sourced it from anywhere else its likely to be wrong." But still frustrating to deal with.
It's like a spreadsheet, but more suitable for complex data
because it's hierarchical.
It's like a mind mapper, but more organized and compact.
It's like an outliner, but in more than one dimension.
It's like a text editor, but with structure.
One of the problems with spreadsheets is that they don't have a concept of table. Tables are emulated in spreadsheets using blocks of cells. Spreadsheets make no difference between a column header and a column value.
What drives me crazy is spreadsheets as a reporting / BI tool. Just because Excel does graphs and charts, and everyone knows how to use Excel, the result is that everyone only knows how to make graphs and charts in Excel.
38 comments
[ 3.3 ms ] story [ 47.2 ms ] threadAs it is, I opened a json endpoint so that Zapier can scrape our orders and fill it into the next-blank-line in a google doc.
Is there a better way to satisfy this use case?
Should be a piece of cake, but you'd definitely want to throttle the rate though to prevent yourself from being throttled. :-)
"My company, Airtable, was founded to address this need."
Here's an example of another article from the New Tech Forum: http://www.infoworld.com/article/2607260/database/how-databa...
We kind of work around spreadsheet limitations now by having a separate google sheet hold source data in various tabs (one for each relational table), then importing data from that sheet into the working spreadsheets (where we do enrichment and analysis) using various lookup and query functions. It's precisely as fragile as the attention span of the most easily distracted team member.
I can't really be more specific unfortunately - NDAs etc.
It a hidden gem of many finance departments.
Really, I think it misses the point. Databases are about storing data, and admit a wide array of front ends, and there are plenty of tools now to use spreadsheets as a front-end for databases (most spreadsheets have basic tools for this built in, and more advanced ones are available.)
Spreadsheets are about viewing/manipulating data, and can both natively store data (in what is, necessarily, a database, though perhaps not a relational one) or connect to foreign sources for it.
"The next generation of spreadsheets will be a database" is a silly statement, for that reason: its possible the next generation of spreadsheets may have a particular kind of database (say, relational) as a native, internal storage system and expose tools for working with it. Or the next popular database front end -- the next Access -- may be a general purpose spreadsheet with the right tools.
And maybe spreadsheets, as a viewing and interaction tool, will be replaced with something with a different more mobile/touch-friendly UI paradigm, as the article seems to suggest (but doesn't really present much of a case for -- making vague suggestions about how spreadsheets don't work on touch [that remind me of the kind of hyperbolic descriptions of why current solutions don't work typical of infomercials] without any suggestion of what concrete elements would work better is not making that case.)
That said, airtable does look pretty cool. As someone terminally addicted to Excel, and looking at Microsoft's recent responsiveness to market dynamics, it makes me wonder whether if this was a major market opportunity, they could create a special "realtional DB tab" in excel and capture 90% of the functionality. Interesting to watch.
Agreed with you on the next gen of spreadsheets, being more powerful spreadsheets. The OP is playing their book: sure, Airtable can do list-taking - and I use Evernote for that - but it sure ain't going to replace Excel.
Joel Spolsky also has a great essay about this: http://www.joelonsoftware.com/items/2012/01/06.html
"What was I talking about? Oh yeah... most people just used Excel to make lists. Suddenly we understood why Lotus Improv, which was this fancy futuristic spreadsheet that was going to make Excel obsolete, had failed completely: because it was great at calculations, but terrible at creating tables, and everyone was using Excel for tables, not calculations."
Excel is used for a great many things, lists, calculations, analysis, charts, storage, and all of the possible mixtures of the two.
Normal people use spreadsheets. These spreadsheets then grow into monsters.
What's needed is an invisible migration path so that people use what they understand but have somewhere to grow when they start abusing formulae to do what a real RDMS would do naturally.
It's essentially a UX problem - to all extents and purposes a database == a spreadsheet with multiple sheets - as soon as you start defining relationships between the sheets.
So creating a spreadsheet that is not as easy in order to support real database like functionality is going to end up the same way...
[1] http://en.wikipedia.org/wiki/Dabble_DB
Sad. It looked like it could have been a game-changer.
Wikipedia's actually got a good article on it: https://en.wikipedia.org/wiki/Javelin_Software
But consider this: if they hadn't mistimed their IPO to be the day after the Crash of 1987, they could easily have been the dominant paradigm in spreadsheets and small databases.
Unlike models in a spreadsheet, Javelin models are built on objects called variables, not on data in cells of a report. For example, a time series, or any variable, is an object in itself, not a collection of cells which happen to appear in a row or column. Variables have many attributes, including complete awareness of their connections to all other variables, data references, and text and image notes. Calculations are performed on these objects, as opposed to a range of cells, so adding two time series automatically aligns them in calendar time, or in a user-defined time frame.
Maybe it's just not well-explained by the Wikipedia authors, or it makes more sense if you see it ...
I love Excel - I wonder how it was possible to analyse a business before it came along - and the primary reason is pivot tables. Pivot tables are an awesome tool.
It should be possible to replicate pivot table functionality using SQL semantics.
[1] http://bit.ly/1dKmrF5
As soon as you add relationships, such as 1-to-many, the average end user is lost. Not just struggling a little, but completely bamboozled. As programmers we often forget that even something as simple as that is a road block for almost all end users. Teaching users about relationships is a dead end, about 90% will never get it. Especially when they are trying to learn about it from a website product.
Instead I think 'airtable' would be better placed marketing to web developers and IT departments. Every company I have worked at has requests from other departments wanting a simple database. Most companies are full of these simple internal databases. A tool that allows us to quickly (~1 day) setup a solution and then get rid of the request would be a boon. You should be targeting web developers and IT professionals with your solution.
Also, the API docs being automatically customised per-table is awesome.
The big problem is unless you are very disciplined it is easy for a large spreadsheet to become unmaintanable. I've come across this a lot when I've been approached by people in my work place asking me to automate a spreadsheet for them. Inevitably someone will insert, delete or just move a column around and the whole fragile mess will come crashing down.
I've found when someone comes to me and says "I spend X amount of time every morning updating this spreadsheet can you automate it for me." The answer is almost always to migrate to a database.
Spreadsheets also seems to encourage people to lazily paste data in without much thought. I've seen some real nightmares - spreadsheets that are 100's of mb in size and contain almost a complete copy of portions of the company's database inside them, which take minutes to open and load.
Pasting data has some other disadvantages as well over time minor changes sneak in due to the mutable nature of cells in excel. Which leads to phone calls like "Can you check the figures for last FY Mike our Accountant is telling me X while Bill our Engineer is convinced it should be Y" eventually you find out Mike is getting his data out of a giant spreadsheet and one of the cells has 'inadvertantly' been modified so it no longer agrees with the source database. Eventually you learn to head off phone calls like this with "The number in the database is correct if you sourced it from anywhere else its likely to be wrong." But still frustrating to deal with.