Sharing my Power Automate template: Automating the Creation of a Formatted End of Month Report

Susan Walters

I'll be presenting this flow at the 2026 bbdevdays conference. This flow won a prize in the 2025 Hackathon for low-code integration.

EOMReportGeneration_1_0_0_3.zip

Month‑end gift reporting shouldn’t depend on who has the most Excel experience (or who has the most patience). In this session, you’ll see how we use Raiser’s Edge NXT Query, Power Automate, Excel scripts, and Microsoft Teams notifications to generate a clean, formatted, and sorted month‑end gift list for finance and development stakeholders—automatically and consistently.

What used to take 20–30 minutes for an experienced team member (and an hour or more for newer staff) now runs in about five minutes, end‑to‑end. The goal is simple: reduce manual steps, speed up close, and deliver a report your team can trust.

This Power Automate solution automates the monthly finance reconciliation gift report process using Blackbaud Raiser's Edge NXT query data, Excel Scripts, SharePoint, and Microsoft Teams.

The flow retrieves gift records from a predefined RE Query, categorizes and sorts gifts according to business rules, generates subtotals and grand totals, creates a new worksheet in a fiscal year workbook, applies automated formatting, and notifies staff when the report is complete.

The automation replaces a highly manual Excel process that previously required extensive sorting, duplicate cleanup, subtotaling, formatting, and review work. The automated process reduces report generation time from up to 2 hours to under 5 minutes while improving consistency and reducing errors.

Required Connections

• Microsoft Power Automate
• Microsoft Excel Online (Business)
• Microsoft SharePoint
• Microsoft Teams
• Blackbaud Raiser's Edge NXT Connector
• Office Scripts / Excel Scripts

Suggested Skill Level Required to Use This Template

Beginner to intermediate

This template requires:

• Experience with Power Automate arrays and filtering
• Knowledge of Excel Scripts / Office Scripts
• Familiarity with SharePoint-hosted Excel files
• Understanding of Blackbaud Raiser's Edge Queries
• Ability to configure variables, arrays, loops, and JSON structures

Configuration Details

Prerequisites

• A SharePoint document library containing the destination Excel workbook
• An Excel workbook with a fiscal year structure
• Office Scripts enabled in Excel Online
• Access to Blackbaud Raiser's Edge NXT Query Connector
• Appropriate permissions for Power Automate, SharePoint, Excel, and Teams

Required Raiser's Edge Query Fields

The RE Query must include all fields needed by the flow. Example fields include:

• Batch Number
• Batch Description
• Constituent Name
• Gift Date
• Gift Amount
• Gift Subtype
• Fund ID
• Appeal ID
• Campaign ID
• Gift Reference
• Payment Type
• Check Date
• Gift ID
• GL Post Date

Excel Scripts

Two Excel Scripts are required:

• Worksheet creation script
  • Creates a new worksheet
  • Moves worksheet to first position
  • Creates the destination table
• Formatting script
  • Parses JSON from Power Automate
  • Sorts sections internally
  • Applies formatting
  • Formats subtotal and total rows

Flow Configuration

The flow must be updated with:

• SharePoint file locations
• Query IDs
• Fund category mappings
• Gift subtype mappings
• Teams notification recipients
• Worksheet naming conventions

Business Logic

The flow uses:

5 special fund categories

8 donor subtype categories

These mappings can be customized to match organizational requirements.

Category

Automation

(Secondary applicable categories: Data Management, Integration, Notification)

Instructions

Overview

This template automates the generation of a monthly reconciliation report from Raiser's Edge gift data.

Setup Steps

Step 1 — Create the Raiser's Edge Query

Build a gift query containing:

• All required output fields
• Desired date filters
• Any additional reporting fields
• Important:
  Anything used in Power Automate must exist in the query output.

Step 2 — Prepare the Excel Workbook

Create a SharePoint-hosted Excel workbook for the fiscal year.

The workbook should:

• Allow Office Scripts
• Contain at least one existing worksheet
• Include any standard formatting preferences

Step 3 — Add Excel Scripts

Create and save:

• Worksheet setup script
• Report formatting script
• Store both scripts in Excel Online for Business.

Step 4 — Configure the Power Automate Flow

Update:

• SharePoint paths
• Query connector references
• Excel file references
• Table names
• Category filtering logic
• Teams notification settings

Step 5 — Test the Flow

• Run the flow using a small date range first.
• Validate:
• Worksheet creation
• Correct gift categorization
• Subtotals and grand totals
• Excel formatting
• Teams notifications

Operational Notes

The process is intended to run after month-end close.

Blank rows in Excel output require placeholder invisible characters.

Separating Excel Script actions into subflows helps avoid workbook concurrency issues.

Users launching the flow through Teams may require additional permissions configuration.

Expected Outcome

The completed report:

• Is fully sorted and formatted
• Includes section subtotals and grand totals
• Requires minimal manual intervention

Julie Michaelson

I am enrolled in a Microsoft Power Automate course this September in Mountain View, which may be the same one you were referring to. Do you happen to know if they will be offering another Microsoft Power Automate course in the future as well?

Susan Walters

@Julie Michaelson , I'm talking about bbdevdays 2026, the virtual conference. Have fun at the Power Automate course - I learned a lot there!