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

# はじめに

株式会社HRBrain 入中です。

2023年年12月2日に記述した[Jira Cloud for SheetsとスプレッドシートのQuery関数を使用してJira ISSUESを集計する①](https://qiita.com/Iriko666666/private/a924bb64f2dadec9a173)で投稿した記事の第二弾になります🎁🎁🎁

# 前回の記事のまとめ

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

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

[Query関数](https://support.google.com/docs/answer/3093343?hl=ja)はスプレッドシートデータ全体に対するクエリを実行する関数です。

## 構文

```
=QUERY(data, query, [headers])
```

 ### data

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

 ### query

[クエリ言語リファレンス](https://developers.google.com/chart/interactive/docs/querylanguage?hl=ja)に記載された構文を使用しSQLのような構文を記述します。クエリの条件を指定します。

### headers

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

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

![スクリーンショット 2023-11-27 0.10.22.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/421982/281d927b-7d9c-aa94-7881-ff1b33194c88.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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/421982/f66e16c1-642c-7c63-3ceb-889234c14727.png)

A列からJ列の以下9項目を利用して、ISSUESを集計していきます。
※ 画面・原因はJiraの[カスタムフィールド](https://ja.confluence.atlassian.com/adminjiraserver/adding-custom-fields-1047552713.html)を利用し追加しました。また、集計に利用しているデータは全てテストデータになります。

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

## 事前準備

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

![スクリーンショット 2023-11-28 18.25.13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/421982/83934696-e3f6-88a6-185b-b29617e41ef3.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列の`作成日`の`月`を集計結果の一列目に表示<br>YEAR(G)にすると`年`を表示|
|SUM(I)/60|I列の`消費時間`(秒表示)を60で割って分表示にして集計結果二列目に表示<br>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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/421982/9a31dd01-74bd-9326-fc9c-c7c76dea4fce.png)

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

### グラフ作成

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

![スクリーンショット 2023-11-28 23.08.23.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/421982/4dcfa47d-b944-1d27-48ea-958933f42035.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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/421982/bfa2f9ef-8622-e6fc-9945-7ef091ef67ad.png)

# 最後に

HRBrainでは一緒に働く仲間を探しています。
ぜひ、[採用サイト](https://www.hrbrain.co.jp/recruit)からカジュアル面談の申し込みお願いします🎁🎁🎁

参考: [Google スプレッドシートの関数リスト](https://support.google.com/docs/table/25273?hl=ja&ref_topic=9054531&sjid=7081046577132790950-AP)