Run Excel Office Script on Dynamic Path using Power Automate: Resolve Unexpected Response Error

Are you tired of dealing with "Unexpected response from the service" error when trying to run an Excel office script on a dynamic path using Power Automate? Look no further! In this video, I'll show you how to use the SharePoint REST API to obtain the drive ID and overcome this error. You'll see how to use the "apply to each" function to loop through the results of the SharePoint API and retrieve the ID dynamically by writing a basic expression. Plus, I'll take you step-by-step through building the Power Automate flow.
And that's not all! This video also includes an overview of the requirements, a sample office script, and a demonstration of how to fix the "Error: Unable to run script" issue. Don't miss out on this opportunity to level up your office script skills! And for more office script videos, check out the following playlist: • Office Scripts .
Want to learn more about the SharePoint Rest API? Check out this link: learn.microsoft.com/en-us/sha....
Don't forget to hit the like and subscribe button!
00:00 Intro
00:38 An overview of the requirements
01:00 A Sample Office Script
01:45 Buiding the Power Automate Flow
03:02 Dynamic Library and Path on Run Script
03:57 Error: Unable to run script
05:43 SharePoint Rest API
08:00 Apply to Each Document Library
10:43 Outtro Please buy me a coffee www.buymeacoffee.com/DamoBird365 ☕

[@wiesjesteevensz-abbenhuis6154]

Thank you very much! Spent a whole morning looking into this and your video helped to make an Office Script run on an Excel file which can reside anywhere in the current tenant. This saved me a lot of time.

[@acadiebeerboy739]

this is awesome sleuth work! I've been actively learning Office Script for a while - coming from the VBA world - in order to start implementing Power Automate, so these troubleshooting tips are a goldmine of information!! thanks for taking time to document them! and please keep those vids coming documenting your Office Scripts discoveries!

[@moyura2]

I utilized this to create attractive PDF forms from an Excel template. Thanks for sharing.

[@kehindeakiode2865]

Brilliant! I'm still a noob at Power Automate but I really appreciate your in depth explanations. Subscribed!

[@jack-plutomicro]

Just a heads up for anyone who might be in the same boat as me. There is a way to save your office scripts to custom locations, however, at this time, scripts saved outside of the default location won't be listed in the available scripts. So as @DamoBird365 mentioned in the comments, you have to make sure you've shared the script if other people will be triggering the flow.

[@DamoBird365]

Thanks for sharing Jack. Also worth keeping an eye on learn.microsoft.com/en-us/office/dev/scripts/develop/power-automate-integration in case SharePoint is eventually supported 👍

[@lubeshbehera1993]

Thanks Damien for sharing this!!👍

[@kevcurry7773]

Fixed my issue, thanks a lot mate

[@ezequiascampos4595]

Thanks a lot for the help. I'm Brazilian and I really like your channel.

[@DamoBird365]

Cheers Ezequias 👍

[@devothunder5210]

this is perfect, thank you. i have a need to put a flow that utilizes the runs script action into a solution that is moved between environments and i needed it to be configured using an environment variable. i knew there was a way to do it :)

[@DamoBird365]

That’s pretty cool. I guess the script itself will still reside on the same OneDrive location but the file path that the script is run on is dynamic between environments. Interesting and thanks for sharing. 👍

[@dakshaagarwal4018]

Works perfectly - thanks Damien! Appreciate you walking through your thought process for each step. Just one question, how did you know to use _api/v2.0/drives as the uri instead of _api/v2.0/drive?

[@DamoBird365]

To be honest, I can’t find official docs on V2.0 instead of v2.1 but there was a blog post global-sharepoint.com/sharepoint-online/get-document-library-id-using-sharepoint-api. Maybe we can get a q in for the Rest API team?

[@johnfromireland7551]

Trial and error is a known technique used in computer science. Keep trying trying until it works. 🙂

[@JorgeMSBLima]

Hey there. I managed to refer to dynamic excel file to run a script. But due to my specific situation, I'm looking to run a dynamic script. I already can get the full SharePoint path to where the script is stored and get the ID as well using get metadata. But the script still fails when using a dynamic script.

[@stephanebouillon2538]

Thanks Damien, Merry Christmas to you too ! My scenario is somewhat different in that it is not so much the excel/script that should be dynamic, but rather the user executing. I have a typical dev/test and production environment and when after testing the flow in dev, I configure a user with access to the excel for the production flow it doesn't find the script, whereas if I configure the test user on the connection reference in production it works. Any ideas if this is a supported scenario ? Also, what do I need to do to see the input parameters for the run script in the run history ? I don't seem to be able to do that as you do in your demo.

[@DamoBird365]

Excel scripts are not yet supported in a solution and so I guess you’ll need to share it? support.microsoft.com/en-gb/office/sharing-office-scripts-in-excel-226eddbc-3a44-4540-acfe-fccda3d1122b input parameters appear when a script is visible, again I wonder if it needs shared? Let me know how you get on and thanks for watching.

[@divyak8545]

HI , Thanks for the video. I am getting this error if I us dynamic mthoug, but If I use the dropdown and folder picker its running the script, CAn u help me on this?

[@scarlettirons6790]

Thanks for the video! I'm having a similar issue - but just want to use a dynamic file name in a fixed folder. I've tried using the file ID in the run script action but this is still coming up with the unable to run script error. Checking the raw inputs of the failed flow, they are seemingly identical to if I select that particular file. Would this Rest API solution fix this issue?

[@DamoBird365]

It would be worth a try. It’s been a while to be honest. Let me know how you get on.

[@DieSuperHaas]

Question, is there any way you can change the "Script" to a dynamic value as well?

[@DamoBird365]

A dynamic script? Interesting. What’s the use case?

[@DieSuperHaas]

@@DamoBird365 No need anymore, typing out the question assisted me. I don't need to specify the script dynamically when I can use your example of dynamic paths. Apologies, it helps to type stuff out sometimes

[@DamoBird365]

I am pleased you have a solution, thanks for coming back to me and keep me in mind for future challenges or video suggestions 👍

[@vvaassaanntthh]

Hi thanks for the great video. Great content. I do need help with something related to office script, that hope you can help with I have a requiremnt whereby I need power automate web, to automatically run macros every day from an excel file (That resides in SharePoint). At the moment power automate web only can run office scripts and not macros. Can you help me with a way around this? Is there a way to convert a macro into an office script (which can then be called by power automate using the run scripts action)

[@DamoBird365]

There’s not a conversion tool. You can run macros via power automate desktop by opening the desktop excel client but I guess that’s not an option. If you know the process, you’ll need to write or record a new office script.

[@ManojKumar-zn2gf]

Out of curiosity, is it possible to have a single office script that can run on two different workbooks with has same information ie. simply it has to create a unique ID in both scripts. Instead of having two scripts in two workbook, I should have only one scripts which can create id on both the worksheet.

[@DamoBird365]

Absolutely, scripts are not attached to a workbook but to a OneDrive or SharePoint site. You then run the script on any file of choice, either through Excel or by calling it from Power Automate 👍

[@ntp.1225]

Thanks for sharing, I have a problem when another user run the script via PowerAutomate, my flow returns error about could not find the script. T__T

[@DamoBird365]

You’ll need to make sure it is shared.

[@ntp.1225]

@@DamoBird365 it works after sharing the script, thank you :)

[@geralddahl9159]

Around the eight to ten minute mark of this video you intentionally insert a space into an expression, and then, in the video linked below you use that same technique in order to manage a context based requirement. Both cases are helpful, thx! kzbin.info/www/bejne/hnWcaWOpgLCrZqc