Skip to main content
Community site session details

Community site session details

Session Id :
Power Automate - Building Flows
Unanswered

Create and update Excel data to sharepoint list single quotation problem

Like (2) ShareShare
ReportReport
Posted on 10 Jun 2025 13:08:56 by 27
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!
  • CU02061120-1 Profile Picture
    27 on 16 Jun 2025 at 13:45:23
    Create and update Excel data to sharepoint list single quotation problem
    @David_MA
    Hi!Do you need more information ?
  • CU02061120-1 Profile Picture
    27 on 13 Jun 2025 at 12:21:19
    Create and update Excel data to sharepoint list single quotation problem
    @David_MA
    Thank you for your reply.
    I checked, I was right, "Title" is the name of the column in the sharepoint list(target list). 
     
    For you to understand more about my flow↓
    What I am doing by using get items action is to get the title data from sharepoint list. So that I can use title data as a key in the control action to judge if the title in the excel  have already been copied to the sharepoint list or not . If it has already been copied, the corresponding data would be updated by the data from excel. If it didn't, the excel data would be copied to the sharepoint list as new.
  • David_MA Profile Picture
    11,868 Super User 2025 Season 2 on 13 Jun 2025 at 11:49:32
    Create and update Excel data to sharepoint list single quotation problem
    You need to replace "Title" in the filter query with the actual name of the column in your spreadsheet. To use the column as a filter query, I believe the column name cannot contain any special characters or spaces.
  • CU02061120-1 Profile Picture
    27 on 13 Jun 2025 at 01:56:28
    Create and update Excel data to sharepoint list single quotation problem
    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.
     
     
    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 「変数を初期化する1 」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.

    No alt text provided for this image
     
     
  • David_MA Profile Picture
    11,868 Super User 2025 Season 2 on 12 Jun 2025 at 15:40:53
    Create and update Excel data to sharepoint list single quotation problem
    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'],'''', '''''')
     
     
     
  • CU02061120-1 Profile Picture
    27 on 12 Jun 2025 at 14:46:03
    Create and update Excel data to sharepoint list single quotation problem
     
    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'],'''','''''')'
     
     
    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 ?
    No alt text provided for this image
     
     
     
    And by the way, I think I can understand what you said. So translation may not necessary.
  • David_MA Profile Picture
    11,868 Super User 2025 Season 2 on 12 Jun 2025 at 12:48:04
    Create and update Excel data to sharepoint list single quotation problem
    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.
     
     
  • CU02061120-1 Profile Picture
    27 on 12 Jun 2025 at 05:50:07
    Create and update Excel data to sharepoint list single quotation problem
    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
    1. variable (for counting ) : name→count    type: string   value:0
    2. do until:  loop stop condition →  variable('count')  equal  triggerBody()?['number']
    3.  
    4. list rows present in a table(get the data from excel):   skip number →1      datetime→ ISO 8601
    5.  
    6. apply to each :  outputs('list rows present in a table')?['body/value']
    7. get items(get the data from sharepoint): filter query→title eq 'title(案件名)' topcount→1
    8.  
    9. control  : condition expression →empty(outputs('get items')?[''body/value']) equal true
    10. ∟ true → [create item]
    11. ∟ false  →for each → [update item]
    12.  
    13. increment variable:  name→count   value→1
  • David_MA Profile Picture
    11,868 Super User 2025 Season 2 on 11 Jun 2025 at 15:24:32
    Create and update Excel data to sharepoint list single quotation problem
    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.
  • CU02061120-1 Profile Picture
    27 on 11 Jun 2025 at 05:44:44
    Create and update Excel data to sharepoint list single quotation problem
    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Tomac Profile Picture

Tomac 986 Moderator

#2
stampcoin Profile Picture

stampcoin 699 Super User 2025 Season 2

#3
Riyaz_riz11 Profile Picture

Riyaz_riz11 577 Super User 2025 Season 2

Loading started