Write collection to Excel

[AGroegelich]

Hi All,

I am new to PowerApps and I cannot figure out how to save a collection back to the Excel sheet. So far have build an app and OnStart a load a collection. That collection is used in a gallery, a detail form and an edit form. In this way a user can modify several records rather quick because the SubmitForm writes to the collection.

But once done the whole collection, or at least the modified records, must be written to the original data source. In my case that is an Excel sheet. How can I do that? Unfortunate I cannot figure that out in the documentation.

BTW I tried to use the field "__PowerAppsId__" is the unique ID but that seems to be not possible. Is it somehow possible to read this number or is it something that PowerApps uses behind the screens?

[mdevaney · Accepted solution]

@AGroegelich 

Suppose you have a collection named myCollection like this

ID	Value1	Value2	Updated
1	"A"	2	True
2	"A"	3	True
3	"B"	6	False

We will also assume your Excel spreadsheet has the exact same columns.

Put this code in the OnSelect property of a button to update the Excel sheet with the new collection data.

ForAll(
    Filter(myCollection, Updated = true),
    Patch(
        myExcelSheet,
        LookUp(myExcelSheet, ID = myCollection[@ID]),
        {Value1: myCollection[@Value1], Value2: myCollection[@Value2]}
    )
)

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up.

[AGroegelich]

Hi mdevaney,

Thank for your response. I guess this will lead to the solution I need. Unfortunate I don’t have the columns ID and Updated in my Excel sheet yet, but that is easily modified.

However I have a new question now. When my form was using the Excel sheet instead of the collection as a data source I noticed that the function SubmitForm only performs an update when the record was really changed. So as a user pressed the update button while nothing was changed no write action to the Excel sheet was done.

That means – I think – that the SubmitForm function can detected if a record is modified or not. How is that done? And can I use that to conditionally set the field Updated to True. That will save some unnecessary update actions which might be good for performance.

[AGroegelich]

Hi @mdevaney and others,

I tried your code but I am getting an error. I have this code:

ForAll(
    Filter(cUsers; Updated <> "No");
    Patch(
        Users;
        LookUp(Users; UsrID = cUsers[@UsrID]);
        {
         UsrID:         cUsers[@UsrID];
         Updated:       cUsers[@Updated]
        }
    )
)

The error I get is: "The type of this argument/parameter UsrID isn't the expected type Text. Type table found" (Translated from Dutch).
I guess that is means that the field UsrID is numeric which is true.

So I tried something like

LookUp(Users; UsrID = Text(cUsers[@UsrID]));

but that didn't work. Any idea what I am missing?

[mdevaney]

@AGroegelich 

Type table was found?  On which column name?  What is your original data source?  What column type is that?

[AGroegelich]

I think the column name is UsrID from the collection

My original data source is an Excel sheet which I loaded to a collection and now I want to write that collection back to the Excel sheet.

In the Excel sheet I am sure the type is numeric. In the collection I am not quite sure, I had an issue earlier where a numeric column was treateds as a string column when sorting

[mdevaney]

@AGroegelich
What I am unclear about is which variable is being seen as a Table by PowerApps.  Once we figure that out, I'd like to determine why its a Table.

[AGroegelich]

Hi @mdevaney,

I have no idea which variable is seen as a table. However it must be something with the cUsers[@UsrID] notation.  As a test I modified the code to:

ForAll(
    Filter(cUsers; Updated <> "No");
    Patch(
        Users;
        LookUp(Users; UsrID = "123");
        {
         UsrID:         123;
         Updated:       "123"
        }
    )
)

and the errors disappeared. Of course in this form not very useful, but it might give a clue.

[AGroegelich]

I found the line that caused the problem. I changed the code to:

ForAll(
    cUsers;
    Patch(
        Users;
        LookUp(Users; UsrID = cUsers[@UsrID]);
        {
         UsrID:         cUsers[@UsrID];
         Updated:       cUsers[@Updated]
        }
    )
)

and this solved the errors. So this filter statement was causing the errors:

Filter(cUsers; Updated <> "No");

Any idea how this can be corrected?

[mdevaney]

@AGroegelich 

Lets try to figure out what's going on with that cUsers table.  We can do a test.

Place a button on your screen and put this code in the OnSelect property.

ClearCollect(tempCollection, cUsers)

Then go to the collections viewer.  Its on the view tab on the top menu, click Collections then take a screenshot.  I suspect this will show us what the table looks like.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."