4 ms·
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 w
by cosmie 5y ago
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 https://docs.microsoft.com/en-us/graph/api/resources/excel
[2] https://docs.microsoft.com/en-us/graph/api/workbook-createsession https://docs.microsoft.com/en-us/graph/api/workbook-createse...
[3] https://powerautomate.microsoft.com/en-us/ https://powerautomate.microsoft.com/en-us/
- liminal 5y agoI 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 5y ago> 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-toolpak-to-perform-complex-data-analysis-6c67ccf0-f4a9-487c-8dec-bdb5a2cefab6 https://support.microsoft.com/en-us/office/use-the-analysis-... [2] https://support.microsoft.com/en-us/office/define-and-solve-a-problem-by-using-solver-5d1a388f-079d-43ac-a7eb-f63e45925040 https://support.microsoft.com/en-us/office/define-and-solve-... [3] https://rason.com/ https://rason.com/
- pjmlp 5y agoEasy, via OLE Automation API. The problem is handling Excel instances.
- snthpy 5y agoThank you. This approach sounds really handy for some things in my environment.