scripts

Microsoft has been working hard to make Office available on every platform. One of the biggest changes has been the ability to run Office the a browser or Office online. However, one drawback has been that automation in Office online has been limited to Web Add-ins which are aimed solidly at professional developers. However, Visual Basic for Applications (VBA) macros are only supported in Windows and Mac clients. They are not supported on the web platform, the iOS or Android platforms. And, well, they still are not. However, something new has arrived which will allow you to create “macros” anywhere, on any platform (eventually).

Microsoft recently announced Office Scripts. It is an end-user approachable, web and collaborative supported scripting language. It is still in preview and only supported in Excel online, for now.

First, to access this you must have your administrator enable it in the Office 365 Portal:

For citizen programmers this line from the above page says it all:

Scripts allow you to record and replay your Excel actions on different workbooks and worksheets. If you find yourself doing the same things over and over again, an Office Script can help you by reducing your whole workflow to a single button press.

So, I enabled it in my test tenant and created the quintessential “Hello Wold” Action Recording. When your administrator enables this on your tenant, you will get this new Automation tab:

From there, I clicked on Record Actions, typed “Hello World!” in cell A1, and then stopped the macro. It then asked me to name it and give a description (optional), and then Save. From there it opened the Code Editor and here is what I see:

	async function main(context: Excel.RequestContext) {
	// Set range A1 on selectedSheet
	let workbook = context.workbook;
	let worksheets = workbook.worksheets;
	let selectedSheet = worksheets.getActiveWorksheet();
	selectedSheet.getRange("A1").values = [
	["Hello World"]
	];
	}

view raw

HelloWorld.js

hosted with ❤ by GitHub

Once you open a new workbook, you can then click on the Code Editor button and you will see all your recorded scripts:

The future is here. Check it out now. You can learn more about Office Scripts from here: https://docs.microsoft.com/en-us/office/dev/scripts/tutorials/excel-tutorial.

Tag: scripts

Office Scripts: The future is here