Service Titan API - Pagination in Power Query / Power BI

rich_qua
New Contributor II

I can't seem to get the pagination to work correctly so I can grab all the invoices.  This is in Power Query / Power BI. 

Couple of requirements

1. Call all information till no more records are available

2. Incremental refresh so it only looks for records not present and adds those.

This code loads everything once but If i refresh it, it just keeps loading forever.

 

 

let
    // Define the function to get invoices for a specific page
    fnInvoices = (pageNumber) =>
    let
        Source = Json.Document(Web.Contents(
            "https://api.servicetitan.io/accounting/v2/tenant/xxx/invoices?pageSize=50&pageNumber=" & 
            Number.ToText(pageNumber), 
            [Headers=[Authorization="Bearer " & AccessToken, #"ST-App-Key"="xxx"]]
        )),
        Data = Source[data],
        hasMore = Source[hasMore]
    in
        [Data = Data, hasMore = hasMore],

    // Generate the list of invoices
    Source = List.Generate(
        () => [ pageNumber = 1, result = fnInvoices(1) ],
        each [result][hasMore] <> false,
        each [ pageNumber = [pageNumber] + 1, result = fnInvoices([pageNumber]) ],
        each [result][Data]
    )

 

 

Anyone help?

6 REPLIES 6

rich_qua
New Contributor II

Thanks Jeff.  I figured out a solution.  When I get some time, ill do a write up on how to get PowerBI connected to ServiceTitan.  

Seconding the interest in how to get PowerBI connected. I'm on Aspire, not ServiceTitan, but I figure there will be crossover.

Hi Rich, I would really like to get your guide on this. Is it in the works? 

rich_qua
New Contributor II

Pagesize is limited to 50 records.  I'm looking to import 1000's. 

JeffHerron
New Contributor III

Default pageSize is 50, but I have successfully set it as high as 3000 on some API calls.
https://developer.servicetitan.io/api-details/#api=tenant-accounting-v2&operation=Invoices_GetList

JeffHerron
New Contributor III

Have you reached out to integrations@servicetitan.com? They are terrific.

What I have done in the past is just set the pageSize parameter to be large enough to get everything I am expecting to be returned in one call. I know this is not always possible, but it might be a quick workaround for you.