How To Handle Apostrophe In Email Address In Power Automate

In Power Automate, most frequently we use the HTTP methods to fetch results from the lists/libraries. And sometimes we tend to get the information of the user’s permission from the group and to check if the user has the required permission to access the item. In such situations what if the user has special characters especially like apostrophe in the email addresses (Neil.O’[email protected]); the web method will throw an exception while requesting.

Okay, let's see the work in action. Now I have created test list in SharePoint and created two additional columns to hold email address (column name: Email_list) and single line of text (column name: copy_email_address).

Create a Power Automate flow, triggered when an item is created in the list.

Next, added a step to “Get file properties” to get the item id.

New step added to “Send HTTP request” to GET the SiteUsers through API using the following endpoint

_api/web/SiteUsers/getByEmail(‘Email’)

here Email is output of the Get_file_properties/Email of Email_list column value.

The response body is set in a variable which was initialized during the beginning of the flow. The variable name is varTextToCopy.

After setting the variable, again add the step “Send an HTTP request to SharePoint” to POST the value in the second column (copy_email_address) using the getbytitle API

_api/web/lists/getbytitle(‘<listname>’)/items(‘<ID>’)

Now the flow is set. Save the flow and create a new Item in the list to trigger the first flow with the email address value with an apostrophe.

The flow will fail in the step where “Send an HTTP request” call is made with the following error message

This error can also be confirmed by Web Request in the browser

Solution to problem!

To overcome this situation, you can sanitize the email address by checking if any quotes available in the string variable then replace single quotes with double quotes with the following expression

Replace(<email address>,'''','''''')

To confirm this, we can verify again by Web Request in the browser

Let’s see, how the same solution works in the flow

Initialize a new variable and set variable with the expression

Replace that variable with the outputs already placed in the HTTP request

Save and Run the flow to test again. 

Now the flow runs without any error and Succeeds.