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
Query
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.
Fields
The last parameter is to define which fields should be displayed and in which order they should be displayed. If you want to access subfields of an object (e.g. name of the milestone), the dot “.” can be used like in JavaScript.
The individual fields are separated by commas.
project_id,iid,title,author.name,milestone.title,milestone.due_date
You can find the complete script here: https://gitlab.com/TobiSell/gitlab-issues-on-google-sheets
this is so great script and usage, thanks. is in GitLab API any limitiations to received just 20 issues? if yes, how did you solve this?
Thanks for the feedback. It could be solved by implementing a paging algorithm, which i haven’t done yet.
To solve this, i would have to implement paging in the script
Hi, to get more than 20 issues try to use ‘pagination=keyset’,
return _gitlabIssues(‘https://’+SERVER+’/api/v4/groups/’+encodeURIComponent(group)+’/issues’+’?’+ ‘pagination=keyset’+ params, fields);
and to call the function
=gitlabGroupIssues(group,”&per_page=60&order_by=created_at&sort=desc&with_labels_details=true”,fields)
I cant fetch Labels names – maybe smth wrong with JSON parsing ?