This repository contains Office Scripts based automation solutions that helps end users achieve automation of daily tasks. It contains realistic scenarios that business users face and provides detailed solution along with step-by-step instructional video links.
For each of the following projects, check out the folders for description, source code, step-by-step Youtube videos for select projects.
This repository is currently not being maintained. Samples and guidance mentioned in the repo and related videos could be out of date. Please refer to official documentation for the latest.
| Project | Details |
|---|---|
| Basics of Office Script and getting started | Learn about the Office Script language, basics of the object model, performance tips, error handling, etc. |
| Learn about the Excel Range object | This article shows the basics of using Range object and its APIs. This is a foundational topic that'll be used across all other projects. Start here.. |
| Top 5 tips to accelarate your learning | Use the following tips to reduce your development time and improve quality of the scripts. |
Check out the following end to end project that automates sample scenarios along with full scripts, sample Excel files used, and videos.
| Project | Details |
|---|---|
| Excel Recalc, create chart, extract chart, table image, email | This project shows how to use Office Scripts and Power Automate actions to re-calculate, create chart and extract images as base-64 string to be used later to send Email. |
| Move select rows across tables using filter and value lookup (2 scripts) | This project moves certain rows that meet a criteria from one table (source) into a another table (target) on another worksheet using (1) plain values based selection and (2) using column filter. |
| Copy Excel tables into new master table | This project shows how to combine data that resides in Excel tables into a new master table with all rows included. It creates a new worksheet and combines all rows into a single table. |
| Filter Table and get Visible range | This project shows how to use Office Scripts to filter Excel table to get list of rows visible as array of objects. |
| Document number generator | This scenario helps a user generate a unique document number with a specific format and add an entry to a range or table |
| Add comments to Excel cell with at-mention of a contact | This project shows how to use Office Scripts to add comments to Excel cell with at-mention of a contact. |
| Learn how to manage calculation mode and calculate in Excel for web | This short project shows how to use Office Scripts to manage calculation mode in Excel for web and calculate in Excel files whose mode is set to be in manual mode. |
| Count blank rows in a sheet or across sheets | Detect if there are any blank rows in sheet(s) where you anticipate data to be present and then report the blank row count for usage in another Power Automate flow. |
| Cross reference Excel files and format Excel cells | This project shows how two Excel files can be cross-refenced and formatted using Office Scripts and Power Automate. |
| Automate tasks across all Excel files on a folder | This project performs a set of automation tasks on all files situated in a folder on OneDrive-Business (can also be used for SharePoint folder). It performs calculation on the manual Excel calc files, adds formatting and inserts a comment with at-mention note to a colleague. |
| Send Teams meeting invite and update Excel | This project shows how to use Office Scripts and Power Automate actions to select rows from Excel file and use it to send Teams meeting invite and update back Excel. |
| Remove hyperlink from Excel cells | This sample clears all of the hyperlink from the current worksheet. It traverses through the worksheet and if there is any hyperlink associated with the cell, it cleares the hyperlink and retains the cell value as is. Logs the time it takes to complete traversal. |
| Output Excel table data as array of objects (as JSON) with hyperlink data for usage in Power Automate | Output Excel table data as array of objects (as JSON) for usage in Power Automate. Contains two samples - one that extracts basic text values and a second one that extracts hyperlink value instead of the text value for one of the columns. |
| External API Calls using Office Scripts | Office Scripts allows limited external API call support. Learn about the limitations and basics of how to make an external call. |
| Simple technique to use Excel macro files (xlsm) in Power Automate Run Script action | Get around the limitation to use xlsm files in Excel online's Run Script action. |
| Project | Details |
|---|---|
| Performance related topics | Basic to advanced performance related topics (..in progress..) |
| Project | Details |
|---|---|
| Seasons greetings animation project | This is a script contributed by Leslie Black that creates Holidays theme animation in Excel using Office Scripts. |
The project is open sourced. Try it out yourself. You'll need Microsoft (Office) 365 account from work or school with E3 or above license. Just head over to https://office.com to sign-in to your account and get started.
Note: This repo is not being maintained.
Feel free to leave a comment or make a suggestion or log an issue by opening a new issue.