Save an Excel Form to a Sharepoint List

[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? 

[gavinleung]

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

[southafrican]

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. 

[gavinleung]

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

[southafrican]

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()

[v-yutliu-msft · Accepted solution]

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,

[v-yutliu-msft]

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,