![\"Gareth](\"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-content\/uploads\/sites\/73\/2021\/07\/Gareth-uniper-150x150.png\")
<\/p>\n<\/p>\n
To learn more about Office Scripts and how to automate a report, today you\u2019ll hear from Gareth Naylor, a Group Wide Architect\/Strategist in the CIO Office from Uniper.<\/p>\n
Before I started this project, I had never used Office Script before. I found the process straightforward thanks to the online documentation which helped me understand what is possible using scripts. As you can see in the code sample, the left-hand column of data was entirely automatically generated by the recorder. Having a low code solution readily available was very helpful.<\/p>\n
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. 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. 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.<\/p>\n
For me, writing my own solution was more challenging as it involved returning processed data back to Power Automate. However, with some support I wrote the code in the right-hand column without using the recorder. It is great to have the option of writing your own code or just recording it.<\/p>\n
After putting it all together and testing, I was able to put the Power Automate Flow into production. Since this the process has worked reliably and without any manual input and this ensures that the statistics are always up to date. Additionally, since creating this solution, I was asked to implement it for other areas of our Intranet. In total, the automated processes that I\u2019ve 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.<\/p>\n
In total, I created three files using six scripts. 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.<\/p>\n
The full process took about six steps:\u00a0<\/span><\/span><\/p>\n 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.<\/p>\n Learn more about Office Scripts, find resources, and watch video tutorials on the Office Scripts Developer Center<\/a>.<\/p>\n Stay up to date, read the Office Scripts developer blog<\/a>.<\/p>\n\n
\/\/Autogenerated Office Scripts Code\r\n\r\nfunction main(workbook: ExcelScript.Workbook) { \r\n\r\n let selectedSheet = workbook.getActiveWorksheet(); \r\n\r\n \/\/ Set range E1 on selectedSheet \r\n\r\n selectedSheet.getRange(\"E1\") \r\n\r\n .setValue(\"ReportMonth\"); \r\n\r\n \/\/ Set width of column(s) E1 to 70.5 \r\n\r\n selectedSheet.getRange(\"E1\") \r\n\r\n .getFormat() \r\n\r\n .setColumnWidth(70.5); \r\n\r\n \/\/ Create a table with format: 'Sheet1' \r\n\r\n let table1 = workbook.addTable(selectedSheet.getUsedRange(true), true); \r\n\r\n table1.setPredefinedTableStyle(\"TableStyleLight9\"); \r\n\r\n \/\/ Set range E2 on selectedSheet \r\n\r\n selectedSheet.getRange(\"E2\") \r\n\r\n .setValue(\"=EDATE(TODAY(), -1)\"); \r\n\r\n \/\/ Set number formats for Sheet1!E:E \r\n\r\n selectedSheet.getRange(\"E:E\") \r\n\r\n .setNumberFormatLocal(\"[$-en-GB]mm-yyyy;@\"); \r\n\r\n \/\/ Set width of column(s) E1 to 261 \r\n\r\n selectedSheet.getRange(\"E1\") \r\n\r\n .getFormat() \r\n\r\n .setColumnWidth(261); \r\n\r\n}<\/pre>\n\/** \r\n * Written code (compare and contrast with the previous autogenerated code) \r\n * Lists rows present in a specific table named tableName and return to Power Automate as JSON \r\n * *\/ \r\n\r\nfunction main(workbook: ExcelScript.Workbook, tableName: string = \"Table1\") { \r\n\r\n const table = workbook.getTable(tableName); \r\n\r\n \/\/ Get the range of only the body of the table, not the header \r\n\r\n const tableRange = table.getRangeBetweenHeaderAndTotal(); \r\n\r\n const tableValues = tableRange.getValues(); \r\n\r\n \/\/ We must specify the table headers or the rows won't show up nicely in Power Automate. We must also specify the headers below in the TypeScript type (... as { ... }). \r\n\r\n const tableHeaders = [\"PageID\", \"Page\", \"ArticleID\", \"Article\", \"PublishedDate\", \"Views\", \"Unique Visits\", \"Users\", \"Likes\", \"Shares\", \"Comments\"]; \r\n\r\n const result = tableValues.map((rowValues) => { \r\n\r\n return rowValues.reduce((row, cellValue, i) => { \r\n\r\n const header = tableHeaders[i]; \r\n\r\n row[header] = cellValue; \r\n\r\n \r\n\r\n return row; \r\n\r\n }, {} as { \r\n\r\n A: string; \r\n\r\n B: string; \r\n\r\n C: string; \r\n\r\n D: string; \r\n\r\n E: string; \r\n\r\n F: string; \r\n\r\n G: string; \r\n\r\n H: string; \r\n\r\n I: string; \r\n\r\n J: string; \r\n\r\n K: string; \r\n\r\n }); \r\n\r\n }); \r\n\r\n console.log(result); \r\n\r\n return result; \r\n\r\n}<\/pre>\nAbout Uniper<\/h3>\n