cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ManasKumar01
Regular Visitor

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

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 folderGetting email attachment to share Point folder

Run office script
Pic_2.JPG 
 Error message 
Error_Pic_3.JPG 
35 REPLIES 35

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.

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. 

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.

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. 

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. 

 

jaanihsm_0-1709835217439.png

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
Regular Visitor

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.

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!

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

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

 

jaanihsm_0-1710460625417.png

 

Yep, this did the trick!  Replaced this:

Ukiman1014_0-1711745982817.png

With this:

Ukiman1014_1-1711746002463.png

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

netptl39
Frequent Visitor

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! 

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

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?

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!

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

PhilatVeriset
Frequent Visitor

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:

PhilatVeriset_1-1716899622166.png

 

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!

 

Helpful resources

Announcements

Community will be READ ONLY July 16th, 5p PDT -July 22nd

Dear Community Members,   We'd like to let you know of an upcoming change to the community platform: starting July 16th, the platform will transition to a READ ONLY mode until July 22nd.   During this period, members will not be able to Kudo, Comment, or Reply to any posts.   On July 22nd, please be on the lookout for a message sent to the email address registered on your community profile. This email is crucial as it will contain your unique code and link to register for the new platform encompassing all of the communities.   What to Expect in the New Community: A more unified experience where all products, including Power Apps, Power Automate, Copilot Studio, and Power Pages, will be accessible from one community.Community Blogs that you can syndicate and link to for automatic updates. We appreciate your understanding and cooperation during this transition. Stay tuned for the exciting new features and a seamless community experience ahead!

Summer of Solutions | Week 4 Results | Results posted on July 24th

We are excited to announce the Summer of Solutions Challenge!    This challenge is kicking off on Monday, June 17th and will run for (4) weeks.  The challenge is open to all Power Platform (Power Apps, Power Automate, Copilot Studio & Power Pages) community members. We invite you to participate in a quest to provide solutions to as many questions as you can. Answers can be provided in all the communities.    Entry Period: This Challenge will consist of four weekly Entry Periods as follows (each an “Entry Period”)   - 12:00 a.m. PT on June 17, 2024 – 11:59 p.m. PT on June 23, 2024 - 12:00 a.m. PT on June 24, 2024 – 11:59 p.m. PT on June 30, 2024 - 12:00 a.m. PT on July 1, 2024 – 11:59 p.m. PT on July 7, 2024 - 12:00 a.m. PT on July 8, 2024 – 11:59 p.m. PT on July 14, 2024   Entries will be eligible for the Entry Period in which they are received and will not carryover to subsequent weekly entry periods.  You must enter into each weekly Entry Period separately.   How to Enter: We invite you to participate in a quest to provide "Accepted Solutions" to as many questions as you can. Answers can be provided in all the communities. Users must provide a solution which can be an “Accepted Solution” in the Forums in all of the communities and there are no limits to the number of “Accepted Solutions” that a member can provide for entries in this challenge, but each entry must be substantially unique and different.    Winner Selection and Prizes: At the end of each week, we will list the top ten (10) Community users which will consist of: 5 Community Members & 5 Super Users and they will advance to the final drawing. We will post each week in the News & Announcements the top 10 Solution providers.  At the end of the challenge, we will add all of the top 10 weekly names and enter them into a random drawing.  Then we will randomly select ten (10) winners (5 Community Members & 5 Super Users) from among all eligible entrants received across all weekly Entry Periods to receive the prize listed below. If a winner declines, we will draw again at random for the next winner.  A user will only be able to win once overall. If they are drawn multiple times, another user will be drawn at random.  Individuals will be contacted before the announcement with the opportunity to claim or deny the prize.  Once all of the winners have been notified, we will post in the News & Announcements of each community with the list of winners.   Each winner will receive one (1) Pass to the Power Platform Conference in Las Vegas, Sep. 18-20, 2024 ($1800 value). NOTE: Prize is for conference attendance only and any other costs such as airfare, lodging, transportation, and food are the sole responsibility of the winner. Tickets are not transferable to any other party or to next year’s event.   ** PLEASE SEE THE ATTACHED RULES for this CHALLENGE**   Week 1 Results: Congratulations to the Week 1 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge.   Community MembersNumber SolutionsSuper UsersNumber Solutions Deenuji 9 @NathanAlvares24  17 @Anil_g  7 @ManishSolanki  13 @eetuRobo  5 @David_MA  10 @VishnuReddy1997  5 @SpongYe  9JhonatanOB19932 (tie) @Nived_Nambiar  8 @maltie  2 (tie)   @PA-Noob  2 (tie)   @LukeMcG  2 (tie)   @tgut03  2 (tie)       Week 2 Results: Congratulations to the Week 2 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 2: Community MembersSolutionsSuper UsersSolutionsPower Automate  @Deenuji  12@ManishSolanki 19 @Anil_g  10 @NathanAlvares24  17 @VishnuReddy1997  6 @Expiscornovus  10 @Tjan  5 @Nived_Nambiar  10 @eetuRobo  3 @SudeepGhatakNZ 8     Week 3 Results: Congratulations to the Week 3 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 3:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji32ManishSolanki55VishnuReddy199724NathanAlvares2444Anil_g22SudeepGhatakNZ40eetuRobo18Nived_Nambiar28Tjan8David_MA22   Week 4 Results: Congratulations to the Week 4 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 4:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji11FLMike31Sayan11ManishSolanki16VishnuReddy199710creativeopinion14Akshansh-Sharma3SudeepGhatakNZ7claudiovc2CFernandes5 misc2Nived_Nambiar5 Usernametwice232rzaneti5 eetuRobo2   Anil_g2   SharonS2  

Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform

On July 16, 2024, we published the 2024 release wave 2 plans for Microsoft Dynamics 365 and Microsoft Power Platform. These plans are a compilation of the new capabilities planned to be released between October 2024 to March 2025. This release introduces a wealth of new features designed to enhance customer understanding and improve overall user experience, showcasing our dedication to driving digital transformation for our customers and partners.    The upcoming wave is centered around utilizing advanced AI and Microsoft Copilot technologies to enhance user productivity and streamline operations across diverse business applications. These enhancements include intelligent automation, AI-powered insights, and immersive user experiences that are designed to break down barriers between data, insights, and individuals. Watch a summary of the release highlights.    Discover the latest features that empower organizations to operate more efficiently and adaptively. From AI-driven sales insights and customer service enhancements to predictive analytics in supply chain management and autonomous financial processes, the new capabilities enable businesses to proactively address challenges and capitalize on opportunities.    

Updates to Transitions in the Power Platform Communities

We're embarking on a journey to enhance your experience by transitioning to a new community platform. Our team has been diligently working to create a fresh community site, leveraging the very Dynamics 365 and Power Platform tools our community advocates for.  We started this journey with transitioning Copilot Studio forums and blogs in June. The move marks the beginning of a new chapter, and we're eager for you to be a part of it. The rest of the Power Platform product sites will be moving over this summer.   Stay tuned for more updates as we get closer to the launch. We can't wait to welcome you to our new community space, designed with you in mind. Let's connect, learn, and grow together.   Here's to new beginnings and endless possibilities!   If you have any questions, observations or concerns throughout this process please go to https://aka.ms/PPCommSupport.   To stay up to date on the latest details of this migration and other important Community updates subscribe to our News and Announcements forums: Copilot Studio, Power Apps, Power Automate, Power Pages

Users online (2,635)