
A Serverless Reporting Pipeline with Glue, SharePoint and Adaptive Cards
Serverless Reporting Pipeline with Glue, SharePoint and Adaptive Cards
The request that started it
"Can you send this report automatically, every day, to the people who need it?". It sounds simple until you notice what's hiding inside it: the people who need it is not a constant. Someone joins the team, someone leaves, someone wants it differently next quarter. And send it doesn't mean put it on a dashboard it means it has to land somewhere people already live, without anyone having to remember to go check.
For the team I built this for, that somewhere was Microsoft Teams, and the recipient list was an Excel file on SharePoint that the business team owned and edited themselves. Not a config file in a repo. Not an environment variable. A spreadsheet, managed by people who don't deploy code and shouldn't have to.
That one requirement recipients live outside AWS, content lives inside AWS is what shaped everything that follows.
The shape of the pipeline
Two Glue jobs, split by responsibility, with Power Automate handling only the very last step:
Job 1: build the report query the source data → shape it into an Adaptive Card (JSON) → write report.json to S3
Job 2: attach an audience → read recipients.xlsx from SharePoint (Graph API) → read report.json from S3 → merge both into one payload → write the payload to S3 → upload the payload to a SharePoint "outbox" folder (Graph API)
Power Automate deliver it trigger: new file in the SharePoint outbox folder → read the file → post the Adaptive Card to the Teams channelSplitting this into two jobs instead of one wasn't an afterthought it's the whole design. Job 1 knows the data and the report format, full stop. It has no idea who's receiving it, and it doesn't need to. Job 2 knows recipients and delivery, and doesn't care what's inside the card. That boundary means I can change what the report contains without touching who gets it, and change the audience without redeploying a single query. It also made this thing testable: I could run Job 1 alone and just read the Adaptive Card JSON before SharePoint ever entered the picture.
Job 1: turning a query into an Adaptive Card
Nothing unusual here a standard Glue ETL job running Spark SQL against a Glue Data Catalog table. The only twist is what it writes at the end. Instead of Parquet, it writes an Adaptive Card, which turns out to be a very natural fit for a Python job you're just templating a dictionary:
def build_adaptive_card(rows): facts = [ {"title": row["metric"], "value": str(row["value"])} for row in rows ]
return { "type": "AdaptiveCard", "$schema": "http://adaptivecards.io/schemas/adaptive-card.json", "version": "1.4", "body": [ {"type": "TextBlock", "text": "Daily Report", "weight": "Bolder", "size": "Large"}, {"type": "FactSet", "facts": facts} ] }The card gets written straight to S3 as
report.json . Query in, card out that's the entire job. No SharePoint, no recipients, no delivery logic. That narrow scope is exactly why I've since reused build_adaptive_card for a completely unrelated report without touching this job's core logic.Job 2: where SharePoint enters the picture
This is where most of the real complexity lives, and where the Microsoft side takes over.
Reading the recipients. The Excel file sits in a SharePoint document library, and getting it means going through the Microsoft Graph API: fetch the drive item, download its bytes, parse with
openpyxl .import requestsfrom openpyxl import load_workbookfrom io import BytesIO
def get_recipients(access_token, site_id, drive_id, item_path): url = f"https://graph.microsoft.com/v1.0/sites/{site_id}/drives/{drive_id}/root:/{item_path}:/content" resp = requests.get(url, headers={"Authorization": f"Bearer {access_token}"}) resp.raise_for_status()
sheet = load_workbook(filename=BytesIO(resp.content)).active return [row[0].value for row in sheet.iter_rows(min_row=2) if row[0].value]Merging report and audience. Deliberately dumb: pull
report.json from S3, pull the recipient list, wrap them together into one payload, write that back to S3. Handing off to SharePoint. The payload gets uploaded through Graph API to a folder that acts as an outbox:
def upload_to_sharepoint(access_token, site_id, drive_id, folder_path, filename, content_bytes): url = f"https://graph.microsoft.com/v1.0/sites/{site_id}/drives/{drive_id}/root:/{folder_path}/{filename}:/content" headers = {"Authorization": f"Bearer {access_token}", "Content-Type": "application/json"} resp = requests.put(url, headers=headers, data=content_bytes) resp.raise_for_status() return resp.json()That upload is the seam between the two ecosystems. Everything before it is AWS's problem. Everything after it belongs to Microsoft 365.
Power Automate: the last mile, on purpose
I could have skipped Power Automate and called Graph API directly from Job 2 to post into Teams. I didn't, and the reason only became obvious in hindsight: the business team needed to change delivery without needing a Glue deployment.
Power Automate watches the outbox folder. A new file shows up, and the flow:
- Reads its content.
- Pulls out the Adaptive Card JSON and the recipient list.
- Posts the card into the Teams channel (or as individual chats, depending on the flow).
Because that step lives in low-code instead of application code, the team can add a channel, bolt on an approval step, or send a parallel email without engineering being in the loop at all. SharePoint ended up being the seam between "engineering owns this" and "the business owns this," and that seam was worth more than the extra hop it costs in latency.
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article