web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Copying Data from a Fo...
Power Apps
Suggested Answer

Copying Data from a Form into Excelsheet on Sharepoint

(2) ShareShare
ReportReport
Posted on by 24
Hello,
I have a PowerApps form that I have created which is linked to an excel spreadsheet stored in Sharepoint. Users come along and update records with new information which is then transferred into the linked excel sheet.
 
For audit purposes, I need to be able to transfer the existing record details (fields on the form) into a separate tab on the same excel spreadsheet before the new information is transferred to the master file. I have tried to explore ways of doing this by creating a flow in Powerapps but so far have been unsuccessful.
 
If any further information is needed, please ask. 
 
Many thanks for any help in advance :)
Categories:
I have the same question (0)
  • Suggested answer
    Haque Profile Picture
    3,653 on at
    Hi @AD-28021546-0,
     
    Please let me digest, I am explaining the problem statement in my own ways, you just validate me if I understood correctly.
     
     

    Users update records through a Power Apps form that is linked to an Excel spreadsheet stored in SharePoint. The Excel file contains a master table where the current data is stored.

    For audit and compliance purposes, before any record in the master table is updated with new information, the existing record’s details must be copied into a separate audit table located in another tab of the same Excel spreadsheet. This audit table serves as a historical log of all changes.

     

    The challenge is to automate this process so that every time a record is updated via the Power Apps form:

     
     
     
     
     
    1. The existing record is retrieved from the master table.
    2. The existing record is appended as a new row in the audit table with additional metadata such as timestamp and user information.
    3. The master table is then updated with the new data submitted by the user.
     

    You do only update operation? No new record is inserted from the PowerApps?

     

    If the above problem statement is true - means I have understood then these are the steps we can follow:

     

    Let's start building the flow:


    Step-1: Let's select the PowerApps Trigger, Trigger: PowerApps (V2), please ad your required input parameters that you want to bring from PowerApps to Flow to update the Excel file.

    Step-2: Retrieve existing record from Master Excel Table/Tab
    • Use List rows present in a table action. Here is a thread you can get help. Please note that in this case the file is stored in OneDrive. You will need to configure SP Site and SP library there.
    • Configure with your SharePoint site, document library, Excel file, and master table name.
    • Use the Filter Query to find the existing record by unique key, e.g.:
      ID eq '@{triggerBody()?['ID']}'
      or
      Email eq '@{triggerBody()?['Email']}'
    Step-3: Add Existing Record to Audit Table
    • Use Add a row into a table action.
    • Configure with the same file but target the audit table. Table2 will be replaced with the name of your Audit table (as shown in the screenshot) 
    • Map fields from the existing record (output of Step 2) to the audit table columns.
    • Add audit fields like timestamp: utcNow() and user info (pass from Power Apps or use flow context) as needed.
     
    Step-4: Let's Update Master Table with New Data
    • Please use Update a row action.
    • Configure with the same file and master table.
    • Use the unique key to identify the row.
    • Update fields with new values passed from Power Apps.
    Step-5: Respond to Power Apps, can be optional, but always good to have.
    • Use Respond to a Power App or flow action to send back success confirmation or updated record.

     

    Please let me know the answer of my questions and if any further assistance is needed.


    I am sure some clues I tried to give. If these clues help to resolve the issue brought you by here, please don't forget to check the box Does this answer your question? At the same time, I am pretty sure you have liked the response!
  • AD-28021546-0 Profile Picture
    24 on at
    Apologies I am only just getting round to replying to you. This has been a great help so thank you.
     
    I think I have followed your instructions and created the flow - I have posted a few screenshots below to see if you can confirm I have taken the correct steps.
     
    These are the fields I want to update on the audit table (history table as shown below)
     
    This is step 2 from your instructions, to list rows present in table (I have redacted some information) This file is the master file being pulled from my sharepoint group and have used the container id as the unique reference in the filter query.
     
     
    Below is step 3 from your instructions, adding row into table action - here I have used the History table where the old records will be stored (some information has been redacted) 
    The grey columns headers with H at the end are the headers from the History table and the excel dynamic content is the associated fields from the master table
     
     
    Now is step 4 from your instructions is updating the master table with new data
     
    Assuming that I have done this correctly, please could you see if I have?
     
    How do I then get this flow to trigger when the form is submitted by the user to copy over old data and then update with new?
     
    At the moment, the onselect code for the submit button on the form is: SubmitForm(FORMNAME) - do I need to add in any additional code to get the flow to trigger?
     
    Many thanks for your help, it is very much appreciated
  • Suggested answer
    Haque Profile Picture
    3,653 on at
    Hi @AD-28021546-0,
     
    Ah! seems like you are almost there. Just add some code around SubmitForm(FORMNAME). So to call the Flow from PowerApps with parameters this is the signature under you submit button:

    FlowInApp.Run(TextInput1.Text, TextInput1.Text, TextInput1.Text, TextInput1.Text.... )

    FlowInApp is the name of your flow. If you want, let's swim here bit lightly.

     
    Please let me know if you could test the entire flow.
  • AD-28021546-0 Profile Picture
    24 on at
    Thank you for your quick response.
    I have added the code around SubmitForm(FORMNAME) , but its not working sadly. The code I have written is below:
    SubmitForm(FORMNAME),
    Historical.Run('Container No._Column1'.Text,Volume_Column1.Value,'Tare Weight_Column1'.Value,baffled.Text,'Dedicated TO PRODUCT_Column1'.Text,'ISO Status_Column1'.Text,'Date Filled_Column1'.Date,'Plant Location_Column1'.Text,'Product _Column1'.Text,'Batch Number2_Column1'.Text,'Net Quantity_Column1'.Value,'Date Emptied_Column1'.Date,'Previous Product in Tank_Column1'.Text,'Owner / Planner_Column1'.Text,'Bottom Access_DataCard1','Last modified_Column1'.Date,'Modifed By_Column1'.Text,'Special Characteristics_Column1'.Text)
     
    Does that look correct, have I written it correctly or am I missing something?
     
     
    The error I am getting against the above code is below:
     
    I am pretty confident its the code I have written is at fault because if I remove it, and just leave SubmitForm(FORMNAME)in, the form works as expected just without the addition of the flow being triggered.
     
    Thank you again for your support with this
     
  • Suggested answer
    Haque Profile Picture
    3,653 on at
    Hi @AD-28021546-0,
     
    Firstly, please move the Historical.Run(...) call to the form's OnSuccess property to ensure it runs after successful submission.
     
    Please verify each control reference passes a valid property value, not the control itself. For example: 'Bottom Access_DataCard1'.Text or if it's a data card, reference the input inside it: 'Bottom Access_DataCard1'.DataCardValue.Text
     

    Based on the error pattern, I would suggest just make sure every control's data is passed successfully.

     
     

    Recommendation on Quotes and commas:

    • Your formula uses single quotes around control names with spaces, which is correct for referencing controls with spaces.

    • Make sure all commas separating arguments are present and no extra commas exist.

     
  • AD-28021546-0 Profile Picture
    24 on at
    Thank you - I have tried do what you said but it still doesnt seem to be working.
     
    This is the code is have now re-written:
    HistoricalRecordsFlow.Run('Container No._DataCard1',Volume_DataCard2,'Tare Weight_DataCard2',Baffled_DataCard2,'Dedicated TO PRODUCT_DataCard1','ISO Status_DataCard2','Date Filled_DataCard1','Plant Location_DataCard1','Product _DataCard1','Batch Number2_DataCard2','Net Quantity_DataCard2','Date Emptied_DataCard1','Previous Product in Tank_DataCard1','Owner / Planner_DataCard1','Bottom Access_DataCard1','Last modified_DataCard1','Modifed By_DataCard1','Special Characteristics_DataCard2')
     
    I am now getting multiple of the error below:
     
    Thank you again for your help with this
  • Suggested answer
    Haque Profile Picture
    3,653 on at
     
    Ah.. things becoming tedious... let's focus on what's happening.
     
    I believe you have noticed what I mentioned earlier "I would suggest just make sure every control's data is passed successfully."
     
    As you have number of parameters to be passed to flow are high, you need to make sure every control's value is justified.
     
    Please do one thing, first pass 1 or two parameters to check if App passes these 1/2 parameters and flow is triggered or not.
     
     
    To narrow down the issue, keep these parameters, remove all others. And the call the flow passing two parameters like below:
    HistoricalRecordsFlow.Run('Container No._DataCard1',Volume_DataCard2)
     
    However, I have copied above code from your given code snippet and removed all other parameters to for test purpose. Here I see wrong
     
    HistoricalRecordsFlow.Run(ContainerNo_DataCard1.Text, Volume_DataCard2.Text) -  I am passing text if arguments expect text.
    or if the controls are numeric inputs:
    HistoricalRecordsFlow.Run(ContainerNo_DataCard1.Value, Volume_DataCard2.Value)I am passing numeric Value if arguments expect numeric inputs.
     
    As I see the error and it is expected because you didn't pass the text value or controls value, you have passed the controls directly and the flow expects text or value of the control.
     
    This is what I mentioned earlier: For example: 'Bottom Access_DataCard1'.Text or if it's a data card, reference the input inside it: 'Bottom Access_DataCard1'.DataCardValue.Text
     
    Please check every control to make sure your are passing right value from the controls to the flow.
     

    Invalid argument type (Control) expecting a test value instead - here is resolved issue of same kind.
     
    Again, please make sure you pass all the arguments correctly.
     
    Finally, if you can pass those two arguments and your flow triggers, do the same for the rest of the parameters.
     
  • Haque Profile Picture
    3,653 on at

    HI , 

    I was following up on tihs, were you able to address the issue? I believe we have worked hard on this to reach to a solution. Eager to hear from you.

     

  • AD-28021546-0 Profile Picture
    24 on at
    My apologies I hadn't replied to you soon, been a very busy week with work etc and just never got near to reply - I have however using your information been working hard on this so thank you. I have now, with your help, managed to get the flow to trigger which is great, thank you again, but I am now coming across errors with my flow (but progress is certainly going in the right direction.
     
    This is the error I am seeing now but with the flow:
     
    I have tow thoughts: is this related to the fact my unique ID, container no has alpha numeric values plus a special character - a container id looks like this ABCD123456-7 (all my container ids are just like this format) 
    or is it something to do with the ODATA filter query (below) that needs to be amended to allow the special character?
     
    Then Power Automate is giving the suggestion below:
    The container field isnt empty
  • Suggested answer
    Haque Profile Picture
    3,653 on at
     
    No worries - feeling good that things are moving. So, in this stage, we can say without error in the app, all parameters are successfully sent/passed to the Flow, now flow is giving you trouble particularly in (Odata) Filter Query!
     
    Well, in this case I would suggest to check exact column name in the Excel file (Container No.) - [upper case/lower case, space and stop] plus what exaclyt Container No brings from the app - can you please print the value in the flow (in compose variable or initiate/set variable) to see? If  container no has alpha numeric values plus a special character - a container id looks like this ABCD123456-7, you can check with StartsWith method to see if a check matched of not.
     
     

    I am sure some clues I tried to give. If these clues help to resolve the issue brought you by here, please don't forget to check the box Does this answer your question? At the same time, I am pretty sure you have liked the response!

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

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
Valantis Profile Picture

Valantis 424

#2
WarrenBelz Profile Picture

WarrenBelz 355 Most Valuable Professional

#3
11manish Profile Picture

11manish 290

Last 30 days Overall leaderboard