Flow: Copy Excel file if original file was updated & remove rows/columns

[Chris90]

I am currently upgrading my existing flow and struggeling with Office Scripts.

My flow does already:

Trigger: If a selected Sharepoint file (Excel file in my case) has changed

Flow: Copy the file to another sharepoint (Goal: Use it with other collaborators and change it) and replace the existing one

Now I want to improve it, as the file is 85mb big I want to remove all columns and rows which aren't necessary for my usecase. To do so I wrote an Office Script and extended the flow to a 3rd step:

3. Excel Online - Run Sharepoint Script

Selecting the newly copied file and the script.osts file. But I get an error message:

The dynamic operation request to API 'excelonlinebusiness' operation 'GetSingleScriptV2' failed with status code 'BadGateway'. Error response: { "error": { "code": 502, "source": "europe-001.azure-apim.net", "clientRequestId": "", "message": "BadGateway", "innerError": { "status": 502...

I saw that 502 errors might be caused by read-only files, but this isn't the case.

But as I'm new to Office Scripts and how to run them with Power Automate I don't know if this is the right way how to do it and in the docs I found on several places that Office Scripts can't be executed if they are stored on Sharepoint. Do you have some tipps how to progress?

[MichelleRanMSFT]

Can you double-check the Excel file you've selected for the run script action? This error seems to indicate that you no longer have access to the selected file.

[Chris90]

Hi @MichelleRanMSFT I checked and I have access. The flow without the Office scripts - just copying the file to a different folder - is still working. 

[MichelleRanMSFT]

From our logs, it looks like we weren't able to find the workbook file that you were running the script against. Have you checked that your run script action is configured to use the correct workbook? Does this error still occur if you select a different workbook to run against, or try using other actions such as get worksheets on that workbook?

[Chris90]

Hi @MichelleRanMSFT I just checked and everything is there. The source file with access, power automate can copy the file and I added 1min delay to make sure that the copied file is registered in Sharepoint. If I run the Office script manually it works. I even reduced the script to just delete the columns again, but still not working.

[MichelleRanMSFT]

Could you share a screenshot of your entire flow (sensitive data removed as needed)? And does the "run script from SharePoint library" action work if you select a different workbook or different script? I'm trying to see whether this could be an error with the flow configuration or if something is going wrong on the Office Scripts side.

[Chris90]

Indeed, if I select another workbook (just a copy of the same file) in the "run script from SharePoint library" action it works. Seems like copying the file and run a script on the copy is causing issues. I already added a 1min delay to give Sharepoint more time to synchronize the update. Should I increase the delay or is this something you can handle in the setup?

[Chris90]

@MichelleRanMSFT could you find the root cause or is there an issue in my configuration?

[MichelleRanMSFT]

Could you share a screenshot of your entire flow? It's difficult to troubleshoot without this information, as on our side, all we know is that we weren't able to find the workbook you were trying to run the script on.

[Chris90]

Does that work, even with the German language configuration in my browser?

[MichelleRanMSFT · Accepted solution]

It looks like you want to run the script on the copied workbook. Have you manually typed in the file path? If so, that won't work: you need to use the file id returned from the copy file action. See Solved: Re: Run Excel script error when passing dynamic fi... - Power Platform Community (microsoft.... and Excel Online (Business) - Connectors | Microsoft Learn for more information.