Autotask REST API项目查询Power Query调用语法问题求助

问题说明 #

需要调用Autotask REST API获取所有项目数据用于Power BI展示处理，但构造请求URL、筛选条件和返回字段时出现错误，现有三版Power Query代码存在不同问题。

错误分析 #

1. 初始代码问题
   直接请求/Projects/端点，Autotask API默认启用分页，仅能返回第一页数据，无法获取全量项目。

2. IncludeFields代码报错原因

   • URL中JSON格式错误：opportunityID后多了冗余逗号，导致JSON无效
   • Power Query中URL的双引号未转义，触发语法错误（Expression.SyntaxError: Token ',' expected.）
   • 未遵循API查询参数规范，search参数需为合法JSON字符串

3. 更新后代码问题

   • 分页请求时重复携带search参数，Autotask返回的nextPageUrl已包含分页上下文，无需重复传递查询条件
   • 未指定返回字段，导致数据冗余，影响请求性能

API调用规范说明 #

字段规范 #

Autotask Projects实体字段区分大小写，常用字段包括：

• id（项目ID）
• isActive（是否激活）
• projectName（项目名称）
• projectNumber（项目编号）
• status（项目状态）
• opportunityID（关联商机ID）
  所有可用字段参考官方Projects实体文档。

请求URL规范 #

• 基础查询端点：https://webservices4.autotask.net/ATServicesRest/V1.0/Projects/query
• search参数需为合法JSON字符串，支持：
  • filter：筛选条件（如{"op":"gte","field":"id","value":"0"}可获取所有项目）
  • includeFields：指定返回字段，减少数据量

分页规范 #

API默认每页返回50条数据，需通过响应中pageDetails.nextPageUrl获取下一页地址，请求该地址时无需再携带search参数。

修正后的Power Query代码 #

let
    // 配置认证参数
    ApiIntegrationCode = "xxxxxx",
    UserName = "xxxxxx",
    Secret = "xxxxxx",
    headers = [
        #"ApiIntegrationCode" = ApiIntegrationCode,
        #"UserName" = UserName,
        #"Secret" = Secret,
        #"Content-Type" = "application/json"
    ],
    // 构造查询参数：筛选所有项目并指定返回字段
    searchQuery = "{""filter"":[{""op"":""gte"",""field"":""id"",""value"":""0""}], ""includeFields"": [""id"", ""isActive"", ""projectName"", ""projectNumber"", ""status"", ""opportunityID""]}",
    apiUrl = "https://webservices4.autotask.net/ATServicesRest/V1.0/Projects/query",

    // 初始请求
    initReq = try Json.Document(Web.Contents(apiUrl, [Headers=headers, Query=[search=searchQuery]])) otherwise error "首次API请求失败",
    initData = initReq[items],

    // 递归分页获取全量数据
    gatherPages = (currentData as list, nextPageUrl as text) as list =>
        let
            nextReq = try Json.Document(Web.Contents(nextPageUrl, [Headers=headers])) otherwise error "分页API请求失败",
            nextData = nextReq[items],
            combinedData = List.Combine({currentData, nextData}),
            // 检查是否还有下一页
            nextUrl = nextReq[pageDetails][nextPageUrl]
        in
            if nextUrl = null then combinedData else @gatherPages(combinedData, nextUrl),

    // 判断是否需要分页
    fullData = if initReq[pageDetails][nextPageUrl] = null then initData else gatherPages(initData, initReq[pageDetails][nextPageUrl]),

    // 转换为表格
    resultTable = Table.FromRecords(fullData)
in
    resultTable

官方文档参考（中文翻译） #

1. Projects实体文档：包含Autotask Projects实体的所有可用字段、数据类型及字段说明，用于确认可请求的字段范围。
2. REST基础查询调用指南：讲解Autotask REST API的GET查询构造方法，包括筛选条件、字段选择、分页等核心语法。

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