Create and update Excel data to sharepoint list single quotation problem

(2) Share

Report

Posted on by CU02061120-1

Hi, everyone!

I made a flow about creating and updating Excel data to sharepoint list.

I used 「control 」action, and used「title」column as the key.

If the title have already been in the sharepoint list, the corresponding data would be updated by excel data.

If it doesn't exist in the sharepoint list, the data would be copied and pasted from excel to sharepoint list.

All things went successfully, except that I found there are some single quotation mark in the title column, which made the query in the [get items] failed.

The query is title eq 'dynamic contents'

The data in the title column Ex. apple'company 1Q

which make the query become title eq 'apple'company 1Q' and failed.

Here is the flow overview

manually trigger → variable (for counting ) → list rows present in a table（get the data from excel）→get items（get the data from sharepoint）→apply to each →control

∟　true → [create item]

∟　false →for each → [update item]

Are there anybody can solve this problem for me ? Thank you!

Categories:

Building flows

I have the same question (0)

All responses (13)

Answers (0)

David_MA 14,090 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Take a look at this post, it will show you how to escape out the single quotes in the filter query: How to handle single quotes in the filter property with Get Items actions | Microsoft Community Hub

Was this reply helpful? Yes No
CU02061120-1 43 on at

Like (0)

Report
Copy link

Link copied!

David_MA

Thank you for your post.

FileLeafRef eq 'replace(variables('FileName'),'''','''''')'

I have seen some contents like this↑. But I don't know how to put it into my flow.

I get data from sharepoint list and excel both.

Do I need to put initial action both after [list rows present in a table] action and [get items] actions?

Here is the overview of my flow

manually trigger → variable (for counting ) → list rows present in a table（get the data from excel）→get items（get the data from sharepoint）→apply to each →control

∟　true → [create item]

∟　false →for each → [update item]

Was this reply helpful? Yes No
David_MA 14,090 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

You will need to show your flow and specifically the portion of your flow where you said you are having issues: "All things went successfully, except that I found there are some single quotation mark in the title column, which made the query in the [get items] failed." Show the configuration of this action and the error message you are getting.

Was this reply helpful? Yes No
CU02061120-1 43 on at

Like (0)

Report
Copy link

Link copied!

David_MA

Again, thank you for your reply.

Sorry, The flow was made in Japanese. By the picture I uploaded and the flow overview below, I think you can understand it.

And about why I said all things went successfully, because when I deleted the single quotation mark in the title data. The flow went successfully.

Here is the overview of my flow

manually trigger → variable (for counting ) →do until→ list rows present in a table（get the data from excel）→apply to each→get items（get the data from sharepoint）→control

∟　true → [create item]

∟　false →for each → [update item]

[do until] is for get data over 256

Was this reply helpful? Yes No
David_MA 14,090 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Unfortunately, the screen shot is not helpful because it does not show how any of the actions are configured, and specifically the action you are having an issue with. Based on one of your earlier posts referring to, "FileLeafRef eq 'replace(variables('FileName'),'''','''''')' " I think you are trying to figure out how to escape out the single quotes in a filter query.

If so, this will explain what you need to do How to handle single quotes in the filter property with Get Items actions | Microsoft Community Hub. If this is the resource you were referring to below, what issue were you having in understanding what you need to do? This resource may also help you, as it contains images of the actions and shows how to configure them: Escape Single Quotes in ODATA Filter Query in Power Automate.

If neither of the above resources help, you'll need to show the configuration of the action and the expression you are currently using.

Was this reply helpful? Yes No
CU02061120-1 43 on at

Like (0)

Report
Copy link

Link copied!
David_MA

Thank you,　I tried to put a initial action between [list rows present in a table]and [apply to each] to get the title data, so that I can replace it. but I failed. Could you help me to solve this problem?

I wrote all the expression below.

manually trigger : no expression in it

variable (for counting ) : name→count type: string value:0

do until: loop stop condition → variable('count') equal triggerBody()?['number']

list rows present in a table（get the data from excel）: 　skip number →1 datetime→ ISO 8601

apply to each : outputs('list rows present in a table')?['body/value']

get items（get the data from sharepoint）: filter query→title eq 'title（案件名）'　topcount→1

control : condition expression →empty(outputs('get items')?[''body/value']) equal true

∟　true → [create item]

∟　false →for each → [update item]

increment variable: name→count value→1

Was this reply helpful? Yes No
David_MA 14,090 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

I don't see anything in your last post that shows you are following the method I referred you to for escaping out the single quote marks in the filter query. Being unable to see your flow does not help either. Your filter query needs to have an expression such as this to escape the single quotes with double single quotes:

あなたの最後の投稿には、フィルタークエリでシングルクォートをエスケープするために紹介した方法に従っていることを示すものが何も見当たりません。あなたのフローが見えないことも助けにはなりません。フィルタークエリには、シングルクォートをダブルシングルクォートでエスケープするための次のような式が必要です。

Title eq 'replace(triggerOutputs()?['body/Title'],'''','''''')'

I think the highlighted portion of this is your filter query?

get items（get the data from sharepoint）: filter query→title eq 'title（案件名）'　topcount→1

If that is your filter query, then it needs to be in the format as shown above in red.

もしそれがあなたのフィルタークエリであれば、上記の赤で示した形式にする必要があります。

Translation by Copilot AI.

Was this reply helpful? Yes No
CU02061120-1 43 on at

Like (0)

Report
Copy link

Link copied!

David_MA

Thank you for your reply. As I said in my first reply.
"I have seen contents I mean the query like this for many times." The issue is I don't know how to put it into my flow.

here it use「triggeroutputs 」function to get some data from trigger? But I don't input any words into my trigger. When I want to run my flow, I just click it. That's all.

Title eq 'replace(triggerOutputs()?['body/Title'],'''','''''')'

https://www.linkedin.com/pulse/escape-single-quotes-odata-filter-query-power-ian-bennett-

Like the link above you send me, if I want to make the "Title eq replace(variables('ClientName'),'''','''''')"query ,first I have to make a initial action like the capture below. I tried, and failed, so deleted the initial action. That is why you could not see anything in my last post that shows I followed the method.
The "value" in the captrue blow, I used dynamic contents" title" from the action[list rows present in a table] action

Depending on the flow capture below, can you tell me where I should put the initial action ?

And by the way, I think I can understand what you said. So translation may not necessary.

Was this reply helpful? Yes No
David_MA 14,090 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

To use this in your flow, you would use it in the filter query of the Get files or Get items action:

The expression: replace(triggerBody()['text'],'''', '''''')

Was this reply helpful? Yes No
CU02061120-1 43 on at

Like (0)

Report
Copy link

Link copied!

@David_MA

Thank you for your reply.

I put the expression in to the [get items] action which is for getting data from excel.
But the flow failed. Error says "get item action failed, expression is invalid. Creating query failed. Client request ID:xxxxxx". The expression below is what I put into.

Title eq replace(triggerBody()['案件名'],'''', '''''')　　　

"案件名"　is one of the columns in the excel which means title in English. In control action in my flow I use it as the key. "Title" is the column name in the sharepoint list.

As I said in the last reply. I don't put any data into the trigger. So I think triggerbody function in the query can not get any data. That is why I tried using initial action to get the title data. So that I can use the replace function below.

Title eq replace(variables('案件名),'''','''''')"
https://www.linkedin.com/pulse/escape-single-quotes-odata-filter-query-power-ian-bennett-

But I do not know where I should put the initial action to.

I put the initial action just after [ list rows present in a table ] action.　The name was 「変数を初期化する１」and then system add a for each automatically. like the capture below.

It failed. The error was "initializevariable can not be nested in an action of type'for_each '"

I tried to delete the for each action , and then the initial was deleted too.

I also tried put the initial just before [get items] action. it failed too. the error was similar→ "initializevariable can not be nested in an action of type'apply_to_each '"

About the initial action I have already told you. The paremeter was as the as the capture below.

I only changed the "value" to dynamic contents" 案件名" which I got from [ list rows present in a table ] action.

Was this reply helpful? Yes No