[HN Gopher] Lambdas as values in Excel
___________________________________________________________________
Lambdas as values in Excel
Author : occamrazor
Score : 81 points
Date : 2021-08-23 10:14 UTC (12 hours ago)
(HTM) web link (techcommunity.microsoft.com)
(TXT) w3m dump (techcommunity.microsoft.com)
| curiousgal wrote:
| Meanwhile hitting Ctrl-A in any text field doesn't do anything.
| Single quotes don't work in formulas and the list goes on...
| occamrazor wrote:
| Excel now has MAP, REDUCE and other helper functions for
| functional programming.
|
| See also https://insider.office.com/en-gb/blog/new-lambda-
| functions-a...
| pjmlp wrote:
| And an F# like language, Power Query.
| bradrn wrote:
| I haven't heard of this before. Do you happen to have any
| more details?
|
| EDIT: Found it: https://docs.microsoft.com/en-us/power-
| query/. But it doesn't look particularly F#-like -- Power
| Query seems more focused around, well, querying data.
| vhhn wrote:
| I think parent comment was referring to the M-code [0]
|
| [0](https://www.howtoexcel.org/power-query/m-code/)
| pjmlp wrote:
| Yep that was it.
| mcbishop wrote:
| I'm very excited about Lambda functions. Create your own set of
| clean logical functions on top of quirkier default functions.
| Optionally with namespacing (e.g. "m." for math functions / "t."
| for text functions). Use recursion in the function definition,
| without external programming.
| maliker wrote:
| I gotta admit, Microsoft is pushing out some cool features that
| are widely accessible. I've been enjoying the Github Actions
| feature, Teams is actually getting my organization to stop using
| Sharepoint, VSCode is great, and now Excel is getting some nice
| function upgrades. We've come a long way from the 90s embrace-
| extend-extinguish MS business strategy (or are at least enjoying
| a period of embrace+extend).
| plussed_reader wrote:
| Disagree about the progress; See Winget.
|
| Classic bungling.
| lp0_on_fire wrote:
| Can you elaborate how on why you think Winget is being
| bungled?
| bob1029 wrote:
| Agreed - Teams causes me some pain, but it is something we are
| getting used to now.
|
| Let's also not forget about the current state of the .NET
| ecosystem. C#9+ on top of .NET 5+ is an amazing developer
| experience.
|
| Being able to contribute to the framework via any authenticated
| GitHub account is nice too. I find myself almost accidentally
| participating in Microsoft's issue threads simply because it is
| so easy to do so.
| dlojudice wrote:
| for a moment I thought it would now be possible to call a
| serverless lambda function with excel being the runtime.
|
| I've been through scenarios where sophisticated calculations were
| done in excel by domain experts and then it was necessary to
| translate them into code by developers, so having an excel-as-a-
| service would be great.
| sandGorgon wrote:
| same here ! This would have been the most exciting feature
| ever!
|
| Build a backend for Excel
| mns06 wrote:
| I used to have a start-up that built a streaming data backend
| for Excel. You can see an animated gif of it here: https://ww
| w.reddit.com/r/dataisbeautiful/comments/8ddmui/rea... the IP
| was acquired, but I reckon there's still ideas to be iterated
| on here.
| cosmie wrote:
| If you're in the Office 365 ecosystem, you can do just that (in
| a sense).
|
| The MS Graph API has a workbook endpoint[1] that lets you do
| nifty stuff with Excel workbooks hosted in OneDrive or
| Sharepoint. Pair that with non-persistent sessions[2], and you
| basically get an Excel workbook as a serverless runtime
| environment.
|
| If you structure the workbook with this type of use in mind, it
| works really handily. Create a non-persistent session, updated
| named items with the input values, run an explicit calculate on
| the workbook, and grab whatever result you're after (a table,
| named range, pivottable, chart graphics, etc), close the
| workbook session (or let it expire).
|
| If you need to keep the data around for historical reasons, you
| can follow the same process but copy the template workbook and
| create a persistent session against the copy, so the
| inputs/outputs are saved.
|
| You can also leverage Power Automate[3] (Microsoft's version of
| Zapier) to create an actual serverless function for your
| specific workflow that can accept your inputs, call the
| appropriate Graph endpoints for those steps, and return the
| output. Although the licensing gets funky, most people with an
| Office 365 license have some level of usage included already.
|
| It's definitely not a solution architecture you want to use for
| anything mission-critical or high-volume, but it's super handy
| for anything that's going to be Excel based anyway and you'd
| like to minimize the surface area for human error during the
| process. Also nifty for situations where a process/scenario/PoC
| is still being matured and developed in Excel, but you need to
| use it for production use cases. Create a stable input/output
| interface with a Power Automate workflow (or other serverless
| interface) that consumers can work against, then continue your
| Excel-based process development without disrupting them. At
| some point when it's stable/mature, port it over to code that
| maintains that same input/output structure and cut over the
| downstream consumers to the new endpoint.
|
| [1] https://docs.microsoft.com/en-us/graph/api/resources/excel
|
| [2] https://docs.microsoft.com/en-us/graph/api/workbook-
| createse...
|
| [3] https://powerautomate.microsoft.com/en-us/
| liminal wrote:
| I once had a client who wanted to make a web app out of a
| spreadsheet that relied on Excel's optimization
| functionality. I wonder if that would work? They might still
| be interested in it.
| cosmie wrote:
| > I wonder if that would work?
|
| I can't say for sure, but likely not. It sounds like your
| client may have been relying on one of the optimization
| add-ons that are automatically installed with Excel[1][2],
| rather than actual Excel features. The company that makes
| those add-ins has developed new versions that work with
| Excel Online, but add-ins in Excel Online execute in the
| local browser context. So I don't think they're
| loaded/usable when you create a headless workbook session
| via the Graph API (although I've never actually tried to do
| that, so could be wrong).
|
| That said, Frontline Systems (the company that makes those
| Excel add-ins) does have a web API[3]. The optimization
| models there are a superset of the capabilities in the
| Excel add-ins, so your client's Excel optimization model
| could likely be ported over to that pretty easily.
|
| [1] https://support.microsoft.com/en-us/office/use-the-
| analysis-...
|
| [2] https://support.microsoft.com/en-us/office/define-and-
| solve-...
|
| [3] https://rason.com/
| thewakalix wrote:
| > You could author one function for each value which checks the
| condition but this is error prone and requires a lot of
| duplication for something pretty basic. For reference, the
| duplicated formulas might look like this (with one formula for
| each value): =AND(A2>50, A2<80) =AND(A3>50,
| A2<80) ...
|
| Appropriately enough, the second line contains a copy-and-paste
| error!
| mayoff wrote:
| Realistically you'd just drag the fill handle rather than using
| copy/paste/edit, so you'd be unlikely to find this bug in a
| real worksheet.
| Karellen wrote:
| http://www.eusprig.org/basic-research.htm
|
| > There is a very extensive research base on the risks of
| using spreadsheets within business see [Panko, 2000] [Panko &
| Ordway, 2005] [Powell, Baker & Lawson, 2007]. Much of the
| research has been coordinated and progressed by EuSpRIG
| [Chadwick, 2003]. Further significant work improving the end
| user approach to software has been undertaken by the EUSES
| consortium [EUSES, 2009].
|
| > The main known risks of spreadsheets include:
|
| > a) Human Error - To err is human, hence the majority (>90%)
| of spreadsheets contain errors. Because spreadsheets are
| rarely tested [Panko, 2006] [Pryor, 2004] these errors
| remain. Recent research has shown that about 50% of
| spreadsheet models used operationally in large businesses
| have material defects [Powell, Baker, Lawson, 2007] [Croll,
| 2008]. Approximately 50% of executives recently surveyed had
| encountered spreadsheet related problems up to and including
| staff dismissal [Caulkins, Morrison & Weideman, 2007].
| louthy wrote:
| Now, if they'd just do that for T-SQL I'd be very happy!
| blululu wrote:
| I feel like this feature was built because the engineering team
| spends too much time reading functional programming/ML zeal here
| on hacker news instead of a real user/customer desire. The irony
| is that this feature will almost certainly be met with
| derision/scorn from the CS crowd and clueless shrugs from excel
| users.
| Ericson2314 wrote:
| > clueless shrugs from excel users.
|
| https://www.youtube.com/watch?v=Rm4y5UqauRw
|
| https://www.youtube.com/watch?v=L7s6Dni1dG8
|
| Demonstrably false.
|
| What has instead happened, I shit you not, is we have "Excel
| influencers" teaching people recursion "without code!" (aka
| without VBScript).
|
| I cried and I laughed when I first saw, it was a watershed
| moment in CS education.
| mcbishop wrote:
| > this feature will almost certainly be met with derision/scorn
| from the CS crowd
|
| Why?
| blast wrote:
| SPJ was the driving force behind the feature:
| https://www.microsoft.com/en-
| us/research/podcast/advancing-e..., so I don't think that is
| true.
| wombatpm wrote:
| So new functions in the spreadsheets instead writing those
| functions in VBA? If VBA was too hard, Microsoft thinks that
| functional programming will go down easier?
| icegreentea2 wrote:
| This lets you more easily reuse excel formulas (within a
| spreadsheet) without enabling VBA or extension modules, which
| is a big boon from a security standpoint.
|
| Given the existing wonkiness of the excel formula programming
| model, anyone who already had a grasp of actual tables and
| array formulas (which to be fair, is already a small subset of
| excel formula users) will be able to pick up these pretty
| easily.
| nexuist wrote:
| Why not? VBA is confusing and outdated. If they ported macros
| to JS I think a lot more people would be comfortable with it.
| In the same vein being able to stay in Excel's formula language
| instead of teaching yourself VBA seems a lot more tenable for
| formula gods who haven't quite figured out imperative
| programming outside of Excel yet.
| zamadatix wrote:
| VBA was "too powerful" in 2 ways. Primarily it was a security
| risk, it had way too much capability beyond implementing custom
| functions on spreadsheet data. Secondarily it was too decoupled
| from the spreadsheet, an programming environment bolted on top
| of the spreadsheet, while lambdas are much smaller in scope
| targeted specifically at just reusable spreadsheet functions.
| thebiss wrote:
| The biggest challenge with Excel features remains versioning:
| functions are tied to a specific Excel releases, and it takes
| years before enough people run a version supporting them.
|
| I wish they made these an add-on.
|
| Until then, I expect to hear my fancy files are "broken."
| dudus wrote:
| My Org has Office 365 but the Office installed on our machines
| are controlled by the Corp. Until recently we had the V2008
| release from July 2020, just a month ago we got updated to
| V2102 from Feb 2021.
| omh wrote:
| Quite a lot of organisations are now running Office 365. It
| gets updated monthly with new features as well as security
| updates.
|
| Even more conservative organisations will often be running
| Office 365 with a lag, still getting features after ~6 months.
|
| If you need 100% compatibility that will always take a while,
| and I'm sure some big enterprises are on old style office. But
| it seems like the vast majority of small/medium business is on
| the Office 365 juggernaut now.
| signal11 wrote:
| True. And a few large organisations are taking those baby
| steps too. Some of these super-conservative organisations
| started using subscription software with Adobe's creative
| cloud, and got familiar with monthly update schedules thanks
| to Windows and RHEL patching. They are slowly coming around
| to accepting that Word and Excel can be deployed and patched
| just like the OS, and paid for like Photoshop, or used online
| along with things like Teams and OneDrive inside the
| organisation.
| masklinn wrote:
| > The biggest challenge with Excel features remains versioning:
| functions are tied to a specific Excel releases, and it takes
| years before enough people run a version supporting them.
|
| Also seems difficult to maintain if these are cell-bound, and
| both the cell edition interface and the Name Manager interface
| were an incredibly poor edition experience from what I
| remember, but maybe that improved since?
| occamrazor wrote:
| There is a new and better Name Manager in the Mac version.
| queuebert wrote:
| Congratulations, Microsoft. Welcome to 1958.
| Aisen8010 wrote:
| They could have used some notation to separate the arguments from
| the function body. The arrow => would be a good candidate.
| harperlee wrote:
| It would be an awful option, think of the diversity of excel
| users. Millions of them would struggle to understand that here,
| this time, that does not represent "equal or greater", and it
| would be an endless source of confusion.
| hencq wrote:
| You're right of course, but I secretly dream they would just
| change the syntax to straight up s-expressions.
| masklinn wrote:
| More importantly, regularity is nice, and Excel already has
| no syntactic delineation between functions and special forms
| e.g. IF will only evaluate the expression matching the
| condition (though AND and OR are somewhat unexpectedly
| eager).
| [deleted]
___________________________________________________________________
(page generated 2021-08-23 23:02 UTC)