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
v-yutliu-msft
Community Support
Community Support

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

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

 

 

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. 

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

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

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

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (45,623)