How to Visualize Data in Power Automate and Export a Graph Image without Power BI

#powerautomate #powerplatform #typescript

Believe it or not, you can graph data in Power Automate and display it as an image without using Power BI or some third-party connector or API endpoint.
The trick is using Excel Online Scripts.

Create Excel Online File

In this example I’ll create an Excel Online file in my SharePoint site.

Create a Table to Hold Your Data

I’ll make a simple table with the headers “state,city,population”.

I name the table “CityDataTable”.

Write a Script

The script is written in Typescript. You can use Chat GPT or another AI Assistant to help. In my experience they are pretty good but you always need to fix or tweak a few things.
Click on “Automate” then “New Script”.

Here is the code I used (generated by Chat GPT and cleaned up by me):



function main(workbook: ExcelScript.Workbook, inputChartName: string, inputData: { state: string, city: string, population: number }[]) {
    // Define the worksheet and table name
    const sheetName = "Sheet1"; // Change this to match your sheet name
    const tableName = "CityDataTable"; // Change this to match your table name

    // Get the worksheet
    let sheet = workbook.getWorksheet(sheetName);
    if (!sheet) {
        throw new Error(`Worksheet '${sheetName}' not found.`);
    }

    // Get the table
    let table = sheet.getTable(tableName);
    if (!table) {
        throw new Error(`Table '${tableName}' not found.`);
    }

    // Delete all existing rows from the table
    let rowCount = table.getRowCount();
    for (let i = rowCount - 1; i >= 0; i--) {
        table.deleteRowsAt(i, 1);
    }

    // Convert inputData to an array of arrays for table insertion
    let values = inputData.map(item => [item.state, item.city, item.population]);

    // Add new rows to the table
    if (values.length > 0) {
        table.addRows(-1, values);
    }

    // Delete existing charts
    let charts = sheet.getCharts();
    for (let chart of charts) {
        chart.delete();
    }

    // Define data range for the chart
    let dataRange = table.getRange();

    // Create a new Treemap chart with the data range
    let chart = sheet.addChart(ExcelScript.ChartType.treemap, dataRange);
    chart.setPosition("E1", "P20"); // Adjust position as needed
    chart.setWidth(1280); // Set width to 1280 pixels
    chart.setHeight(720); // Set height to 720 pixels

    // Set chart title
    let chartTitle = chart.getTitle();
    chartTitle.setText(inputChartName);
    chartTitle.setVisible(true);

    // Enable data labels and show category names
    let dataLabels = chart.getDataLabels();
    dataLabels.setShowCategoryName(true);

    // Get chart name and type
    let chartName = chart.getName();
    let chartType = chart.getChartType(); // Returns an enum value

    // Get chart image
    return {
        chartImg: chart.getImage(),
        chartName: chartName,
        chartType: chartType.toString() // Convert enum to string for readability
    }
}

Build a Flow

How you build your flow depends on your use case. The trigger will depend on whether you want to use this in a Scheduled Email Report or a Power App.

Trigger

In this case for demonstration purposes I’ll make it a public HTTP GET endpoint with no authorization.

I add some Relative Path Parameters for “type” and “count”.
“Type” will drive how the chart image is delivered (HTML or a file download).
“Count” is how many rows from my Dataverse Table I will visualize in the chart.

List Rows

Next I’ll list rows from my “Cities” table. The row count will be the “count” from the Relative Path Parameter. I’m sorting the rows by “Population” descending so that the largest cities appear first.

Select Data for Array to Pass to Excel Script

I use a Select action to select and form the array as I need it for my Excel Online Script.

Run Script (with Excel Online)

Add a “Run script” action.

Point the “Run script” action to the Excel File you created and populate the Script’s inputs.

For the “inputData” input, which is the array input, I sort the output of the Select action by “state”.
sort(body('Select_Cities,_States_and_Pops'),'state')

Compose a File Content Object

The “Run script” will return a Base64 encoded string for the Chart Image.

I construct an object with this in a Compose like the following.

{ "$content": @{outputs('Run_script')?['body/result/chartImg']}, "$content-type": "image/png" }

Return the Image in an HTTP Response Action

Since my flow is just to demonstrate different ways of returning the image, I have a condition based on the “type” parameter in the trigger url.

File Download Response

If the trigger type is “file” it will return a file that the browser will download.

To give the downloaded file a name, use a “Content-Disposition” header.

Here I construct a file name with a formatted date time string to make sure it’s unique.
{ "Content-Disposition": "attachment; filename=@{outputs('Run_script')?['body/result/chartName']} @{convertFromUtc(utcNow(),'Central Standard Time','yyyy-MM-dd hh-mm tt')}.png", "Content-Type": "image/png" }

The body is just the file content object I constructed in the Compose above.