如何用PowerShell+Microsoft Graph API写入Excel Online单元格？遇400错误求助

问题概述 #

已在Azure注册应用，拥有clientId和clientSecret，配置了Files.Read.All、Files.ReadWrite.All、Sites.Manage.All等管理员同意的API权限，且应用对Teams频道中的Excel文件具备编辑权限。当前PowerShell脚本可读取Excel文件及单元格B1的值，但执行Patch写入操作时触发400 Bad Request错误。

错误原因排查 #

脚本中存在以下关键问题：

• 变量名不匹配：定义了$excelFileName但筛选文件时误用$fileName，导致无法正确获取文件对象；且未将获取到的文件ID赋值给$fileId，后续请求URI中的$fileId为空。
• JSON序列化深度不足：默认ConvertTo-Json的深度可能不足以正确序列化二维数组结构，导致请求体格式不符合Graph API要求。

修复后的完整脚本 #

# --- Configuration ---

$tenantId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
$clientId = "yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy"
$clientSecret = "qqqqq~qqqqqqqqqqqqqqqq.qqqqq_qqqqqqqqqqq"
$teamId = "zzzzzzzz-zz15-4zzz-9zzz-eazzzzzzzzzz" # Extracted from the team link
$channelId = "19:xyxyxyxyxyxyxyxyae24xyxyxyxyx183@thread.skype" # Extracted from the channel link
$excelFileName = "Information_Point.xlsx"
$worksheetName = "NewHireLog"


# Get Access Token
$body = @{
     grant_type = "client_credentials"
     client_id = $clientId
     client_secret = $clientSecret
     resource = "https://graph.microsoft.com"
}

$response = Invoke-RestMethod -Method Post -Uri "https://login.microsoftonline.com/$tenantId/oauth2/token" -ContentType "application/x-www-form-urlencoded" -Body $body

$accessToken = $response.access_token


# Get Files Folder ID
$response = Invoke-RestMethod -Method Get -Uri "https://graph.microsoft.com/v1.0/teams/$teamId/channels/$channelId/filesFolder" -Headers @{ Authorization = "Bearer $accessToken" }

$driveId = $response.parentReference.driveId
$folderId = $response.id


# List Files in the Folder
$response = Invoke-RestMethod -Method Get -Uri "https://graph.microsoft.com/v1.0/drives/$driveId/items/$folderId/children" -Headers @{ Authorization = "Bearer $accessToken" }
 
$files = $response.value
# 修正变量名：使用$excelFileName替代$fileName
$file = $files | Where-Object { $_.name -eq $excelFileName }
# 新增：将文件ID赋值给$fileId
$fileId = $file.id
 
# Get the value of cell B1
$response = Invoke-RestMethod -Method Get -Uri "https://graph.microsoft.com/v1.0/drives/$driveId/items/$fileId/workbook/worksheets/$worksheetName/range(address='B1')" -Headers @{ Authorization = "Bearer $accessToken" }
  
$cellValue = $response.values[0][0]
  
Write-Output "Current value of B1: $cellValue"
  
# Update the value of cell B1 to "Changed Value"
$updateBody = @{
    values = @(
        @("Changed Value")
    )
}

# 修复：添加-Depth参数确保JSON序列化完整，避免格式错误
$jsonBody = $updateBody | ConvertTo-Json -Depth 10

$response = Invoke-RestMethod -Method Patch -Uri "https://graph.microsoft.com/v1.0/drives/$driveId/items/$fileId/workbook/worksheets/$worksheetName/range(address='B1')" -Headers @{ Authorization = "Bearer $accessToken" } -Body $jsonBody -ContentType "application/json"

# Output the response
$response

额外注意事项 #

• 若工作表名称包含空格或特殊字符，需对URI中的$worksheetName进行URL编码，例如使用[Uri]::EscapeDataString($worksheetName)处理后再拼接URI。
• 确保Excel文件未被其他用户锁定，文件锁定状态会导致写入失败。

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