Problem with populating a created excel table with data from a transposed HTML table using power automate

Question

I have created a transposed HTML table in power automate that looks like the following table:

My problem is trying to create an excel table in power automate to populate the above data given that the number of the date columns, "2024-07", "2024-08", "2024-09", etc change over time. So in December-2024 a new column, "2024-12" would be added and that require changing the table in power automate flow. My attempt so far in creating this table in power automate is as follows:

I was told it can be done dynamically by office scripts or Graph API calls but I have no idea how to do it in power automate. I appreciate your help

This is the sample data.

[
  {
    "Portfolio": "Enterprise App and Emerging Tech",
    "2024-07": "21",
    "2024-08": "1",
    "2024-09": "2",
    "2024-10": "1",
    "2024-11": "47"
  },
  {
    "Portfolio": "Mobility and Customer Experience",
    "2024-09": "2",
    "2024-10": "1",
    "2024-11": "24"
  },
  {
    "Portfolio": "Data and Analytics",
    "2024-10": "5",
    "2024-11": "27"
  },
  {
    "Portfolio": "Cloud - Infra - CS and Obser",
    "2024-11": "13"
  }
]

Are you expecting to have the table rendered exactly like your image in Excel with that formatting? — Skin
– Skin, Commented Dec 7, 2024 at 23:19
Yes. I want the same HTML table to be populated in excel. But number of columns will change. — Ashraf
– Ashraf, Commented Dec 8, 2024 at 4:06
The formatting is not that important to me. What is important is populating the excel table with the transposed HTML table data dynamically regardless of the number of date columns. — Ashraf
– Ashraf, Commented Dec 8, 2024 at 10:58

Skin · Accepted Answer · 2024-12-09 00:06:30Z

1

There are a few ways to skin this cat but I've written an OfficeScript that will do it for you but you may need to amend the script if there are any other requirements.

Caveat: This does not accept your HTML table, it needs JSON data to write the table.

function main(
    workbook: ExcelScript.Workbook, 
    worksheetName: string, 
    tableName: string, 
    topLeftCell: string, 
    tableData: object[]
) {
    // Create or obtain a reference to the worksheet supplied in the parameters.
    let worksheet = workbook.getWorksheet(worksheetName) ?? workbook.addWorksheet(worksheetName);

    // Now create the table if it doesn't already exist.
    const headers = Object.keys(tableData[0]);

    // Create a reference to the top left cell.
    const topLeftCellReference = worksheet.getRange(topLeftCell);

    // Write headers to the cell as specified by the given parameter.
    topLeftCellReference.getResizedRange(0, headers.length - 1).setValues([headers]);

    // Now write all of the rows.
    const rows = tableData.map(row => headers.map(header => row[header]));
    const dataRange = topLeftCellReference.getOffsetRange(1, 0).getResizedRange(rows.length - 1, headers.length - 1);
    dataRange.setValues(rows);

    // Create the table
    let tableRangeAddress = topLeftCell + ":" + dataRange.getAddress().split(":")[1];
    let table = worksheet.addTable(tableRangeAddress, true);
    table.setName(tableName);
}

It literally should be as simple as creating that script and referencing it through PowerAutomate with your data using the operation as shown below.

edited Dec 9, 2024 at 0:06

answered Dec 8, 2024 at 23:50

Skin

11.7k3 gold badges16 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ashraf Dec 9, 2024 at 3:14

I tested your script with sample data, and it works. However, how can I reference the generated JSON data from Power Automate (specifically the data from the HTML table) in this script?

Skin Dec 9, 2024 at 3:27

JSON and HTML are not the same thing so your question ...

how can I reference the generated JSON data from Power Automate (specifically the data from the HTML table) in this script?

... is an oxy moron. You need to use JSON, to use HTML is not what typescript wants when it comes to processing that data. It's possible but you'd need to invoke some regex and I just wouldn't recommend it. You're one step beyond where you need to be with the HTML so my question is, how did you create the HTML? You must've had some JSON data to begin with to get to that point, no?

Ashraf Dec 9, 2024 at 7:31

This is the generated json data:

[   {     "Portfolio": "Enterprise App and Emerging Tech",     "2024-07": "21",     "2024-08": "1",     "2024-09": "2",     "2024-10": "1",     "2024-11": "47"   },   {     "Portfolio": "Mobility and Customer Experience",     "2024-09": "2",     "2024-10": "1",     "2024-11": "24"   },   {     "Portfolio": "Data and Analytics",     "2024-10": "5",     "2024-11": "27"   },   {     "Portfolio": "Cloud - Infra - CS and Obser",     "2024-11": "13"   } ]

. Then I used "Create HTML Table" action in power automate to generate the HTML table.

Ashraf Dec 9, 2024 at 7:40

But as I said before, the date columns change depending on the source data. So everytime I run the flow, the generated json data might be different.

Skin Dec 9, 2024 at 9:04

Explaining yourself is important here, I understood what you said re: dynamic dates but you never explained that each item isn't balanced and that you may have missing properties across each item. I see that now. My answer will need to be amended and I don't have time to do that at present.

Ashraf · Accepted Answer · 2024-12-09 12:32:05Z

0

I found the solution. I searched youtube and found it without needing office scripts. Here is the video: https://www.youtube.com/watch?v=Kupz71dWYyY

answered Dec 9, 2024 at 12:32

Ashraf

596 bronze badges

Collectives™ on Stack Overflow

Problem with populating a created excel table with data from a transposed HTML table using power automate

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related