To learn more about Office Scripts and how to automate a report, today you’ll hear from Gareth Naylor, a Group Wide Architect/Strategist in the CIO Office from Uniper.

Build an Office Script

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.

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.

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.

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’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.

Office Script samples

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.

The full process took about six steps: 

1. Using Power Automate, I downloaded CSV files from an email as .xlsx files.
2. I then used an Office Script for each file to organize the data into tables and add the publication month to the report.
3. Next, I used Office Scripts to return multiple rows of each table as JSON to Power Automate.
4. Power Automate then appended the data to the Excel files.
5. I used Office Scripts to de-duplicate data in a file.
6. Finally, I used Power Automate to refresh the Power BI report, and then send a Teams message and email about the report.  

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.

//Autogenerated Office Scripts Code

function main(workbook: ExcelScript.Workbook) { 

  let selectedSheet = workbook.getActiveWorksheet(); 

  // Set range E1 on selectedSheet 

  selectedSheet.getRange("E1") 

    .setValue("ReportMonth"); 

  // Set width of column(s) E1 to 70.5 

  selectedSheet.getRange("E1") 

    .getFormat() 

    .setColumnWidth(70.5); 

  // Create a table with format: 'Sheet1' 

  let table1 = workbook.addTable(selectedSheet.getUsedRange(true), true); 

  table1.setPredefinedTableStyle("TableStyleLight9"); 

  // Set range E2 on selectedSheet 

  selectedSheet.getRange("E2") 

    .setValue("=EDATE(TODAY(), -1)"); 

  // Set number formats for Sheet1!E:E 

  selectedSheet.getRange("E:E") 

    .setNumberFormatLocal("[$-en-GB]mm-yyyy;@"); 

  // Set width of column(s) E1 to 261 

  selectedSheet.getRange("E1") 

    .getFormat() 

    .setColumnWidth(261); 

}

/** 
 * Written code (compare and contrast with the previous autogenerated code) 
 * Lists rows present in a specific table named tableName and return to Power Automate as JSON 
 * */ 

function main(workbook: ExcelScript.Workbook, tableName: string = "Table1") { 

    const table = workbook.getTable(tableName); 

    // Get the range of only the body of the table, not the header 

    const tableRange = table.getRangeBetweenHeaderAndTotal(); 

    const tableValues = tableRange.getValues(); 

    // 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 { ... }). 

    const tableHeaders = ["PageID", "Page", "ArticleID", "Article", "PublishedDate", "Views", "Unique Visits", "Users", "Likes", "Shares", "Comments"]; 

    const result = tableValues.map((rowValues) => { 

        return rowValues.reduce((row, cellValue, i) => { 

            const header = tableHeaders[i]; 

            row[header] = cellValue; 

  

            return row; 

        }, {} as { 

            A: string; 

            B: string; 

            C: string; 

            D: string; 

            E: string; 

            F: string; 

            G: string; 

            H: string; 

            I: string; 

            J: string; 

            K: string; 

        }); 

    }); 

    console.log(result); 

    return result; 

}

Learn more about Office Scripts, find resources, and watch video tutorials on the Office Scripts Developer Center.

Stay up to date, read the Office Scripts developer blog.

About Uniper

Uniper is an international energy company with around 12,000 employees in more than 40 countries. The company plans to make its power generation CO2-neutral in Europe by 2035. For more information about Uniper visit https://www.uniper.energy/.