Unanswered

Output data from Listbox to Excel sheet on OneDrive

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hello all!

I'm new to PowerApps and struggling along by myself at work. I'm hoping that this hasn't been answered before. I've tried searching on Google and on these forums, but haven't found an answer quite yet. I am trying to take data from a List Box and export the amount of items selected to an Excel document on my OneDrive. Here's the code that I've been trying to no success:

Collect(WellnessTracking.'Preventive Activities', CountRows(preventiveActivities_listBox.SelectedItems))

Here's an image of my table

My goal is for a user to select which activities they participated in in a list box in my app and that count be placed in the appropriate cell in my Excel sheet. Thanks for the help!

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

So this has been talked about in many varieties - but let me see if I can address yours more.

My first question would be, where is the data that you are using in your PowerApps coming from?

The next point, creating Excel from PowerApps...not quite what you would think it would be. PowerApps is really great with using Excel as a datasource, but that was the intention - A DataSource. Not as an Excel file builder.

Now there are connectors available that will let you do this to some degree, but my thought goes back to my first question - if you have a datasource that is feeding your PowerApp, then why not just tie an Excel file to your datasource as well and use a Pivot Table or other data gathering / analysis features to provide that list you want?

If it's too complex of data, you could also consider Power BI to help out.

But again...all goes back to where is your actual data?

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

Like
a
(0)

Report
Copy link

Link copied!
HI @Anonymous ,

Could you please share a bit more about your scenario?

Do you want to patch the amount of items selected in your ListBox control into your Excel table?

Based on the formula that you mentioned, I think there is something wrong with it. The first argument of the Collect() function is required to provide a data source rather than a Column table.

I have made a test on my side, please modify your formula as below:

Collect(
WellnessTracking,
{'Preventive Activities': CountRows(preventiveActivities_listBox.SelectedItems)}
)

In addition, I also think the Patch function could achieve your needs. Please take a try with the following workaround:

Set the OnSelect property of the "Submit" button to following formula:

Patch( WellnessTracking, /* <-- WellnessTracking represents your Excel table data source */ Defaults(WellnessTracking), { 'Preventive Activities': CountRows(preventiveActivities_listBox.SelectedItems) } )

More details about the Collect function and Patch function, please check the following article:

Collect function

Patch function

Best regards,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@RandyHayes

Thanks for getting back to me. You may have inadvertantly answered a question of mine - can you build an Excel sheet from PowerApps. My intended goal is to have a user facing interface to track events that would then fill out the Excel sheet. So I'm attempting to use PowerApps as an Excel file builder. I take it from the tone of your message that this isn't possible? The goal of the app was to have multiple users enter data on seperate lines on a central Excel sheet. Is there a better data source for this? Or is this not an intended application of PowerApps? Thanks!

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @v-xida-msft

Thanks for getting back to me. My intended goal for my application is a data gathering app for multiple users. Users would select items in various listboxes and other elements that would fill out an Excel sheet - one row per user. I will try your suggested solution and see if that works as intended. I may have misunderstood the format of both the Collect and Patch functions.

Thanks!

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

@Anonymous

So, the answer still is Yes - you can build Excel sheets from PowerApps. The road to that has some potholes in it.

Slam dunk - No. Some work and understanding - Yes.

Better sources for your intended solution - Yes. I would consider SharePoint as a data source for this. It's just as easy to set up with a List (just like Excel). Works well as a DataSource in this case. And, as I mentioned in previous postings, You can always tie Excel to your SharePoint as a DataSource should you want to have "pretty" formats and sheets.

PLUS...you totally avoid the Sharing issue of people with the file open while trying to update and edit it from PowerApps. SharePoint is built to share!

If your solution needs to actually create an Excel file, then you need to look to the other Connectors such as the Plumsail Documents or the Excel Online connector. Both of these will "generate" an Excel file (both has cost to them), but they are not intended to then be consumed as a DataSource in PowerApps.

I hope that helps provide more detail - if not, feel free to post back.

Was this reply helpful? Yes No