Unanswered

Update excel from gallery

(0) Share

Report

Posted on by Agalavan27

Hello,

I have an excel in Sharepoint with 3 sheets and a table on each sheet (Table1, Table2, Table3), each of the tables has columns (Name, Surname, Email, Access1, Access2, Access3). Through a dropdown I access each sheet, and with a combobox I select the records I want from the table chosen in the dropdown. These chosen records in the combobox are displayed in a gallery.

I need several things:

1.- If I modify one of the rows, that this modification is reflected in the corresponding sharepoint excel sheet.
2.- Add a button that allows me to add a new blank row in which I can enter a new person (His name, his surname, his email and his accesses (YES or NO) if he has them.
3.- A button next to each row that allows me to delete that row.

All these changes must be registered in the shapoint excel.

Sorry, but I've barely been using this tool for two weeks.

Thank you

Categories:

Building Power Apps

I have the same question (0)

All responses (15)

Answers (0)

neuronic 177 on at

Like (0)

Report

Hi Agalavan27. I suggest first you create a new screen and insert an "edit Form".
This will allow you to edit and add records to the Excel tables.
Set the "DateSource" of the form to the Excel file.
Set the "Item" of the form to "yourGalleryName".selected"
You will need a button to submit the form.
Set the button "onSelect" to SubmitForm(yourFormName)

This may help to get you started.
The you will need to think about how to insert a new records (using Patch function), and deleting records.

Was this reply helpful? Yes No
neuronic 177 on at

Like (0)

Report

Apologies, last line should have said:

Then you will need to think about how to insert new records (using "New Form"), and deleting records.

Was this reply helpful? Yes No
Agalavan27 22 on at

Like (0)

Report

Hi neurotic and thanks for your answer.

Isn't it possible to do it on the same canvas?
that is to say, in the same liezo to see.
DESPLAYABLE: Where you can choose one of the different excel tables.
COMBOBOX: Where I select the desired records
GALLERY: Where the rows chosen in the combo will be displayed
Isn't it possible in that same canvas to have a button to add new rows? And be able to modify those rows of the gallery so that when doing so the tables of the source excel are modified?

Thank you again

Was this reply helpful? Yes No
neuronic 177 on at

Like (1)

Report

Hi, it is not possible to use a gallery directly to perform editing of records. With quite a bit of work it is possible to make a gallery appear to perform edits. However, it is far more straightforward to use a form to do editing and adding records. There is no problem with the gallery and the form being on the same screen if required. If you want to you can set things up such that the form is only visible when you need to edit/add record/s.

Was this reply helpful? Yes No
Agalavan27 22 on at

Like (0)

Report

Hi and Thank you;

I'll try it, however I'm very new.

I'll try, because I'm very new.
I have another problem at the same job.
As I said, a combobox will display the content (email and name) of a previously selected table in a dropdown.
When data from the combobox is selected, it will be displayed in a gallery, with the same columns as the original excel (name, surname, email, etc.), the fact is that I want to add a "select all", I have tried it with a check but we don't have anything in the gallery and I thought that it could be done from "items2 of the gallery itself. How can it be done?

My "item" code in gallery

If(
SelectSheet.Selected.Value = "Sheet1";
Filter(Sheet1Actu; If(IsBlank(PersonSelect.Selected.EMAIL); false; EMAIL in PersonSelect.SelectedItems.EMAIL));
If(
SelectSheet.Selected.Value = "Sheet2";
Filter(Sheet2Actu; If(IsBlank(PersonSelect.Selected.EMAIL); false; EMAIL in PersonSelect.SelectedItems.EMAIL));
If(
SelectSheet.Selected.Value = "Sheet3";
Filter(Sheet3Actu; If(IsBlank(PersonSelect.Selected.EMAIL); false; EMAIL in PersonSelect.SelectedItems.EMAIL))))
)

- SelectSheet: My dropbox where I select the table.
- Sheet1, Sheet2, Sheet3: The options into dropbox.
- Sheet1Actu, Sheet2Actu, Sheet3Actu: Tables in SQL, this tables are automatically updated when Excel is updated.
- PersonSelect: Combobox where I choose the people that will be shown in the gallery.

Any idea?.

Thanks a lot

Was this reply helpful? Yes No
neuronic 177 on at

Like (0)

Report

Hi, you can add "select all" to the dropdown. How are you getting information for dropdown? If is is with a collection, something like:
ClearCollect(colForDrop,{ person: "all"});
Collect(colForDrop, ShowColumns(personTable, "Person"))

Was this reply helpful? Yes No
Agalavan27 22 on at

Like (0)

Report

HI,

I access the information directly from 3 tables in SQL, I have not created any collection.
I edit an excel in Sharepoint, through a flow in PowerAutomate the SQL tables are updated and then, from a dropdown I choose the table with the following code in OnSelect.
If(SelectSheet.Selected.Value="Sheet1"; Sheet1Actu;
If(SelectSheet.Selected.Value="Sheet2"; Sheet2Actu;
If(SelectSheet.Selected.Value="Sheet3"; Sheet3Actu)))
Once this is done, I have the combobox where I choose the people I want to show in the gallery, with the same code as above but in "Items".
If(SelectSheet.Selected.Value="Sheet1"; Sheet1Actu;
If(SelectSheet.Selected.Value="Sheet2"; Sheet2Actu;
If(SelectSheet.Selected.Value="Sheet3"; Sheet3Actu)))
In this way, the combobox shows me all the people in the chosen table.

Was this reply helpful? Yes No
neuronic 177 on at

Like (0)

Report

OK thanks; can you show me the code for the dropdown's "Items"?

Was this reply helpful? Yes No
Agalavan27 22 on at

Like (0)

Report

Hi,

It's no code, in Dropdown "items" :
["Select Sheet"; "Sheet1"; ; "Sheet2"; "Sheet3"]

Was this reply helpful? Yes No
neuronic 177 on at

Like (0)

Report

Thanks, sorry, I think I have misunderstood you. Do you want some way of selecting all the records in a gallery? As far as I know, PowerApps itself cannot select more than one record. You could add a field to the underlying table/tables. It could be a "Yes/No" field. This could appear in the gallery as a checkbox. Then add code to OnCheck and OnUncheck of checkbox to patch the table's Yes/No field, for onCheck, something like:

Patch(yourTable,LookUp(yourTable,ID=ThisItem.ID),{yesNoField:true})

I hope this is what you meant!

(Please note, in my region commas are used instead of semi-colons.)

Was this reply helpful? Yes No