Automate Excel to REDCap Integration

I collect responses with Microsoft Forms, which in turn stores everything in a Microsoft Excel workbook. Each week I need the newest completed rows pushed into our REDCap project, yet the column names and formats don’t line up one-to-one. I’m looking for a Python solution that fetches the Excel data through an API (Microsoft Graph or another approach you recommend), remaps and reformats each field to match the corresponding REDCap variable, then uploads the records via the REDCap API in a single, repeatable job.

Key points
• Source: one Excel file that grows continually with new form submissions.
• Destination: a live REDCap project with existing instruments and variable names.
• Transformations: rename/match fields, convert dates & times to the exact REDCap format, and apply any look-up values so the data passes REDCap’s validation rules.
• Execution: script should run unattended (Windows Task Scheduler or similar) and produce a concise log of rows processed and any validation errors returned by REDCap.

Deliverables
1. Clean, well-commented Python script (.py) with clear separation between configuration (tokens, project IDs, field map) and main logic.
2. README explaining setup, required Python packages (e.g., pandas, requests, msal), and step-by-step instructions for obtaining the necessary API tokens for both Microsoft 365 and REDCap.
3. A brief test run using a sample subset of my data to confirm correct field mapping and successful upload.

Once the script runs smoothly end-to-end on my side, the project is complete.