How to Send All Values in Collection to OneDrive (Excel)

[animartis]

Hello,

I am building an Inventory App that will allow users to scan a bin's QR code, then put in a quantity to adjust the stock level accordingly. I have the app connected to a OneDrive Excel sheet, but would like to build a collection first then push the collection to Excel. For example, I would like the user to see a gallery of everything they've scanned then submit that collection and add all the records to the Excel sheet. Is this possible?

[rebeccas]

I don't usually tie apps to Excel. I would suggest you look at a SharePoint list instead, it will work much better for you in lot's of ways. 

That being said..you should be able to use Patch to do what you need. I can tell you how I would do this with a SharePoint list and you may have to adjust it to do this in Excel. In SharePoint the button that would write all the data in my collection to my SPList would be:

ForAll(CollName, Patch(SPList, Defaults(SPList), {SPColumn1: CollColumn1, SPColumn2: CollColumn2}))

I'm not 100% how to modify this to work for Excel but hopefully this can get you started. I hope this helps! 

[akmiecik1]

Can you connect to the excel datasource and create an edit screen based on column names.

Then have another screen for the input data.

Populate the Default value of the edit screen with variables passed from the input data screen.

Have user submit the edit screen.

I imagine you could overlay the input screen over the edit screen, hiding all the edit screen DataCard but, leaving the checkmark to submit.

Let me know if you need more detail.

[animartis]

@rebeccasThanks! I was considering using an SP list but wanted to do some backend lookups/calculations and figured it would be better to have it all housed in an Excel workbook. Could you provide more detail as to why you would use a list? Just want to make sure I'm not underthinking it.

[rebeccas]

@animartis 

SharePoint list work very well with PowerApps and with Flow. They have several different column types you can use that will help you format the data how you need and you can do calculations in a column if you need to also. If I have a calculation that I need to show in PowerApps I make it a label and don't actually store in my SharePoint list but I have some that store in my list. It works well with PowerApps to help set any "rules" that will need to be followed to keep the formatting the same for your columns. 

Most people's compliant with the SPList is the number of items. Default in PowerApps is going to be 500 and you can raise it to 2,000 if you would like but it will slow it down unless you take a few extra measures. I control the number of items on SPList with auto archiving. When an item gets a certain status then I have a Flow that runs and moves it to another list...stuff like that. 

Plus, you have the ability to use People Picker that stores the person with all their details (name, email, etc) in one one column that you can easily refer to in your Flow if you want actions to follow. If you use PowerApps you will eventually use Flow to. Other added bonuses is each line item gets an autoID, it tracks and who and when it it was modfied and created and you can also add attachments to a line item if you have the need.

I would give it a try. Let me know if you get going and have any questions and I will help where I can! 🙂

[v-xida-msft]

Hi @animartis ,

Do you want to save a Collection back to your Excel table?

Based on the needs that you mentioned, I think the ForAll function and Patch function could achieve your needs. Please consider take a try with the following formula:

ForAll(
          YourCollectionData,
           Patch(
                     'YourExcelTable',
                     Defaults('YourExcelTable'),
                     {
                         Column1: YourCollectionData[@Column1],
                         Column2: YourCollectionData[@Column2],
                         Column3: YourCollectionData[@Column3],
                         ...
                     }
           )
)

In addition, the Excel table (hosted in OneDrive) is not a delegable data source, which means that you could not delegate the data process to your Excel table from app, instead, you could only process your data locally. In default, you could only process 500 records at most locally, you could change this limit to maximum value -- 2000.

More details about the Delegation in PowerApps, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

More details about difference between common data sources (e.g. Excel table, SQL Server, SP List, .. etc) in PowerApps, please check the following blog:

http://powerappsguide.com/blog/post/excel-vs-sharepoint-vs-sql-server-vs-cds

Best regards,