Shadow IT is a big problem for enterprises. Employees are getting more and more tech skills each year, and will apply these skills to solving problems - either because they need something and don't know the best way to get it built by IT teams, or because they are frustrated with going through proper channels and either being denied or given delivery dates too far in the future.
The number one most popular 'developer tool' in the world, which is used to build more 'software' than any other may surprise you - is Microsoft Excel. Millions of people build solutions to their problems using Excel, either using pages and pages of formulas, or writing VBA or JavaScript code. This may server their needs but it comes with a stack of problems:
- Backup: usually the spreadsheet is on one persons machine, if that goes their work is lost
- Version control: There usually is nothing more than naming conventions such as MySpreadsheet_12.xlsx. No history, no way of going back to a working version.
- Sharing the work: work is shared via email. This means updates to fix bugs are missed, other changes are made, and different workers can be using different sheets giving different numbers for the same inputs.
Moving away from Excel is a hard problem as it is prevalent in the workplace. Personally I spent a year at a global investment bank working on a platform to ideally replace Excel across the entire investment and retail bank. It would help, but this is still a massive undertaking. Users are also very comfortable in Excel, so if a new system was built for each of their needs then they may not like the new system and revert back to their old friends the spreadsheets.
The solutions to the Excel problem really involve migrating away from Excel piece by piece, keeping the spreadsheet as the front end that users are comfortable with but factoring out the back end and logic to a more consistent place, with proper version control, ability for fixes to be deployed etc. This will need buy in from IT, but is less work than a rebuild of a new app, and allows the users to change their front end where needed to suit the ever changing business.
But where to put this business logic? And how does it integrate with Excel? Well, Azure Functions has you covered!
Azure Functions
Azure functions are a great way to build and host code, with access control, scaling and everything you need available for a very low cost. You can build an Azure Function as a REST endpoint to host the business logic from Excel.
Once you have your logic there, you can access it from Excel using a new feature in Office both on the desktop and web - integration of Azure Functions into Excel custom functions.
You can read the announcement here:
developer.microsoft.com/office/blogs/office-add-in-development-community-pnp-october-2019-update
Want to try it out? All the code to get started is on GitHub:
Top comments (5)
Maybe this is how it works in Microsoft..or I completely misunderstood Excel metaphor here..In last 10y, I never saw "tool" written in Excel in professional environment 🤷
Really? I've seen loads. I've done a lot of work in banks where it runs on Excel - literally teams build complex spreadsheets for legal reporting, analysis of data, compliance etc. Small businesses will use it for building sales dashboards and accounting, as well as people management.
All these cases involve relying on code buried in spreadsheets either as complex multi cell formulas, or in VBA code. Code that is lost if the sheet is lost, or cells deleted.
It might be related to banking industry 🤷♂️
In any case sounds like a nightmare and any solution is better described state
Not just banking - though a large part of our money relies on Excel. Happens in other industries as well, usually outside the 'official IT' space, so devs don't often see it.
And yeah - it is a nightmare!