How to retrieve data in bulk using Python x Backlog API

Hello.
This
is Kawa from the 7-Tier System Solutions Department.

It's June. When will we have a national holiday called "Rainy Day"?

Speaking of rain, I recently spent some time playing around with the Backlog API, which we use for internal task management, and it seemed like it could help improve efficiency in many ways, so I thought I'd write an article about it as a memo.
It's very convenient because it's easier to obtain data than having to tap around in the GUI.

This article provides sample code to retrieve a list of users, categories, and issues

If you want to do it quickly with curl, please refer to the article below written by Mandai, who is in charge of wild development at our company

Effectively utilize Backlog's API

Usage environment

Microsoft Windows 11 Pro
Python 3.12.2

Preparation

We will issue an API key, confirm the subdomain/project ID, and use the Python requests module, so we will prepare each of these

■ Issuing an API key
After logging in to Backlog, you can issue an API key by going to the profile icon in the upper right corner, then [Personal Settings], then [API]. (This may depend on your account permissions.)
○ Official documentation

■ Check the project ID.
When you navigate to the "Issues" section of the project page, check the subdomain and project ID from the URL and make a note of it.
https://(subdomain)/find/xxx?projectId=(ID)

■ If the requests module
is not installed, pull it using pip.

pip install requests

To get a list of users

If your company uses a management tool like Baklog, account management can be a problem. (It can be quite a hassle to manage various tools while being busy with daily tasks.) If you can
get a list of users in one go, you can see at a glance which accounts are in use and which are not, so let's try retrieving the information by following the instructions in the official documentation.

Official documentation

This code outputs a list of users participating in a specific project to a CSV file

import csv import requests # Get users def fetch_users(api_key, project_id): url = f"https://<subdomain>/api/v2/projects/{project_id}/users" params = { "apiKey": api_key } response = requests.get(url, params=params) if response.status_code == 200: return response.json() else: print("Failed to get user:", response.text) return None # Format json def json_to_csv(json_data): csv_data = [] for item in json_data: user_id = item['id'] user_name = item['name'] csv_data.append([user_id, user_name]) return csv_data # Output to a csv file def main(): api_key = "<APIキーを記載> " project_id = "<Enter your project ID>" users = fetch_users(api_key, project_id) if users: csv_data = json_to_csv(users) with open('users.csv', mode='w', newline='') as file: csv_writer = csv.writer(file) csv_writer.writerow(['id', 'name']) # Output the header row csv_writer.writerows(csv_data) print("User data output to users.csv") else: print("Unable to retrieve user data") if __name__ == "__main__": main()

After execution, it is OK if a file called user.csv is generated in the directory where the code is placed

ID, name
1234, Beyond Taro
5678, Beyond Hanako

To get a list of categories

If you are using customer names as categories, you may want to manage them.
You can get them all at once by simply tweaking the user acquisition code mentioned above, so try using it.

Official documentation

import csv import requests # Get categories def fetch_categories(api_key, project_id): url = f"https://<subdomain>/api/v2/projects/{project_id}/categories" params = { "apiKey": api_key } response = requests.get(url, params=params) if response.status_code == 200: return response.json() else: print("Failed to get categories:", response.text) return None # Format json file def json_to_csv(json_data): csv_data = [] for item in json_data: category_id = item['id'] category_name = item['name'] csv_data.append([category_id, category_name]) return csv_data # Output to csv file def main(): api_key = "<APIキーを記載> " project_id = "<Enter your project ID>" categories = fetch_categories(api_key, project_id) if categories: csv_data = json_to_csv(categories) with open('categories.csv', mode='w', newline='') as file: csv_writer = csv.writer(file) csv_writer.writerow(['id', 'name']) # Output the header row csv_writer.writerows(csv_data) print("Category data output to categories.csv") else: print("Category data could not be retrieved") if __name__ == "__main__": main()

After execution, it is OK if a file called categories.csv is generated in the directory where the code is placed

id,name
123456,ABC Co., Ltd.
654321,DEF Co., Ltd.

Get a list of issues in a specific category

Finally, here is the code to get a list of issues for a specific category (which we use to classify our customers)

There are various items that can be specified for issues, so please feel free to customize them as you like.
Status list

The following code is an example, in which the following conditions are specified to return data:

status_id
created_since
created_until

This time, we also want to specify a period, so we will use the preset datetime.
Note that the created_since/until format in the code is entered manually.

import json import csv import sys import requests from datetime import datetime, timedelta def fetch_issues(api_key, project_id, category_id, status_id, created_since, created_until): url = "https://<subdomain>/api/v2/issues" params = { "apiKey": api_key, "parentChild": 0, # Get all issues including parent issues "projectId[]": project_id, "createdSince": created_since, "createdUntil": created_until, "statusId[]": status_id, "categoryId[]": category_id } response = requests.get(url, params=params) if response.status_code == 200: return response.json() else: print("Failed to fetch issues:", response.text) return None def json_to_csv(json_data): csv_data = [] for item in json_data: created = item['created'] issue_type = item['issueType']['name'] summary = item['summary'] assignee = item['assignee']['name'] if item.get('assignee') else '' # Set an empty string if there is no assignee csv_data.append([created, issue_type, summary, assignee]) return csv_data def main(): api_key = "<APIキーを記載> " project_id = <Enter project ID> category_id = <Enter category ID> status_id = <Status ID> # Specify date and time created_since = "2024-01-01" created_until = "2024-01-31" all_issues = [] seen_issue_ids = set() while True: json_data = fetch_issues(api_key, project_id, category_id, status_id, created_since, created_until) if not json_data: break # Convert json to csv format csv_data = json_to_csv(json_data) # Record the issue ID for item in json_data: issue_id = item['id'] if issue_id not in seen_issue_ids: all_issues.append(item) seen_issue_ids.add(issue_id) # Get the latest created date of the retrieved data and set it as the start_date of the next request if json_data: last_created = json_data[-1]['created'] last_created_date = datetime.strptime(last_created, "%Y-%m-%dT%H:%M:%SZ").date() created_until = (last_created_date - timedelta(days=1)).strftime("%Y-%m-%d") else: break # Output as csv csv_writer = csv.writer(sys.stdout) csv_writer.writerow(['Creation Date', 'Category', 'Content', 'Responsible Person']) # Output the header row csv_writer.writerows(json_to_csv(all_issues)) if __name__ == "__main__": main()

- Example output

Creation date and time, category, content, person in charge
2024-03-08T23:23:50Z, handled, test case, Beyond Hanako
2024-03-08T23:06:50Z, handled, xxx setting request, Beyond Taro

complete

-------------------------------------------------
we use Backlog for collaboration within and between departments, as well as for project management with external companies