Sharing your Office Scripts in Excel can greatly enhance collaboration and efficiency, whether you’re working within your team or across different organizations. In this post, we’ll explore how to seamlessly share your Office Scripts within a workbook by adding a button, offering tips for distributing scripts within your organization, and finally, providing guidance on sharing scripts with people outside your organization.
You can follow along by downloading the exercise file below:
This workbook contains multiple worksheets with related datasets typically encountered in a business setting. To help users easily navigate and organize the content, we’ve developed an Office Script that creates and updates a table of contents worksheet. You can use this script to simplify your workbook management. Just copy the script below and paste it into a new Office Script to get started:
Name your script something like “Add TOC, Check for Existing TOC” and save it. After saving, go to Script Details to explore the available options for sharing your script.
Sharing within a workbook
By default, your Office Script is configured to be accessible only to you in this workbook. To enable others to use it, you can modify the script’s access settings. If the script is being used often, particularly by those who may not be comfortable navigating to Office Scripts and running it manually, a convenient way to share it within the workbook is by adding a button, similar to how you would for a VBA macro.
You can right-click the button to modify, relocate, or make other adjustments. This setup is great for sharing and using the script within this specific workbook, but what if users want to use it in other workbooks?
Sharing with other users in your organization
To share your Office Script across your organization, I suggest accessing your OneDrive account online. By default, all Office Scripts are stored in the Documents folder under Office Scripts. From there, you can view and modify the sharing permissions of your files.
Sharing to SharePoint
You can move this script to SharePoint using the Move options, allowing it to be stored in a shared location. This makes it easier to reuse and manage across the organization!
Sharing outside your organization
One of the sharing options in OneDrive is to share the link with anyone. However, when opening an Office Script in OneDrive, it may not render correctly and instead display the raw JSON format of the script rather than the scripted function. This issue often occurs due to how OneDrive handles certain file types or how the script is accessed through the OneDrive interface. While the file can be saved locally and opened in Excel using the OSTS file extension, I find this process a bit cumbersome, to be honest.
As an alternative, for a quick and easily shareable script that displays nicely and is searchable, I recommend pasting your script into a GitHub Gist. Although this method won’t store the script in your OneDrive—it’s a copy-pasted version—it allows you to format the file with a .js extension, making it visually appealing. In fact, this is what I did with the original script I shared with you, which I’m including again below.
Since Microsoft owns GitHub, there may be more opportunities in the future to enhance integration between this feature and OneDrive or SharePoint.
Conclusion
Sharing your Office Scripts for Excel can greatly enhance collaboration and efficiency, making it easier for you and your team to automate tasks and streamline workflows. While the file type isn’t the most portable at the moment, with a little effort, you can get your scripts where they need to go.
Do you have any questions about sharing scripts or want to explore how Office Scripts can further elevate your Excel experience? Let me know in the comments!
Resources
- “Sharing Office Scripts in Excel,” Microsoft Support
Leave a Reply