Sure, I’ve been around this industry forever, but I hardly ever have a chance to make personal productivity apps for me. I’m also spending loads of time training and have less and less time for building stuff. I’ve never been into coding, not even copy/paste of someone’s script on a CRM form.
And as much as it pains me to say it, I made a Power App with an Excel data source. But that’s what the situation called for, and that’s what Power Platform enabled me to do.
Please note that I am aware that there are a million ways to accomplish what I did, this is the way that I chose to make it with my time and available resources.
Power Apps come in all shapes and sizes to solve business problems of all kinds. They don't have to be fancy, they have a job to do. As long as they do the job, that's often just what you need.
This is the story of making a gradebook app.
Some of the details:
- Four users of the app, we are all licensed users on the same tenant
- We are supporting cohorts of students and their weekly assignments
- Each cohort has about 100 members
- Cohorts last six weeks
- Most of the time we have two active cohorts because their weeks overlap, so that’s around 200 active students at a time
Also of note:
- I am not an admin of the tenant
- I have a good Enterprise Office/Microsoft 365 license, but no Power Platform add-ons
The general flow of things is that each week students have short writing assignments. Trainers need to review the assignments and provide feedback to students in a timely manner. There are a few other things we track, but the heavy lifting is the writing assignments and feedback cycle.
The writing assignments
Each cohort has 6 forms, one for each week. We open up each week using native functionality to start the new week’s form on a certain date.
Data
Let’s start with the backend, the data.
There is a connector that allows me to capture Forms responses.
There is a connector that allows me to populate an Excel row in a table.
Our unique identifier in this case is the student email address. This comes with good and bad. Good, it’s easy for me on the build side. Bad, so many ways for this to not make the match we hope for, and it causes the cloud flow to fail. For now, we review failures and manually correct our gradebook as needed. Better error handling/avoidance is on the near-term backlog. ***edited to add*** I added some error handling this weekend and woohoo, it was so easy!
Each week has a form. Each form has a flow. And because of data retention policies each cohort has its own series of forms. So, March cohort has 6 forms and 6 flows; April cohort has 6 forms and 6 flows, and so on. For me, for now, this is ok. We have a master set of each and making the forms and flows for a new cohort takes fewer than ten minutes total.
The flow is very simple. Very.
The trigger is this named form is submitted.
Now take the info from the form.
Match to this row of this table of this file in this location.
Add these things to the row.
The Power App
For the app itself, it is built in the Teams channel we use to manage the cohorts.
It has 8 screens.
Main screen- This is where we pick the student we need to view. This is a read-only screen.
Student detail screen- We see the student info to include identifying info and assignments in summary form. This is a read-only screen.
Weekly screens- there are six of these pages, one for each week. They are effectively the same except each week shows that week’s questions and answers. From here we can track some of their non-writing activities and we can offer them feedback on the writing assignments.
Let’s look more at the functionality of each screen.
Main screen
This is a simple page to select the student. For now, the students are listed by their username they have on the cohort Team. That might change to their email in the future, we’ll see how it goes in practical day-to-day use. Sometimes we need to see a student for weekly feedback. Sometimes it’s to update their participation in other activities.
When a trainer is going to spend some time offering feedback, it’s easier if we know who is waiting for the feedback. So, the checkbox will only show us students who have submitted assignments and have not yet received feedback for that assignment. This query was the most cumbersome because we have to perform the same check for each of the six weeks in this one query. It was a bunch of if this and this or this and this, and so on. Once the first weekly logic was set, adding the other weeks was very simple.
I had noticed that sometimes the selected student wasn’t clearing as expected. It was sticky. I would look at Student A, go back home. Select Student B, and on view would see Student A instead. So I added a clear button for when that happens. It was so inconsistent that this was the fastest way to solve the problem instead of troubleshooting the why, we just solved it with a button.
Student detail screen
The available actions on this page are to go home (click that shiny cohort badge/logo takes you home), or drill into a week. Looking at this example you can quickly see that the first two weeks have the writing completed by the student, and feedback provided. But the third week has the student input, but no feedback yet. So, the trainer clicks the button for the week from the center of the page. If you were here to track other activities, you would still drill into the week to track that.
Weekly feedback screens
This is where the action happens. Before we got here, everything was read-only and directional. First, we have the non-action, action items. You can go home from here by selecting the icon. You can go back to the student summary screen with the back button.
Most of the fields on this page are read-only.
If you are here to track the non-writing activities, you can add your responses to those fields (there are two) and select the save icon. This sends the updates back to the Excel table. (Yup, we’re still on Excel here, did you forget?)
If you are here to provide feedback to the writing assignment, you enter your text in the review box, and select the Send Feedback button. This will send the save back to the table for any of the three fields you could have edited. But it also uses the Office connector and sends feedback in an email to the student. The email comes from the current user of the app. This is NOT a flow, it’s via a formula here in the app.
If you need to view other weekly activities for this student while you are here, you can navigate to any of the weeks directly from this page using the buttons on the top.
Random findings
- Still not a huge fan of Excel as a data source, but it’s what we have available here so it’s what we use. It is stored in a location far away from our other daily files, so chances of it accidentally being updated are less than if it were in a common location.
- Even experienced people have to search for formula help, and they make things via trial and error. I’m surrounded by super smart people and asked for help when I needed it. They still had to go look things up. And often things didn’t work on the first try.
- There is usually more than one way to do the same thing. For example, sending the feedback to the student each week could have been a cloud flow. The send feedback button was originally intended to trigger a flow to grab info from the table, then compose and send the email. But, it turned out for our purposes, using native functionality we could compose it all inline in the app itself and make it a more direct path to send, and it was easier to make it come from the current user with the in app email send.
- I could probably make a single flow for each cohort combining the six weeks into one cloud flow. For me that adds a layer of complexity that is just not needed here. It would make troubleshooting problems more difficult.
- Flow error messages can give enough detailed information to be able to manually complete the task, if the task is small.
- Things were easier for me because I am pretty good at quickly recognizing patterns and using them as needed with minor modifications.
- Things were more difficult for me because I am a three-dimensional thinker and Excel is not a relational database, it’s pretty darn flat.
- It’s ok to ask for help.
All in all, I spent about 20 hours on this so far. That includes everything from the structure of the table that powers the backend to the apps and automation. I also needed help a couple of times that added up to another couple of hours from my friends. This app will save our team several times that amount in a single cohort and we start new cohorts each month. It was definitely worth making this app. Even with Excel.