Using Office Scripts in Power Automate to Generate Graphs without Third Party Connectors or Services

Using Office Scripts in Power Automate to Generate Graphs without Third Party Connectors or Services

Office Scripts is a relatively newer feature in Microsoft 365. Currently available on Excel Online, Office Scripts allows TypeScript code to execute within the context of Power Automate. It can be used to automate Excel; however, because of its integration with Power Automate it also allows the user to call into these scripts and retrieve results. It can also help bridge some functionalities that might be difficult to implement in Power Automate, for example, sorting arrays.

In this Demo, the user will learn:

  1. How to use Office Scripts
  2. How to create temporary an Excel file without using a placeholder template already uploaded
  3. Populate the temporary Excel files with data, generate charts, and return to Power Automate

    Please refer to https://docs.microsoft.com/en-us/microsoft-365/admin/manage/manage-office-scripts-settings?view=o365-worldwide on how to enable office scripts in the tenant.

Setup:

  1. A SharePoint list with three columns to hold some values, as in the example below:

  2. Power Automate
  3. Excel Online

Steps:

  1. Head to Excel Online and open a new blank workbook. The user will use this to create the office script.

  2. Click on the “Automate” tab. This is only available on Excel Online (not desktop), and if the tenant has this enabled. On the Automate tab, click on “New Script”.

  3. The code editor will open

  4. Type in the script below and save

function main(workbook: ExcelScript.Workbook, sourceWorkSheetName: string, newWorkSheetName: string, fieldNameToCompute: string): string {

let sheet: ExcelScript.Worksheet = workbook.getWorksheet(sourceWorkSheetName);

let table: ExcelScript.Table = sheet.getTables()[0];

let sheetCheck: ExcelScript.Worksheet = workbook.getWorksheet(newWorkSheetName);

if (sheetCheck) {

sheetCheck.delete();

}

let newSheet: ExcelScript.Worksheet = workbook.addWorksheet(newWorkSheetName);

let pivotTable: ExcelScript.PivotTable = newSheet.addPivotTable(“My Pivot”, table, “A1”);

pivotTable.addFilterHierarchy(pivotTable.getHierarchy(“Department”));

pivotTable.addFilterHierarchy(pivotTable.getHierarchy(“Status”));

pivotTable.addRowHierarchy(pivotTable.getHierarchy(fieldNameToCompute));

pivotTable.addDataHierarchy(pivotTable.getHierarchy(fieldNameToCompute));

let rangeInUse = newSheet.getUsedRange();

let chart: ExcelScript.Chart = newSheet.addChart(

ExcelScript.ChartType.barStacked,

rangeInUse,

ExcelScript.ChartSeriesBy.rows);

return chart.getImage();

}

  1. This will create an office script with extension .osts in your One Drive under /Documents/Office Scripts

  2. All office scripts are saved to your One Drive. Although, in this example, I had used Excel to write the code for the office script, the script itself can be executed on any excel file.
  3. Let’s look at what the office script does before moving on to the next steps.
    1. When you create a new script the main function with the following signature would have already been created

      function main(workbook: ExcelScript.Workbook)

      {

      // Your code here

      }

    2. I modified the method signature to add parameters to accept the name of the source worksheet, name of a new worksheet, a field name in the SharePoint list and it returns a string value. The chart will be returned as a base64 string to the Power Automate.
    3. sourceWorkSheetName will be passed from Power Automate to the office script. We will see how to construct this without relying on a pre-uploaded template later in this blog post.
    4. In short, what the code does is to start by checking if a sheet with a name already exists. If the sheet exists, we delete it and create a new one. I then add a pivot table and add a pivot table hierarchy to create a pivot table in the new sheet. After that, I use the data in the pivot table to generate a stacked bar chart and return it to Power Automate.

Let us now look at the Power Automate side of the things. Overall, the flow looks like this:

I start by an instant flow with a manual trigger.

I initialize variables to hold CAML query to retrieve SharePoint list items (variable: viewXML), another variable to define the new worksheet name (variable: tmpWorksheetName), and a third to hold the base 64 representation of an Excel File.

To get the base64 representation of an Excel file, create a blank workbook, add some random content, and use any number of techniques to generate. For my scenario, I searched on the internet for an online converter to convert file into base64. This is the one I used: https://www.giftofspeed.com/base64-encoder/

My empty Excel workbook looked like this:

Once we have the base64, we can store it in a variable in Power Automate and use this to create an Excel file.

The scope is where all the actions to make it happen are. Let’s have a look at the scope at a high-level and the actions that I have.

Here are the actions needed:

  1. Create a file in SharePoint – uses the base64 string to create a file. Create file uses base64ToBinary function to convert the base64 string into binary for the Excel file. Once this action is executed, an Excel file is created in the “Documents” document library.

  2. Send an HTTP request to SharePoint using CAML query to retrieve the list items

  3. Excel online actions are used next to create a worksheet in the newly created workbook and create a table in the worksheet. This worksheet will hold the Excel data that is retrieved from SharePoint

  4. Next, I iterate over the results returned from the HTTP request to SharePoint as follows

  5. In the “Apply to Each” I iterate over the results array and add rows to the table
  6. After all the rows have been added to the Excel table, I use the “Run Script” action in the Excel Online connector

  7. Notice that the “Run script” action automatically creates placeholders for the different parameters that I had defined in the script. In the scenario above,
    1. sourceWorkSheetName is coming from a variable defined in the flow
    2. newWorkSheetName is called “Pivot”
    3. fieldNameToCompute is the name of the field from SharePoint. Note, that the name passed here is what shows on the Excel table, and not the SharePoint internal field name. In my case, both are the same.
  8. After the script executes, it returns the base64 string for the chart image, which I then use to send an email as shown below
  9. Because the image returned is a base 64 string, we must specify as shown in the image above.
  10. After the email is sent, it looks like this:

In conclusion, we have just learned how to generate a chart without using any third-party connectors. Office scripts can be used to create all kinds of complex graphs and return them as images back to Power Automate. These images can be embedded in emails or reports.