cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
southafrican
Frequent Visitor

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
Community Support

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 Resolver I
Resolver I

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
Frequent Visitor

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. 

Highlighted
gavinleung Resolver I
Resolver I

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
Frequent Visitor

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
Community Support

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
Community Support

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
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Solution Authors
Top Kudoed Authors
Users online (8,043)