Workaround for XLSM issues

Solution 1:

If you can't find a nicer way then you can do this using an Office Script. It may or may not work as you need it but you can work through that.

If you go into Excel Online (any workbook) under the user that will run the script in PowerAutomate, you should have an option in the ribbon called Automate.

Find it and create a new script called ... Get All Data from Worksheet

Paste this code in ...

function main(workbook: ExcelScript.Workbook, worksheetName: string)
{
  let worksheet = workbook.getWorksheet(worksheetName);
  let usedRange = worksheet.getUsedRange(true);

  return usedRange.getValues();
}

Now, you can call that from PowerAutomate using the Run script action under Excel Online (Business) ...

Action

Result

JSON Response

[
  [
    "Header 1",
    "Header 2",
    "Header 3",
    "Header 4",
    "Header 5",
    "Header 6",
    "Header 7"
  ],
  [
    "21",
    "22",
    "23",
    "24",
    "25",
    "26",
    "27"
  ],
  [
    "31",
    "32",
    "33",
    "34",
    "35",
    "36",
    "37"
  ],
  [
    "41",
    "42",
    "43",
    "44",
    "45",
    "46",
    "47"
  ],
  [
    "51",
    "52",
    "53",
    "54",
    "55",
    "56",
    "57"
  ],
  [
    "61",
    "62",
    "63",
    "64",
    "65",
    "66",
    "67"
  ],
  [
    "71",
    "72",
    "73",
    "74",
    "75",
    "76",
    "77"
  ],
  [
    "81",
    "82",
    "83",
    "84",
    "85",
    "86",
    "87"
  ],
  [
    "91",
    "92",
    "93",
    "94",
    "95",
    "96",
    "97"
  ],
  [
    "101",
    "102",
    "103",
    "104",
    "105",
    "106",
    "107"
  ],
  [
    "111",
    "112",
    "113",
    "114",
    "115",
    "116",
    "117"
  ]
]

From there, you can do what you need with the data.