Letter: Importing spreadsheets

Will Calcapp ever support importing your spreadsheets directly? Allan posed this question to us and received a lengthy reply from our co-founder David.

We spend a lot of our time answering thoughful letters sent by you, our users. In the spirit of transparency, we’ll start posting lightly edited versions of our answers. By doing this, we hope to do a better job of keeping you abreast of future developments and on what our thoughts are on various issues.

Allan wrote us to let us know that thanks to Calcapp now supporting cross-references, he would be able to convert his spreadsheets to Calcapp. In theory. The problem is that Allan — much like many of you, we suspect — has complicated spreadsheets built over many years that would take days or even weeks to untangle and convert. Are we by any chance working on an import feature that could do the work automatically?

This is what our co-founder David had to say:

Thanks for the long mail. I certainly understand that the very thought of migrating your spreadsheets to Calcapp manually is daunting. Also, I’m sure you’re plenty busy “just” running the business and that you’d be hard-pressed to take time out of your schedule to do the migration. You’re certainly not alone in your predicament and I think that this is absolutely a major stumbling block for Calcapp adoption.

We have realized that this is a problem for a long time now, but the product has been sufficiently immature for us to prioritize getting other things done first. We’re happy that we finally have cross-references – we create apps for a few clients on a consultancy basis and we’ve had to wait for cross-references to land before migrating a few of our apps from an earlier Calcapp version which did support cross-references. (The platform we’re creating today is known as Calcapp 3 internally, but there’s also Calcapp 2 from 2008 and Calcapp 1 from 2003.)

Truth is, we still lack fundamental features and these need our immediate attention. Our number one priority is getting these fundamental features done so that we can commercialize the product.

As we’ve mentioned on the blog, we think that having support for image uploads and formatted text is essential. As things stand, you can’t offer an app for public use that communicates your brand identity in any way, shape or form and that’s a deal-breaker for many users. Also, I’ve seen hundreds of half-finished apps that have been abandoned when users realized that the platform doesn’t support copy and paste – and if you need to create 50 panels (reachable from a list panel, say) that are nearly identical but differ in terms of formulas, you don’t want to waste your time re-creating them all manually. Also, I’ve seen lots of abandoned apps that consist of calculation panel after calculation panel, culminating with a list panel that would serve as a main menu – but due to the lack of copy and paste functionality, you can’t make that main menu the first panel of the app, you have to start over and remove all panels you’ve built, which obviously is unacceptable.

So we still have fundamental features we’re missing. We also don’t support tabular data (data defined in tables that you’d retrieve using functions like HLOOKUP and VLOOKUP) and we have reluctantly made our peace with not being able to ship support for tabular data with our initial commercial release. We’re hoping to get to it soon after, though.

I have spent quite a bit of time thinking about how to tackle the migration problem, though (I just can’t commit engineering resources to it just yet). The way I see it, there are two ways to combat the problem. First, we’d like at some point to offer the ability for apps you build to work with arbitrary data sources, including relational databases and data stored in Excel spreadsheets located in the cloud (say, a OneDrive or Dropbox folder). That way, Excel would be the party doing the number-crunching on a server and simply feed the data back to the app for display purposes.

There are a few issues with that approach, though. Apps wouldn’t work offline (they do today) and you’d get noticeable lag between the time you enter figures and when you’d be able to see the results. Also, keeping your app synchronized with your spreadsheet could get messy if you move cells around, etc. But fundamentally, I don’t much like essentially creating a front-end for Excel, because then we’re no longer in control of our core technology, the calculation engine. (Which actually improved markedly with our last release – it now uses a dependency graph to ensure that it doesn’t perform calculations needlessly, just like every spreadsheet on the planet since the eighties.) Also, I don’t think we can create a great user experience if we don’t control all the moving parts.

While enabling apps built with Calcapp to be wedded to spreadsheets will likely happen in the future (solving your problem, because you can keep using your spreadsheet), I’m much in favor of creating an import feature. I think that this is, like you point out, a hard problem, but not a completely intractable one.

The problem with spreadsheets is that they’re not structured the way apps you build with Calcapp are. We can actually tell your field captions from your initial field values from your field formulas, because we ask you to fill in the blanks in all the right places. A spreadsheet, on the other hand, is like a canvas that you can use for lots of things. Is that cell an input cell with an initial value or a label for the true input cell to the right of it? The spreadsheet doesn’t know, nor does it really need to know. That limits spreadsheets and makes our job much harder.

Label cells are usually to the left of their input cells, though, and you can tell input cells from label cells simply by following the dependency trail (an input cell is referenced from other cells, label cells are not). Implicitly-defined tables can be identified by looking at the cell ranges that the HLOOKUP, VLOOKUP, MATCH and INDEX functions reference.

We’d love to apply machine learning (artificial intelligence) to the problem of identifying the constituent parts of spreadsheets. While machines can’t “think,” they are great at recognizing patterns.

As you can tell, this is a problem we’d love to work on. It would be extremely useful to all the people out there with old spreadsheets that need to be converted, and it’s a hard, fun problem (much preferable to papering over visual glitches in old versions of Internet Explorer). It will get done, eventually, but as noted, there are unfortunately more pressing matters in the immediate future.

« Release: Our February, 2017 update is here Feature: Introducing calculated properties (video)  »