Back in May of this year, I showed how to display data from Gitlab in a Google Sheet using the GitLab API and Google App Scripts. Based on this, a new script has been created in the last few days that I would like to share with you.
This time it’s about querying a whole list of issues via the Api and displaying them in Google Sheets. For example to send them to a customer.
The goal is to create a function like:
=gitlabProjectIssues("myProject", "state=opened&order_by=milestone_due", "iid,title,author.name,milestone.due_date")
How to query gitlab issues
The Gitlab API is very powerful and allows easy integration with other tools. There are two different ways to query issues, both of which I would like to support. One way is to look at the issues of a project and the other way is to query all issues from a group. The latter is very handy if you have multiple projects (e.g. app and server) in separate repositories, but manage them via shared milestones.
Accordingly I created two functions. The two are quite similar in behavior:
=gitlabProjectIssues(PROJECT_ID, QUERY, FIELDS) =gitlabGroupIssues(GROUP_ID, QUERY, FIELDS)
Project or group
The PROJECT_ID and GROUP_ID can be derived from the issue URL of the respective project
- Project issues: https://gitlab.com/gitlab-org/gitlab/-/issues -> gitlab-org/gitlab
- Group issues: https://gitlab.com/groups/gitlab-org/-/issues -> gitlab-org
There are different possibilities for the query, which can also be combined with each other:
- Issue status: state=opened
- Labels: labels=foo
- Milestones: milestone=1.0.0
- Author: author_id
- Order: order_by=milestone_due
A combination would look like this: “state=opened&labels=foo“. A complete list can be found at https://docs.gitlab.com/ee/api/issues.html#list-issues.
The individual fields are separated by commas.
You can find the complete script here: https://gitlab.com/TobiSell/gitlab-issues-on-google-sheets