Timeout issue with Excel JavaScript WebQuery to NetSuite report

36 Views Asked by At

I have an issue with an Excel WebQuery to a NetSuite report timing out, it times out at about 90,000 records and there are 100,002 records in the report.

This is my working query that times out:

let
    Source =
    ()=> Web.Page(Web.Contents("https://{redacted_account_num}.app.netsuite.com/app/reporting/webquery.nl?compid={redacted_compid}&entity=139057&email={redacted_email}&role=1189&cr=1030&hash={redacted_hash}")),
    Delay = Function.InvokeAfter(Source, #duration(0,0,3,0)),
    Data0 = Delay{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Document Number", type text}, {"Police QID", type text}, {"Police Role", type text}, {"Police Rank", type text}, {"Date Created", type date}, {"BAS SLA Info", type text}, {"Qty Shipped", type text}, {"BAS Category", type text}, {"Cooneen Size", type text}, {"Gender/Size/Length", type text}, {"Item", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","=","",Replacer.ReplaceText,{"Qty Shipped"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Qty Shipped", Int64.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Document Number", "Police QID", "Police Role", "Police Rank", "Date Created", "BAS SLA Info", "BAS Category", "Cooneen Size", "Gender/Size/Length", "Item", "Qty Shipped"})
in
    #"Reordered Columns"

This is my crude attempt at paginating through the results to attempt to extract smaller amounts of data at a time to avoid the time-out, my paginated code simply times out and returns nothing.

let
    // Function to fetch a single page
    FetchPage = (offset as number, limit as number) as table =>
        let
            Url = "https://{redacted_account_num}.app.netsuite.com/app/reporting/webquery.nl?compid={redacted_compid}&entity=139057&email={redacted_email}&role=1189&cr=1030&hash={redacted_hash}&limit=" & Text.From(limit) & "&offset=" & Text.From(offset),
            Source = ()=> Web.Page(Web.Contents(Url)),
            Delay = Function.InvokeAfter(Source, #duration(0,0,3,0)),
            Data0 = Delay{0}[Data],
            PromotedHeaders = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
            ChangedType = Table.TransformColumnTypes(PromotedHeaders,{{"Document Number", type text}, {"Police QID", type text}, {"Police Role", type text}, {"Police Rank", type text}, {"Date Created", type date}, {"BAS SLA Info", type text}, {"Qty Shipped", type text}, {"BAS Category", type text}, {"Cooneen Size", type text}, {"Gender/Size/Length", type text}, {"Item", type text}}),
            ReplacedValue = Table.ReplaceValue(ChangedType,"=","",Replacer.ReplaceText,{"Qty Shipped"}),
            ChangedType1 = Table.TransformColumnTypes(ReplacedValue,{{"Qty Shipped", Int64.Type}})
        in
            ChangedType1,

    // Pagination parameters
    PAGE_SIZE = 1000, // Define the number of records per page
    OFFSET_START = 0, // Starting offset
    maxPages = 101, // Define the maximum number of pages to fetch

    // Initialize the first page fetch
    FinalTable = Table.Buffer(FetchPage(OFFSET_START, PAGE_SIZE)),

    // Loop to fetch each page
    Output = List.Generate(
        ()=> [Offset = OFFSET_START, Table = FinalTable],
        each [Offset] < PAGE_SIZE * maxPages,
        each [Offset = [Offset] + PAGE_SIZE, Table = Table.Combine({[Table], FetchPage([Offset], PAGE_SIZE) })]
    ),

    // Get the final combined table
    CombinedTable = Table.Combine(List.Transform(Output, each [Table])),
    
    // Reorder columns in the combined table
    ReorderedColumns = Table.ReorderColumns(CombinedTable, {"Document Number", "Police QID", "Police Role", "Police Rank", "Date Created", "BAS SLA Info", "BAS Category", "Cooneen Size", "Gender/Size/Length", "Item", "Qty Shipped"})
in
    ReorderedColumns

0

There are 0 best solutions below