33
9

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

HRBrainAdvent Calendar 2023

Day 6

Jira Cloud for SheetsとスプレッドシートのQuery関数を使用してJira ISSUESを集計する②

Last updated at Posted at 2023-12-05

はじめに

株式会社HRBrain 入中です。

2023年年12月2日に記述したJira Cloud for SheetsとスプレッドシートのQuery関数を使用してJira ISSUESを集計する①で投稿した記事の第二弾になります🎁🎁🎁

前回の記事のまとめ

Jira Cloud for SheetsとスプレッドシートのQuery関数を使用してJira ISSUESを集計する①では、Jira Cloud for Sheetsのインストールの方法と、JiraのISSUESをスプレッドシートに同期する方法を記載しました。
この記事では、スプレッドシートに同期したJiraのデータをQuery関数を使用して集計していく方法を記載していきます。

GoogleスプレッドシートのQuery関数

Query関数はスプレッドシートデータ全体に対するクエリを実行する関数です。

構文

=QUERY(data, query, [headers])

data

クエリを実行するセルの範囲で、A2:C20のように指定します。
指定できる値はブール値、数値(日付/時刻など)、文字列になります。

query

クエリ言語リファレンスに記載された構文を使用しSQLのような構文を記述します。クエリの条件を指定します。

headers

クエリの結果にヘッダーが含まれている場合、TRUEに設定します。省略するかFALSEにすると、ヘッダーなしと見なされます。

スプレッドシートのデータをクエリを使用して取得する簡単な例

スクリーンショット 2023-11-27 0.10.22.png

example.sql
=QUERY('社員一覧'!A:C, "SELECT A, B")

クエリで取得してきたデータを別シートに出したい場合、データの範囲指定の前に'社員一覧'!のようにシート名!を追加します。

社員一覧シートのAからC列のデータから、SELECTしたA、B列をQueryシートに出力しています。

Jiraから同期したISSUESを集計してグラフを作成する

では、早速ISSUESのデータを集計してグラフを作成していきましょう。

集計データのイメージ

スクリーンショット 2023-11-28 18.07.42.png

A列からJ列の以下9項目を利用して、ISSUESを集計していきます。
※ 画面・原因はJiraのカスタムフィールドを利用し追加しました。また、集計に利用しているデータは全てテストデータになります。

名前
A キー
B 画面
C 原因
D 担当者
E 優先度
F ステータス
G 作成日
H 更新日
I 消費時間
J 緊急度

事前準備

  • 最終的にグラフを出すシート2022-2023年グラフのB1・C2セルに日付範囲が選択できるようにプルダウン項目を設定
  • クエリで集計したデータが出力されるシートを作成
    • 同一シート内でもクエリ結果を出すことはできますが、どのクエリでどのグラフデータを作成しているか後で判別しやすいようにクエリごとにシートを分けました。

スクリーンショット 2023-11-28 18.25.13.png

月ごとの問い合わせ件数を出すためのクエリ

monthlySpentTime.sql
=query(
  '過去1年分の問い合わせチケット'!A:J,
  "SELECT MONTH(G)+1,SUM(I)/60 WHERE G >= date '"
    &TEXT('2022-2023年グラフ'!B1, "yyyy-mm-dd")
    &"' AND G <= date '"&TEXT('2022-2023年グラフ'!C1, "yyyy-mm-dd")
    &"' GROUP BY YEAR(G),MONTH(G) LABEL MONTH(G)+1 '月', SUM(I)/60 '月間消費時間'",
  1
)

クエリ内容説明

クエリ内容 説明
'過去1年分の問い合わせチケット'!A:J 過去1年分の問い合わせチケットのA列からJ列をデータ集計の範囲として指定
`MONTH(G)+1 G列の作成日を集計結果の一列目に表示
YEAR(G)にするとを表示
SUM(I)/60 I列の消費時間(秒表示)を60で割って分表示にして集計結果二列目に表示
SUMは指定した列(I)の数値を合算
WHERE G >= date '"&TEXT('2022-2023年グラフ'!B1, "yyyy-mm-dd")& 2022-2023年グラフシートのB1セルで選択した日付よりG列の作成日が大きいデータを取得
TEXT 指定した表示形式に従い、数値をテキストに変換し参照
& 文字列を連結する
GROUP BY 指定した項目をグルーピング
LABEL MONTH(G)+1 '月' SELECT句で選択したMONTH(G)+1のカラム名をとする

※ 重複する内容は省略しています

スクリーンショット 2023-11-28 18.42.02.png

クエリで抽出した結果が上記のように表示されます。

グラフ作成

スプレッドシートのメニューバー挿入>グラフを押し、グラフを表示します。
挿入されたグラフの右上にあるミートボールメニューを押下しグラフを編集を押下します。
設定でデータの範囲を月ごとの問い合わせ消費時間で出力したデータ範囲を指定して設定します。
X軸にのデータ範囲を指定すると、以下のようなグラフが表示されます。

スクリーンショット 2023-11-28 23.08.23.png

これで、月ごとの問い合わせにかけた消費時間(分)のグラフを表示することができました。

他のクエリ例

では、他のシートにグラフを出すためのクエリを書いていきます。

日付指定×機能ごとの消費時間

spentTime.sql
=QUERY('過去1年分の問い合わせチケット'!A:J, "SELECT B, (SUM(I) / 60) WHERE G >= date '" 
& TEXT('2022-2023年グラフ'!B1, "yyyy-mm-dd") & "' AND G <= date '" 
& TEXT('2022-2023年グラフ'!C1, "yyyy-mm-dd") 
& "' GROUP BY B LABEL (SUM(I) / 60) '消費時間(分)'", 1)

日付指定×原因

cause.sql
=query(
  '過去1年分の問い合わせチケット'!A:J,
  "SELECT B,C,A,J,I/60 WHERE G >= date '"&TEXT('2022-2023年グラフ'!B1,
  "yyyy-mm-dd")&"' AND G <= date '"&TEXT('2022-2023年グラフ'!C1, 
  "yyyy-mm-dd")&"' LABEL I/60 '消費時間(分)'",
  1)

まとめ

GoogleのスプレッドシートのQuery関数を利用して同期したJira ISSUESを簡単に集計することができました。これを利用すると、様々なグラフ結果を作成できプロダクトに役立てることができるかと思います。
ぜひ、利用してみてください!!

スクリーンショット 2023-11-28 23.17.14.png

最後に

HRBrainでは一緒に働く仲間を探しています。
ぜひ、採用サイトからカジュアル面談の申し込みお願いします🎁🎁🎁

参考: Google スプレッドシートの関数リスト

33
9
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
33
9

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?