AWS Builder Center
A Serverless Reporting Pipeline with Glue, SharePoint and Adaptive Cards

A Serverless Reporting Pipeline with Glue, SharePoint and Adaptive Cards

Serverless Reporting Pipeline with Glue, SharePoint and Adaptive Cards

Data & Analytics Intern @ Itau Unibanco

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 channel
Splitting 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 requests
from openpyxl import load_workbook
from 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:
  1. Reads its content.
  1. Pulls out the Adaptive Card JSON and the recipient list.
  1. 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.
Any opinions in this article are those of the individual author and may not reflect the opinions of AWS.
Enjoyed reading this content? Let the author know!

Your likes, comments, shares, and saves help creators reach more builders.

Loading recommendations

Loading article