Excel Web Compatibility for AAD-Protected API Queries

[rhythmatician]

Summary

Excel Desktop successfully connects to our AAD-protected API via Power Query using Organizational Account authentication.
However, Excel Online (Web) fails to refresh the same queries, showing either:

• “Unable to connect to this data source on your behalf. Please reauthenticate…”
  → AADSTS650057: Invalid resource

or

• “Sync.Power Query refresh is not supported for queries combining different data sources.”

This prevents browser users from updating WireSetCerts.xlsx directly in SharePoint — a key workflow for our calibration lab.

---

Current Architecture

Layer	Role	Notes
Excel (Power Query)	Client UI	Users edit WireSetCerts table and run Sync query.
Power Query	Transformation logic	Reads Excel.CurrentWorkbook() for user edits and calls Web.Contents("https://jgiapi.com", ...).
Flask API (https://jgiapi.com)	REST backend	Protected by Azure AD (Entra) via app registration flask-api.
Excel Authentication	AAD (Organizational Account)	Uses delegated tokens via the user’s M365 session.
Hosting	SharePoint + Cloudflare + Flask (local or NGINX reverse proxy)	HTTPS + verified domain jgiapi.com.

---

What Works

✅ Excel Desktop:

• Can combine local tables and AAD-authenticated web sources.
• Privacy levels can be ignored (Ignore Privacy Levels checks for this file).
• Tokens refresh properly under Organizational account.

✅ AAD Domain Verified:

• jgiapi.com TXT record added and verified (MS=ms73199928).
• flask-api registered as a single-tenant Entra application.
• Scope https://jgiapi.com/access_as_user created and authorized for the Power BI Data Refresh client (b52893c8-bc2e-47fc-918b-77022b299bbc).

✅ Microsoft Graph Permissions Already Granted:

• Application permissions:

  • Files.ReadWrite.All
  • Sites.ReadWrite.All
  • (and redundant but harmless) SharePoint Sites.FullControl.All

• Delegated permissions: openid, profile, User.Read, offline_access

• Status: All show Granted for Johnson Gage and Inspection, Inc.

No additional app permissions or admin consent are needed.

---

What Fails

❌ Excel Web:

• Cannot refresh Power Query that mixes:

  • Excel.CurrentWorkbook() (Workbook source)
  • Web.Contents(...) (AAD-protected web source)

Error:

Sync.Power Query refresh is not supported for queries combining different data sources.

Even though both sources are within the same tenant and same AAD session.

---

Root Cause

Excel for Web executes refreshes server-side via Power BI’s Data Refresh service.
That runtime enforces single-source isolation — any query combining Excel.CurrentWorkbook() with an HTTP request is blocked.

Desktop can bypass this via privacy settings; Web cannot.

---

Proposed Solution (Recommended)

✅ Option A — Server-Side Sync via Microsoft Graph (No Premium License)

Move the workbook-reading logic into the Flask API.
Excel → Flask → Graph → SharePoint → DB.
Power Query becomes a single-source web query (Web.Contents → Flask API), which Excel Web supports natively.

---

Flow

1. Excel Web Power Query →
   POST https://jgiapi.com/wire-set-certs/sync-from-sharepoint
   (includes SharePoint file URL + table name)

2. Flask API uses its app-only Microsoft Graph token (via ClientSecretCredential) to:

   • Resolve the SharePoint file.
   • Read table rows (asset_id, wire_roll_cert_number) using Excel Graph API or file download.
   • Compute diffs and apply updates in DB.

3. Flask returns a JSON list:

   [
     {
       "ok": true,
       "status": 204,
       "message": "Updated",
       "asset_id": 123,
       "new_wire_roll_cert_number": "072513D",
       "attempted_wire_roll_cert_number": null
     }
   ]

4. Power Query displays this as a single table → ✅ Excel Web refresh succeeds.

---

Implementation Details

Flask endpoint (example):

from azure.identity import ClientSecretCredential
import requests
from flask import Flask, request, jsonify
from base64 import urlsafe_b64encode

TENANT = "<tenant_id>"
CLIENT_ID = "<app_id>"
CLIENT_SECRET = "<secret>"

def get_graph_headers():
    cred = ClientSecretCredential(TENANT, CLIENT_ID, CLIENT_SECRET)
    token = cred.get_token("https://graph.microsoft.com/.default").token
    return {"Authorization": f"Bearer {token}"}

def encode_share_url(url):
    return urlsafe_b64encode(url.encode()).decode().rstrip("=")

@app.post("/wire-set-certs/sync-from-sharepoint")
def sync_from_sharepoint():
    body = request.get_json()
    share_url = body["shareUrl"]
    table_name = body.get("tableName", "WireSetCerts")

    # Resolve share URL -> driveItem
    r = requests.post(
        f"https://graph.microsoft.com/v1.0/shares/{encode_share_url(share_url)}/driveItem",
        headers=get_graph_headers()
    )
    r.raise_for_status()
    item = r.json()
    drive_id = item["parentReference"]["driveId"]
    item_id = item["id"]

    # Read table rows
    r = requests.get(
        f"https://graph.microsoft.com/v1.0/drives/{drive_id}/items/{item_id}/workbook/tables/{table_name}/rows?$select=values",
        headers=get_graph_headers()
    )
    rows = r.json().get("value", [])
    if not rows:
        return jsonify([])

    header = rows[0]["values"][0]
    idx_asset = header.index("asset_id")
    idx_num = header.index("wire_roll_cert_number")

    changes = []
    for row in rows[1:]:
        vals = row["values"][0]
        asset_id = int(vals[idx_asset])
        num = vals[idx_num].strip()
        changes.append({"asset_id": asset_id, "wire_roll_cert_number": num})

    # Apply updates in DB (placeholder)
    results = []
    for c in changes:
        ok, status, msg = apply_update_if_needed(c["asset_id"], c["wire_roll_cert_number"])
        results.append({
            "ok": ok,
            "status": status,
            "message": msg,
            "asset_id": c["asset_id"],
            "new_wire_roll_cert_number": c["wire_roll_cert_number"] if ok else None,
            "attempted_wire_roll_cert_number": None if ok else c["wire_roll_cert_number"]
        })

    return jsonify(results)

---

Updated Power Query (single-source)

let
  Payload = Json.FromValue([
    shareUrl = "https://jgiquality.sharepoint.com/sites/<site>/Shared%20Documents/WireSetCerts.xlsx",
    tableName = "WireSetCerts"
  ]),
  RespBin = Web.Contents("https://jgiapi.com",
    [ RelativePath = "wire-set-certs/sync-from-sharepoint",
      Headers = [#"Content-Type"="application/json"],
      Content = Payload ]),
  Resp = Json.Document(RespBin),
  Results = Table.FromRecords(
    Resp,
    type table[
      ok=logical, status=number, message=text,
      asset_id=Int64.Type,
      new_wire_roll_cert_number=text,
      attempted_wire_roll_cert_number=text
    ])
in
  Results

---

Why This Fix Works

Problem	Why it breaks	Solution
Excel.CurrentWorkbook() + Web.Contents()	Multi-source mashup → blocked in Excel Web	Move all workbook reading to API
AAD-only API	Excel Web cannot combine delegated + local sources	Use app-only Graph read with single web source
Privacy Levels	Ignored in Desktop, enforced in Web	Web-only query avoids privacy checks

---

Deliverables

• Implement /wire-set-certs/sync-from-sharepoint
• Test Graph integration (app-only token)
• Confirm token includes roles: [Files.ReadWrite.All, Sites.ReadWrite.All]
• Replace existing PQ query with single-source version
• Verify Excel Web refresh success

---

References

• [AADSTS650057: Invalid resource](https://learn.microsoft.com/en-us/azure/active-directory/develop/reference-aadsts-error-codes#aadsts650057)
• [Excel Power Query online refresh limitations](https://learn.microsoft.com/en-us/power-query/connectors/combined-data)
• [Microsoft Graph API — Excel Table Rows](https://learn.microsoft.com/en-us/graph/api/table-list-rows)
• [Client credential flow — Microsoft Identity Platform](https://learn.microsoft.com/en-us/entra/identity-platform/v2-oauth2-client-creds-grant-flow)