Getting the SharePoint Site URL in a Microsoft Flow

Working with clients on deployments in the Microsoft world isn’t always easy. Some clients want to develop in one environment (or tenant) and then move that development to a new tenant. Invariably, there are steps that get missed. When they do, the production deployments break, which impacts users – and it’s frustrating.

One of the problems with Microsoft Flows is the need to update the source URL for the SharePoint operations. If you trigger based on some list event, and then want to update another list in the same site, you’ve got to enter that URL again.

The first solution to this is to define a variable at the top of the flow and then use that variable for the Site URL for every SharePoint operation. That helps, because it centralizes the setting of the site URL to a single place. However, it’s still easy to miss when you’re doing a deployment.

A way to enhance this is to use an expression to set the variable so that it’s always the right URL, but that’s not easy.

In your trigger inputs – trigger()[‘inputs’] – there’s a variable called path. The problem is that this path looks something like /datasets/https%253A%252F%252Fthorprojects.sharepoint.com%252Fsites%252FTEST/tables/f0de155c-08bc-4a46-9772-74dde8a1ece0/onupdateditems

To get the SharePoint path out of that, you need to do a few things. Initially, I tried stripping the last three levels in the URL iteratively, but that proved to be unwieldy. What I ultimately realized is that the lengths of the start and end of the URL are fixed, thus I can use hardcoded values for the lengths to trim rather than relying on the slash delimiters. For this URL, I can use the following statement to get the SharePoint portion.

substring(trigger()[‘inputs’][‘path’], 10, sub(length(trigger()[‘inputs’][‘path’]), 69))

Of course, I still have some replacements to do to get my encoded characters back, so the statement becomes:

replace(replace(substring(trigger()[‘inputs’][‘path’], 10, sub(length(trigger()[‘inputs’][‘path’]), 69)), ‘%252F’, ‘/’), ‘%253A’, ‘:’)

That’s relatively clean. I just initialize my variable to this, and we’re good – if the trigger is an On Changed. It turns out the URL for On Created is slightly different. (It ends with onnewitems.) To cover both, I added an if statement to test whether it’s a new or changed event and ended up with:

if (greater(indexOf(trigger()[‘inputs’][‘path’], ‘onupdateditems’),0),
replace(replace(substring(trigger()[‘inputs’][‘path’], 10, sub(length(trigger()[‘inputs’][‘path’]), 69)), ‘%252F’, ‘/’), ‘%253A’, ‘:’),
replace(replace(substring(trigger()[‘inputs’][‘path’], 10, sub(length(trigger()[‘inputs’][‘path’]), 65)), ‘%252F’, ‘/’), ‘%253A’, ‘:’))

Now, I’ve got one formula that I can give developers to get the SharePoint Site URL for the current site, and I don’t have to worry whether the URL gets updated during a deployment or not.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.