Connecting Wrike's API with MS Excel and Power BI

 

Update (April 2020): Please note that the Wrike team strongly recommmend using the BI Export functionality that provides a better experience than a manual query writing in the Power BI. Also, please be aware that API requests samples used in this article are taken from Wrike's API v3, while the current version is API v4. You can find the relevant API requests samples in our documentation.

 

This article requires a basic understanding of the security requirements and limitations of Wrike’s API – please read more about Wrike's API authorization here and in this API Community post.

Preface

Microsoft Excel is a popular solution for organizing data. Wrike offers an integration with Excel in the form of XLS import/export, but sometimes the ability to sync your Excel data with Wrike will be useful. Here comes Wrike’s open API, which allows integrations with other applications, and not only web-based ones.

From this post you’ll learn how to get Wrike data directly into Excel. Excel data can be stored and organized in your Excel database, and you will be able to refresh and sync it using the Power Query add-on and Wrike’s API. We’ll also go over the basics of Power Query and its usage in Microsoft apps, such as Excel and Power BI.

Wrike + Excel: How it Works in Technical Terms

Wrike’s API is a RESTful application, which allows you to make HTTP calls to obtain data. It may be used as source for your Excel database (or any other similar tool, which can be integrated with Excel).

MS Excel supports different types of external data sources, which you can read more about here. Recent Excel versions allow you to get data from online databases and other web sources (including the public resources) such as REST API’s (like Wrike's). However, the process of obtaining data from web APIs is not specified through the Excel UI, so I will try to explain how to do that with the help of the Power Query.

What is Power Query?

It’s an extension built on the M-language platform, which is used in MS Excel and Power BI. The M-language requires some additional training, but is easy to understand, especially if you’re already familiar with VBA or SQL.

The interaction with Power Query (PQ) in Excel is organized with the help of the Query Editor. PQ comes with the latest version of Excel (2016) on Windows or may be installed separately.  Power Query  is also part of Excel’s 2016 Get & Transform feature, which allows multiple actions with your data. You can read more about it here: Get and Transform.

Before You Start

  • Read about Power Query
  • Read about authorization in Wrike’s API
  • Ensure that your MS Excel supports Power Query. If it doesn’t, install it as an add-on (Power BI Desktop has PQ included). Important: PQ is not available on Office for Mac currently.

Working with Query Editor in MS Excel

  1. Creating a New Data Source – Blank Query
    To use Power Query and Query Editor we must first add a new data source. Add a new data source from the ‘Data’ tab, ‘Get & Transform’ section. Select ‘New query’ on the ribbon (screenshot), choose from ‘Other sources’ > ‘Blank query’. Once this is done, you will find yourself in the PowerQuery extension, which is normally launched in a separate window.
    Please see the detailed description of Data sources on Microsoft’s Support portal.

  2. Understanding Query Editor
    Power Query offers a wide range of functions (all of them are described in numerous tutorials if you want to learn more). Some functions are related to basic data transformation options, while others are focused on formulas (all summed up here).

    With Query Editor actions that you take to modify the properties of a query, or of data received, are tracked in a list of applied steps. You can switch between these steps, undo changes, or add extra modifications. All changes are reflected in the PQ code (known as Power Query Formula Language, PQFL, formerly called M-language), which is accessible in Advanced Editor. All of your operations with data made from the UI are recorded on the code level as well, and can be modified at any point.

Advanced Query Editor: Building PowerQuery Code to Get Wrike Data

In order for our Excel query to work with Wrike’s API we need to use PQ’s Advanced Editor feature (learn how to use it in this MS Excel guide), which provides direct access to the PQFL code behind your query.

The main reason for using this feature is that we need to indicate HTTP headers (‘Authorization’ headers, to be specific), and pass them with the request to our API data endpoint. PQFL is a great tool for carrying out intricate operations, not pre-built in the Power Query UI. Read the full documentation on PQFL.

Before starting with the Advanced Editor we need to make sure we have a permanent access token, which can be obtained from the Dev console in Wrike. Please see our previous post, which explains more about the permanent token.

In our example we will get information on all Timelogs shared with the user, who is the bearer of the access token. We will use this method from Wrike’s API docs to get the correct endpoint.

Now that we have all of the necessary info, let’s take the final steps.

1. Open your Query in Advanced Editor

2. Paste this line of code into the Advanced Editor:

let
    Source = Web.Contents("https://www.wrike.com/api/v3/timelogs",
[Headers=[#"Authorization"="bearer <your_access_token>"]]),
convertToJson = Json.Document(Source)
in
    convertToJson

This triggers a simple HTTP GET call to the specified /timelogs endpoint with the additional ‘Authorization’ header and parses the JSON response received. Please don’t forget to substitute <your_access_token> with your permanent token.

3. Make sure to click ‘Done’ to close the Advanced Editor window and save changes to our query. Your query should be saved with ‘Source’ and ‘Imported JSON' actions in the ‘Applied steps’ section (screenshot).

4. Once you close the Advanced Editor window you will see your query results parsed in 2 parts: 1) ‘kind’, which signifies the type of endpoint requested (timelogs) and 2) data, which represents a list of requested items (screenshot). Click ‘List’ to get to the data records obtained from Wrike’s API (when you do this an additional step is added to the Applied steps).

5. Use the ‘To Table’ button to convert parsed data into a table format and confirm your settings – I recommend leaving them as is (another step is added to Applied steps section). You will see all your data saved into a single ‘high-level’ column.

6. We are almost done with the preparatory steps – the last step is to expand your column into a multi-column table, with the column names taken from the parsed Wrike parameters. To do this, click the expansion button, select the needed columns and confirm (screenshot).

7. Use ‘Close & Load’ button to get all the data loaded to Excel spreadsheet

Next steps: Working With Obtained Data

Excel is a great tool for working with data and Power Query itself offers multiple options, such as filtering, sorting, and merging data. I recommend to look through the PQ Guide to understand what it can do. The sections “Filter, sort, and group data” and “Shape data in a query” are particularly interesting.   

There are couple more things I personally recommend studying, because I find them quite useful:

  • PQ offers the ability to combine multiple queries and drill down to specific tables, you may read more about it on Microsoft’s pages: Combine Multiple Queries and Drill Down into a Related Table sections.
  • Excel allows you to schedule data refresh on an automatic basis, you may find the controls on the ‘Design’ tab, ‘External Table Data’ section > ‘Refresh’ > ‘Connection properties’ (screenshot). This setting may help you keep your Query data up-to-date and it’s a great way to get changes from Wrike.

Miscellaneous: Constructing Complex Query with URL-encoded Parameters

In our example we walked through constructing a simple Query to Wrike’s API, which used a URL string where all parameters could be encoded. For instance, we could use https://www.wrike.com/api/v3/folders?project’ URL to get a complete Project list .

But what if we need to get some complex parameters, for instance, to obtain the list of tasks with extra ‘fields’ parameters?

In this case the ‘Query’ option for ‘Web.Contents()’ function should help. We can use this piece of PQ Formula Language to get all the tasks shared with the user who got the token.

let
    Source = Web.Contents
(
"https://www.wrike.com/api/v3/tasks",
[
Query=[#"fields"="[sharedIds,dependencyIds,briefDescription,parentIds,superParentIds,
subTaskIds,responsibleIds,description,recurrent,authorIds,attachmentCount,hasAttachments,
customFields,superTaskIds,metadata]"],
Headers=[#"Authorization"="bearer <access_token>"]
]
),
convertToJson = Json.Document(Source)
in
convertToJson

Please, pay attention to the syntax of the ‘Query’ option, it’s similar to ‘Headers’, but the 2nd level parameters need to be passed in [] brackets.

Please note: the output limits of 1000 objects are applied when we use this method, please see this Community post for details.

Integration with other MS tools:  MS Power BI and MS Access

MS Power BI is the new prominent Extract, Transform, Load (ETL) solution from Microsoft, which uses Power Query as the core module for working with data sources. Even though it has a great number of data-source presets to choose from (including Web API’s with the ability to specify HTTP request headers) I could not get it to work via these presets. It seems like like the standard form does not completely support the OAuth2 authorization flow.

However, Power BI has the same option of constructing your own Query, which was examined in this post, therefore, all things covered here are applicable to Power BI.

Considering that Power BI allows integrations with other data sources and provides nice modelling options, the space for experimenting is almost infinite!  Excel gives an option to export to MS Access, which means you may use it as a ‘hub’ between your Wrike account and Access database. That’s not even taking into account that Microsoft is actively improving Power BI and Excel Power Query, adding more and more data-connect options.

I hope this post was helpful to Excel and Power BI fans, please feel free to ask any follow-up questions and share your own Power Query experience.

Pavel M Community Team at Wrike Узнайте о самых популярных функциях Wrike и советах по его использованию

9
Комментариев: 27
Spot On Innovative Approach Stellar Advice
Avatar

Hi Pavel,

Great post! Im trying to get a query to show me the project ID's in one column and the task ID's in the other, but can find no way to associate between the projects and the tasks (other than individually by putting the projectID in the query).

Cheers,

David

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

Hi David! Thank you! 

If I understood you correctly, you would like to have a sort of 'joint' table, there it would be possible to see the attributes for both tasks and projects related to them.

It might be useful to try merging queries in this case. Let's say you will have the 1st query related to /tasks endpoint. I will need to make a 2nd one, to /folders. Combining queries is quite easy, please take a look at this tutorial by MS. If the 'base' table would be the one with tasks, I would advise to use 'parentIds' parameter from /tasks to match with 'id' from folders/projects.


Once you combine the queries you will have a joint table with data on tasks + projects / folders related to them.

Please let me know if that was helpful!

Pavel M Community Team at Wrike Узнайте о самых популярных функциях Wrike и советах по его использованию

1
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

Hi Pavel,

Great, I did not see the optional field "parentIds", now its working!

As a side note: To get it to work I had to use the following for the source:

Source = Web.Contents("https://www.wrike.com/api/v3/tasks?fields=[""parentIds""]"

Note the double quotation marks "" in order to get around the quotation marks not being recognised :)

Cheers,

David

 

1
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

David, that's awesome! 

Curious about the syntax for the fields - you opted to include the parameter in URL and it looks like a great tip to make them work from there!

My solution was to add them using the Query option, should be working as well:

Query=[#"fields"="[parentIds]"]

Would your way work with multiple parameters in a single query?

Pavel M Community Team at Wrike Узнайте о самых популярных функциях Wrike и советах по его использованию

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

Hi Pavel,
Thanks for the information.
I am trying to sync Excel with one of my reports in Wrike. I am using PowerQuery code (in advance query option). The code that I used is

let
    Source = Web.Contents("https://www.wrike.com/workspace.htm#path=reports-new&reportId=18709213",
[Headers=[#"Authorization"="bearer <My_Wrike_Permanent_API_Access_Token>"]]),
convertToJson = Json.Document(Source)
in
    convertToJson

The error that I am getting is: -

Expression.Error: The 'Authorization' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, If-Modified-Since, Prefer, Referer.

Kindly suggest, what to do.

Regards,
Sahil 

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar
0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

Hi Pavel,

I am able to connect wrike directly with excel desktop and power BI desktop. But its not getting refresh directly on Excel browser and power BI online.
Any suggestions, would be appreciated.

Regards,
Sahil

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

Sahil, sorry to hear that! Do you have any authentication errors for web version when trying to refresh?

Pavel M Community Team at Wrike Узнайте о самых популярных функциях Wrike и советах по его использованию

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

Hi Pavel,

I have created a personal gateway for power bi. It is giving error "Your data source can't be refreshed because the credentials are invalid". Data source credentials I am using are: -
url - "wrike api path"
Authentication method - "Anonymous" (used basic login method as well, where I entered my office 365 userid password using which I login wrike)
but that gave the same error.
Any help is appreciated, Thanks in advance.

Regards,
Sahil

1
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

Hi Sahil! I have created the ticket in Support  for you, I think it might be easier to help you by digging deeper into your case.

Pavel M Community Team at Wrike Узнайте о самых популярных функциях Wrike и советах по его использованию

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

Thanks Pavel. Let me know if any details or information is required from my end.

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

Hi Pavel,

Nice article. Helped me a lot. I found the following when I use this:

Source = Web.Contents("https://www.wrike.com/api/v3/timelogs?trackedDate={""start"":""2017-07-10"",""end"":""2017-07-17""}"

It gives me time log from 10 July to 16 July. I tested the above with different end date and the result is always one day less than mention in "end".

I successfully merged two queries so far to get username for timelog data.

Good work and keep it up.

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

Hello, 

I was using the code you provided in for tasks below to connect with power BI. Is there a limit on how much data it can extract? I have two years worth of task data but only 800 lines came through to the query.

 

let
    Source = Web.Contents
(
"https://www.wrike.com/api/v3/tasks",
[
Query=[#"fields"="[sharedIds,dependencyIds,briefDescription,parentIds,superParentIds,
subTaskIds,responsibleIds,description,recurrent,authorIds,attachmentCount,hasAttachments,
customFields,superTaskIds,metadata]"],
Headers=[#"Authorization"="bearer <access_token>"]
]
),
convertToJson = Json.Document(Source)
in
convertToJson

 

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

This is absolutely true that  Power BI has the same option of constructing your own Query, which was examined in this post, therefore, all things covered here are applicable to Power BI. Wrike offers an integration with Excel, windows 10 error code 0xc00000f helped me to get this.

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

I went through getting the token and pasting it in the advanced editor. Here is my code:

 

let

    Source = Web.Contents("https://www.wrike.com/api/v3/timelogs",

[Headers=[#"Authorization"="bearer <eyJ0d……W-B15Stg66Vfg>"]]),

convertToJson = Json.Document(Source)

in

    convertToJson

 

I used anonymous as my credentials but keep receiving this error : "DataSource.Error: The underlying connection was closed: An unexpected error occurred on a send."

I am not sure how to debug since this is my first API. Any help would be greatly appreciated.

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

Team,  Is there a similar tutorial for connecting through Google Sheets?

 

Thanks

Bill

 

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

I highly recommend Parabola for connecting Wrike and Google Sheets.  

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

Excellent, thank you.  I'll try that.

Bill

 

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

Hi! I need help to connect projects with tasks. We use PowerBi to build our reports. I have the following structure:

When I want to relate task + project or task + folder + project, I am not managing to obtain for each task, to which project it belongs.
I made the relationship with Tasks ParentIds (several) + Project Id (one) but for most of the tasks the project does not bring me, only for a few tasks. 

Would someone be so kind to help me? I'd be really grateful!

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar
Stephen

@all, excited to tell you about our new BI Export. I think everyone here will find this really interesting.

Check it out here - BI Export and Tableau Connector (Enterprise) 👍

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

Hi! Any idea on how I get subtasks to carry over as well into excel

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

Any plans on getting Power BI (the Microsoft version) integration? 

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar
Stephen

@Bec - Tasks and Subtasks are separate. Once your Subtasks are tagged to parent Folder/Projects, they should show up here - let me know if you're still having issues and I'll have our support team take a closer look for you 👍

@John - no out of the box integration with Microsoft planned right now. However, you can use the BI Export to achieve integration with MS. Let me know if you need some helping doing this.

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

Hi Stephen, If you possibly could. They don't seem to be migrating across for us.

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar
Stephen

I've raised a ticket for you now Bec, you'll receive an email confirmation of it soon.

If you could come back after you speak to Support to update the thread with what the issue was, it might help others having the same problem.

Thanks 🙂

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

Hi there!  This article is really helpful, but I have a further question.  I've used the syntax above to get data from folders, tasks, etc, but now I am looking to refine my API calls a bit using the parameters documented in Wrike for Developers. 

I am having trouble with the syntax in Power Query/Power BI for parameters with the type "Object".  For example, I want to retrieve all the tasks that have a particular custom field value.  Based on the documentation, I should be able to use the customField object parameter filtered by a value string.  I am trying variations of [Query=[#"customField"={#"value"="XYZ"}] but with this one it doesn't recognize the value filter.  In other variants it just tells me I'm making a bad request. 

I'd like to also retrieve results based on created or updated date, but again these are parameters with the type "Object."

Thanks so much for your assistance.  

0
Действия с комментариями Постоянная ссылка
Spot On Innovative Approach Stellar Advice
Avatar

@Kate Hi, happy to see you on the Community 🙂

I can create a Support ticket for you if you'd like - please let me know 👍

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

0
Действия с комментариями Постоянная ссылка

Folllowing List for Post: Connecting Wrike's API with MS Excel and Power BI
[this list is visible for admins and agents only]

Didn’t find what you were looking for? Write new post