Power Apps Excel-Style Editable Table – Part 1

Excel is the most popular business app in the entire world. Everyone who works in an office understands what the app is and knows the basics of how to use it. Including an editable table that is designed similar to an Excel spreadsheet is an excellent addition to many Power Apps. It provides users an efficient way of doing data entry and makes them feel comfortable with a concept they already know. Unfortunately, Power Apps does not have this feature built-in.

In this two-part series I will show you how to build an Excel-style editable table in PowerApps. Part 1 will show you how to setup the table, edit values and save changes back to a datasource. Part 2 will focus on how to add new records and delete existing records.

Inventory Count App

The Inventory Count App is used by employees of a home improvement store to record the actual amount of each product the business owns. An employee clicks on the New button to record information about several items into a table and clicks Save once finished. If a correction is needed the item can edited or deleted from the table.

Create a new SharePoint list called ‘Inventory Count’ with 4 columns: ItemNumber (text), Description (text), Quantity (number) and Location (text).

Open Power Apps and create a new Canvas App From Blank called Inventory Count App. Insert a gallery called gal_EditableTable onto the canvas with the ‘Inventory Count’ SharePoint List as the datasource.

Then place 4 text input controls inside the gallery named txt_ItemNumber, txt_Description, txt_Quantity and txt_Location and use this code in each of their Default properties respectively: ThisItem.ItemNumber, ThisItem.Description, ThisItem.Quantity, ThisItem.Location. The app will now look like the image shown below.

Now we will work on styling the gallery to make it appear like an editable table. Change gal_EditableTable to have these properties

TemplatePadding: 0
TemplateSize: 40

Then change txt_ItemNumber, txt_Description, txt_Quantity and txt_Location to reflect these properties.

BorderColor: DarkGray
BorderThickness: 1
Color: Black
Fill: White
FocusedBorderColor: Self.BorderColor
FocusedBorderThickness: Self.BorderThickness
HoverBorderColor: Self.BorderColor
HoverColor: Self.Color
HoverFill: RGBA(186, 202, 226, 1)
PressedBorderColor: Self.BorderColor
PressedColor: Self.Color
PressedFill: Self.Fill
RadiusBottomLeft: 0
RadiusBottomRight: 0
RadiusTopLeft: 0
RadiusTopRight: 0

Once the style changes are completed the gallery now looks like an editable table.

As a final touch we will add a label above the gallery with the column header names. Set the label’s Fill property to a color that matches your app’s theme and make the FontWeight bold. Our gallery now looks like an Excel spreadsheet.

Detecting Edited Rows

Employees require the ability to edit values in the table and save them back to the SharePoint list. To ensure the best app-performance possible we only want to update records that were changed. We will track this by inserting a toggle called tog_isChanged on the right-side of the gallery.

Put this code in the Default property of the toggle to control its behaviour.

ThisItem.ItemNumber<>txt_ItemNumber.Text
Or ThisItem.Description<>txt_Description.Text
Or ThisItem.Quantity<>Value(txt_Quantity.Text)
Or ThisItem.Location<>txt_Location.Text

Try changing a few values in the table to understand how the toggle works. When the toggle is in the ‘On’ position it means the row was modified. The ‘Off’ position tells us that the row remains unedited.

Once we are satisfied the toggle is working correctly we can hide it by changing the Visible property to False. The employee does not need to see the toggle.

Editing The Table and Saving Changes

Initially, the table should be in a view-only state. The employee will click the Edit button to make the gallery editable. Place an ‘Edit’ icon and a label with the word “Edit” above the gallery.

Put this code in the following properties of both ico_Edit and lbl_Edit

OnSelect: Set(varGalleryMode, "Edit")
Visible: varGalleryMode=Blank()

Then use this code in the DisplayMode property of the Text Input controls: txt_ItemNumber, txt_Description, txt_Quantity and txt_Location.

If(varGalleryMode="Edit", DisplayMode.Edit,DisplayMode.View)

Now when we click on the Edit button the gallery changes from “View” mode to “Edit” mode.

Next we’ll add 2 more pairs of icon and labels for “Save” and “Cancel”.

They should only appear when the gallery is in “Edit” mode. Put this code in the Visible property of the icons and labels.

varGalleryMode = "Edit"

When the employee clicks “Save” any changes made should be written back to the SharePoint List. We will create a collection called colUpdates to store the modified records. The first step is to define the collection schema by placing this code in the OnStart property of the app.

ClearCollect(colUpdates,{
    ID: 1,
    ItemNumber: "A",
    Description:"A",
    Quantity: 1,
    Location:"A"
});
Clear(colUpdates);

Then insert this code into the OnSelect property of the Save icon and its associated label. It will loop through the gallery looking for any changed records, store them in the colUpdates collection and then patch multiple rows to SharePoint simultaneously to achieve the best performance.

// Create a collection to store updated values
ForAll(
    Filter(
        gal_EditableTable.AllItems,
        tog_isChanged.Value
    ) As ChangedRows,
    Patch(colUpdates,
    Defaults(colUpdates), {
        ID: ChangedRows.ID,
        ItemNumber: ChangedRows.txt_ItemNumber.Text,
        Description: ChangedRows.txt_Description.Text,
        Quantity: Value(ChangedRows.txt_Quantity.Text),
        Location: ChangedRows.txt_Location.Text
    })
);

// Update SharePoint with new values
Patch('Inventory Count', colUpdates);
Clear(colUpdates);

// Return gallery to view mode
Set(varGalleryMode, Blank());

Once the SharePoint list is updated the gallery returns to “View Only” mode.

Cancelling Changes

Finally, we want to allow employees to return to “View” mode without saving any of the changes they made. Put this code in the OnSelect property of the cancel icon and label.

// Reset text inputs to erase any edits made
Set(varResetTextInputs, true); Set(varResetTextInputs, false);

// Clear any changes from colUpdates
Clear(colUpdates);

// Return gallery to view mode
Set(varGalleryMode, Blank());

Then write this code in the Reset property of txt_ItemNumber, txt_Description, txt_Quantity and txt_Location.

varResetTextInputs

When the cancel icon is clicked all records are reset and show their original values.

Want To Read Part 2 Of This Series?

My next article will show you how to add new records and delete existing records using an editable table. Click here to read Power Apps Excel-Style Editable Table – Part 2.

Questions?

If you have any questions or feedback about Power Apps Excel-Style Editable Table – Part 1 please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.