Show HN: Turn an Excel file into a web application
keikai.io
keikai.io
https://www.ecb.europa.eu/stats/policy_and_exchange_rates/eu...
See basically "no dev needed", perfect for old timers who can't code. Or not.
private void placeAnOrder() { listSheet.unprotect(""); Range costCell = spreadsheet.getRangeByName(exchangeSheet.getSheetId(), COST_CELL); if (!costCell.toString().isEmpty()) { Double cost = costCell.getRangeValue().getCellValue().getDoubleValue(); if (cost > 0) { Double amount = spreadsheet.getRangeByName(exchangeSheet.getSheetId(), "amount").getRangeValue().getCellValue().getDoubleValue(); Range orderTable1stRow = spreadsheet.getRange(BOOK_NAME, listSheet.getSheetId(), "C6:G6"); orderTable1stRow.getEntireRow().insert(Range.InsertShiftDirection.ShiftDown, Range.InsertFormatOrigin.RightOrBelow); orderTable1stRow.setValues(DateUtil.getExcelDate(new Date()), cost, destinationCurrency, destinationRate, amount); } } listSheet.protect(PROTECTION); }
And when I'm referring to "old timers", I'm referring to users, not the developers. Why would a user need to understand that snippet? Are we even talking about the same thing?
> "C6:G6"
I think the bigger problem is it's not going to be a developer maintaining this. It's going to be a pretty literate VBA user and nothing will go wrong. Inevitably they will move on and pass over the code, the 'user application' to someone who will feel reassured they can read it too, but then when it comes time to make a change, they won't be able to. And then it'll be passed over again and become a black box.
A developer (if there's funding) will not like picking up something undocumented, then it becomes a project (less funding). The process, a magical black box that may or may not have stopped working also loses documentation. Governance is lost. Particularly but not exclusively in financial applications, potential for misuse and loss, either accidentally or fraudulently, has been introduced.
So, the above looks (mainly) OK to me. But only because I know to insist on maintaining a manual backup or otherwise maintaining knowledge of how the black box works.
I welcome solutions that serve this need for "better than excel" without succumbing to its pitfalls but I'm not holding my breath. Even this article just outlines a solution that could be implemented in Excel with a little extra knowledge from the user, making it "no better than excel" for the specified use case.
On a turing-completeness spectrum, they live on the opposite half of where VBA+formulas stand proudly. Therefore, it is no surprise that for anything beyond "out-of-the-box", it is expected that an experienced IT developer/consultant will be needed to figure something out.
A decent reason to disallow custom code is that these platform are to be upgraded regularly, yet every major version seems to bring its lot of breaking changes while my decade-old macros still run like a champ. Oh the irony.
It works well - we have successfully applied this to about 20 really critical spreadsheets for functions such as pricing, deal valuation, risk analysis, and even for engineering analysis. Users simply use a web app (that can look like the underlying spreadsheet - or not). The system is also integrated with SAML, so authentication is there, and you can also integrate the apps you create with 3rd party systems - we are looking at launching Excel-based pricing apps directly from SF.com, for example.
Anyway, the site is https://easasoftware.com/ - there are some vids there which probably do a better job of explaining it than I have done here. It's not just about putting an app on top of a spreadsheet, but really putting an app on top of processes that involve spreadsheets. And you can use things other than Excel as the logic module, like Matlab, R, Python - in fact we are dabbling with using EASA to deploy ML models created in Python.
You can embed an auto-updating Excel file in a SharePoint webpage using a "webpart" that renders as HTML. (Microsoft is getting away from web-rendering Excel with Silverlight,and allowing more functionality without the need for a browser plug-in.)
The trick is to setup the Excel data connection using the "Get External Data" -> "From Other Sources" -> "OData Data Feed" and not use the "Data Connections / Microsoft Query" method. Note: Microsoft Query connections won't update server side, the Excel file needs to be opened, data connections refreshed, and re-saved.
It does require a bit of Excel expertise to setup, but there is no need to create a Java application to get it to run.
I made an app called https://Sheet2Site.com which takes your spreadsheet of items/objects and translates it into an app, with filters.
Components of different types, corresponding to rows of different schemas? Where first column says the type? Or is it one sheet per type of object?
And then you import them? And have a page with a bunch of components with data from the sheet?
class="onp-sl-overlap-box onp-sl-transparence-mode onp-sl-great-attractor-theme"Why do you think Excel is so ingrained into all kinds of companies and processes?
Because you should have a reeeealy good justification to write a single line of code for something that can be done in Excel much cheaper, and mostly you don't.
Not everyone can code, but everyone can transform data in a spreadsheet and email it off to the next person.
I've seen people having multiple versions of the same excel file and not remembering which was the last one...
...and here: https://easasoftware.com/case-studies/leaseplan-transforming...
I would theoretically be possible to write some software/reporting solution for every use of Excel but it wouldn't be an efficient use of resources.
And as you're adding features, you instantly see the results; even as you type if you use the formula builder.
If you're working on any kind of software tooling, watching how people work with Excel is incredibly insightful.
Spreadsheets are nontechnical users' version of "move fast and break things". Often times that can be useful, even essential, but there are many more cases where the primary utility of working that way is also the major flaw. Its faster than traditional report development because it skips any kind of review or QA.
One company I know which did this was called "Actuate"; that was years ago, a quick googling shows they were bought, now called "OpenText" (?) - also had something to do with MongoDB and something called BIRT (Business Intelligence and Reporting Tools)...
Last I had any involvement with them for a company I worked for, it was with their reporting suite of tools; the company I worked for became a VAR for them, so anytime the tool had a bug, we could quickly get them in an IRC chat (told you it was a while back) and get a patch within a few hours to a day or so, depending on the issue.
The reporting system was fairly unique for the time; a basic WYSIWYG drag and drop editor, ala VB - and for more complex designs, a VB-like object-oriented "reporting design" language. It was essentially what VB6 should have been on the OOP side of things, but geared entirely toward reporting.
Reports could pull data from any number of different sources, from flat ascii files, to excel documents, to database engines, ODBC, etc.
One of the last things I heard from them, at a seminar they put on that my company sent me to in Santa Monica, was this idea of reforming people using Excel spreadsheets. They called the whole sharing and copying of such spreadsheets, such that there was no one single authoritative source - a "spreadmart". So they wanted to do something about it.
They came up with some kind of Java-based solution with a custom front-end that looked exactly like Excel of the time, worked just like it - pivot tables, whole nine yards. But it interfaced to a backend using these "business logic" modules. The idea was all instances of the "spreadsheet" could see the same data via those modules, and those modules would be created by an IT staff (or DBA or something), and anything interacting with them in a company would have to go thru them - reports, gui, excel spreadsheets, etc. The idea was to present one cohesive view of the data for all users.
I don't know where it ended up - if they launched the product or not (we were being given a sneak peek at it at the seminar - it was a pretty nice demo, overall for the time).
A couple of weeks after coming back from the seminar, the company let me go (I'd worked for them for about 8 years - missed the whole "dot-com" thing because I believed in this company so much - lived and learned).
People/teams/companies need different degrees of security, complexity, data, etc to their day-to-day operations. Someone may be interested in fiddling with data without others seeing, others may not want to share certain data broadly (answering your point below). Excel has served as a way to unify these needs for the most part. Some other options available tends to fall on the outer-layers, where excel may not be optimal and someone felt a specialized tool could improve speed/quality/cost.
> Why do you think Excel is so ingrained into all kinds of ...
Programmers underestimate and put down excel all the time, but I think it's actually how many projects they end up with get started.
Someone had a problem, solved it for themselves using excel, got noticed, expanded the solution to other problems until the excel solution itself became the problem and then... it ends up as a project for you.
There's nothing wrong with that. It does the job, and it's not like the first course of action when there's a problem should be to assemble a scrum team with a cast of characters like a sitcom, and blow a million dollars to implement a bespoke software solution. Sometimes excel is just enough (and then some).
Also in the same project I remember another Excel file with mechanical calculations that actually updated a nice graphic that simulated all the linkages and different extensions of the actuators and computed forces at different points. The unholy alliance of elegant Lagrangian mechanics with Visual Basic.
I hope to never touch Basic again but it was a valuable lesson about getting things done and I'm glad I took it so early in my career.
- Because Excel allows the user to (i) perform ad-hoc calculations, (ii) audit the input and outputs, and (iii) easily modify the functionality in real time.
- People don't like filling out forms and most web apps are basically filling out forms.
Say we had a REPL that also had a table (like excel, each cell has an address... a 2D stack if you will).
Say we interact using a mouse to select a cell or in the REPL to specify what cell we want to write to. Then, if we use a Lisp, we have tabular code and tabular data...
I might code this up for fun.
Or a spark interactive cluster with a notebook interface if you have more data than can fit on a single machine
Also, the #1 Excel rule in Wall Street is "never use your mouse"
IMHO, I think the better project is an equivalent of an RMarkdown / RStudio tool that generates a final report from a declarative set of instructions.
From the "Introduction" [3] of its Online Documentation: "Siag is an X-based spreadsheet for Unix. It uses Scheme (a Lisp dialect) both for expressions and as an extension language, which makes it easy to create new functions (for native Lispers, that is). There is no requirement to know Scheme to use Siag, expressions can be entered in traditional spreadsheet syntax as well."
About point 3, isn't a spreadsheet basically a sophisticated form?
I've seen it countless times. Somebody sends an excel file over e-mail and it is either an old outdated version of the file, or the persons that receives it hangs on to it for months as a source of reference.
MS Access is much better for allowing simultaneous editing, especially (IIRC) if it's backed by an RDBMS, since it will use row level locks.
My previous employer had several databases with read-only web interfaces, but Access editing interfaces. They're very fast to develop, and support all the usual database features (constraints, keys, search etc), and could be set to load a form-editing view by default.
But even if you are just using it as a form, with bulk operations and keyboard shortcuts, it's usually much easier to fill out an Excel form vs. a web / app form.
Their manager wants them to use Excel because all their past managers have wanted them to use Excel. And so on.
Also, it's already there on all the computers.
Take a look a talk by Joel Spolsky called You Suck at Excel. It's on YouTube. It will give you a quick glimpse into how Excel powerusers are using it and how fast you can create something useful.
* No need to depend on an IT department which is already overbooked
* No need to find budget to fund a project
* No need to wait forever for the IT project to finish.
It gives an business person control, as they can do it themselves.
We are both building a software basing on Office :)
It seems you still need to code the plumbing to get the sheet to work and serve it in some webserver, so you might not be able to get rid of your developers any time soon. And I didn't see any mention of it but it seems to be using Java (since it mentions mvn in the example's GitHub page).
One thing that I've found amazing is that if you build from scratch, you can have a cross-platform native desktop application that runs on software that's pretty much installed on all company computers.
Why?
Just because you can do it, does not mean you should.
There is another group of people who favor storing html pages in a db.
If you’re only doing things other people tell you that you should, it’s worth taking some time for self-reflection to figure out why, and to find out what you _yourself_ are interested in.
Nature walks are great for that kind of thing :)
I wonder for some period of time why people like to climb, so I am excited by your answer. Given all the documentaries, news and travel ads, one can reasonably imagine what it looks like on the other side, don't we? Moreover, its geometry would remain the same in one's life time, for any specific mountain. One can get a pretty close idea from Google Map's terrain view. If one is not that into the details, like the precise curvature for each piece of a mountain, then they are largely the same.
I find myself not attracted to climbing for exactly the reason that they are not different enough from each other to satisfy my curiosity.
In the last year or two though I’ve realized that every climb is it’s own experience, even on the same mountain. The season, the plants and animals that come with it (or snow!), the weather that particular day, whether it’s a dry or wet year, the time of day. Some days you get turned back before you reach the peak. Maybe an avalanche happened overnight. Or you time it just right to get a view of sunset/sunrise/aurora/milky way. There’s always something new to learn even if it’s your 100th ascent. They actually change much more than you’d think.
I always thought it would be tough or impossible to, say, get all the 13ers/14ers in Colorado, or all the 4,000ers in the northeast. Now I realize I could spend a lifetime just getting to really know a handful of them. I find a strange comfort in that. Maybe I’m getting older.
Also, I love to ski and snowboard. Gotta get up to ride down! But I’ve never lost the desire to go around one more bend, or over one more peak, to see what I can see beyond.
Other than privacy issues of course.
And I don't believe it's true to say they've only killed unpopular things. Anything outside of the core business (and I will argue Sheets/Docs is outside) is up for the chop.
Only one customer can do that, not all of them. As a small provider of enterprise software I would offer to sign a source code escrow agreement.
They do not have 74 million paying customers. However, I agree that the "free" version is safe for as long as the enterprise offering continues to do well (5 million PAYING users as of Feb 2019: https://cloud.google.com/blog/products/g-suite/5-million-and...).
0: https://www.eweek.com/it-management/xl2web-package-lives-up-...
Or until a spreadsheet is edited in Excel that uses language other than English - formula functions will also take nationalised names.
Or until multiple people make changes to a spreadsheet, but there is no version control in Excel.
One place I worked at created a Python library called the DAG (Directed Acyclic Graph) for this. It enabled an easy way to translate a chain of Excel cell functions into Python code, which can then be version controlled, diffed, code reviewed, etc.
(I haven't used it in many years but MS Access was a pretty efficient way to get an application together).
Power Query solves many cases where a macro was previously required, but relatively few people are aware of it.
The important point would be to recognize that when they ask you this for the second or third time it might be time to automatize it.
I'm being forced by my manager to do this for a client due to a close deadline (the idea is to implement a spreadsheet as interface to make it quicker than a classic web app), lbut they keep asking me to implement advanced features that would be 10 times faster to implement with a regular interface or sometimes not even possible on a sheet. Spreadsheets have a precise use case and it's not to replace web apps.
[0] Actually since I had the idea waaaay before the business need and project, I had a personal MIT-licensed clean room toy implementation of it sleeping somewhere on my hard drive and used that for the business prototype.
It currently maintains millions of business software jobs.
Well, too bad the smart guys in Silicon Valley are busy perfecting their yak shaving, otherwise I'm sure they would have found the holy grail of business software that us foolish dark matter programmers have failed to deliver on for several decades in no time.
The reason is, I don't think there is a single website out there, which couldn't be described in a spreadsheet.
What I suggest though, is take a look at this, and throw your companies sheet at it in a 30 minute hack session. If this proves unfruitful, I'd be happy to hear all about it.
Spreadsheets are a terrible way to make web-sites, though.
One of the bits I agree about, is that we should be very wary that we, developers, don't get replaced by one.
Friendly reminder that this is literally the whole point of the job. Automation. Making people stop doing tasks that can be done by machines - which often means replacing people with code. Sometimes even ourselves.
Spreadsheets > Web.As much as everyone wants to build full stack for every application, Excel is fantastic for complex math out of the box.
Excel is the average persons database.
I have found being able to expand on this has been incredibly useful and modular.
And as a note, VBA is definitely a real programming language, it up to the developer on how to treat Excel.
Of all the spreadsheets that start out exactly like the ones that need to be converted to an app, how many were thrown away having served their temporary purpose just fine? How many of them never outgrow the immediate needs and skills of its creator?
Perhaps spreadsheets should be seen as tools for business process prototyping. Starting a proper software engineering project only after a task has outgrown its spreadsheet based implementation may be a pretty efficient approach.
I work with a lot of data in my daily life, and see most BI tools struggling to recreate the same functionality that Excel offers, often failing in the process.
People try to recreate the Excel experience online too much, forgetting that Excel itself is a really solid solution and it’s worth taking serious.
It was brilliant. Excel was also the wrong tool. We constantly had problems with file locks, network shares, etc. After one year I worked with another guy to rewrite it as a web app.
Copy of 2019Q1-finance-report-3__fixed)_Jon edits - bugfix (2).xls.xlsx[1] https://www.mztools.com/ [2] https://www.rondebruin.nl/win/s9/win002.htm [3] https://www.xltrail.com/
I never had the same experience learning Excel, maybe the problem is my mindset. The only learning experience I enjoyed about excel is 'You suck at Excel' by Joel Spolsky.
Can someone recommend a good Excel learning path for someone in this situation?
One secret I'll let you in on: most people use plugins like KuTools to do the heavy lifting. There are tons of industry-specific plugins. When you really can't find a plugin to do what you want, then, and only then, should you be writing macros and saving them to your xlsb.
But what if you're not able to use it at work? Then I recommend picking up pet projects and continually look for ways to improve. Just keep asking, "could this be easier?"
Whenever you get stuck on Excel issues, I recommend watching Excelisfun [0] who has thousands of hours of video content on every Excel feature you can think of. If you have difficulties with VBA, check out MSDN help docs.
P.S. Don't write a fuzzy text matching algo yourself. You will drive yourself crazy and Microsoft has one for free to download.
*Disclaimer: I'm the founder.
The clients are very satisfied, because the web app that's outputted by WeMaik is so easy to design and produce that they're produced by industry-knowledgeable consultants, and not web developers.
We go from idea to actionable mock-up in half a day, present it to the client, and then build the real solution assisted by WeMaik. The connections, queries, logic are not no-code, though, for this solution. We describe the specs in the spreadsheets we send to WeMaik, and they configure it.
Im also working on an internal CRM tool for http://fairpixels.pro with a sleek frontend that can be used as a browser homepage (showing key business stats) all while the same Sheet is used to manage customers.
I highly recommend looking into this because it allows for a simple to manage backend for those that live in Sheets.
(1) Getting a _validated_ email address of the submitter. It does support an email field, but makes it a free-form text input with no way of enforcing ownership of that email.
(2) Pre-populating some of the fields when we send the user to a form via a link. Without this, the user ends up filling the fields even though they have already submitted that information in another form.
Are there any powerful (forms + spreadsheet) products out there that solve these (and are cheaper than, say, AirTable) ?
(2)
(I'm a co-founder of Calcapp.)
I believe the GP was talking more about "click here to confirm your account" type validation, not "does it have an @ in it".
>Consistently refers to her as "Admin Lady"
Dude what the actual fuck
Excel sheets are nothing buy a worse version of bash scripts, at least in my opinion. At least bash scripts let you plug them into something bigger using standard process operations.
I suspect this lady and her employer find worth in this approach, which would make your assertion incorrect.
I've also seen numerous $100k+++ "proper" software projects advocated by people who share a similar mindset as you, when a mildly complicated spreadsheet that could be built in a week or two could accomplish the same thing and more.
Bash scripts don't visualize. Bash is harder to learn than Excel.
Go to vertex42.com and tell me that everyone who could ever use any of those templates will invariably need to throw it all away and ask a developer to build a custom tool to replace it.