Collecting evaluation responses across training programs, workshops, and learning events is a task almost every organization handles. But turning those responses into actionable insights, and knowing when all participants have submitted, is traditionally manual, error‑prone, and time‑consuming.

This article introduces a fully automated, production‑ready solution that connects:

Microsoft Forms → Excel (SharePoint) → Office Scripts → Power Automate

…to automatically identify the Top‑Ranked Instructor and send an email only when the evaluation phase is truly complete.

The entire solution is packaged as a Power Platform Solution ZIP, making it easy for anyone in the community to download, import, configure, and use without rebuilding the flow or script manually.

You can get it here:

👉 https://github.com/spashikanti/SunilP-FormsToExcel-TopRankEmailer/releases/latest

🎯 Why This Solution Was Needed

Most organizations want an automated way to:

Collect instructor/course feedback
Determine who performed the best
Trigger a notification when analysis is ready

But here’s the challenge:
Microsoft Forms does not have a built‑in “send email when all responses are submitted” feature.

Even with Power Automate, it's hard to know:

“Have all participants filled the form?
If not, should I wait until the deadline?
If yes, what happens next?”

This solution solves that problem elegantly.

🧩 What the Solution Does

Here’s what the automation handles for you:

✔ Collect evaluations from Microsoft Forms

Responses feed directly into an Excel table stored in SharePoint or OneDrive.

✔ Track when the evaluation is “complete”

The solution checks completion using two methods:

Expected number of submissions
Deadline fallback (in case not everyone completes the form)

✔ Identify the highest‑ranked instructor

An Office Script evaluates all data and identifies:

Top instructor
Highest score
Status of the workflow
Whether deadline is reached

✔ Send an automated email

Power Automate sends a professional HTML email to the top‑ranked instructor when:

All expected submissions are received
OR
The deadline is reached

This ensures the workflow always completes, even with partial responses.

🧱 Solution Architecture

Microsoft Forms  
      ↓  
Excel (RatingsData table in SharePoint)  
      ↓  
Office Script  
      ↓  
Power Automate Flow  
      ↓  
Email Notification

This creates an efficient pipeline where each platform does exactly what it does best.

📊 Excel File Structure

The Excel workbook contains two sheets (SharePoint library):

1. RatingsData

Stores Forms submissions:

Column	Purpose
ParticipantEmail	Submitter’s email
Instructor	Person being evaluated
Score	Numeric score (1–5)
Timestamp	Auto-generated

2. Settings

This is where the admin configures:

ExpectedSubmissions
DeadlineDate

This sheet allows maximum flexibility, no changes to the flow required.

🧩 Office Script: The Intelligence Layer

The Office Script (GetEvaluationStatusAndTopInstructor.ts) is the brain of the system.

It:

Reads the Excel table
Calculates the number of submissions
Parses the Excel deadline
Determines if the deadline is reached
Identifies the top instructor and highest score
Returns a workflow status:

Complete
DeadlineReached
InProgress
Power Automate uses this status to decide whether to send the email.

🔁 Power Automate: The Orchestration Layer

All automation logic is packaged neatly inside a Power Platform Solution, including:

The cloud flow
All environment variables
Script references

The flow:

Triggers when a new Form response arrives
Inserts a row into Excel
Executes the Office Script
Uses the returned status to determine the next step

When does the email send?

Complete → All submissions received
DeadlineReached → Deadline passed, even if incomplete
InProgress → Do nothing yet

This is far more reliable than manually checking counts or timestamps.

📦 Easy Deployment: Solution ZIP with Environment Variables

To make the solution reusable and tenant-independent, it is distributed as a Power Platform Solution.

When you import it, you only provide:

Microsoft Form ID
Excel file path
Table name
Office Script ID

No flow rebuilding
No JSON editing
No hardcoded paths
No fragile connections

Just import → configure → run.

▶️ How to Use the Solution

Upload the Excel template to SharePoint or OneDrive
Import the Solution ZIP via Power Apps → Solutions
Provide Environment Variables
Reconnect connectors
Run a test submission

You will see:

Excel updates
Script calculates status
Email triggers only at the correct time

It is designed to be simple, predictable, and robust.

📨 Example Output

The email sent to the top-ranked instructor includes:

Their name
Their score
Whether the deadline was reached or all submissions received
Clean, professional HTML formatting

This makes the process transparent and elegant.

🛠 Optional Enhancements

Some future improvements you can explore:

CC to program coordinator
Multi-instructor average scoring
Power BI leaderboard
Teams notification instead of email
Dataverse-based storage
Multi-form consolidation

The solution is modular, so enhancements are easy.

🤝 Community Impact

This project was created because the community kept asking for it:

“How do I wait until all Forms responses are in?”
“How do I send an email only when evaluation is complete?”
“How can I calculate rankings automatically?”
“How can I package flows properly for others to use?”

This project solves all of these issues in a clean, reusable manner, following Microsoft best practices like:

Power Platform Solutions
Environment Variables
Office Scripts for Excel automation
Event‑driven flows

Anyone can now download the ZIP, configure a few values, and immediately run the automation.

🙌 Conclusion

This solution is designed to:

Save time
Ensure accuracy
Improve reporting
Eliminate manual steps
Provide a standardized approach for instructor evaluations

Whether you are a trainer, an HR team, a technical lead, or a community contributor, this pattern can serve as a blueprint for many similar scenarios.

🎁 Download, try it out, and adapt it to your own needs:

👉 https://github.com/spashikanti/SunilP-FormsToExcel-TopRankEmailer/releases/latest

👨‍💻 Author

Sunil Kumar Pashikanti
Principal Architect | Power Platform Community Contributor
🌐 https://sunilpashikanti.com
📝 Blog: http://sunilpashikanti.blogspot.com

Automating Instructor Evaluations with Microsoft Forms, Excel, Office Scripts & Power Automate — A Complete End‑to‑End Solution

🎯 Why This Solution Was Needed

🧩 What the Solution Does

✔ Collect evaluations from Microsoft Forms