600個のGitHub Issuesを、GASとGitHub API、Webhookを使って棚卸しする

本稿は **VR法人HIKKY Advent Calendar 2024** の 8日目の記事です。

https://qiita.com/advent-calendar/2024/hikky

昨日の記事は [@m2wasabi](https://qiita.com/m2wasabi) さんの [令和の時代に自前メールサーバを立ててJiraにWebhookする](https://qiita.com/m2wasabi/items/6f91c67e74cfd17f8ba9) でした。
悲しみのディスティニーの記録らしいです…！

# 作るもの
Google Apps Script（GAS）と、GitHub API、Webhookを利用して、スプレッドシートに下記のような表を自動更新してくれる仕組みを作ります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/53292a8d-035c-1373-2d13-8330f95caaff.png)

# はじめに
Vket Cloud エンジンの開発のディレクターとして、2週間前にアサインされた岩花と申します！

弊社では、Vket Cloud エンジンという、PCやスマホでアクセス可能なWebメタバースを作成できるエンジンを開発しています。
Vket Cloud エンジンは、Webメタバースを展開するのに必要な機能を取り揃えており、HIKKYが開発しているサービスのうち、下記のサービスは、Vket Cloud エンジンをベースに作成されています。

|名称|イメージ|概要|
|--|--|--|
|[Vket Cloud](https://cloud.vket.com/)|![20241203_164303.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/3c401736-a26a-d30b-50bd-97fbd17e28c5.png)|Webブラウザで動作するマルチプレイ可能なメタバースを作成・公開できるサービス|
|[マイルーム＆ブースメイカー](https://vket.com/)|![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/894a6a46-a0dc-315b-f9d8-be3ee196d436.png)|[バーチャルマーケット](https://event.vket.com/)のブース入稿がWebブラウザから行えたり、メタバース上の自分の部屋を持つことができるサービス|
|[ワールドビルダー](https://scenebuilder.vket.com/)|![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/2273cc94-85b8-8d1e-cd4b-c9e2c9899311.png)|Webブラウザで、Vket Cloudのワールドを作成することができるサービス|

そんなVket Cloud エンジンですが、いろいろなサービスのベースとなっている故、各派生サービスや、クライアント様、ユーザー様からの要望が、どんどんと積まれていきます。それらは、GitHubのIssuesへと集積され、現在は600個ほどOpenなIssueが立てられている状態となっています。

600個もあると、過去に優先度付けられないまま、忘れ去られているIssueも存在しており、それらが取り出すことのできない状態になっていました。

そこで、自分自身がVket Cloud エンジンのタスクを全て把握することためにも、600個すべてのIssuesを棚卸しして、すべてに優先度をつける仕組みを作ることにしました。

# 方針
- プロダクトの価値を最大化するための判断基準を、客観的に判断できるフォーマットにしたい
- 各Issuesに数値でポイントを付けて、定量的に評価できるようにしたい
    - 600個並べて、5段階などで評価するにも、それぞれのIssuesの目的が違うので、段々基準がぶれていきそう…
- 各プロダクトや目指すべき価値ごとに、まとめて優先度を設定できるようにしたい
- 各プロダクトのステークホルダーが納得できるように、わかりやすくしたい

上記方針をもとに、優先度付けのフォーマットの一つであるRICE法をアレンジした手法を用いて、判断付けすることにします。

RICE法は、Reach・Impact・Confidence・Effort（リーチ・インパクト・確度・投下労力）の頭文字からきています。機能やテーマベース法で上げたイニシアチブをこの4項目で評価して、以下の式でスコアリングします。（[原文](https://www.intercom.com/blog/rice-simple-prioritization-for-product-managers/) | [和文](https://note.com/kntrttm/n/ned54aac8b3b9)）

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/0c73b5cf-3a9a-27ca-be83-8c5007df45c1.png)
*[RICE: Simple prioritization for product managers](https://www.intercom.com/blog/rice-simple-prioritization-for-product-managers/)より引用*

判断の方針が決まりましたが、とにもかくにも、各Issueごとポイントを付けていく必要があります。今回は、Spread SheetにすべてのIssuesを並べてポイントを付けられる仕組みを作ることにしました。

また、一度判断した後、メンテナンスされなくなると、振出し戻ってしまうため、GitHub API、Webhookを利用して、可能な限り自動化、メンテナンスコストを下げられるようにします。

# 全体の構成

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/75fa4997-d733-4267-766f-aaed28c5e4e8.png)

- GitHub APIを利用して、Issuesの内容を全て取得して、スプレッドシートを更新する仕組み
- GitHub Webhookを利用して、Issueの作成・更新・削除・再オープンなどをトリガーにして自動更新する仕組み

の二つの仕組みを作成し、スプレッドシートに、stateがopenとなっているIssuesを同期します。

それらをもとに、各Issueが、何割のユーザーにどういった影響を与えるのかを定量的にポイントを付けていくことで、トータルのポイントを算出し、それをIssueの優先度とすることにします。

# GitHub APIを利用して、Issuesの内容を全て取得して、スプレッドシートを更新する仕組み
まずは、GitHub APIを利用して、現在のIssuesをすべて取得する仕組みを作成します。
Apps Scriptを実行するたびに現在の最新の状態となるように、スプレッドシートを追加、編集、削除するようにします。

## 構成
```mermaid
sequenceDiagram
  actor User as ユーザー
  participant Spreadsheet as スプレッドシート
  participant SyncScript as 同期スクリプト（Google Apps Script）
  participant FetchIssuesLibrary as Issues取得ライブラリ（Google Apps Script）
  participant GitHubAPI as GitHub API

  User ->>+ Spreadsheet : 同期ボタンをクリック
  Spreadsheet ->>+ SyncScript : 同期リクエスト
  SyncScript ->>+ FetchIssuesLibrary : GitHub Issues取得要求
  FetchIssuesLibrary ->>+ GitHubAPI : OpenなIssuesの取得をリクエスト
  GitHubAPI -->>- FetchIssuesLibrary : Issuesデータを返却
  FetchIssuesLibrary -->>- SyncScript : Issuesデータを返却
  SyncScript ->> Spreadsheet : データを更新
  opt 新規Issueがある場合
    SyncScript ->> Spreadsheet : 新規Issueを追加
  end
  opt 既存Issueがある場合
    SyncScript ->> Spreadsheet : 更新
  end
  opt スプレッドシートに存在するがAPIにない場合
    SyncScript ->>- Spreadsheet : 該当Issueを削除
  end
  Spreadsheet -->>- User : 同期結果を表示
```

同期スクリプトと、Issues取得ライブラリは、別のApps Scriptのファイルとして作成しています。
これは、GitHubのAPIにアクセスするためのtokenを、全員がアクセスできる場所へ置かないようにするためです。
[個人用アクセス トークンを管理する - GitHub Docs](https://docs.github.com/ja/authentication/keeping-your-account-and-data-secure/managing-your-personal-access-tokens)

別のApps Scriptとして作成したファイルを読み込むためには、Apps Scriptのライブラリ機能を利用します。
[ライブラリ  |  Apps Script  |  Google for Developers](https://developers.google.com/apps-script/guides/libraries?hl=ja)

自分にしか閲覧・編集できない、Apps Scriptを作成し、そのApps Script上で、GitHub APIと連携、Issues取得ライブラリとして保存します。

保存したIssues取得ライブラリを同期スクリプト側で読み込むことによって、全体にGitHubのtoken情報を公開することなく、Issuesをスプレッドシートに同期できる仕組みを作れます。

## Issues取得ライブラリ（Google Apps Script）の作成
### APIにアクセスするために必要な情報の確認と、tokenの発行
GitHubのAPIで、Issuesにアクセスするためには、下記の情報をリクエストに含める必要があります。
- owner
- repo
- token

[issue 用の REST API エンドポイント - GitHub Docs](https://docs.github.com/ja/rest/issues/issues?apiVersion=2022-11-28#list-repository-issues)

#### ownerとrepoの確認
ownerとrepoは、各々のリポジトリのトップのURLから確認することができ、下記画像のような対応関係になっています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/9f012326-6644-fc9f-3076-34c5e6033eed.png)

#### tokenの作成
ハンバーガーメニューから、**Settings > Developer settings > Personal access tokens > Fine-grained tokens > Generate new token**から、新しいtokenを作成するページに遷移します。
[New Fine-grained Personal Access Token](https://github.com/settings/personal-access-tokens/new)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/3e93d483-e2d0-ebd1-1dd9-88606721626b.png)

項目のうち、下記のように設定して、対象のリポジトリのIssuesへアクセスできるようにします。

- Token name*: 任意の名前
- Expiration*: 任意の期間
- Repository access: Only select repositoriesで、対象のリポジトリを選択
- Repository permissions: Issues, Metadataに、Read-onlyを付与

この状態で、ページ最下部の**Generate token**を押下することで、tokenを作成することができます。

作成されたtokenは、遷移先のページからコピーすることができます。一度しか表示されないので、必ずこの時点でtokenを控えておきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/88c67973-fbed-1847-c66c-c7fd7af873f3.png)


### Issues取得ライブラリのコードを作成する
Google Driveに移動し、**右クリック > その他 > Google Apps Script**から、Apps Scriptのファイルを作成することで、自動で編集画面に遷移します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/0d46435b-f997-6347-34e5-f0bfec9bc760.png)

:::note alert 
このApps ScriptにGitHub tokenなどの情報を記載します。
適切な権限設定を行わないまま、以降の設定には進まないでください。
:::

#### スクリプト プロパティにtokenなどの情報を設定する
左側のサイドバーから**プロジェクトの設定**を押下し、設定画面に遷移します。
このページのスクリプト プロパティに、owner, repo, tokenをそれぞれOWNER, REPO, GITHUB_TOKENとして情報を記載します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/3a51339c-5258-40bc-71fe-9dc0b02c4106.png)

スクリプト プロパティは、APIキーなどの情報をスクリプト上に記載することなく、管理することができる機能です。
また、owner, repo, tokenの情報を記載することにより、ChatGPTなどのAIに、気兼ねなくスクリプトの確認をできるようになります（これが一番うれしい）。

スクリプト プロパティを編集するためには、**スクリプト プロパティを編集**を押下することで、プロパティの追加、保存ができるようになります。

[プロパティ サービス  |  Apps Script  |  Google for Developers](https://developers.google.com/apps-script/guides/properties?hl=ja)

#### コード
前準備が長かったですが、左側のサイドバーから**エディタ**を押下し、Scriptを記載していきます。

paramとして、すべてのIssuesを取得するかどうかのオプションを設定できるようにしています。チェックの度に毎回600個のIssuseを取得するのは、時間もかかるし、良心が痛むので…。

returnとして、取得したIssuesから、
- number: issueの連番のID
- title: issueのタイトル
- created_at: issueを作成した時刻
- assignees: 現在アサインされているユーザーを", "区切りで結合する。一人もアサインされていなければ、"No one"とする
- url: issueのURL

を抜き出して、返却するようにします。

```javascript:GetGitHubIssues.gs
const properties = PropertiesService.getScriptProperties();
const GITHUB_TOKEN = properties.getProperty('GITHUB_TOKEN');
const OWNER = properties.getProperty('OWNER');
const REPO = properties.getProperty('REPO');

/**
 * GitHub Issueを取得
 * @param {bool} getAll issuesをすべて取得するかどうか
 * @return {Array<Object>} Issueのリスト
 */
function getOpenGitHubIssues(isGetAllIssues) {
  const issues = [];
  const per_page = 100;
  let page = 1;
  const url = `https://api.github.com/repos/${OWNER}/${REPO}/issues?state=open&per_page=${per_page}`;
  const options = {
    method: 'get',
    headers: {
      'Authorization': `Bearer ${GITHUB_TOKEN}`,
      'Accept': 'application/vnd.github+json'
    }
  };

  if(isGetAllIssues) {
    while (true) {
      const pageUrl = `${url}&page=${page}`;

      const response = UrlFetchApp.fetch(pageUrl, options);
      const data = JSON.parse(response.getContentText());

      if (data.length === 0) {
        break;
      }

      issues.push(...data);
      page++;

      Utilities.sleep(2000);
    }
  } else {
    const response = UrlFetchApp.fetch(url, options);
    const data = JSON.parse(response.getContentText());

    issues.push(...data); 
  }

  return issues.map(issue => ({
    number: issue.number,
    title: issue.title,
    created_at: issue.created_at,
    assignees: issue.assignees.map(assignee => assignee.login).join(', ') || 'No one',
    url: issue.html_url
  }));
}

```

上記スクリプトをエディタに記載して保存します。

左側のサイドメニューから、現在のファイルの*三点マーク*を押下し、名前の変更を押下します。

ファイルの名称を、`コード.gs`から、`GetGitHubIssues.gs`に変更し、**Ctrl + S**で保存します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/7f3b392a-1257-046d-0387-7adb5f7828c4.png)

これでIssues取得ライブラリ（Google Apps Script）側は完了です。

### スクリプト IDの確認
このIssues取得ライブラリを、別のApps Scriptで読み込むためには、スクリプト IDが必要になります。
スクリプト IDとはApps Scriptを特定するための一意のIDです。
[スクリプト プロジェクト  |  Apps Script  |  Google for Developers](https://developers.google.com/apps-script/guides/projects?hl=ja)


左側のサイドバーから**プロジェクトの設定**を押下し、設定画面に遷移し、スクリプト IDをコピーして控えておきます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/268e0cc5-a8f4-dc86-5b2e-a36e19b80603.png)

## スプレッドシートの作成
実際のIssuesを格納するためのスプレッドシートを作成します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/d3891ff8-8c2b-d33f-c5ce-3f00f7535a68.png)

- A列: number
- B列: title
- C列: created_at
- D列: assignees
- E列: url

に対応しており、それ以降は、ポイント計算のために利用します。
ポイント計算の仕組み自体は後程作るので、いったんA列, B列, C列, D列, E列の1行目に、各タイトルを設定しておきますを作成しておきます。

また、スクリプト実行時、意図しないシートでスクリプトが実行されると困るため、シートの名前が特定の名前でないと実行されないような仕組みを同期スクリプトに組み込みます。
今回は、対象のシートの名前を**GitHub Issues**にします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/e6e61ab8-cca6-4dba-1185-18b8ba2818fe.png)

## 同期スクリプト（Google Apps Script）を作成する
スプレッドシートが作成できたら、ツールバーにある **拡張機能 > Apps Script**から、Apps Scriptの編集画面に移動します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/205451f6-0c5b-971d-834f-3d9adebfd543.png)

### Issues取得ライブラリ（Google Apps Script）を読み込む
スクリプトの編集画面にて、左側のサイドメニューにある、**ライブラリの＋ボタン**を押下すると、ライブラリの追加モーダルが表示されます。

**スクリプト ID入力欄**で、控えておいたIssues取得ライブラリのスクリプト IDを入力し、**検索**を押下することで、作成したライブラリを読み込まれます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/1b4280f5-7e75-6ff8-2fb0-df0d11775e5e.png)

下に入力されるIDは、同期スクリプト内でのライブラリの名称として利用できます。
`GetGitHubIssues`としておき、**追加**を押下することで、ライブラリを利用することができるようになります。

:::note
Issues取得ライブラリのプロジェクト名前を、`無題のプロジェクト`から、`GetGitHubIssues`に変更していた場合は自動で入力されます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/391ab436-7347-be4e-383e-0f56948fd086.png)
:::

### コード
スプレッドシートを実際に編集するApps Scriptを作成します。

実行するたびにApps Scriptを起動するのは面倒なので、ツールバーに**カスタム > GitHub Issuesを同期**という項目を追加しておき、そこからアクセスできるようにしておきます。

同期を行った後、最後にポイント順でソートを行ったほうがわかりやすいので、ソートに利用する列も指定します。

グローバルの定数として、下記を設定できるようにしておきます。

SHEET_NAME: Apps Scriptの実行を許可するシート名
IS_GET_ALL_ISSUES: すべてのIssuesを取得するかどうか
NUMBER_COLUMN: issuesを一意に定めるための番号を記載した列
EXCLUDED_COLUMN: GitHubのIssues以外の列が始まり
SORT_COLUMN : 降順でのソートを行う列


```javascript:SyncIssuesWithSpreadsheet.gs
const SHEET_NAME = "GitHub Issues";  //Apps Scriptの実行を許可するシート名
const IS_GET_ALL_ISSUES = true;      //すべてのIssuesを取得するかどうか
const NUMBER_COLUMN = 'A';           //issuesを一意に定めるための番号を記載した列
const EXCLUDED_COLUMN = 'F';         //GitHubのIssues以外の列が始まり
const SORT_COLUMN = 'M';             //降順でのソートを行う列

/**
 * 初期設定（カスタムメニュー追加）
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("カスタム")
    .addItem("GitHub Issuesを同期", "runScriptManually")
    .addToUi();
}

/**
 * 現在アクティブなシートがSHEET_NAMEと一致する場合にスクリプトを実行
 */
function runScriptManually() {
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const activeSheetName = activeSheet.getName();

  if (activeSheetName === SHEET_NAME) {
    syncIssuesWithSpreadsheet();
  } else {
    SpreadsheetApp.getUi().alert(`現在のシートは "${activeSheetName}" です。\n指定されたシート "${SHEET_NAME}" をアクティブにしてください。`);
  }
}

/**
 * シート名からシートを取得
 * @param {string} sheetName シート名
 * @return {Sheet|null} シートオブジェクト、またはnull
 */
function getSheetByName(sheetName) {
  return SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
}

/**
 * メイン関数
 */
function syncIssuesWithSpreadsheet() {
  Logger.log("シートを取得");
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  Logger.log("GitHubから現在のOpen Issueを取得");
  const apiIssues = GetGitHubIssues.getOpenGitHubIssues(IS_GET_ALL_ISSUES);

  Logger.log("スプレッドシートの指定列（NUMBER_COLUMN）のNumber一覧を取得");
  const sheetIssues = getSpreadsheetIssues(sheet, NUMBER_COLUMN);

  Logger.log("スプレッドシートにあるがAPIにない場合は、削除");
  const numbersToRemove = sheetIssues.filter(number => !apiIssues.some(issue => issue.number === number));
  removeIssuesFromSheet(sheet, NUMBER_COLUMN, numbersToRemove);

  Logger.log("スプレッドシートとAPIのデータを比較して更新されていた場合は、編集");
  updateIssuesInSheet(sheet, NUMBER_COLUMN, EXCLUDED_COLUMN, apiIssues);

  Logger.log("APIにあるがスプレッドシートにない場合は、追加")
  const issuesToAdd = apiIssues.filter(issue => !sheetIssues.includes(issue.number));
  addIssuesToSheet(sheet, NUMBER_COLUMN, issuesToAdd);

  Logger.log("ソート")
  sortSpreadsheet(sheet, SORT_COLUMN);
}

/**
 * スプレッドシートから指定列のIssue Numberを取得
 * @param {Sheet} sheet スプレッドシートのシート
 * @param {string} column アルファベット列名（例: 'A'）
 * @return {Array<number>} スプレッドシート上のIssue Numberのリスト
 */
function getSpreadsheetIssues(sheet, column) {
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) {
    // データがヘッダー行しかない場合、空の配列を返す
    Logger.log("スプレッドシートにデータがありません。");
    return [];
  }

  const columnIndex = getAlphabetNumber(column);
  const data = sheet.getRange(2, columnIndex, lastRow - 1, 1).getValues();
  return data.flat().map(value => Number(value)).filter(value => !isNaN(value));
}

/**
 * スプレッドシートから指定されたIssueを削除
 * @param {Sheet} sheet スプレッドシートのシート
 * @param {string} column アルファベット列名（例: 'A'）
 * @param {Array<number>} numbersToRemove 削除するNumberのリスト
 */
function removeIssuesFromSheet(sheet, column, numbersToRemove) {
  const data = sheet.getDataRange().getValues();
  const columnIndex = getAlphabetNumber(column);

  for (let i = data.length - 1; i >= 0; i--) {
    const cellValue = data[i][columnIndex - 1];

    // columnに何も入力されていない場合、スキップ
    if (cellValue === null || cellValue === undefined || cellValue === '') {
      continue;
    }

    const numericValue = Number(cellValue);
    if (numbersToRemove.includes(numericValue)) {
      const row = i + 1;
      const rowData = data[i];

      Logger.log(`行 ${row} を削除: ${JSON.stringify(rowData)}`);

      sheet.deleteRow(row);
    }
  }
}

/**
 * スプレッドシートに存在するIssueをAPIからの最新情報で更新
 * @param {Sheet} sheet スプレッドシートのシート
 * @param {string} column 指定列（例: 'A'）Issue番号が格納されている列
 * @param {Array<Object>} apiIssues GitHub APIから取得したIssueのリスト
 */
function updateIssuesInSheet(sheet, NUMBER_COLUMN, EXCLUDED_COLUMN, apiIssues) {
  const lastRow = sheet.getLastRow();
  const EXCLUDED_COLUMNIndex = getAlphabetNumber(EXCLUDED_COLUMN);
  const data = sheet.getRange(1, 1, lastRow, EXCLUDED_COLUMNIndex - 1).getValues();
  const columnIndex = getAlphabetNumber(NUMBER_COLUMN);

  // スプレッドシートのIssue番号と行番号のマッピングを作成
  const issueMap = {};
  for (let i = 1; i < data.length; i++) {
    const issueNumber = Number(data[i][columnIndex - 1]);
    if (!isNaN(issueNumber)) {
      issueMap[issueNumber] = i + 1;
    }
  }

  // APIからのIssue情報を基に更新
  apiIssues.forEach(issue => {
    const row = issueMap[issue.number];
    if (row) {
      const currentRowData = data[row - 1];

      const updatedData = [
        issue.number,
        issue.title,
        issue.created_at,
        issue.assignees,
        issue.url
      ];

      if (!arraysEqual(currentRowData, updatedData)) {
        Logger.log(`行 ${row} を更新: ${JSON.stringify(currentRowData)} => ${JSON.stringify(updatedData)}`);

        sheet.getRange(row, 1, 1, updatedData.length).setValues([updatedData]);
      }
    }
  });
}

/**
 * 2つの配列が等しいか確認
 * @param {Array} arr1 配列1
 * @param {Array} arr2 配列2
 * @return {boolean} 等しい場合はtrue
 */
function arraysEqual(arr1, arr2) {
  if (arr1.length !== arr2.length) {
    return false;
  }
  for (let i = 0; i < arr1.length; i++) {
    if (arr1[i] !== arr2[i]) {
      return false;
    }
  }
  return true;
}

/**
 * スプレッドシートに新しいIssueを追加
 * @param {Sheet} sheet スプレッドシートのシート
 * @param {Array<Object>} issuesToAdd 追加するIssueのリスト
 * @param {string} column 指定列（例: 'A'）
 */
function addIssuesToSheet(sheet, column, issuesToAdd) {
  if (issuesToAdd.length === 0) {
    return;
  }

  const columnIndex = getAlphabetNumber(column);
  const columnData = sheet.getRange(1, columnIndex, sheet.getLastRow(), 1).getValues();
  const lastRow = columnData.reverse().findIndex(row => row[0] !== null && row[0] !== '');
  const startRow = lastRow === -1 ? 1 : columnData.length - lastRow + 1;
  const newData = issuesToAdd.map(issue => [
    issue.number,
    issue.title,
    issue.created_at,
    issue.assignees,
    issue.url
  ]);

  Logger.log('追加されたデータ:');
  newData.forEach((row, index) => {
    Logger.log(`行 ${startRow + index} を追加: ${JSON.stringify(row)}`);
  });

  sheet.getRange(startRow, 1, newData.length, newData[0].length).setValues(newData);
}

/**
 * アルファベットを番号に変換する関数
 * @param {string} letter アルファベット1文字
 * @return {number} 対応する番号（A=1, B=2, ..., Z=26）
 */
function getAlphabetNumber(letter) {
  const char = letter.toUpperCase();
  const code = char.charCodeAt(0);
  if (code >= 65 && code <= 90) {
    return code - 64;
  } else {
    throw new Error('無効な入力です。アルファベット1文字を指定してください。');
  }
}

/**
 * スプレッドシートをソート
 * @param {Sheet} sheet スプレッドシートのシート
 * @param {string} column 指定列（例: 'A'）
 * @param {bool} ascending 昇順、降順指定
 */
function sortSpreadsheet(sheet, column, ascending = false) {
  const columnIndex = getAlphabetNumber(column);
  const lastRow = sheet.getLastRow();
  
  if (lastRow < 2) {
    Logger.log("ソート対象のデータがありません。処理をスキップします。");
    return; // データがない場合はスキップ
  }

  const columnData = sheet.getRange(2, columnIndex, lastRow - 1, 1).getValues();
  
  // 指定列にデータが存在するか確認
  const hasData = columnData.some(row => row[0] !== null && row[0] !== '');
  if (!hasData) {
    Logger.log(`列 ${column} にデータがありません。ソート処理をスキップします。`);
    return; // データがない場合はスキップ
  }

  // データがある場合のみソートを実行
  Logger.log(`列 ${column} のデータを${ascending ? "昇順" : "降順"}でソートします。`);
  sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn())
    .sort({ column: columnIndex, ascending: ascending });
}

```

上記スクリプトをエディタに記載して保存し、ファイルの名称を、`コード.gs`から、`SyncIssuesWithSpreadsheet.gs`に変更しておきます。

これで、スプレッドシート側で再読み込みを行うことで、ツールバーにカスタムメニューが表示されるようになり、**カスタム > GitHub Issuesを同期**から、手動でスプレッドシートとGitHub Issuesをが同期されるようになります！

初めて実行するときは、認証が必要となるため、問題なければ許可します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/f3d90cd2-1b61-5e71-992c-b4580b9bf4f6.png)


# GitHub Webhookを利用して、Issueの作成・更新・削除・再オープンなどをトリガーにして自動更新する仕組み
GitHub Webhookを利用して、Issuesの追加、編集、クローズが行われるたびに、スプレッドシートを追加、編集、削除するようにします。
これで、必要な分だけデータが更新され、何も押さなくても自動でデータが更新されるようにできます。
[webhook について - GitHub Docs](https://docs.github.com/ja/webhooks/about-webhooks)

通知されたWebhookは、Apps Scriptをウェブアプリとしてデプロイすることで、Webhookを受け取って処理するサーバーとして利用します。
（ウェブアプリとしてデプロイしないと、サーバーとしての動作は行えません）
[ウェブアプリ  |  Apps Script  |  Google for Developers](https://developers.google.com/apps-script/guides/web?hl=ja)

:::note warn
GitHub Webhookの設定を行うには、該当のリポジトリのadmin権限が必要となります。
[webhookの作成 - GitHub Docs](https://docs.github.com/ja/webhooks/using-webhooks/creating-webhooks)
:::

## 構成
```mermaid
sequenceDiagram
  participant Issues as GitHub Issues
  participant Webhook as GitHub Webhook
  participant WebhookHandler as Webhookハンドラ (Google Apps Script ウェブアプリ)
  participant Spreadsheet as Googleスプレッドシート

  Note right of Issues: Issueの作成・更新・削除・再オープンがトリガー
  Issues ->> Webhook : イベントをトリガー (例: Issueの更新)
  Webhook ->> WebhookHandler : イベント情報を通知 (JSON形式)
  WebhookHandler ->> Spreadsheet : スプレッドシートを更新<br>（Issueの追加/変更/削除）

  opt Issueが新規の場合
    WebhookHandler ->> Spreadsheet : 新規Issueを追加
  end
  opt Issueが更新された場合
    WebhookHandler ->> Spreadsheet : 該当Issueを更新
  end
  opt Issueが削除された場合
    WebhookHandler ->> Spreadsheet : 該当Issueを削除
  end
```
## Webhookハンドラ（Google Apps Script ウェブアプリ）を作成する
### コード
スプレッドシート側のApps Scriptにファイルを追加します。
左側のサイドメニューの**ファイルの＋ボタン**を押下し、名前を`WebhookHandler.gs`としてファイルを追加します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/541d2a05-b21e-c1f9-b2a7-0e6a3866d414.png)

このApps Scriptはウェブアプリとして利用しますが、Apps Scriptで作成されたウェブアプリはリクエストメソッドとして、GETとPOSTが利用できます。

それぞれ、GETを受け取ったときに動作する`doGet()`、POSTを受け取ったときに動作する`doPost()`が利用できますが、GitHub Webhookが発火されたときはPOSTがリクエストされるので、今回は、`doPost()`を利用します。

余談として、ChatGPT先生に、Apps Scriptで作成したウェブアプリのスクリプトについて訊くと、`doPost()`内にreturnで結果を返すように言われます。

しかし、実際にreturnを入れると、GitHubのWebhook側の画面で302となってしまうので、returnしないようにします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/54c09579-fa1e-3017-2390-270c903cca47.png)

```javascript:WebhookHandler.gs
/**
 * GitHub WebhookからのPOSTリクエストを受信
 * @param {Object} e - Webhookのリクエストデータ
 */
function doPost(e) {
  try {
    const payload = parseWebhookPayload(e);
    const action = payload.action;
    const issue = extractIssueData(payload);

    const sheet = getTargetSheet(SHEET_NAME);

    handleIssueAction(sheet, action, issue);

    sortSpreadsheet(sheet, SORT_COLUMN);

  } catch (error) {
    Logger.log('Error: ' + error.message);
  }
}

/**
 * Webhookのペイロードをパース
 * @param {Object} e - Webhookのリクエストデータ
 * @return {Object} パースされたペイロードデータ
 */
function parseWebhookPayload(e) {
  return JSON.parse(e.postData.contents);
}

/**
 * Issueデータを抽出
 * @param {Object} payload - Webhookのペイロードデータ
 * @return {Object} Issueデータ
 */
function extractIssueData(payload) {
  const issueData = payload.issue;
  return {
    number: issueData.number,
    title: issueData.title,
    created_at: issueData.created_at,
    assignees: issueData.assignees?.map(assignee => assignee.login).join(', ') || 'No one',
    url: issueData.html_url
  };
}

/**
 * 対象のスプレッドシートのシートを取得
 * @param {string} sheetName - 対象シートの名前
 * @return {Sheet} シートオブジェクト
 */
function getTargetSheet(sheetName) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  if (!sheet) {
    throw new Error(`シート "${sheetName}" が見つかりません。`);
  }
  return sheet;
}

/**
 * Issueのアクションに応じた処理を実行
 * @param {Sheet} sheet - スプレッドシート
 * @param {string} action - Issueのアクション ("opened", "edited", "closed"など)
 * @param {Object} issue - Issueデータ
 */
function handleIssueAction(sheet, action, issue) {
  if (["opened", "edited", "assigned", "unassigned", "reopened"].includes(action)) {
    upsertIssue(sheet, issue, NUMBER_COLUMN); // 追加または更新
  } else if (["deleted", "closed"].includes(action)) {
    removeIssue(sheet, issue.number, NUMBER_COLUMN); // 削除
  }
}

/**
 * Issueを追加または更新
 * @param {Sheet} sheet - スプレッドシート
 * @param {Object} issue - Issueデータ
 * @param {string} column - Issue番号が格納される列
 */
function upsertIssue(sheet, issue, column) {
  const issues = getSpreadsheetIssues(sheet, column);
  const firstDataRow = 2;
  const row = issues.indexOf(issue.number) + firstDataRow;

  const issueArray = [
    issue.number,
    issue.title,
    issue.created_at,
    issue.assignees,
    issue.url
  ];

  if (row > 1) {
    updateIssue(sheet, row, issueArray); // 既存Issueを更新
  } else {
    addIssue(sheet, issueArray, column); // 新しいIssueを追加
  }
}

/**
 * 既存のIssueを更新
 * @param {Sheet} sheet - スプレッドシート
 * @param {number} row - 更新する行番号
 * @param {Array} issueArray - 更新するIssueデータ
 */
function updateIssue(sheet, row, issueArray) {
  sheet.getRange(row, 1, 1, issueArray.length).setValues([issueArray]);
}

/**
 * 新しいIssueを追加
 * @param {Sheet} sheet - スプレッドシート
 * @param {Array} issueArray - 追加するIssueデータ
 * @param {string} column - Issue番号が格納される列
 */
function addIssue(sheet, issueArray, column) {
  const columnIndex = getAlphabetNumber(column);
  const columnData = sheet.getRange(1, columnIndex, sheet.getLastRow(), 1).getValues();
  const lastRow = columnData.reverse().findIndex(row => row[0] !== null && row[0] !== '');
  const startRow = lastRow === -1 ? 1 : columnData.length - lastRow + 1;
  sheet.getRange(startRow, 1, 1, issueArray.length).setValues([issueArray]);
}

/**
 * Issueを削除
 * @param {Sheet} sheet - スプレッドシート
 * @param {number} issueNumber - 削除するIssue番号
 * @param {string} column - Issue番号が格納される列
 */
function removeIssue(sheet, issueNumber, column) {
  const issues = getSpreadsheetIssues(sheet, column);
  const firstDataRow = 2;
  const rowIndex = issues.indexOf(issueNumber) + firstDataRow;

  if (rowIndex > 1) {
    sheet.deleteRow(rowIndex); // 該当行を削除
  }
}


```

## 作成したApps Scriptをウェブアプリとしてデプロイする
作成したApps ScriptはそのままだとWebhookを受け取るサーバーとしては動作しません。サーバーとして動作させるために、ウェブアプリとしてデプロイを行います。

上部のヘッダーにある**デプロイ > デプロイを管理**を押下すると、デプロイのバージョンを管理するモーダルが表示されます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/16ec7500-a154-8a86-ca7c-7957393705b4.png)

一度もデプロイを行ったことがない場合は、**デプロイメントを作成**を押すと、新しいデプロイを行うことができます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/e43292db-2b43-0450-7820-ac49227c6a4a.png)

ウェブアプリとしてデプロイするためには、**種類の選択の右側にある歯車マーク > ウェブアプリ**を選択することで、ウェブアプリとしてのデプロイを選択することができます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/a957a236-a948-7851-97f2-4a768301af1a.png)

デプロイの設定を行うための画面が表示されるため、各項目に下記のように入力します。

バージョン: 新バージョン
説明: 任意の説明
次のユーザーとして実行: 自分
アクセスできるユーザー: 全員

この状態で**デプロイ**を押下することで、GitHub Webhookが叩くことのできるウェブアプリとして、デプロイすることができます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/6d726110-17a2-e59e-de9a-0a6a6f0b563d.png)

デプロイが完了すると、ウェブアプリにアクセスするためのウェブアプリ URLが発行されるため、コピーします。
これを、GitHub Webhookに登録することで連携が完了します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/42abe3bd-12de-25ae-6677-7e8dd8e9b5e8.png)

:::note
Webhookハンドラに変更を加えて、実際の動作を変えるためには、毎回デプロイを行い、変更されたURLをGitHub Webhookに登録し直す必要があります。（めんどい）

デプロイのバージョンを上げるためには、再度**デプロイ > デプロイを管理**から、**鉛筆マーク**を押下することで、新しいデプロイの設定を行えます。

- バージョン: 新バージョン
- 説明: 任意の説明

として、**デプロイ**ボタンを押すことで、新しくデプロイが行われ、更新されたウェブアプリ URLが表示されるので、これをコピーして、再度登録し直すことで、Webhookハンドラの動作が変わるようになります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/cd43eb4c-c13d-6332-fbff-e95e641a8456.png)

:::

## GitHub Webhookの設定を行う
リポジトリの**Settings > Webhooks > Add webhook** から、Webhookの設定画面に遷移します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/4f4cf45a-3df7-8c45-f8fe-080c1d0117d5.png)

項目のうち、下記のように設定して、リポジトリのIssuesに変更が加わるたびに、通知されるようにします。

- Payload URL*: ウェブアプリ URL
- Content type*: application/json
- Which events would you like to trigger this webhook?: Let me select individual events で、Issuesを付与

これで、Issueの追加、編集、クローズを条件にして、Webhookがトリガーされ、スプレッドシートが編集されるようになります！

# スプレッドシートを整える
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/10e388b2-9cf2-4f6e-946f-9f5012bfe1b9.png)

最後にスプレッドシート側で、F列以降のIssue単位のポイント付けのための仕組みを作ります。

評価の計算方法としては、下記のような計算式で算出するようにしています。
total_pointが、そのIssueの優先度のポイントとなります。
```
total_point = (reach * ((value + impression) / 2) * confidence) / story_point
```
impactについてですが、判断基準がぶれそうだったので、

- value: ユーザーに提供される価値
- impression: 提供されたものがどれくらいユーザーにとって重要か

をカテゴライズして、最大10pointとして、設定し、平均を出すことで、impactとすることにしました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3629942/e0a25b6b-0b4e-3730-0118-5eb5a22bd298.png)

カテゴリごとにポイントを割り振って、別シートで管理することにします。これによって、ユーザーインタビューや、ビジネス側の意向などにより、各項目に変更の優先度に変更があっても柔軟に対応できるようにしておきます。

あとは、スプレッドシートの関数をごにょごにょして、total_pointを計算して完成です！

# 最後に
スプレッドシートを使って、棚卸ししてみた結果ですが、過去にissueとして挙がっていたものの、属人化されてしまっていていつの間にか忘れ去られていたアイディアなどを掘り起こすことができるようになりました。

各項目のポイントに関しては、現状は、僕個人の評価としてざっくりつけているだけですが、今後、アナリティクスのデータやユーザーインタビューによってより正確な判断がしやすくなったかなと思います。

完成したシートをもとに、プロダクトオーナーや、関係しているエンジニアの方とこのシートを見ながらコミュニケーションをとってみましたが、おおむね優先度の順位に違和感はなかったようなので、初めての棚卸しとしては上手くいったかなと思っています。

明日の記事は [@Fukuro9](https://qiita.com/Fukuro9) さんです！　Vket Cloudで使われる独自言語を、AIに理解してもらうようです！　お楽しみに！

---
■■■ 宣伝コーナー ■■■

『バーチャルマーケット2024Winter』が始まってます！
メタバース空間で行われるクリエイターのお祭りです。
VRChatというアプリで体験できますので是非遊びに来てください！

https://event.vket.com/

また、本稿で紹介したVket Cloudを使って作られたWebメタバースイベント『Vket Fusion Fes』も開催されています！
こちらは、Webブラウザだけでアクセスできますよ！

https://cloud.vket.com/worlds/2614