Naturally, that doesn't happen, but other things emerge, more specifically hidden dreams: Tim would like to study Psychology at the local University, while Dawn originally became a receptionist just so she could have a day job while striving for a career as an illustrator. His latest "achievement"? It's time for appraisals, and he's desperately hoping someone mentions him as a role model. It gets even worse at The Office, with none other than David Brent calling the shots. Autogenerated Office Scripts Codeįunction main(workbook: ExcelScript.Workbook) ).Ĭonst tableHeaders = Ĭonst result = tableValues.Life at the office can be hard: demanding bosses, uncooperative colleagues, yada yada yada.
In the following code sample, you can see some of the code that was autogenerated using the Action Recorder as well as code I wrote with the Code Editor. Finally, I used Power Automate to refresh the Power BI r eport, and then send a Teams message and email about the report.I used Office Scripts to de-duplicate data in a file.Power Automate then appended the data to the Excel files.Next, I used Office Scripts to return multiple rows of each table as JSON to Power Automate.I then used an Office Script for each file to organize the data into tables and add the publication month to the report.Using Power Automate, I downloaded CSV files from an email as.To create these scripts, I used a combination of the Action Recorder, which records actions you take in the Excel workbook and saves them into a script and the Code Editor, which enables you to write and edit scripts through TypeScript/JavaScript. In total, I create 3 files using 6 Office Scripts. In total, the automated processes that I’ve created with Office Scripts save us around 6 hours of effort per month and I have many colleagues, both in and out of IT, who are beginning to use Office Scripts to make their work lives easier. Additionally, since creating this solution, I was asked to implement it for other areas of our Intranet.
Since this the process has worked reliably and without any manual input and this ensures that the statistics are always up to date. It is great to have the option of writing your own code or just recording it.Īfter putting it all together and testing, I was able to put the Power Automate Flow into production. However, with some support I wrote the code in the right-hand column without using the recorder. Given IT Inside is the main IT resource for Uniper, this Power BI report is key to our goal of improving the style and content of articles that we publish.įor me, writing my own solution was more challenging as it involved returning processed data back to Power Automate.
Before using Office Scripts, I was spending time manually downloading data from email, formatting it in spreadsheet, and adding it to a Power BI report that gives statistics on the usage and top articles in our company intranet, IT Inside. Using Office Scripts and Power Automate, I built a script that fully automates a report that used to take me at least one to two hours per month. Having a low code solution readily available was very helpful. As you can see in the code sample, the left-hand column of data was entirely automatically generated by the recorder. I found the process straightforward thanks to the online documentation which helped me understand what is possible using Office Scripts. Building a Scriptīefore I started this project, I had never used Office Script before. Today you’ll hear from Gareth Naylor, a Group Wide Architect/Strategist in the CIO Office from Uniper.