I've created a cloud power automate flow to listen for a new Excel file in a OneDrive folder, wait 10 seconds, run an Excel Office Script, wait 10 seconds, then upload the file to an FTP. Please see the flow here:
When a new file is uploaded to OneDrive, the flow runs without error:
Unfortunately, the Office Script did NOT run. I can, however, open the Excel file from OneDrive in a browser, choose the Automate tab, and then run the Office Script and it DOES run from within Excel Online. It just doesn't run when I try to run the script from Power Automate. What am I doing wrong? Thanks!
Thanks for all the feedback. The issue was the line in the script that @lbendlin noted. The script worked properly when I was directly interacting with the spreadsheet in the browser, but because it was looking for the ActiveWorksheet, there was no sheet to select (the file wasn't open). I explicitly referenced the name of the sheet I was trying to copy from, and now everything is resolved. Thanks for your help!!
let selectedSheet = workbook.getActiveWorksheet();
I don't think that will do anything useful in automated mode. As a result you may be copying stuff from the sheet to itself. Better use absolute sheet addressing by name.
Yes @grantjenkins as I mentioned I have already tried this with File Identifier and the result is the same. I've included the office script below. Again, the office script runs properly when I run it form within Excel Online. Thank you so much!
@mbial As @lbendlin mentioned, you should be using File identifier in your Run script which will ensure it's the exact file that was just created. This should work as expected. Below shows what you should be using for File.
Are you able to show your actual Office Script code to see if there's something in there that's causing the issue.
Same result using 'File Identifier'. Power automate runs successfully, but script doesn't change Excel file. The Office Script simply copies a sheet and pastes it to another sheet in the same excel file. I also tried using the following template with the same result:
Run an Office Script on Excel workbooks added to a OneDrive folder
"File" has to be a file identifier, not a file name. As @grantjenkins mentions you need to retrieve the identifier of the newly uploaded file and then use that for running the script.
Thanks for your feedback. Unfortunately, this still isn't working, so I've simplified the entire flow to 3 basic steps:
The flow completes successfully, but the script didn't actually do anything (even though the script step shows as successfully completed). We're no longer hung up on the next step of file creation, the focus is purely on successfully running the script on the new file. Any other ideas? Thanks again!
I'm assuming the file you created should be updated within your Office Script. If so, then it might be working, but the content you're using in your Create file is from the file content when it was first created, not from after the updates.
After running your script, add a Get file content to get the latest (updated) content of the file, and use that content in your Create file action.
----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.
Have you tried to use this "official" template ?
If you don't mind asking - what is the script supposed to do? Can you verify somehow that it ran?
I tried a couple of scripts in my OneDrive and these work as expected.
Thanks for your reply. I believe Office Scripts for Excel are stored in the user's OneDrive account. When I open these uploaded files in OneDrive in the browser, I'm able to select the automate tab, choose the script I want to run, and everything is perfect. Not sure how that would differ from me running it the way I've described above. Thanks again!