API Pagination and the 'nextpage' Token

What is the reason for paginating tasks but not the timelog?

Also it isn't the case that we don't have to include additional parameters along with a nextPageToken as stated in step 4. At least I know this is the case with the fields parameter because I took this information for granted and couldn't figure out at first why I was getting an incomplete dataset.

The 1000 task/pagination functionality substantially reduces the utility of this API for us to the point where we can't realistically use it. 

We have a pretty small organization and I've got ~21,000 tasks, so I need to run 21 reports individually to get the next page tokens. Why can't I just get all of the tokens in one pull?

Plus, manually pulling the next page tokens also means the list has to be manually monitored and kept up to date as the task count increases over time. I don't want to have to have someone babysitting task counts every day to make sure the reports are accurate. 

Hi Evan! Understand your concerns, however the limits are applied for a reason, to actually make you separate your queries into smaller parts, this is the requirement to keep our API from overheat. 

With certain efforts of automation on your side renewing page tokens could make the process easy - if you could program your system to look for next page token every time till it's no longer given in response and cycle the queries.

Hi Pavel,

I understand the need to create some bumper rails on system requirements as it relates to data requests, but the caveats of the current method (and your recommendations) put a substantial coding burden on the user to create workarounds instead of designing a system that actually works for it's intended purpose. 

I would love to know the percent of users that have below 1,000 tasks (and therefore wouldn't have to create a workaround). I bet it's not very high. If it is, in fact, the core of your customer base, then we're clearly not the right type of organization for your product. If it's low, then you've developed a product where the majority of users will have create their own workarounds to actually get any value from it?

Said workaround for this wouldn't necessarily be so off-putting if I wasn't already creating workarounds in other parts of this process. I need an output of hours by project/folder. A very basic, core report for a project management system that tracks hours. 

But to get there, I need to combine three separate reports from the timelog method, the tasks method, and the folders/projects method to be able to match time entries, to tasks, to projects/folders. This should not be this complicated. 

As a point of context, the /folders/{folderId}/timelogs report doesn't create the right granularity for what we want to see, which is why I'm going down the tasks route.

Evan, I don't work for Wrike but I gotta say - the pagination for results is standard and considered to be best practice. I accomplished this by just looping while nextpageToken isn't null and adding the new results to my new object. I accomplished this all through a stored procedure. There should be tons of support online to do what you want, but I don't think it's Wrike's fault here. You shouldn't have to have anyone monitoring the task counts.

Since I was able to get what I need a few months ago, maybe I can help you in some way?

Pavel, are there any plans to implement pagination for the timelog? Also I found this statement from step 4 "If you included additional parameters on Step 1, you don’t need to include those here." to be incorrect - if I don't continue to include the parameters querying for specific fields, they won't come through in the next page. Can you comment on this? 

Derek,

Thanks for adding some context. The pagination itself isn't the problematic part for me, but that I can only get the pagination tokens one at a time and then have to run each new page once there is a new page.

I'm not a developer writing an app, just somebody that is trying to get some data to run some reports. My attempts at this are limited by the tools/environment I'm using, so perhaps I'm not the intended audience for this.

The goal for my project is to get the hours data into a google sheets doc to be visualized via Google Data Studio. I've been using Postman to test my URL based API calls and a google sheets add-on made by Supermetrics to pull the URL and then format the json into spreadsheet format to be used as the data source. If I was using something more powerful and actually knew how to write more complex code maybe this wouldn't be such an issue.

Gotcha. You'd probably have to just put the Supermetrics add-on out of the picture for this. I think you'd have to have some script run a recursive function that loops each page and compiles all the data to one big object with all your tasks, and then spits out and XML or JSON file. From there you could either host the file and point Supermetrics at it, or you could manually load the XML file to Google Sheets to get what you want.

If Wrike made it any easier to do so, I'd send you a private message to help you out.

Appreciate your thoughts. 👍 I think we're tabling this project for now, but if/when it comes back up we'll give this a go.

Hi Derek, thanks for jumping in and helping Evan here! 

I am afraid that pagination for Timelogs is not on the roadmap currently, question to you - why would you need that and how could it be helpful? There should be no limit for timelogs to be displayed.

We will also check the behaviour on Step 4 for consistency, thanks to paying my attention to this matter.

Is the Pageination API broken on v4? I am trying to query all tasks I set a pageSize=1000 and add a list of option fields. It returns a list of tasks and a nextPageToken. I submit the same request and now submit the nextPageToken. It returns to me the same list of task over and over, it doesn't seem to be showing the next page as described above, I just see the same page repeatedly.

Thanks for posting Jeffrey. We'll need to take a closer look so I've raised a ticket to Support. They'll be in touch soon by email to help! 👍

Hey Stephen,

I raised a support ticket and the issue has been resolved. I had a bug in my code so the nextPageToken wasn't getting cleared out on the last page. It is working now.

Perfect! Glad to hear it's sorted Jeffrey 👌

Hi.

Can someone please tell me if the API pagination mechanism has changed between V3 and V4.

We use the API in Xplenty. Xplenty formally support V3 of the API. Maybe they have some work to do to support V4.

I've satisfied myself that the paging works using V3 of the API in Xplenty, but not V4; I only get a single page of tasks via V4.

Thanks in advance for you help,

Mark F.

@Mark - nothing has changed in terms of the API pagination mechanism in V4 so the same approach can be taken for this.

What API call are you using so we can better understand what's happening here?

Hi @Stephen thanks for your response.

If we use API call https://www.wrike.com/api/v4/folders/IEAAWVOMI4EI42D4/tasks?fields=%5B%22briefDescription%22%2C%22parentIds%22%2C%22responsibleIds%22%2C%22description%22%2C%22authorIds%22%2C%22attachmentCount%22%2C%22hasAttachments%22%2C%22customFields%22%5D&status=Active&authors=%5BKUAC2HO5%5D&customStatuses=%5BIEAAWVOMJMAAMHN6%5D&pageSize=500 via the Xplenty API mechanism, we only get 500 tasks returned. I assume it is the first 500 tasks (I haven't dug this deep to confirm this yet).

However, if we simply change the API call to use V3 API, we get all the tasks we are looking for.

@Derek Hyde and @Pavel M Do you have an example on how to "automate" the nextpageToken request? That would be super helpful! thank you!

Are you supposed to use the same token from the ?pagesize=1000 response for all calls? Or do you use the nextPageToken returned with each call for the next?

Also, does anyone have working code for Power Query/Power BI that they can share?

You use the token returned with each call. It is different. If you use the same token you will get the same page over and over.

@Jeffrey Thanks for the response.

Any working code that can be shared? I'm having a hard time getting this to work right.

I can't share the code since it is owned by my employer. But I wrote my integration in Golang which probably isn't what people are looking for anyway. I did my testing with Postman, so you should be able to test the APIs with postman or curl.

Hi @Pavel,

I am fetching data with pagination already. It is working fine. I'd like to know if there is a limit of time to request a specific page. In other words, a time for the "nextPageToken" to expire. For example, if I make a first request and decide to get a second page after a while (after seconds, minutes, etc), how long can I wait? 

If I make a first request, for Task fetch, for instance, and a new Task is created before the retrieval of the last page or a similar situation, can I consider that this last created Task will be available on the last page or I should make a new request from the beginning (1st page)? Also, is there a way to retrieve a specific page that isn't necessarily the next page?

Thank you.

Hi @Rodrigo, welcome to the Community, happy to see you here 🙂

We'll need to take a closer look, so I'm raising a Support ticket for you - someone from the team will reach out to you soon to help 👍

Lisa Community Team at Wrike Wrike Product Manager Become a Wrike expert with Wrike Discover

Lisa Wrike Team member Become a Wrike expert with Wrike Discover

@Jim Briggs this blog provides a good guide:

https://sqlcodespace.blogspot.com/2017/09/power-bipower-query-api-response_17.html

I hope it helps.

If someone want to get all tasks, this code worked for me on powerQuery. Please, take in consideration that I'm not expert on the topic, just colect information from this post and made it work. I sure there should be a better way.

Thanks to all that colaborated on this post.

let

  ufnGetList = (pgToken) =>
    let
      content = Web.Contents("https://www.wrike.com/api/v4/tasks", [Query=[#"descendants"="TRUE",#"pageSize"="1000",#"nextPageToken"=pgToken], Headers=[Authorization="bearer KEY"]]),
      result = Json.Document(content),
      nextPageToken = try result[nextPageToken] otherwise null,
      data = result[data],
      record = [data = data, nextPageToken = nextPageToken]
    in
      record,

  resultSet = List.Generate(
    () => ufnGetList(""),
    each _[nextPageToken] <> null,
    each ufnGetList(_[nextPageToken]),
    each [nextPageToken = _[nextPageToken], data = _[data]]
  ),

  lastPageToken = try List.Last(Table.FromRecords(resultSet)[nextPageToken]) otherwise "",
  lastResultSet = ufnGetList(lastPageToken)[data],
  convertLastResultSet = Table.FromList(lastResultSet, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  expandLastResutSet = Table.ExpandRecordColumn(convertLastResultSet, "Column1", {"id", "accountId", "title", "status", "importance", "createdDate", "updatedDate", "completedDate", "dates", "scope", "customStatusId", "permalink", "priority"}, {"id", "accountId", "title", "status", "importance", "createdDate", "updatedDate", "completedDate", "dates", "scope", "customStatusId", "permalink", "priority"}),

  tableFromRecords = Table.FromRecords(resultSet)[data],
  tableFromList = Table.FromList(tableFromRecords, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  expandColumn = Table.ExpandRecordColumn(Table.ExpandListColumn(tableFromList, "Column1"), "Column1", {"id", "accountId", "title", "status", "importance", "createdDate", "updatedDate", "completedDate", "dates", "scope", "customStatusId", "permalink", "priority"}, {"id", "accountId", "title", "status", "importance", "createdDate", "updatedDate", "completedDate", "dates", "scope", "customStatusId", "permalink", "priority"}),

  fullResultSet = try Table.Combine({expandColumn, expandLastResutSet}) otherwise expandLastResutSet

in

  fullResultSet

Thanks so much for sharing, Roberto VALENTE DA SILVA!

Lisa Community Team at Wrike Wrike Product Manager Become a Wrike expert with Wrike Discover

Lisa Wrike Team member Become a Wrike expert with Wrike Discover

Thank you Roberto! That worked like magic in Excel. I was getting lost as I couldnt figure out how to get the parentID's and customfields to show. But then figured I just needed to add in the query parameters and then record the steps after the initial complete data is loaded.

Hi Shoten Deshmukh, any chance you can share the code that includes the CustomFields? 🙂

Hi Jeremiah de Jesus,

This may help you

let 
  Source = Json.Document(Web.Contents("https://www.wrike.com/api/v4/spaces/IEABAEQXI4KZQGFR/folders", 
               [Query=[#"descendants"="TRUE",#"project"="TRUE",#"fields"="[customFields]"],
              Headers=[Authorization="bearer KEY"]])),
    data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "accountId", "title", "createdDate", "updatedDate", "description", "sharedIds", "parentIds", "childIds", "scope", "permalink", "workflowId", "customFields", "project"}, {"id", "accountId", "title", "createdDate", "updatedDate", "description", "sharedIds", "parentIds", "childIds", "scope", "permalink", "workflowId", "customFields", "project"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"accountId", "scope", "childIds", "parentIds", "sharedIds"}),
    #"Expanded project" = Table.ExpandRecordColumn(#"Removed Columns", "project", {"authorId", "ownerIds", "customStatusId", "startDate", "endDate", "createdDate", "completedDate"}, {"authorId", "ownerIds", "customStatusId", "startDate", "endDate", "createdDate.1", "completedDate"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded project",{"authorId", "createdDate.1"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"startDate", type date}, {"endDate", type date}, {"completedDate", type datetimezone}, {"updatedDate", type datetimezone}, {"createdDate", type datetimezone}}),
    #"Extracted Values" = Table.TransformColumns(#"Changed Type", {"ownerIds", each try Text.Combine(List.Transform(_, GetName), ";") otherwise null, type text}),
    #"Invoked Custom Function" = Table.AddColumn(#"Extracted Values", "WorkFlow", each try GetStatus([customStatusId]) otherwise null),
    #"Removed Columns2" = Table.RemoveColumns(#"Invoked Custom Function",{"customStatusId"}),
    responsability = Table.AddColumn(#"Removed Columns2", "Responsability", each try GetCustomField([customFields], "IEABAEQXJUAASBIS") otherwise null),
    projectID = Table.AddColumn(responsability, "ProjectID", each try GetCustomField([customFields], "IEABAEQXJUABP35Q") otherwise null),
    location = Table.AddColumn(projectID, "location", each try GetCustomField([customFields], "IEABAEQXJUAAR4TC") otherwise null),
    cost = Table.AddColumn(location, "cost", each try GetCustomField([customFields], "IEABAEQXJUAASTJU") otherwise null),
    #"Changed Type1" = Table.TransformColumnTypes(cost,{{"cost", Int64.Type}})
  in  
    #"Changed Type1"

FUNCTION GetCustomField

let
    Source = (lst as list, id as text) as text => let 
        #"Converted to Table1" = Table.FromList(lst, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "value"}, {"id", "value"}),
        #"Filtered Rows" = Table.SelectRows(#"Expanded Column2", each [id] = id),
        //#"Removed Columns3" = Table.RemoveColumns(#"Filtered Rows",{"id"})
        result = #"Filtered Rows"{0}[value]
    in
        result
in
    Source

FUNCTION GetName

let
    Source = (id as text) as text => let 
      Source = Web.Contents("https://www.wrike.com/api/v4/contacts",
      [Headers=[Authorization="bearer KEY"]]),
      convertToJson = Json.Document(Source),
      #"Converted to Table" = Record.ToTable(convertToJson),    
      Value = #"Converted to Table"{1}[Value], 
      #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),  
      #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "firstName", "lastName", "type", "profiles", "avatarUrl", "timezone", "locale", "deleted"}, {"id", "firstName", "lastName", "type", "profiles", "avatarUrl", "timezone", "locale", "deleted"}),   
      #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"deleted", type logical}}),
      #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([id] = id)),
      fullname = Table.AddColumn(#"Filtered Rows", "fullName", each [lastName]&", "&[firstName]),
      result = Table.SelectColumns(fullname,{"fullName"}),
      xxx = Table.FirstValue(result)
    in
      xxx
in
    Source

FUNCTION GetStatus

let
    Source = (id as text) as text => let 
      Source = Web.Contents("https://www.wrike.com/api/v4/workflows",
      [Headers=[Authorization="bearer KEY"]]),
      convertToJson = Json.Document(Source),
        data = convertToJson[data],
        #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "standard", "hidden", "customStatuses"}, {"id", "name", "standard", "hidden", "customStatuses"}),
        #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each [id] = "IEABAEQXK4A3OY24"),
        customStatuses = #"Filtered Rows"{0}[customStatuses],
        #"Converted to Table1" = Table.FromList(customStatuses, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "name", "standardName", "color", "standard", "group", "hidden"}, {"id", "name", "standardName", "color", "standard", "group", "hidden"}),
        #"Filtered Rows1" = Table.SelectRows(#"Expanded Column2", each [id] = id),
        result = Table.SelectColumns(#"Filtered Rows1",{"name"}),
        xxx = Table.FirstValue(result)
    in
        xxx
in
    Source