Office Scripts are incredibly useful, often stretching beyond use cases of just Excel (if you want to know more check out 5 Scripts every power automate developer should know). But there is one big problem with them, and its all Microsofts fault (isn't it always).
The 2 problems are
- Office Scripts can only be ran from your OneDrive (In Excel you can now run from SharePoint sites, ideal for Shared Scripts, but this functionality is missing from Power Automate)
- The connector uses the Office Scripts ID, which is not a standard OneDrive ID (and not just the path)
So this means everything is fine when you run the flow, but if you want to deploy a service account it all falls apart. First you need to copy the script file to the service accounts OneDrive, second when you deploy you have to create a copy (as there is a error due to the connector being unable to find the script). The script maybe there, but it has a different ID.
Fortunately there is a work around.
Dynamically Select Script
There is an option to dynamically select script in the connector, but as I said, it requires the script ID, which is unique to the script.
When comparing the ID from OneDrive and the script ID I noticed a pattern:
OneDrive
b!ZRVA09xUnUOCwKEiI7XMdx-ebslnsblIsT-999999-QxOv11S4vtZ6rj3Pwo.01GFTE5MRTJOVG5R2X559999DLHCQP3CWGSSX
Script ID
ms-officescript%3A%2F%2Fonedrive_business_itemlink%2F01GFTE5MRTJOVG5R2X5599999DLHCQP3CWGSSXI
As you can see after the '.' in the OneDrive ID it matches the end of the script ID after officescript%3A%2F%2Fonedrive_business_itemlink%2F
b!ZRVA09xUnUOCwKEiI7XMdx-ebslnsblIsT-999999-QxOv11S4vtZ6rj3Pwo.01GFTE5MRTJOVG5R2X559999DLHCQP3CWGSSX
ms-officescript%3A%2F%2Fonedrive_business_itemlink%2F
+
01GFTE5MRTJOVG5R2X559999DLHCQP3CWGSSX
So we can now use the OneDrive 'Get file metadata using path' action to get the ID, then use below expression to convert it to our script ID
concat('ms-officescript%3A%2F%2Fonedrive_business_itemlink%2F',split(outputs('Get_file_metadata_using_path')?['body/Id'],'.')[1])
Parameters
Now we can dynamically select the script, the next problem we have is passing script parameters.
Peaking the code we can see the connector pulls the schema and generates keys based on inputs: ScriptParameters/key
But when we have dynamic script selection the connector cant get the schema, so it just has a ScriptParameters key. To fix this we just pass the inputs in as a json object:
So our connector changes from:
to:
And thats it, the script will now work with any account, as long as it has the script saved to its OneDrive/Documents/Office Script folder.
There is an additional workaround you could add to make it even easier, and that would be to have a central SharePoint library holding all of the scripts. Then in the flow add an exception handler to download the file from SharePoint if it isn't found in the accounts OneDrive.
Hopefully Microsoft will create a proper fix, as I said its already added Scripts from SharePoint to Excel, so I expect we will soon be able to select scripts from SharePoint, which will be so much easier.
Top comments (14)
This is cool, bummer PA doesn't support running scripts from SP yet.
Looks like it finally does 😁😁
Oh wow, this is great news
Hi. I have yet another case. I got a tiny script temporary pausing the sheet protection in order for me to perform changes, before resuming it. There are no parameters, but puting "" into the ScriptParameter section seems to run into issues. I would be thankful to hear any suggestion on why this is and how to make it work.
Hi hjl2004, have you tried adding a parameter but not using it?
There are no parameters. The script is switching the sheet protection on/off. There is no interface part.
But you can still pass a parameter, just dont use it
It doesn't work for me. I get the error "Script identifier format was invalid.
clientRequestId: ". When looking into input under action 'run script' I don't see "ScriptParameters": {"data": "@body('Select')"} as you described but "ScriptParameters": {"data": "ms-officescript%3A%2F%2Fonedrive_business_itemlink%2Fosts"} instead. What's the issue here? Can you help?
Hi hjl2004, If you are not seeing @body('Select') then it appears you have the wrong input, the above is the route url of the scriptId. That should go in the script input (with the uique ID appended).
Recently I have seen that sometimes the schema is now identified (making it a lot easier as we now see the parameter inputs instead of just the parent ScriptParameter input), but that might add some confusion if you are looking for ScriptParameter. If Ive missed the problem let me know and we can pick up directly (others have contacted me through LinkedIn )
Yes, I actually did put the URL+uniqueID in the 'script input' as described by you. The problem seems to be, that my output of 'body/Id' is "%252fShared%2bDocuments%252fCode%252fScripts%252fTBL2JSON_Input_from_JR.osts". As there is no unique ID your split does take the file extension "osts" instead.
You are using OneDrive, but is there a way to use scripts located at a sharepoint? Is there anything like 'ms-officescript%3A%2F%2Fsharepoint_itemlink%2F'?
I see, the ScriptParameter should be a object (above its a select action). Example
ScriptParameter:
{
"parameterName": "ParameterValue"
}
would pass the word "ParameterValue" as the parameterName
Script:
Function main(workbook: ExcelScript.Workbook, parameterName :string) {
I am using 'Run script from sharepoint library' and facing issue in making this pattern to choose the right script dynamically, any help around this?
Drive Id - b!s9cbCzQ2902ADQHt5GLPH8s9xUuMNF9OvnC7S0asF5tu6c5CSpfoRLt47xoNTCq1 (sharepoint library)
Script Id - 01KVPU3K75YAKLYQLVUNG2KTZHYVJITQ43
The easiest way I find to do it is to select everything you want in the action, then peak the code. This will show all of the inputs you need. Copy them into an environment variable (I recommend just one object environment variable), then replace the values with the environment variable you are using
Hi, David.
This works great in Power Automate online, but for stupid reason doesn’t seem to work in Power Automate desktop. Any ideas on how to potentially get it to work in the desktop version ?