Removing Invalid Characters for SharePoint Online files.

At some point during your Power Automate or Logic Apps journey, you will need to create a file in SharePoint Online, OneDrive or another file storage system, with text or data you get from somewhere else.

Imagine you send an electronic form to new customers to fill out with their company details and you want to use the data they provide on that form to automatically create a document set in a library.

This is already fairly well documented online, but this is my take.

We’ll start with a text string in a Compose. This could have come from anywhere: A Microsoft Form, A SharePoint list, an CRM system or whatever. In this case I typed it.

As you can see, it contains a / character, which is not allowed in an SPO file name.

Here is a list of invalid characters in OneDrive and SP Online

It’s worth noting that single quotes (‘) should be avoided, some organisations don’t allow # or $ and if you use OneDrive sync to your PCs you should also avoid the semicolon (;).

Let’s start by trimming any leading and trailing spaces from the file name. We can do this at the same time as initialising a string variable for the Company Name:

Now we need to initialise an array variable containing the characters we don’t want.

createArray('.','"','*',':',';','<','>','?','/','\','|')

Notice the single quote isn’t in there. Logic Apps expression language can’t handle that and there’s no way to escape it (that I know of). The workaround is to append it to the array with Append to array variable:

Now we need to asses whether there are any invalid characters in the company name. We do this with Filter array:

We’re filtering arraySpecials for items (item()) that exist in strCompanyName. The result of this might be an empty array if there are none. In our case, we have a forward slash (/) character in there so the result will be one item. I’m not sure why Power Automate puts the Flow trigger button icon in there. It’s actually an expression: item()

If the array is empty, we’re done, we can move on in the workflow and use the value of strCompanyName as the file, folder or document set name in SharePoint. To assess that, count the number of items with length():

The If no/ If false branch should have nothing in it. If the length is greater than 0, in the Yes/True branch, we do some replacing.

There’s a bit going on in this screen shot. In the Yes branch there’s an Apply to each (or For each if you’re in Logic Apps) with the filtered array of special characters as the input.

Within the loop, we have a Compose action and place strCompanyName in it. The reason for this is you can’t set a variable using the same variable within the input expression: We need to temporarily store it first.

Next is the Set variable action. You can see above, the expression in the Value input of Set strCompanyName replaces the current item, item(), with an empty string( ” ). You can put any string you like in here to replace your invalid chars. You might want a space or a dash (-), your call. I’m just deleting them.

Now I just want to see the contents of strCompanyName. At this point the work is done. Use strCompanyName in your process to create a file, folder or document set.

You can see the whole flow run has been successful and removed the forward slash from the Company Name.

Also looking at the Apply to each, a single iteration of the loop has occurred and replaced that / character:

Now let’s try putting more invalid chars into the input string:

There are 5 iterations of the loop:

Lastly, use trim() as before to remove any leading or trailing spaces from the string, which will exist if the starting string had any special characters at the beginning or end that had a space next to them, e.g. < hello >

You might be thinking: But what about all those horrible double spaces. If you’re interested in removing those, read on..

Add a Do Until (Power Automate) or Until (Logic Apps) loop. The loop condition is strCompanyName does not contain double space. It’ll keep going round until there are no more double spaces in strCompanyName.

Within the loop, we compose the strCompanyName again so we can use it within the replace() expression in the following Set variable action.

It’s not that clear from the screen grab, but the replace() formula’s first parameter is the Compose, the second (search text) is a double space and the last (replace text) is a single space.

You might wonder why this is in a loop. If the Company Name string contains multiple continuous spaces, you will need a few iterations of this to get them all: