调用MS Graph API更新OneDrive Excel遇WAC访问令牌获取失败求助

我正在开发Python脚本，通过MS Graph API更新OneDrive中Excel工作表的A1单元格为'200'。脚本逻辑包含获取访问令牌、创建Excel会话、更新单元格、关闭会话，但创建会话时抛出AccessDenied错误，提示“Could not obtain a WAC access token”。已在应用注册中配置相关权限并授予管理员同意，请求技术帮助。

相关代码 #

from msal import ConfidentialClientApplication
import requests

def get_access_token(client_id, tenant_id, client_secret):
    """
    Authenticate and obtain an access token using MSAL for a confidential client.
    """
    authority = f"https://login.microsoftonline.com/{tenant_id}"
    app = ConfidentialClientApplication(
        client_id,
        authority=authority,
        client_credential=client_secret
    )
    result = app.acquire_token_for_client(scopes=["https://graph.microsoft.com/.default"])
    if "access_token" in result:
        return result['access_token']
    else:
        raise Exception("Failed to acquire access token: " + result.get("error_description", ""))

def create_session(access_token, user_id, file_id):
    """
    Create a session for an Excel file to make changes that persist on a specified user's OneDrive.
    """
    url = f"https://graph.microsoft.com/v1.0/users/{user_id}/drive/items/{file_id}/workbook/createSession"
    headers = {"Authorization": f"Bearer {access_token}", "Content-Type": "application/json"}
    data = {"persistChanges": True}
    response = requests.post(url, headers=headers, json=data)
    if response.status_code == 200:
        return response.json()['id']
    else:
        raise Exception("Failed to create session: " + response.text)

def update_cell(access_token, user_id, file_id, session_id, sheet_name, cell_address, value):
    """
    Update a specific cell in the Excel sheet using a session on a specified user's OneDrive.
    """
    url = f"https://graph.microsoft.com/v1.0/users/{user_id}/drive/items/{file_id}/workbook/worksheets/{sheet_name}/range(address='{cell_address}')"
    headers = {
        "Authorization": f"Bearer {access_token}",
        "Content-Type": "application/json",
        "workbook-session-id": session_id
    }
    data = {"values": [[value]]}
    response = requests.patch(url, headers=headers, json=data)
    if response.status_code != 204:
        raise Exception("Failed to update cell: " + response.text)

def close_session(access_token, user_id, file_id, session_id):
    """
    Close the session after modifications are made to ensure changes persist on a specified user's OneDrive.
    """
    url = f"https://graph.microsoft.com/v1.0/users/{user_id}/drive/items/{file_id}/workbook/closeSession"
    headers = {
        "Authorization": f"Bearer {access_token}",
        "Content-Type": "application/json",
        "workbook-session-id": session_id
    }
    response = requests.post(url, headers=headers)
    if response.status_code != 204:
        raise Exception("Failed to close session: " + response.text)

def main():
    """
    Main function to execute the process.
    """
    CLIENT_ID = 'REMOVED'
    TENANT_ID = 'REMOVED'
    CLIENT_SECRET = 'REMOVED'
    USER_ID = 'REMOVED'
    FILE_ID = 'REMOVED'  # Directly use the known file ID
    SHEET_NAME = 'Sheet1'
    CELL_ADDRESS = 'A1'
    NEW_VALUE = '200'

    try:
        access_token = get_access_token(CLIENT_ID, TENANT_ID, CLIENT_SECRET)
        session_id = create_session(access_token, USER_ID, FILE_ID)
        update_cell(access_token, USER_ID, FILE_ID, session_id, SHEET_NAME, CELL_ADDRESS, NEW_VALUE)
        print("Cell updated successfully!")
        close_session(access_token, USER_ID, FILE_ID, session_id)
        print("Session closed successfully!")
    except Exception as e:
        print(str(e))

if __name__ == "__main__":
    main()

错误信息 #

Failed to create session: {"error":{"code":"AccessDenied","message":"Could not obtain a WAC access token.","innerError":{"date":"2024-05-11T13:23:08","request-id":"89fd141b-9888-4a5d-9aa6-3951878c1c45","client-request-id":"89fd141b-9888-4a5d-9aa6-3951878c1c45"}}}

• 确认应用权限配置：检查Azure应用注册中是否添加了Files.ReadWrite.All或Sites.ReadWrite.All的应用权限，且已完成全局管理员同意。委派权限无法在客户端凭证流（acquire_token_for_client）中生效，必须使用应用权限。
• 验证文件归属与访问权限：确保目标Excel文件属于指定的USER_ID用户，或该用户拥有文件的读写权限。如果文件来自共享库，需确认应用已被授予访问该库的权限。
• 跳过会话直接更新单元格：若创建会话始终失败，可修改更新逻辑，无需创建会话直接修改单元格。示例代码如下：

  def update_cell(access_token, user_id, file_id, sheet_name, cell_address, value):
      url = f"https://graph.microsoft.com/v1.0/users/{user_id}/drive/items/{file_id}/workbook/worksheets/{sheet_name}/range(address='{cell_address}')/values"
      headers = {
          "Authorization": f"Bearer {access_token}",
          "Content-Type": "application/json"
      }
      data = {"values": [[value]]}
      response = requests.put(url, headers=headers, json=data)
      if response.status_code != 200:
          raise Exception("Failed to update cell: " + response.text)
  

  此方法通过PUT请求直接修改单元格值，适合单次简单更新场景，无需依赖WAC会话。
• 检查访问令牌权限：解码获取的access_token（可通过本地JWT解码工具解析），确认scp字段中包含Files.ReadWrite.All或对应文件操作的权限。若权限缺失，重新配置应用权限并重新获取令牌。
• 重试请求排除临时故障：WAC服务偶尔会出现临时不可用，可间隔几分钟后重试创建会话的请求。

内容的提问来源于stack exchange，提问作者user24989978