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

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

昨日の記事は @m2wasabi さんの 令和の時代に自前メールサーバを立ててJiraにWebhookする でした。
悲しみのディスティニーの記録らしいです…！

作るもの

Google Apps Script（GAS）と、GitHub API、Webhookを利用して、スプレッドシートに下記のような表を自動更新してくれる仕組みを作ります。

はじめに

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

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

名称	イメージ	概要
Vket Cloud		Webブラウザで動作するマルチプレイ可能なメタバースを作成・公開できるサービス
マイルーム＆ブースメイカー		バーチャルマーケットのブース入稿がWebブラウザから行えたり、メタバース上の自分の部屋を持つことができるサービス
ワールドビルダー		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項目で評価して、以下の式でスコアリングします。（原文 | 和文）

RICE: Simple prioritization for product managersより引用

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

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

全体の構成

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

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

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

GitHub APIを利用して、Issuesの内容を全て取得して、スプレッドシートを更新する仕組み

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

構成

同期スクリプトと、Issues取得ライブラリは、別のApps Scriptのファイルとして作成しています。
これは、GitHubのAPIにアクセスするためのtokenを、全員がアクセスできる場所へ置かないようにするためです。
個人用アクセス トークンを管理する - GitHub Docs

別のApps Scriptとして作成したファイルを読み込むためには、Apps Scriptのライブラリ機能を利用します。
ライブラリ  |  Apps Script  |  Google for Developers

自分にしか閲覧・編集できない、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

ownerとrepoの確認

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

tokenの作成

ハンバーガーメニューから、Settings > Developer settings > Personal access tokens > Fine-grained tokens > Generate new tokenから、新しいtokenを作成するページに遷移します。
New Fine-grained Personal Access Token

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

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

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

作成されたtokenは、遷移先のページからコピーすることができます。一度しか表示されないので、必ずこの時点でtokenを控えておきます。

Issues取得ライブラリのコードを作成する

Google Driveに移動し、右クリック > その他 > Google Apps Scriptから、Apps Scriptのファイルを作成することで、自動で編集画面に遷移します。

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

スクリプト プロパティにtokenなどの情報を設定する

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

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

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

プロパティ サービス  |  Apps Script  |  Google for Developers

コード

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

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

returnとして、取得したIssuesから、

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

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

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で保存します。

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

スクリプト IDの確認

このIssues取得ライブラリを、別のApps Scriptで読み込むためには、スクリプト IDが必要になります。
スクリプト IDとはApps Scriptを特定するための一意のIDです。
スクリプト プロジェクト  |  Apps Script  |  Google for Developers

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

スプレッドシートの作成

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

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

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

また、スクリプト実行時、意図しないシートでスクリプトが実行されると困るため、シートの名前が特定の名前でないと実行されないような仕組みを同期スクリプトに組み込みます。
今回は、対象のシートの名前をGitHub Issuesにします。

同期スクリプト（Google Apps Script）を作成する

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

Issues取得ライブラリ（Google Apps Script）を読み込む

スクリプトの編集画面にて、左側のサイドメニューにある、ライブラリの＋ボタンを押下すると、ライブラリの追加モーダルが表示されます。

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

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

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

コード

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

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

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

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

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

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をが同期されるようになります！

初めて実行するときは、認証が必要となるため、問題なければ許可します。

GitHub Webhookを利用して、Issueの作成・更新・削除・再オープンなどをトリガーにして自動更新する仕組み

GitHub Webhookを利用して、Issuesの追加、編集、クローズが行われるたびに、スプレッドシートを追加、編集、削除するようにします。
これで、必要な分だけデータが更新され、何も押さなくても自動でデータが更新されるようにできます。
webhook について - GitHub Docs

通知されたWebhookは、Apps Scriptをウェブアプリとしてデプロイすることで、Webhookを受け取って処理するサーバーとして利用します。
（ウェブアプリとしてデプロイしないと、サーバーとしての動作は行えません）
ウェブアプリ  |  Apps Script  |  Google for Developers

GitHub Webhookの設定を行うには、該当のリポジトリのadmin権限が必要となります。
webhookの作成 - GitHub Docs

構成

Webhookハンドラ（Google Apps Script ウェブアプリ）を作成する

コード

スプレッドシート側のApps Scriptにファイルを追加します。
左側のサイドメニューのファイルの＋ボタンを押下し、名前をWebhookHandler.gsとしてファイルを追加します。

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

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

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

しかし、実際にreturnを入れると、GitHubのWebhook側の画面で302となってしまうので、returnしないようにします。

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を受け取るサーバーとしては動作しません。サーバーとして動作させるために、ウェブアプリとしてデプロイを行います。

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

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

ウェブアプリとしてデプロイするためには、種類の選択の右側にある歯車マーク > ウェブアプリを選択することで、ウェブアプリとしてのデプロイを選択することができます。

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

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

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

デプロイが完了すると、ウェブアプリにアクセスするためのウェブアプリ URLが発行されるため、コピーします。
これを、GitHub Webhookに登録することで連携が完了します。

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

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

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

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

GitHub Webhookの設定を行う

リポジトリのSettings > Webhooks > Add webhook から、Webhookの設定画面に遷移します。

項目のうち、下記のように設定して、リポジトリの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がトリガーされ、スプレッドシートが編集されるようになります！

スプレッドシートを整える

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

評価の計算方法としては、下記のような計算式で算出するようにしています。
total_pointが、そのIssueの優先度のポイントとなります。

total_point = (reach * ((value + impression) / 2) * confidence) / story_point

impactについてですが、判断基準がぶれそうだったので、

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

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

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

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

最後に

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

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

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

明日の記事は @Fukuro9 さんです！　Vket Cloudで使われる独自言語を、AIに理解してもらうようです！　お楽しみに！

---

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

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

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