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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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"] | |
| ]; | |
| } |
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.
Future is here ? Really? Google docs has been offering these features and more for at least 10 years…
They sure have. I will admit for a late comer to productivity suite software, Google had some novel approaches to a (then) web 1.0 only world and build something that worked. They are still #1 in search still too. Bing that! 😉
But for people who have been recording and writing macros, VBA add-ins, C++ extensions, VB.NET & C# extensibility add-ins, VB Script, JavaScript & PowerShell Scripts, VSTO add-ins, OpenXML server-side solutions, Graph API and Exchange web services, for Microsoft Office since 1995, 2000, 2005 and 2010 before Google came along, this “IS” the future for them. These Microsoft Office based solutions have been foundational to their organizations for the last 25+ years… just sayin’. 🙂