Copying Data from a Form into Excelsheet on Sharepoint

(1) Share

Report

Posted on by AD-28021546-0

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:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

Haque 443 on at

Like (1)

Report
Copy link

Link copied!
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:

The existing record is retrieved from the master table.

The existing record is appended as a new row in the audit table with additional metadata such as timestamp and user information.

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!

Was this reply helpful? Yes No
AD-28021546-0 10 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Suggested answer

Haque 443 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No