Getting Error "The file format is not recognized. Please check that the selected workbook is valid." while running a office script in power automate

[ManasKumar01]

Hi All,
I am trying to save the email attached excel file to share point library and then running a office script to extract the data from newly created excel file. But  while running the "Get data from a file" office script getting below error
"The file format is not recognized. Please check that the selected workbook is valid."
I have modified and used "Get attachment V2" to create excel file  and manually ran the "Get data from a file" action in which it runs office script to get data from opened the newly generated excel file, but getting error.

Office Script used to get data from excel file

function main(workbook: ExcelScript.Workbook,
    wsName: string, startCell: string,
    headerRowsToRemove: number,
    footerRowsToRemove: number) {


    //Declare and assign the worksheet
    let ws = workbook.getWorksheet(wsName);


    //Delare and assign the data range
    let wsRng = ws.getRange(startCell).getSurroundingRegion()


    //Get the values of the data range in array
    let wsRngArr = wsRng.getValues();


    //Count the rows in array
    let wsRngArrRowCount = wsRngArr.length-2;


    //Calcualte the number of rows to return
    let wsRngArrRowLength = wsRngArrRowCount -
        headerRowsToRemove - footerRowsToRemove + 1;


    //Remove the top and bottom rows
    wsRngArr = wsRngArr.slice(headerRowsToRemove, wsRngArrRowLength)
  console.log("Sucessfully Processed.")
    //Return the range
    return { wsRngArr };
  }

Flow screen shot for reference.

Getting email attachment to share Point folder

Run office script
 

 Error message 

 

[jaanihsm]

I am working with an XLSX file with legacy connections that I cannot remove. I can't use it when creating a table so my script copies the cells in a new worksheet (same .xlsx) then clears all format. Script then deletes any other sheets.

The script works on it's own running on the excel file but I want to incorporate it in a Power Automate flow and not do it manually.

[mickey97]

It is an xlsx. No, with a blank workbook I am getting the same behavior. The first time I run the flow it works, but then I revert back (re-add the workbook, etc.) and run it again from scratch and it fails each time going forward. 

[GeoffRen]

So it sounds like for both of you, Run Script works with a plain xlsx but your production xlsx files that contain other dependencies do not work. Do you know what this offending dependency is so I can try and repro it on my side? Then we can try and support it in the future.

[mickey97]

In my case, a plain XLSX does not work. In both my plain and production XLSX it works the very first time I try to do anything and then fails every time after that. 

[jaanihsm]

In my case the production xlsx is a copy of the plain xlsx from another library. They are the same. So I'm really not sure why it's not working when the script is run in power automate. 

I also tried creating a new file from production xlsx but I get the same error.

Is it maybe that SharePoint needs to be refreshed after the copy???

[kbomysoad]

After "Create File" add a step called "Find files in folder" using the name of the file that was created in the previous step, then Run Script on the file found in the folder.

[jaanihsm]

Hi! 'Find files in folder' is only for OneDrive files, looks like...

My files are saved in SharePoint. I don't see a function similar to it that will work in SP?

Thanks!

[sylwir061]

Hi!

Solution proposal! 🙂

I just got the same error on a flow that runs for ages without any problems or changes, and two days ago it stopped with the error as in the thread title.

The flow uses:

1. SharePoint create file (blank xlsx)

2. RunScript that fills data to the newly created file

I tried replacing "create file" with "copy file" - did not help

What did help was this: Solved: Office Script Returning File Format Error - Power Platform Community (microsoft.com)

To get the Id I used "Get file metadata using path" (sharepoint) and provided the path manually there. Till now I had that path provided in "Run script", and it worked perfectly well. Now it seems we need an intermediary agent ("Get file metadata using path" for SharePoint/"Find files in folder" for OneDrive), since path will not work anymore when applied to "Run Script" directly.

I hope Microsoft will fix this, as it has to be due to some recent changes. Otherwise, if I need to replace file path with "Id" in ALL my flows that use "run script" on newly created files... RIP x_x

[jaanihsm]

I think so far this heeeeelped! Thank you @sylwir061!

[Ukiman1014]

Yep, this did the trick!  Replaced this:

With this:

Previous step was SharePoint action to create a copy of the Excel sheet into a different doc library.

[netptl39]

I have a flow that works and one that fails and I can't figure out why!

Same error: The file format is not recognized. Please check that the selected workbook is valid.

+30sec does nothing. 

Automation Script works when I run it from the opened excel.

Any sugestions on why would be highly welcomed! 

[DeevakerG]

I have the same issue. My script works fine in excel but not via power automate. 
clientRequestId: c871bd5d-7eeb-4afa-8372-2ab6e85bb7f1

[markbowler7]

I am suddenly getting this issue today. Scripts were working fine yesterday - running on .xlsx files in SharePoint. I've dropped in new versions of the files today via a OneDrive shortcut to the folder from my Downloads folder using File Explorer. Files open in Excel desktop and online with no issues - why is this so flaky?

[markbowler7]

Ah, found out what this issue was - dragging and dropping the files (even though the name was the same) into the linked folder using File Explorer caused them to be treated as new items in SharePoint (previous history all wiped out), hence the script couldn't find them (as I think the scripts use the SharePoint ID rather than the file/path name). Pity the error message was so confusing - if it had said file not found it might have been more obvious!

[GeoffRen]

This is good feedback, I'll give the team feedback on this error message.

[PhilatVeriset]

We ran into the same error after using ennvironment variables for a dynamic input when the automation is transferred to another ennvironment aka from dev to test, from test to prod.

Our files are xlsm format and we still made it work.

We found out that the Workbook Library must be entered in a speical code format, which is not very intuitive:

You can find this b!uhPM...-value when loooking it up from a past execution which has been successful or sometimes it is visible when the connection to the file is broken/lost. Otherwise I am note sure how to generate or find it.

Hope this helps!