Building a PowerApp that uses Excel as a database

Building a PowerApp that uses Excel as a database

Part one: Reading data from Excel
On the left, the PowerApp. On the right, the excel spreadsheet used as a data source.

Click the image above to watch how the PowerApp updates Excel

For 100 days, I will be doing 100 push-ups a day. I built a PowerApp to track the push-ups I have done each day.

The PowerApp does the following:

  • Lookup a row from Excel containing today’s count
  • Calculate and display the remaining push-ups for today. Each day starts at 100.
  • Show a set of buttons for the number of push-ups completed.
  • When a button is pressed, update the Excel spreadsheet and show the remaining count. This will be covered in a future blog post

To get this project started, I needed my data in Excel. Starting with a blank worksheet, our data starts off like this.

Initial data to be used with the PowerApp
Preparing the Excel file for use with PowerApps

Before you can connect an Excel file you will need to convert the data into a table. To do this, select cells that contain your data then use Insert > Table with the selected values to convert your data into a table.

Once your table is created, assign the Table Name and rename each of the Column Names to describe the data they contain.

I used the following for my table:

  • Table Name: pushupTable
  • Column1: Date
  • Column2: done

Click the image to watch how this is done

Creating your PowerApp

Next, we’ll visit https://make.powerapps.com/ to create a new PowerApp. We’re going to be creating a Canvas App for this project.

Create a blank canvas app (click to view)

This will give us a completely blank screen. You can use the Insert tab to add various items and place them.

Insert items into the canvas (Click to view)

To build the push-up tracker, we added the following fields:

  • Count label
  • “Remaining push-ups” label
  • 1 button
  • 5 button
  • 10 button
  • 20 button
  • 25 button
  • 50 button
Completed layout

With the layout completed, it’s helpful to give each field a meaningful name. You can double click the field name on the Tree view to rename each. You’ll access these within the coding portion of PowerApps so give each a name that will make sense if you have to maintain this later. You can also choose a color theme under the Home tab.

Next, we’ll move on to connecting this to Excel. First, you need to connect OneDrive for Business and select the Excel file and table that you want to use.

Select the Excel file through the OneDrive connector.
Why am I getting an error about no tables in my Excel file?
If you see this, you still need to convert your data into a table in order to use it as a data source in PowerApps. Follow the instructions here.

Once your table appears as a data source, your excel file is now connected.

Lookup a row from Excel containing today’s push-up count

Let’s look at our first requirement for our app: Lookup a row from Excel containing today's push-up count

Breaking this requirement down into steps, we need:

  • Access data from our connected data source pushupTable (The Excel table)
  • Locate a single row matching today’s Date
  • Find the value in the done column inside the matching row
Setting variables in PowerApps
Nearly all statements in PowerApps are function calls, which means setting a variable is possible only at the time of assignment.

What this means is you have two ways to assign variables. You can use Set() to assign a global variable (Meaning can be accessed from any screen) and you can use UpdateContext() and/or Navigate() to create or assign local variables (visible only from a single screen.)

Creating a global variable:
Set(ThisIsMyGlobalVar, "This is the stored value");

Creating a local variable:
UpdateContext({ thisIsMyLocalVar: "This is the local stored value" });
Navigate('Screen Name', ScreenTransition.Cover, { thisIsAssignedInNewScreen: 'Var only on Screen Name'})
PowerApp application startup
When a canvas app starts, it runs what is contained within App.OnStart and then displays the first screen in the tree view.

We’ll use App.OnStart to set a global variable pushupsToday that contains the row of Excel data that we care about and then use Navigate to create a local variable Count_text that contains the text for the Count label.

Why are we using Navigate if the first screen is shown anyway?
We’ve chosen this path because we want to store Count_text as a local variable as it’s only used on this one screen.

In a simple app like this it doesn’t matter about our global variable usage but in larger apps it’s a good idea to keep variables scoped to only the places they will be used.
Accessing a specific row in Excel from your PowerApp

In order to find a row in our Excel table we use the LookUp function. Here’s the code we’re using:

This code looks up a Record from the table PushupTable and assigns it to the global variable pushupsToday.

Why is my Excel date off by one day?
You’ll notice some strange syntax in the Formula parameter of the LookUp function above. This syntax is required because of the way Excel handles Date fields.

In Excel, when you put a Date value in a column, it is stored internally as a DateTime value. For example, if you have a value of 09/16/2019 in an Excel cell, then behind the scenes Excel stores this as 09/16/2019 12:00AM (+0000) (This is in the UTC (+0:00) timezone).

Why is this important?
When you pull this data into PowerApps, PowerApps will run in the timezone of the device it is run on. This means that both Date and Time values are converted into your local timezone.

In our case, we are in EST (America/New_York) (-0400). This means that when we enter a date of 09/16/2019 and Excel stores it as 09/16/2019 12:00AM +0000, PowerApps so helpfully converts it for us as 09/15/2019 8:00PM (-0400), which is the wrong date!

The way to fix this is to “push forward” the Date record rendered inside of PowerApps the value of TimeZoneOffSet being used by the PowerApp. This is required even if you aren’t storing Time data in your Excel records because of how Excel internally renders dates.

Thus, to work with Date values from Excel in a PowerApp, you need to do the following:

DateAdd(DateColumn, TimeZoneOffset(Today()), Minutes)

Timezones are one of the hard parts of computer programming.
Calculating and displaying the remaining push-ups for today

After we have the record stored in pushupsToday we use this to populate a local variable that we will assign inside our Pushup Status screen. To do this, we assign a Context parameter inside of a call to the Navigate function. Note that the call to Navigate isn’t required but is used here so we can push the variable into the right area. It’s not possible to create local variables from within App.OnStart any other way.

Here is the full code we’ve used in our App.OnStart:

App.OnStart
Set(
    pushupsToday,
    LookUp(
        PushupTable,
        DateAdd(
            Date,
            TimeZoneOffset(Today()),
            Minutes
        ) = Today()
    )
);
Navigate(
    'Pushup Status',
    ScreenTransition.Cover,
    {
        Count_text: 100 - pushupsToday.done,
        loading: false
    }
);
Assigning dynamic label text

We’ve assigned a local variable called Count_text with the text that we want to display on the big label. To assign the label to show the value for this, do the following:

Assigning the remaining push-ups to display (Click to watch)

Why are we using a variable to hold the text?
When we assign Count.Text to a variable it will update whenever the value of that variable is updated. In a future blog post you’ll see how we use this to always display the most recent data from Excel so that our push-up count is always accurate.

Putting everything together

The first step in our PowerApp is now done. We are showing the remaining number of push-ups for the current day on top of the app. In coming blog posts we will cover how to enable each of the buttons and how we’ll use what we did today to dynamically update the remaining count so it is always accurate.

As a final walkthrough, lets run our app and show how all of the moving pieces are connected. View the image below to see how the values are captured from Excel and then used to display inside the app.

Nick Hance
Posted on:
I'm a software project rescue specialist. Before starting a new project, let's talk about how we can save your current project.
Post author