Update project controlling Excel from Promato hours with Outlook and OneDrive

Quick overview

This workflow monitors an Outlook inbox for project controlling Excel templates, pulls booked hours for the reporting month from a Promato SQL Server database, updates the template in OneDrive with actual person-days and cumulative values, and replies by email with the updated file and a deviation summary.

How it works

1. Triggers every minute when an unread Outlook email arrives with an .xlsx attachment and derives the project key from the filename plus the reporting month from the subject (YYYY-MM) or the last completed month.
2. Looks up matching Promato projects in Microsoft SQL Server, resolves the project selection (single project or combined set), and queries booked time entries for the reporting month.
3. Aggregates Promato booked hours into person-days (hours/8) per person and month and stops with an error reply if no bookings are found.
4. Uploads the Excel template to OneDrive and reads it via Microsoft Graph to detect the controlling sheet and its layout from labels (months, Plan/Ist rows, and optional totals sections).
5. Matches Promato people to template rows, calculates plan-vs-actual deviations with a traffic-light status, and writes the actual values back into the correct cells while keeping existing formulas.
6. If a "Kumulgrundlage" worksheet exists, updates or appends the cumulative month column in a single Microsoft Graph workbook session.
7. Downloads the updated workbook, emails it back to the original sender with a formatted summary (including unmatched people and per-project actuals), marks the source email as read, and deletes the temporary OneDrive copy.

Setup

1. Create Microsoft Outlook OAuth2 credentials with permission to read messages, download attachments, send replies, and mark messages as read.
2. Create Microsoft OneDrive (Microsoft Graph) OAuth2 credentials with permission to upload, edit, download, and delete files in the target drive.
3. Create Microsoft SQL Server credentials with read-only access to the Promato database and update the SQL queries to match your Promato schema, views, and column names.
4. Ensure your Excel template follows the expected layout (month headers in German, Plan/Ist rows per person, a "Summe" section, and optionally a "Kumulgrundlage" sheet) and send it as an attachment named like Projektcontrolling_<Project>.xlsx.
5. Configure the mailbox to monitor and adjust the polling interval if needed before activating the workflow.