Office Scripts in Excel is an automation alternative to Visual Basic for Applications (VBA). Macros are still recorded as a series of actions, but now, these steps are performed using TypeScript, which a superset language of JavaScript. Assuming everything is licensed and configured correctly, everyone should see an Automate tab along the top of their Excel workbooks, for any of their files saved to SharePoint Online or OneDrive for Business:
The simple ask for today, generate a custom GUID value, similar to the Power Automate cloud flow example. As with the that example, the GUID value will be a mix of numbers and letters, but also be 64 characters long:
The full snippet can be found here, but essentially, there are a handful of variables, a loop, and a function doing the heavy lifting. The first variable, selectedSheet, is pretty straight-forward as it gets the active worksheet of the Excel file. Next, initialize three additional variables:
(1) To control the length of the GUID, assign a value to the lenOfGuid variable. (2) For building the GUID as it’s generated in the loop, initialize the txtOfGuid variable. (3) Lastly, because the GUID needs letters, create an array of the alphabet, charOfGuid. Next, loop and append the randomized characters using a switch function:
Conclusion:
Personal plea, start moving away from VBA macros and adopt Office Scripts where possible. The syntax is easier to read, and it can perform many of the same tasks as VBA. Unfortunately, VBA is falling out of popularity. Newer developers aren’t looking to learn VBA as there aren’t as many job opportunities there, so attracting talent will be tough.
“We who believe in freedom cannot rest until it comes.”
Ella Baker
#BlackLivesMatter
console.log('Charles'); 