I'm going to show you how you can turn a boring spreadsheet like this into a fully
functional app, mostly just by dragging things around on a screen.
The best part is that if you have Microsoft 365, you've probably already paid for it!
Welcome back to the Pro Tech Show!
Today we're going to be using Power Apps.
This is a tool in Microsoft 365 that lets you build apps for web and mobile by dragging and dropping;
and being a Microsoft 365 tool, you can connect it to loads of different cloud-based data
sources.
I know some of you are extremely passionate about open source, so if you know of an open
source alternative, drop it in the comments. I'd love to check it out.
To get started, log in to your Microsoft 365 portal and click on the Apps menu in the top
-left, that looks like a waffle.
If you don't see Power Apps listed here, click All Apps to find it in the list.
Here we see a few common starting points.
You can see a list of pre-made templates.
We're going to keep it simple and start from an Excel spreadsheet I've saved in
OneDrive.
For our scenario, we'll pretend the Pro Tech Show is branching out and starting a holiday
club for kids.
Here we've got a spreadsheet with a few details about our club members.
And yes, I know, if this were real I'd need a lot more information, but I'm not really
starting a holiday club, am I?
To use the data in our Excel file, it needs to be defined as a table.
To do that, select the data and click "Format as Table".
We can give our table a name by clicking on it and then the "Table Design" tab, and changing
the name here.
Now at our Power Apps screen, I'll click the Excel button, then "Add a New Connection".
There are a bunch of connectors in here, some are standard and others are premium.
Premium ones may require an additional licence, so I'll link a list in the video description.
As a general rule, data stored in Microsoft 365 tends to be included in the standard connectors
and external data like a SQL database tends to be premium,
but this could change at any point because Microsoft.
The connector I want is OneDrive for Business, which is currently a standard one.
From here I can choose my Excel file and then I can choose the table we defined.
Power Apps will automatically build a three-screen app from the Excel table.
If I click the Play button up here to preview the app I can see a list of our members,
search for people, view details, edit them, and so forth.
If I change Mary's favourite colour from red to blue then reopen my spreadsheet you'll
see the record for Mary has been updated by the app.
You'll also see an extra column has been added.
This is a unique identifier used by Power Apps to track records in my spreadsheet;
but if I'm being honest, Excel is not a very good way to store app data.
You'd be better off using a SharePoint list or even the Dataverse or a SQL database.
The problem with an Excel spreadsheet is if anyone moves your file, the app will break;
and if you try to make a change in the app while someone else is the spreadsheet open,
it won't work.
So I'm going to recommend you actually use a list instead.
You can always export a list back to a spreadsheet if you need to.
It doesn't make any difference to how you build the app, you just click on this button
instead of this button and everything else is basically the same.
Now I don't know how many of you will be familiar with SharePoint lists, but I'm willing
to bet most of you have used a spreadsheet before.
So even though it makes no difference to the app, I'm using a spreadsheet to hopefully
make it easier for you to follow what's going on.
Back to our app studio, and a little orientation.
In the middle you have the app, and you can drag and drop stuff about in it.
In the top-right, this play button will run the app so you can see how it will appear
to the end-user.
You can also preview it by holding the Alt key and clicking about on the app directly.
On the right you have the properties of the currently selected component.
Click on something else and this changes to reflect it.
This bar along the top here also shows the properties of the current component in a different
way.
You've got a drop-down with the properties, then this box to edit them.
This is easier when you have a longer bit of text to work with, like a formula.
On the left there is a two level menu.
You'll mostly be working in the tree view, which shows the different components of the
app.
These rectangles that everything gets grouped under are the different screens in your app.
Think of them like pages.
You can use the insert menu to add components, or you can do it from the toolbar at the top.
You can edit the existing screens if they're kind of close to what you're after, but
I'm going to come over here and click "New screen" to start from scratch.
There are premade templates, but I'll just choose a blank layout.
It's laid out for a phone in portrait orientation, but I think my fictional holiday club leaders
will use a tablet for this; so we'll hit the settings icon, display, and change it
to landscape.
Conveniently, this also works better for a computer display.
I can give it a name while I'm here, change the icon, the colour, and so on.
Now one thing I need to do before I go further is click "Save".
This is really important because autosave doesn't kick in until you've made at least
one manual save, and you could spend a lot of time getting your app just right and then
accidentally lose it all by hitting the back button in your web browser.
This is the second time I've built an app for this video because… yeah, I did that!
For this screen I think I'm going to combine the auto-generated functions onto a single
page to make use of the extra space on the tablet.
Let's start by naming our screen and adding a title bar.
I'll add a rectangle from the insert menu.
With the rectangle selected, we can adjust its size by dragging on these handles, or typing
a value into the properties fields over here.
I'll make it 72 pixels high and change the colour to the orange colour from my channel's
logo.
You can pick a standard colour from the list, but in my case I'll use the custom tab to
enter the hex value.
Let's add a logo.
Insert, media, image, and drag it into the corner here.
It will automatically snap to the boundaries of nearby objects, making it easy to line
things up.
Now in the properties panel I can select an image.
I haven't got any yet so let's upload my logo.
That's good, but it's crushed against the edges, so I'll add a bit of space.
I could just resize it, but having it snap to the corner is convenient so I'll add
a bit of padding instead and the image scales within its boundary box.
Six pixels on all sides should do it.
Now I'll add a text label to give this screen a title, and tweak the formatting a little.
Next I want a list of our members. For that I want a "Gallery" which I'll place here on the left.
We've got this placeholder Latin for now. To get some real data we need to choose our data source.
I'll choose our Members table from the spreadsheet, and there we go.
There are some predefined layouts, but you can customize it as much as you want.
If I expand the gallery object in the tree view, you can see the different components inside it.
I'll choose the predefined "Image and Title" layout. Unfortunately, it has chosen the
kids' ages as the title,
and it would be more useful to see their names; so I'll select the field, come over here and
click on the text property. You'll notice it has this "fx" icon. That means the text is defined by a
function. You can see it up here: "ThisItem.Age". If I delete that and type "ThisItem.", it'll give me a
list of the fields from my spreadsheet. In our spreadsheet we also had a link to photos of the
kids. To get these showing up I'll select the image field, and instead of choosing an image file
I'll enter another function "ThisItem.Photo", and now we can see who we're working with.
Before you ask, these are all random stock photos. My kids would never stay still long enough for
this! The next thing we want is a form to edit member details, conveniently called an "Edit form".
I'll drag that over to the right, and it prompts me to connect my data source.
This link is kind of pointless because it doesn't actually do anything. It just displays our data
sources over here. To actually connect it we select "Data source" from the properties over here,
then click "Edit fields" to choose what we want to see in the form. Let's add Name, Age and Favourite
Colour. The fields appear in the form, but they're currently blank because it doesn't know which
member we want to edit. To tell it whose details we're working on, we need to set the "Item" property
to reference the currently selected member. For that we need the name of our gallery object. To
find the name you can click on it to select it and it'll be highlighted on the tree view over here.
It has a default name now but we can rename it from the menu or by double clicking the name.
I'll call it "MemberGallery" to make it easier to keep track of,
and I'll rename our form to "MemberForm" while I'm at it.
Now back to our form item. We'll type "MemberGallery" to reference the gallery object,
then a dot to say we want to reference one of its properties,
and the property we want is "Selected". So "MemberGallery.Selected". Now if I preview the app,
you'll see that as I click on different children their details are populated into the form. I can
change them but I don't have a way to save them yet. If we go back to our original auto-generated
screen that is now messed up since we changed the app's orientation, you can see the function that
gets called when we click the save button. We'll do the same thing on our screen. Insert, icon,
I'll choose the same check mark.
Let's change the colour though.
Now the function we need for our "OnSelect" action is "SubmitForm",
then the name for form in brackets, "MemberForm".
You can view available functions from this dropdown.
Now if I make changes and click the button, the changes are written to my spreadsheet.
That's a working edit screen, so I'll delete the auto-generated ones.
The main point of this app is to sign kids in and out, so let's make a new screen to do that.
To save time, I'll duplicate the screen we already have and name this one "SignInScreen".
We don't need the form, but I want to create a second gallery object which I can do by copy-pasting,
either from the menu or with Ctrl-C and Ctrl-V.
I want them both to take up half the screen. Instead of trying to eyeball this,
I'm going to go to the properties, click on "Width" to select it, and into the function type
the name of the whole screen (that's "SignIn Screen") dot Width to get the width of the whole screen,
then divide that by 2. And now my gallery is exactly half the width of the screen. Copy-paste.
I'll call the one on the left "SignedOut", and the one on the right "SignedIn",
and add a couple of titles to that effect.
Now, obvious problem. Both galleries show every member. To show a subset of members
in each gallery, we need to change its data source. Right now, they both reference the
Members table. Let's filter the right-hand gallery so it only shows members that have
been signed in. Click on "Data source" so we have it selected in our function bar, and
the function we want is "Filter". Open brackets, and it gives you some guidance. The first
thing we need to give it is the source for our data. That's still our Members table.
Then we need a comma and a logical test. Looking at our table, we want members where the "Signed In"
column says "Yes", so that's our test. Note the quotation marks. Double quotes around
the word "Yes" means to treat it as literal text. The single quotes around "Signed In" are
to tell it this is all one object. This is because the column name in Excel has a space
in it. Without the single quotes it would interpret that as a column called "Signed",
then a space, and something separate called "In", and we'd get errors. We'll do the opposite
for the left-hand gallery. Writing "<>" like this means "not equal",
so this filters for members where the "Signed In" column is not equal to "Yes". You might think
to write "=No" instead, but this way is safer because the column in Excel is just
free text. Someone might edit it incorrectly, and then there's no guarantee the text will
either be "Yes" or "No", and the kid would just disappear from the screen entirely. We can,
however, guarantee that the text will either be "Yes", or it will not be "Yes". Next I'm going
to choose the "SignedOut" gallery and add an action to sign the kids in. "OnSelect", and
the function to update a data record is "Patch", then our source (Members), the record we want
to update (which we can reference as ThisItem), and the fields we want to update (which
in this case is SignedIn), and the value of "Yes". If you need to update multiple fields,
you can separate them by a comma as long as you keep them all within these squiggly brackets.
You don't need to remember all of this. There's documentation for all the functions, which
I'll link in the video description. I'll do the inverse for the other gallery as well,
and now if I run the app I can move kids from signed in to signed out and back again just by clicking on them.
Imagine this running on an iPad and you've got an easy to use sign-in system that updates the spreadsheet automatically.
How do you know which screen you're going to land on though?
Well, if you run the app proper it starts with whichever is at the top;
so let's add a new screen and move it to the top so it acts as a landing page.
I'll keep it simple and just add two buttons
then give them an "OnSelect" action to navigate to the respective screens.
Easy. One last thing we might want to do is keep a log of activity. Let's go back to
our spreadsheet and make a new worksheet. We'll create columns for a timestamp, member affected,
action taken, and the staff who carried out the action. Then format this as a table and
name it "Log".
I'll rename this sheet as well, although this doesn't really matter.
Now back in our Power App, I'll choose "Data" from the menu and "Add data".
We can use the existing connector, choose the spreadsheet, then our "Log" table.
This may not show immediately if you've just added it, so be patient and try again in a minute or so.
What we want to do now is add a new entry to our Log table every time a kid gets signed in or out;
so we have a full log of when they arrived, when they left, and which member of staff was responsible.
To do this, we need to modify our "OnSelect" action.
We'll need to use the "Patch" function twice: once to update the Members table,
and again to update the Log table.
We can separate the two "Patch" functions using a semicolon; which tells Power Apps to do one,
then the other.
We've already got the function to update the Members table,
so stick in a semicolon and "Patch", open brackets for the next one.
The source this time is "Log", and because we're creating a new record rather than
updating an existing record,
we'll use the "Defaults" function to create a new blank entry in the Log table.
Now we can define the values for our columns.
The first column is "Time", and we can get the current time by using the function "Now()".
The next column is the name of the "Member". As before we'll reference the current record using
"ThisItem", and we can get the name filled with ".Name".
The "Action" in this case is we're signing them out;
And to get the name of the "Staff" member, we can use the "User()" function to retrieve the logged in
user account, and use ".FullName" to get their name.
Repeat for the other gallery object, this time making sure the action says "Signed In",
and we're done.
Now if I run the app and make a few changes, you'll see that not only is the status updated
in the Members worksheet; but the Log table in our spreadsheet contains a list of the actions taken,
when they were taken, and my name in the "Staff" column.
There's more we could do, of course, but let's call this app done.
To make it available, we need to publish it;
because right now if I try and launch it outside the editing studio,
I'm going to get the original auto-generated screens.
This means you can still work away in the app and make changes to it and save them
without impacting the live user experience.
You don't want your edits to show on people's devices before you've finished everything and have
it tested; so nothing changes in the live app until you hit the "Publish" button in the top-right.
Now if I go back and open the app, you'll see the updated version.
You can find your apps by going to "Power Apps" in the main waffle menu and scrolling down,
or clicking "Apps" on the left.
You can share your app with other people from here as well.
They can launch it from this Power Apps web interface,
or using the app that's available for Windows, Apple, and Android.
Comment below to let me know what you build with this,
and hit the like button if you found the video useful.
For the sake of my fellow IT folks, please consider using a list or Dataverse instead
of a spreadsheet, and don't forget to document your work.
We'll thank you for it, believe me.