cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
southafrican
Level: Powered On

Save an Excel Form to a Sharepoint List

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? 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Save an Excel Form to a Sharepoint List

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
gavinleung
Level 8

Re: Save an Excel Form to a Sharepoint List

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
Level: Powered On

Re: Save an Excel Form to a Sharepoint List

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
Level 8

Re: Save an Excel Form to a Sharepoint List

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
Level: Powered On

Re: Save an Excel Form to a Sharepoint List

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

 

 

Community Support Team
Community Support Team

Re: Save an Excel Form to a Sharepoint List

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Community Support Team
Community Support Team

Re: Save an Excel Form to a Sharepoint List

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Kudoed Authors (Last 30 Days)
Users online (3,805)