Exporting your TimeLog data from Azure DevOps to Excel
TechsBCN’s TimeLog for Azure DevOps
Azure DevOps is a great Microsoft tool to manage development projects. In TechsBCN is a day-to-day tool. It’s really useful to keep on the same page business stakeholders, developers, testers, environments, etc. Despite all the advantages in Azure Devops, we missed some features which are not natively supported, specially regarding time tracking.
By default, Azure DevOps only allows users to inform the remaining time and the incremental dedicated time to a particular task. This information is a little bit weak when a further analysis is required on team members dedication, which is a common task performed by project managers or team leaders.
Fortunately, DevOps has an extension management system which allows users to extend the behavior of this tool. There are a lot of vendors (like 7pace or Harvest) which provides several solutions to cover this time tracking lack which DevOps may have. However, most of these tools are not for free and have a lot of functionalities which we don’t really need in TechsBCN.
Taking benefit of our development skills, we decided to create an free tool (for ever), to whom would like to just track the time spent on DevOps: TimeLog for Azure DevOps. This tool is really simple to use and provides the functionality that we (and most probably others) require for meet its time tracking needs.
Exporting data to Excel
As in TechsBCN we know that there must be several people (like project managers, team leaders, etc) who would like to have its time tracking data in an Excel spreadsheet, we have prepared a step-by-step guide to explain how TimeLog data can be exported in Excel. Let’s do it:
1. Create an Azure DevOps Personal Access Token (PAT)
The first step to retrieve data from Azure DevOps in our Excel spreadsheet is to create a Personal Access Token. This token can be created for a maximum of one year and with full access, although in TechsBCN we just recommend to create it just with required permissions (for security). The permissions you will need to follow this guide are listed below:
- Analytics (Read)
- Extension Data (Read)
- Graph (Read)
Let’s go through it step by step:
Go to top Azure DevOps top menu to manage Personal Access Tokens (PATs)
Create a new PAT
Setup your PAT. The maximum expiration period is 1 year. Even though it can be created with “full access”, we recommend to set it just for required scopes (listed above)
Here you have your PAT. It will be visible until you close this window. You can always create new ones if you need it.
2. Set up your Excel spreadsheet
Once you have your PAT, it’s time to setup your Excel spreadsheet. It’s essentially using DevOps API to retrieve data from Time Log for Azure DevOps extension. You will need following URL from where data will be retreived:
https://extmgmt.dev.azure.com/{your-organization}/_apis/ExtensionManagement/InstalledExtensions/TechsBCN/DevOps-TimeLog/Data/Scopes/Default/Current/Collections/TimeLogData/Documents
Let’s go through it step by step:
In Excel, go to “Data” -> “From Web”
Set the URL mentioned above and click “OK”
If this is the first time you’re doing this, you will get this message. Go to “Basic”, paste the PAT you created in the previous step in “Password” field (leave the field “User name” field empty) and click “Connect”
If the connection was successful, you will get this window (Microsoft Power Query Editor). Click on “List” item to drill-down the results obtained from DevOps API
Convert these records in a table clicking on “Transform” -> “To Table” button
On the new window, leave the “Select or enter delimiter” option to “None” and click “OK”
Split resulting records in columns by clicking the button highlighted in the screenshot
You will see a preview of data to be included in Excel spreadsheet. Click on “Close & Load” button
And that’s it! Pretty simple, eh? 
3. Import additional data (for instance, users or tasks)
Keep in mind that TimeLog for Azure DevOps extension is just saving information about time tracked for each user to each task. All the rest of data you may require in your spreadsheet (like names of users, tags in tasks, sprints, etc) must be imported from DevOps. This can be done with exactly the same procedure you have performed in step 2, but reaching different API endpoints.
For instance, you will be able to get additional data from following endpoints:
- Work Items data: https://analytics.dev.azure.com/techsbcn/main/_odata/v3.0/WorkItems?$select=WorkItemId,WorkItemType,Title,State,Area (Analytics scope required in PAT)
- Users data: https://vssps.dev.azure.com/TechsBCN/_apis/graph/users?api-version=6.0-preview.1 (Graph scope required in PAT)
NOTE: please remember that if the Personal Access Token you created is liked to a particular set of permissions, you may need to extend those permissions if you require to access to other scopes of other API endpoints available in Azure DevOps
Conclusion
We hope this guide helps you to import TimeLog for Azure DevOps extension in your Excel spreadsheet. If you require further assistance or there is any misleading information in this guide, please do not hesitate to contact us. We will love you get your feedback