はじめに
株式会社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にすると、ヘッダーなしと見なされます。
スプレッドシートのデータをクエリを使用して取得する簡単な例
=QUERY('社員一覧'!A:C, "SELECT A, B")
クエリで取得してきたデータを別シートに出したい場合、データの範囲指定の前に'社員一覧'!のようにシート名と!を追加します。
社員一覧シートのAからC列のデータから、SELECTしたA、B列をQueryシートに出力しています。
Jiraから同期したISSUESを集計してグラフを作成する
では、早速ISSUESのデータを集計してグラフを作成していきましょう。
集計データのイメージ
A列からJ列の以下9項目を利用して、ISSUESを集計していきます。
※ 画面・原因はJiraのカスタムフィールドを利用し追加しました。また、集計に利用しているデータは全てテストデータになります。
| 列 | 名前 |
|---|---|
| A | キー |
| B | 画面 |
| C | 原因 |
| D | 担当者 |
| E | 優先度 |
| F | ステータス |
| G | 作成日 |
| H | 更新日 |
| I | 消費時間 |
| J | 緊急度 |
事前準備
- 最終的にグラフを出すシート
2022-2023年グラフのB1・C2セルに日付範囲が選択できるようにプルダウン項目を設定 - クエリで集計したデータが出力されるシートを作成
- 同一シート内でもクエリ結果を出すことはできますが、どのクエリでどのグラフデータを作成しているか後で判別しやすいようにクエリごとにシートを分けました。
月ごとの問い合わせ件数を出すためのクエリ
=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のカラム名を月とする |
※ 重複する内容は省略しています
クエリで抽出した結果が上記のように表示されます。
グラフ作成
スプレッドシートのメニューバー挿入>グラフを押し、グラフを表示します。
挿入されたグラフの右上にあるミートボールメニューを押下しグラフを編集を押下します。
設定でデータの範囲を月ごとの問い合わせ消費時間で出力したデータ範囲を指定して設定します。
X軸に月のデータ範囲を指定すると、以下のようなグラフが表示されます。
これで、月ごとの問い合わせにかけた消費時間（分）のグラフを表示することができました。
他のクエリ例
では、他のシートにグラフを出すためのクエリを書いていきます。
日付指定×機能ごとの消費時間
=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)
日付指定×原因
=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を簡単に集計することができました。これを利用すると、様々なグラフ結果を作成できプロダクトに役立てることができるかと思います。
ぜひ、利用してみてください！！
最後に
HRBrainでは一緒に働く仲間を探しています。
ぜひ、採用サイトからカジュアル面談の申し込みお願いします🎁🎁🎁