I recently started a consulting job for a construction company.
They had started out using Excel to quote for all their construction jobs. Excel is an amazingly flexible tool and it can definitely be used as a quote calculator.
The problem with using Excel is it doesn’t scale well. As they added more project managers to the organization and price lists changed, they were having difficulty with version control. Were their project managers using the most recent version of the template? Were prices up to date?
They wanted me to fix their Excel quote template, but instead I sold them on PowerApps!
I’m going to step away from the usual Excel related posts and this one is going to be an introduction to PowerApps.
What Is PowerApps?
PowerApps is a drag and drop formula based app building platform from Microsoft.
It allows you to quickly build useful business apps using an intuitive visual builder that doesn’t require traditional coding, it’s all formula based. You don’t need to be a professional developer to build useful apps. It’s been built with the Excel user in mind and actually has a lot of formulas in common with Excel.
PowerApps can easily connect to different data sources like Excel, SharePoint, Azure SQL, Outlook, and many others to read and write data. It can also connect to various non Microsoft third-party apps like Dropbox and Google Docs.
You can start with an existing app template and modify it, create simple apps based on data or build from scratch.
How Do You Get PowerApps?
The basic PowerApps plan is included in all Office 365 Business plans and you can sign up for a free trial at powerapps.microsoft.com.
This is going to allow you to create and use apps that connect to a wide variety of data sources.
There are a few features that you’re going to need to pay more for and the details can be found on the PowerApps pricing page. These features include premium connectors, the common data service for apps, data model driven apps, and many other Enterprise-grade features.
The basic Office 365 business plan features are more than enough to create some very useful apps though.
How Do You Build PowerApps?
PowerApps is a browser based online platform so you’ll need to log in at powerapps.microsoft.com to build any apps.
When you log in, you’ll come to the PowerApps Home screen.
- Information on build environment and account settings are in the top right corner.
- The main menu is on the left side.
- You can choose the design mode. Either canvas apps or model driven apps can be created.
- There is also some basic interactive training available in PowerApps. This will guide you through basic things in the PowerApps builder like how to connect to a data source.
- A list of recent apps you have built or that have been shared with you will be listed here.
- You can also view pre-built app templates and create your app based on one these.
- You can see a list of all the apps you have built or that have been shared with you.
Different Types of PowerApps
There are two types of PowerApps, canvas apps and model driven apps based on the common data service. They are essentially the same, they use the same builder UI and they have the same objects available inside the builder. The only difference is how you start creating the app and the PowerApps pricing plan needed.
Canvas apps can be created with the basic plan.
There are a few different ways to create a canvas app.
- Start from scratch by creating a blank app. This will require you to build everything in the app.
- Start from a template. There are a few dozen pre-built canvas app templates available. You can start from any of these and customize them as needed.
- Start from a data source. This will allow you to build a simple app to view, add or edit data in a single table from most any data source.
The last two option will build fully functional apps for you and are a great way to learn how things work so you can eventually build your own apps from scratch.
Model Driven Apps
Model driven apps based on the common data service will require you the plan 2 pricing tier.
If the app you are designing will be based on data from CDS and will require complex business logic then a model driven app should be used.
Creating A Canvas PowerApp From Data
The quickest and easiest ways to create a fully functional app is to start from data. This will create a simple app based on a single table that allows you to view, edit and create data in your data source.
Go to Create in the menu ➜ choose Start from data ➜ click Create ➜ choose your data source. Currently, the only option is to create a phone layout app from data.
Depending on your selected data source, you may need to provide further details and login credentials to connect. For example, selecting SharePoint will require you to supply the site URL and list name to use as the data source along with your credentials for authorization.
In the following example uses a list in SharePoint with the following fields to create a simple contact app.
- First – The first name of the contact.
- Last – The last name of the contact.
- Email – The email address of the contact.
- Phone – The phone number of the contact.
- Company – The company the contact is employed by.
- Address – The full street address of the contact.
- City – The city of residence of the contact.
- Country – The country of residence of the contact.
- Postal – The postal code of the contact.
This will create a completely functional app with three screens (Browse, Detail and Edit screens). Without any customization, the app comes with the following features.
- Icons to refresh the data source to see the latest data and sort the data in either ascending or descending order.
- A plus sign icon to add a new record to your data. This will take you to the edit screen with blank fields.
- A search bar that allows you to search for records in your data.
- A gallery that displays select fields from all the records in your data. Each record is displayed in its own data card.
- Each data card has an chevron arrow icon which allows you to navigate to the detail screen to see all the fields for that record.
- A back button icon to navigate back to the browse screen.
- A trash can icon that allows you to delete the currently selected record.
- A pencil icon that allows you to edit the currently selected record. This will take you to the edit screen with all the fields populated with the current record. You can then edit any field.
- All the fields are displayed for the current record.
- An X icon that allows you to discard any edit or new record.
- A check mark icon that allows you to save any edit or new record back to the data source.
- All the fields in your data set are listed with an input box for creating or editing records.
So as long as you have some data in a nice table format, you can create a simple and user friendly app with the ability to view, edit and create data. All in about 1 minute.
To build a new app or edit an existing app, you will need to use the PowerApps builder user interface. There are 9 main areas to the PowerApps builder user interface.
- Ribbon Tabs and Ribbon Commands
- Various Commands
- Object Property Selector
- Formula Bar
- Object Tree View
- App Canvas
- Object Properties and Settings
- Object Hierarchy Navigation
- Zoom Bar
Ribbon Tabs and Ribbon Commands
Like most Office apps (Excel, Word, PowerPoint etc.), PowerApps uses the familiar tab and ribbon structure.
There are 5 tabs.
- File tab will take you to a system menu screen where you can save your app, share the app, or view collections, media and variables used in the app.
- Home tab is where you can add new screens to your app, format, re-order, align or group objects together.
- Insert tab is where you can add objects (buttons, galleries, labels, forms, pictures, icons etc…) to your app.
- View tab allows you to see the current data sources in your app or add new data sources. It also allows you to see collections, media and variables used in the app and open up advanced settings for selected objects.
- Action tab allows you to quickly add actions (including Microsoft Flows) to the OnSelect property of selected objects.
This area allows prominent access the app checker, undo and redo, preview and sharing commands.
Object Property Selector
The object selector and the formula bar work together.
Each object you add to the app will have a variety of properties. Each property can have a formula that will determine the value of the property.
For example, if you add a button object to your app, you’ll probably want it to do something when the user presses it. This can be achieved with the button’s OnSelect property.
OnSelect = Navigate(Screen2, ScreenTransition.Cover)
Adding the above formula into the button’s OnSelect property will navigate the app to Screen2.
The formula bar has a few tricks worth knowing.
- Click on the fx icon to search available functions by category.
- There is an icon to expand or collapse the formula bar.
- You can click an drag the bottom edge of the formula bar to expand it.
- When the formula bar is expanded, there is a Format text option available. This will automatically format the formula and make it more human readable. There is also a Remove formatting option to removing any formatting that’s been applied.
Object Tree View
This window allows you see a hierarchical list of all the screens in your app and the objects they contain.
You can use this window to navigate your app and select, rename, copy or delete objects.
Every app has an App object in the tree view. The OnStart property of the App object is where you can add formulas to run when the app starts. This is a good place to load any locally saved data or set globally used variables.
Clicking on the three ellipse menu will allow you to force Run OnStart so you can test your app in the builder with the preview mode.
This is where you build and design each app screen. It will show the currently selected screen and all the objects in that screen.
You can drag and drop objects around the canvas or resize them with the resizing handles to design your app.
Object Properties and Settings Window
The object properties and settings window is really just a more user friendly object property selector and formula bar for a selection of properties. It will allow you to quickly customize properties which usually won’t be formula based like the color, height or width etc…
The contents of the window will depend on which object is currently selected in the tree view or the app canvas.
Some objects will have a hierarchy structure. For example, a gallery might contain pictures, labels, input boxes or icons and these will appear nested under the gallery object in the tree view.
This is another place to view a selected objects hierarchy and it will also allow you to select the parent object.
With the zoom bar, you can zoom in on the app canvas. This can make it easier to finely adjust object placement. There’s also a handy fit to window button which will automatically size the canvas so it is entirely visible.
PowerApps Preview Mode
The PowerApps preview mode is a handy feature that allows you to test your app while working in the builder UI. This mode will allow you to use the app as it would currently work if published.
This can be enabled by either clicking on the play button in the top right area of the PowerApps builder or by pressing F5 on your keyboard.
There is also a way to use your app without enabling the preview mode. If you hold the Alt key, you will be able to use the app from the PowerApp builder.
PowerApps Layout Form Options
There are two available layout options: Tablet and Phone. Choose wisely though, as you won’t be able to change the form factor after selection.
Once the form factor is selected, you can change the screen size and orientation. Go to the File tab in the app builder UI ➜ App settings ➜ Screen size + orientation ➜ you can select from various Orientation and Size options as well as change some more Advance settings.
While you can change these at any time, it’s best to change them before building your app. Your apps will not adjust to the new size and orientation automatically and you will need to manually adjust all the elements in your app to fit the new size or orientation.
Saving A PowerApp
Saving your PowerApp for the first time is very important.
After you save the first time, PowerApps will auto-save any changes that are made to the app so you won’t need to continually save.
The best practice is to save the app as soon as it’s created, even if it’s just a blank app. This way the auto-save will kick in and you won’t have to worry about losing your app if your browser crashes.
To save a PowerApp go to the File tab ➜ select Save or Save as ➜ choose The cloud ➜ give your app a unique name ➜ click on the Save button. This will save the PowerApp in the cloud to your Office tenant.
You can also save an app locally to your computer by selecting This computer option. This will allow you to download an msapp file.
Sharing A PowerApp
After you’re finished building your app and it has been saved, you will be ready to share the app with its users.
There are a couple of different ways to share an app.
- Immediately after saving an app, you will be prompted to share it.
- From the PowerApps builder UI, go to the File tab ➜ click on Share.
- From the PowerApps home screen, go to Apps ➜ select the app ➜ click on Share.
Either way will open up a docked window pane with the options for sharing the app.
- You can search for users in your organization by name or email address and add them to the user list. You can also share the app with groups of users or Everyone in the organization.
- As you add new users they will appear in the New users section. You can also see any existing user listed below the new users.
- The sharing window is also where you can remove user access to the app. Each user will have an X which will allow you to remove their access to the app. Note you will also need to remove the users access to the underlying data used in the app.
- You can designate any users as co-owners to the app which will allow them to edit the app and share it with others in the organization.
- Data permissions used in the app are listed here as a reminder. You will need to ensure any app users also have permissions to these data sources.
- There is an option to automatically send email invitations to new users. Make sure to uncheck this option if you are sharing the app with everyone and you work in a large organization.
- Click on the Share button to accept any changes made and share the app with the new users added.
Users will now see and be able to use your app when logged into their Office account with the PowerApps Android or Apple apps. You will also need to share the web link to the app with any users if they are going to be using it from a browser.
How Can You Use A PowerApp?
Currently, only people inside your organization will be able to use your PowerApps. A PowerApp can’t be used by anyone outside your Office 365 tenant.
For anyone inside your organization, there are quite a lot of options to use PowerApps.
- PowerApps can be used from any browser either desktop or mobile.
- There is an Android and an Apple PowerApp app. You can launch and use any app you have access to from these apps.
- PowerApps can be embedded and used in SharePoint, Dynamics 365 and Power BI.
To use a PowerApp from a browser, you’ll need the web link for the app.
To get the web link for any apps, you can go to the Apps tab ➜ select the app you want the web link for ➜ select the app Details. You can then copy and paste the web link URL from the details into any web browser to use the app.
Other No Code Or Low Code App Builders
PowerApps isn’t the only low code app builder out there. There are quite a few alternatives to choose from.
AppSheet is a Seattle-based company founded by a former Microsoft employee in 2014. They have around 20 employees.
AppSheet is a fully data-driven app builder. Define your data sources and table columns and watch AppSheet build an app for you.
- Simpler and easier to get started with.
- Public apps, accessible to anyone on the Internet, can be created.
- Less expensive (if your Office 365 plan doesn’t include PowerApps).
- As apps are built based on your data, modifying apps to fit new business requirements is often quick work.
- Less polished user experience.
- There is no user interface designer, meaning that AppSheet offers no flexibility in terms of how your app looks and behaves or what screens are offered to your users.
- Formulas are very limited compared to PowerApps, with fewer supported functions and no ability to calculate arbitrary properties.
- Actions — which can be run by your users from certain views — are less powerful than the behavior formulas supported by PowerApps.
- Less robust integration with the Microsoft ecosystem, including SharePoint, PowerBI and Active Directory.
Neosupport AB is based in Sweden and was founded by a doctor and his son to build e-health calculators in 2003. They launched a beta of their app builder in 2016.
Calcapp is a formula-driven app designer supporting hundreds of Excel-compatible formula functions. Like PowerApps, formulas can be used to calculate everything from if a button or field should be visible to the e-mail recipients of a PDF report.
- Simpler and easier to get started with.
- Public apps, accessible to anyone on the Internet, as well as apps you embed in your website, can be created.
- Less expensive (if your Office 365 plan doesn’t include PowerApps).
- Built-in PDF report generation, where reports can be e-mailed to users.
- While the generated apps are web apps, they can be installed to users’ home screens — with your app title and icon — and work offline.
- Only appropriate for calculation-heavy apps and not data-driven apps. While static data can be imported, the process is cumbersome and data can only be exported using third-party services.
- Formulas can’t be used to perform actions, meaning that a button can’t, for instance, conditionally send a report or take multiple actions.
- Much less robust integration with the Microsoft ecosystem, including SharePoint, PowerBI and Active Directory.
- The visual designer isn’t as versatile as that offered by PowerApps.
- Small collection of templates.
Zoho creates a range of line-of-business tools and was founded in India in 1996. They have roughly 8,000 employees.
Zoho Creator is a wizard-oriented app builder, which allows “low-code” scripting using a custom scripting language.
- The use of wizards makes Zoho Creator easy to get started with.
- Great compatibility with the vast Zoho ecosystem, including Zoho Flow (a Microsoft Flow competitor).
- More expensive.
- The custom scripting language Deluge can be hard to learn.
- Without custom scripting, Zoho Creator’s workflows offer far less flexibility than the behavior formulas of PowerApps.
- The visual designer isn’t as versatile as that offered by PowerApps.
- A number of bugs were observed during testing, which were hard to recover from.
PowerApps is a powerful and flexible tool for quickly creating useful business apps.
They can be a great option for replacing Excel templates used by many people inside an organization.
Have you started using PowerApps yet?