Ever had a script you just can't scratch? Excel on the web now has just the thing

It won't be like VBA this time, we promise

Microsoft has brought in Office Scripts for Excel on the web and pushed out the XLOOKUP function, a replacement for the well-used VLOOKUP, to both web and desktop versions of the spreadsheet program.

Excel is a strategic application for Microsoft, being one of the elements that keep businesses hooked on Windows. Web versions of Office apps were introduced (without enthusiasm) at Microsoft's Professional Developers Conference in 2008, the idea being that these were lightweight versions good for touching up or collaborating on documents, but wouldn't be competition for the real thing. The company was trying to keep up with Google (which already had Google Docs at that stage) while not undermining its desktop business.

Since then things have played out as you would have expected. Desktop Office has trundled on, but the web versions today cover a fuller range of features. Microsoft does not publish a comprehensive list of the differences, though there is a starting point here.

There are still issues with the web versions, like the lack of Visual Basic for Applications (VBA) macros along with desktop-specific technologies like COM automation, which lets you control Office applications programmatically. There are also features that do not work online, such as sparklines (mini-charts) and some advanced chart types in Excel.

VBA is a COM technology that will never work online, but Microsoft has now introduced Office Scripts, in preview for Excel, but judging by the name, likely to appear for other applications as well.

A simple Office script in Excel for the web

A simple Office Script in Excel for the web

Office Scripts "allow you to record and replay your Excel actions on different workbooks and worksheets". The scripts are not stored in the document, but are part of a user's online Excel environment. They are written in JavaScript or TypeScript, and use the Excel part of the Office JavaScript API (as used by Office Add-ins), though without support for events.

Office Add-ins have been around for years, work across desktop and web, and have more functionality than Office Scripts, but are aimed at developers and require web deployment, whereas Office Scripts are accessible to any user.

What can you do with an Office Script?

The API is extensive, but one issue we ran into is that when you record an action it lacks the option to use relative references (found in desktop Excel), so all the cell references are absolute values.

There is no option to "do this starting at the current cell". You may be able to overcome this by editing the script. Samples are thin on the ground but no doubt more will come soon. A typical case would be where you have a regular input of raw data that you need to format nicely, using the same steps every time.

Currently Office Scripts require an Office 365 E3 or E5 subscription and to be enabled by an administrator. Other subscription types will come later. Microsoft also said it would "look to bring Office Scripts to other Excel endpoints", hinting perhaps at a desktop version. Microsoft's Alex Jerabek, a dev writer, reckoned: "VBA remains the best solution for automating tasks in Excel for desktop. We hope to eventually grow the capabilities of the Office Scripts platform to match what VBA currently offers."

Look it up

Microsoft has also announced the general availability of a new Excel function, XLOOKUP. This is a replacement for VLOOKUP – a big deal in the Excel world since, according to Microsoft, VLOOKUP is "the 3rd most used function (after SUM and AVERAGE)". All that telemetry has to be useful for something.

The idea of both VLOOKUP and XLOOKUP is to query an Excel worksheet to find additional data. For example, you could look up a product's price or stock from its part number, if these three values are in Excel columns. VLOOKUP returns a single value, but XLOOKUP returns an array, so it could return both the price and the stock.

It can also do both vertical and horizontal matches, therefore also replacing HLOOKUP. You can see all the details here. If you are used to doing magic with the MATCH function to get around limitations of VLOOKUP, you will like XLOOKUP.

XLOOKUP example showing a nested lookup

XLOOKUP example showing a nested lookup

The snag with this, as with many Excel formulae, is that once you start nesting functions, they soon get hard to read – even...

SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))

... (from the official examples) is not beautiful. You could, of course, use an actual database manager instead, but the convenience and accessibility of Excel will no doubt continue to ensure its popularity. ®

Sponsored: Practical tips for Office 365 tenant-to-tenant migration

SUBSCRIBE TO OUR WEEKLY TECH NEWSLETTER


Biting the hand that feeds IT © 1998–2020