Write collection to Excel

(0) Share

Report

Posted on by AGroegelich

140

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?

Categories:

Building Power Apps

I have the same question (0)

All responses (14)

Answers (1)

Sort by

Verified answer

mdevaney 29,991 Moderator on at

Like
a
(3)

Report
Copy link

Link copied!
@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.

Was this reply helpful? Yes No
AGroegelich 140 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
AGroegelich 140 on at

Like
a
(0)

Report
Copy link

Link copied!
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?

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@AGroegelich

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

Was this reply helpful? Yes No
AGroegelich 140 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
AGroegelich 140 on at

Like
a
(0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
AGroegelich 140 on at

Like
a
(0)

Report
Copy link

Link copied!
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?

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@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."

Was this reply helpful? Yes No
AGroegelich 140 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @mdevaney,

I made the screen shot and sent it to you by PM.

The Patch is working without the filter statement. It appears that for some reason it cannot work if the [@MyField] notation in my app.

Was this reply helpful? Yes No