Answered

Save an Excel Form to a Sharepoint List

(0) Share

Report

Posted on by southafrican

Hello

I'm relatively need to Powerapps and I was wondering if it is possible to save a form from a Excel file (Data source) to a sharepoint list.

So basically I've got an edit form that has data from a excel file, now I'd like the changed and new data to be save to a sharepoint list.

Is this possible?

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

gavinleung 216 on at

Like (0)

Report
Copy link

Link copied!

You can grab the data from the Excel by using the data form or table, and use Patch function to update the Sharepoint List.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-patch

Was this reply helpful? Yes No
southafrican 31 on at

Like (0)

Report
Copy link

Link copied!

How is the best way to go about this, what code would I use? I want it so when a user clicks the save button in red (View attachement) the data in the form saves to the Sharepoint list.

Was this reply helpful? Yes No
gavinleung 216 on at

Like (0)

Report
Copy link

Link copied!

I think you'd better do these before having any code:
1. Create a Sharepoint List with corresponding columns and type. See that you have a date picker, you may also want to put it as a date into the Sharepoint List. As a result, you may come across the delegation issue as Date field is non-delegated. You can read more here: https://docs.microsoft.com/en-us/connectors/commondataservice/
2. Create a Data Connection in the PA
3. Use a Data form and make it invisible, and use SubmitForm to submit the data on the Sharepoint List.

But btw, why do you need 2 data sources ? You can actually store the data / record in the Excel file ?

Cheers,
Gavin

Was this reply helpful? Yes No
southafrican 31 on at

Like (0)

Report
Copy link

Link copied!

So basically I want to move away from Excel and use sharepoint, I want the user to be able to make any changes to records to be made and added to sharepoint. That is why I need another data source.

So in the save button what would I put with SubmitForm()

Was this reply helpful? Yes No
Verified answer

v-yutliu-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @southafrican ,

I'm afraid there's no way to directly save data from excel to sharepoint list in PowerApps currently.

However, I suggest you try to use collection as intermediate medium.

I've made a similar test for your reference:

1)connect your app with the excel file and sharepoint list

2)save data from excel to collection

Collect(collectionname,excel tablename)

3)save data from collection to sharepoint

In this step, fields' data type in sharepoint list is very important.

If you use complex data type, the formula should make a little change.

I assume that all your fields are text type.

Then try this formula:

ForAll(collectionname,Patch(listname,Defaults(listname),
{list fieldname1:collection fieldname1,
list fieldname2:collection fieldname2,
list fieldname3:collection fieldname3,....}
)
)

Please replace collctionname, listname, fieldname with the names that you want to use.

Here's a doc about how to update multiple records at the same time:

https://powerapps.microsoft.com/en-us/blog/bulk-update-using-forall-and-patch/

Here's a doc about how to update complex data type of sharepoint list:

(If you use complex data type in sharepoint, please refer this doc to modity my formula)

https://powerapps.microsoft.com/en-us/blog/default-values-for-complex-sharepoint-types/

Best regards,

Was this reply helpful? Yes No
v-yutliu-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi @southafrican ,

What I posted is about how to move original data of excel to sharepoint list.

You can not make this by using submitform function.

After you move original data to sharepoint list, then if you want to edit data of sharepoint, you could use submitform function to update.

Best regards,

Was this reply helpful? Yes No