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:
As you can see after the '.' in the OneDrive ID it matches the end of the script ID after
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
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:
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:
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 (1)
This is cool, bummer PA doesn't support running scripts from SP yet.