Zenn
⛳

GAS github issueをスプレッドシートで管理する

2024/05/23に公開
GAS
Google Apps Script
tech

はじめに

今回は、「GitHub Issueをスプレッドシートで管理する」機能を開発しました。この記事では、開発の背景・目的、及び実装内容についてまとめます。

背景・目的

現在、ウェブ制作会社に勤務しており、フロントエンドエンジニアとしてウェブサイトを作成しています。自社では、テストアップ後の社内修正指示をGitHubのIssueを使って管理しています。しかし、このGitHub Issueには以下のような不便さがあります。

  • Issueの内容を確認するためには、個々のIssueを開かなければならない
  • 一覧で進捗状況を確認できない
    これらの問題点から、進捗状況を一覧で確認できた方が効率的であると考えました。そこで、「Google Apps Script（GAS）を使ってGitHub Issueをスプレッドシートで一覧管理できるのではないか？」と思い、実装を始めました。

要件設計

今回、利用したサービスと実装した機能は以下の通りです。

利用したサービス

  • GitHubのWebhooks
    Webhookを使って、Issueが更新されたときにスプレッドシートに自動的に反映されるようにしました。

  • GASのトリガー関数
    GitHubのWebhookと組み合わせて、以下の機能を実装しました。

  • Issueが更新された時、スプレッドシートに自動的に反映

  • スプレッドシートを編集した時、GitHub Issueに自動的に反映

実装した機能

  • 環境変数の読み込み
  • Organizationのメンバーを取得してタブ選択を可能にする
  • Labelsを取得してタブ選択を可能にする
  • 登録されたIssueの一覧をスプレッドシートに反映
  • Issueが編集されたら自動的にスプレッドシートに反映
  • スプレッドシートを編集したら自動的にGitHub Issueに反映

コード

環境変数を読み込む

環境変数をスプレッドシートで管理できるようにしました。スプレッドシートで管理するメリットは以下の通りです。

  • GASの場合、コードを書き換えた後に再度デプロイする必要がありますが、スプレッドシートの場合は値を変更するだけで即座に反映されます。
  • GASの編集画面を開く必要がありません。
    以下のコードは、Googleスプレッドシートの「config」シートから環境変数を取得し、GitHubのリポジトリにアクセスするための設定を行うものです。
let GITHUB_OWNER = '';
let GITHUB_REPOSITORY = '';
let GITHUB_ACCESS_TOKEN = '';
let GITHUB_URL = '';
let SPREADSHEET_ID = '';

const loadConfiguration = () => {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const configSheet = ss.getSheetByName('config');
  const range = configSheet.getDataRange();
  const values = range.getValues();
  const config = {};
  values.forEach(row => {
    config[row[0]] = row[1];
  });
  return config;
}

const config = loadConfiguration();
GITHUB_OWNER = config["GITHUB_OWNER"] || '';  
GITHUB_REPOSITORY = config["GITHUB_REPOSITORY"] || ''; 
GITHUB_ACCESS_TOKEN = config["GITHUB_ACCESS_TOKEN"] || ''; 
SPREADSHEET_ID = config["SPREADSHEET_ID"] || ''; 
GITHUB_URL = `https://api.github.com/repos/${GITHUB_OWNER}/${GITHUB_REPOSITORY}` || '';

具体的には、

  • 環境変数用の変数を作る
  • スプレッドシートから情報を習得する
  • 習得した情報をそれぞれの環境変数に格納する

organizationのメンバーを取得して、タブ選択できるようにする

fetchAllLabels関数

この関数は、GitHubのAPIを使用してリポジトリ内のラベル情報を取得します。UrlFetchApp.fetchは、GASが提供するHTTPリクエストのメソッドです。取得したデータはJSON形式で返されるため、これをJavaScriptのオブジェクトに変換し、ラベル名の配列を作成します。

const fetchAllLabels = () => {
  const url = `${GITHUB_URL}/labels`;
  const headers = {
    'Authorization': 'token ' + GITHUB_ACCESS_TOKEN,
    'Accept': 'application/vnd.github.v3+json'
  };

  const options = {
    'method': 'GET',
    'headers': headers,
    'muteHttpExceptions': true
  };

  const response = UrlFetchApp.fetch(url, options);
  const getlabelsData = JSON.parse(response.getContentText());
  return getlabelsData.map(label => label.name);
}

initializeLabelDropdown関数

この関数は、fetchAllLabels関数で取得したラベルの配列から、データ入力規制のプルダウンリストを作成します。指定したシート範囲に対して、プルダウンリストのデータバリデーションを設定します。

const initializeLabelDropdown = (sheet) => {
  const labels = fetchAllLabels(); 
  const range = sheet.getRange(3, 5, 300);//ここは、適宜変更

  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(labels, true)
    .setAllowInvalid(false)
    .setHelpText("Select a label from the list")
    .build();
  range.setDataValidation(rule);
}

issueを取得してスプシに記入する

fetchIssues関数

この関数は、GitHubのAPIを使用してリポジトリ内のIssueを取得します。取得したデータはJSON形式で返され、プルリクエストでないIssueのみをフィルタリングして返します。

const fetchIssues = () => {
  const url = `${GITHUB_URL}/issues`
  const options = {
    'method': 'GET',    
    'headers': { 'Authorization': `token ${GITHUB_ACCESS_TOKEN}` },  
    'muteHttpExceptions': true
  };
  const response = UrlFetchApp.fetch(url, options);
  const fetchIssuesData = JSON.parse(response.getContentText());
  return fetchIssuesData.filter(issue => !issue.pull_request);
}

fetchLatestIssueComment関数

この関数は、指定したIssueの最新コメントを取得します。取得したコメントデータはJSON形式で返され、最新のコメントを返します。

const fetchLatestIssueComment = (issueId) => {
  const url = `${GITHUB_URL}/issues/${issueId}/comments`;
  const options = {
    method: 'GET',
    headers: { 'Authorization': `token ${GITHUB_ACCESS_TOKEN}` },
    muteHttpExceptions: true
  };
  const response = UrlFetchApp.fetch(url, options);
  const comments = JSON.parse(response.getContentText());
  return comments.length > 0 ? comments[comments.length - 1].body : '';
}

populateSheetWithIssues関数

const populateSheetWithIssues = (sheet) => {
  const issues = fetchIssues();
  issues.sort((a, b) => b.number - a.number);
  const issuesMap = new Map(issues.map(issue => [issue.number, issue]));
  Array.from(issuesMap.values()).reverse().forEach(issue => {
    const latestComment = fetchLatestIssueComment(issue.number);
    sheet.insertRowBefore(3);
    sheet.getRange(3, 1, 1, 9).setValues([//3行目から1行分、1列目から9列分にgetIssue()の内容を反映
      [issue.number, //1列目
      issue.title,   //2列目
      issue.labels.map(label => label.name).join(', '), //3列目
      latestComment, //4列目
      '', //5列目:データ送信列
      '', //6列目:データ送信列
      '', //7列目:データ送信列
      '', //8列目:データ送信列
      issue.html_url] // 9列目
    ]);
  });
}

fetchIssues関数で取得したissuesを降順に並び替える。
new Mapでオブジェクトのkeyにissueのnumberを使用する。
降順に並び替えたissueをスプレッドシートに反映していく。

sheet.getRange().setValues([])

の部分で、指定した範囲に値を設定していきます。
ここには、お好みで
今回は、このようなスプレッドシートを作成しました。

スプレッドシートを編集した時、github issueに反映する

postCommentToGithubIssue関数

この関数は、スプレッドシートの特定の行が編集された時に、GitHub Issueにコメントを追加します。編集対象はメンションとコメントです。

const postCommentToGithubIssue = (sheet,editedRow) => {
  const issueId = sheet.getRange(editedRow, 1).getValue(); 
  const mention = sheet.getRange(editedRow, 6).getValue(); 
  const newComment = sheet.getRange(editedRow, 7).getValue(); 
  let submitComment = mention ? `@${mention} ${newComment}` : newComment;

  const url = `${GITHUB_URL}/issues/${issueId}/comments`;
  const options = {
    method: 'POST',
    headers: {
      'Authorization': `token ${GITHUB_ACCESS_TOKEN}`,
      'Accept': 'application/vnd.github.v3+json'
    },
      payload: JSON.stringify({ body: submitComment }),
      muteHttpExceptions: true
  };
  UrlFetchApp.fetch(url, options);

  sheet.getRange(editedRow, 6).clearContent();
  sheet.getRange(editedRow, 7).clearContent();
}

スプレッドシートの6列目（F列）と7列目（G列）にそれぞれメンションとコメント用の列を作成します。

  • メンションとコメントを一つの変数にまとめます。
  • HTTPリクエストを使用してGitHubに情報をPOSTします。
  • POSTした後、6列目と7列目の内容をクリアします。

updateIssueLabelsInGithub関数

この関数は、スプレッドシートで編集されたラベル情報をGitHub Issueに反映します。

const updateIssueLabelsInGithub = (sheet,editedRow) => {
  const issueId = sheet.getRange(editedRow, 1).getValue(); 
  const newLabels = sheet.getRange(editedRow, 5).getValue().split(","); 
  if (newLabels.length === 0) {
    return; 
  }
  const currentLabels = fetchCurrentIssueLabels(issueId);
  const allLabels = new Set([...currentLabels, ...newLabels]);
  const url = `${GITHUB_URL}/issues/${issueId}`;
  const options = {
    method: 'PATCH',
    headers: {
      'Authorization': `token ${GITHUB_ACCESS_TOKEN}`,
      'Accept': 'application/vnd.github.v3+json'
    },
    payload: JSON.stringify({ labels: Array.from(allLabels) }),
  };
  UrlFetchApp.fetch(url, options);
  sheet.getRange(editedRow, 3).setValue(Array.from(allLabels).join(", "));
  sheet.getRange(editedRow, 5).clearContent();
}

ラベルの追加に関してもコメントと同じ流れです。

  • 新しいラベルがない場合は処理を終了します。
  • 現在のラベルを取得し、新しいラベルと合わせてセットにします。
  • HTTPリクエストを使用してGitHub Issueのラベルを更新します。
  • スプレッドシートに更新されたラベルを反映し、5列目の内容をクリアします。

スプレッドシートを編集したときに、自動的にgithub issueに反映されるようにする

onEdit関数

onEdit関数は、ユーザーがスプレッドシートのセルを編集したときに自動的に実行されるトリガー関数です。この関数では、編集されたセルの位置に基づいて異なる処理を実行します。

条件分岐の説明

  • スプレッドシートのシート名が「Issues」で、8列目（H列）の2行目が編集された場合：

  • initializeLabelDropdown(sheet)：ラベルのプルダウンリストを初期化します。

  • populateSheetWithIssues(sheet)：スプレッドシートにIssueの一覧を反映します。

  • スプレッドシートのシート名が「Issues」で、8列目（H列）の3行目以降が編集された場合：

  • postCommentToGithubIssue(sheet, editedRow)：コメントをGitHub Issueに追加します。

  • updateIssueLabelsInGithub(sheet, editedRow)：ラベル情報をGitHub Issueに更新します。

const onEdit = (e) => {
  const range = e.range;
  const sheet = range.getSheet();
  const editedCellColumn = range.getColumn();
  const editedRow = range.getRow();

  if (sheet.getName() === "Issues" && editedCellColumn === 8 && editedRow === 2) {
    initializeLabelDropdown(sheet);
    populateSheetWithIssues(sheet);
  } else if(sheet.getName() === "Issues" && editedCellColumn === 8 && editedRow > 2) {
    postCommentToGithubIssue(sheet,editedRow);
    updateIssueLabelsInGithub(sheet,editedRow);
  }
}

github issueが変更されたときに自動的にスプレッドシートに反映する

ここでは、GitHub Issueに変更があったときに、自動的にスプレッドシートに反映するためのコードを説明します。

  • Issueが追加されたら、スプレッドシートに追加する
  • コメントが追加されたら、スプレッドシートに追加する
  • Issueがクローズされたら該当するスプレッドシートの行を削除する

doPost関数

doPost関数は、Google Apps Scriptでウェブアプリを作成する際に、HTTP POSTリクエストを処理するための関数です。GitHubからのWebhookリクエストを受け取り、Issueの状態に応じてスプレッドシートを更新します。

const doPost = (e) => {
  const postData = JSON.parse(e.postData.contents);
  const issue = postData.issue;
  const action = postData.action;
  const issueId = issue.number;

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Issues");
  if (!sheet) {
    sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet("Issues");
  }
  const lastRow = sheet.getLastRow();
  const insertRow = 3;

  if (action === "closed") {
    handleClosedIssue(sheet, logSheet, lastRow, issueId);
  } else if (action === "opened") {
    handleOpenedIssue(sheet, lastRow, insertRow, issue, issueId);
  } else if (action === "created") {
    handleCreatedAction(sheet, lastRow, issueId);
  } else if (action === 'labeled' || action === 'unlabeled') {
    handleLabeled (sheet, lastRow, issueId);
  }
}

doPost関数は、Issueで実行されたアクションに応じて適切なハンドラー関数を呼び出します。

handleClosedIssue関数

この関数は、Issueがクローズされたときにスプレッドシート内の該当する行を削除します。

const handleClosedIssue = (sheet, logSheet, lastRow, issueId) => {
  for (let i = 1; i <= lastRow; i++) {
    const rowIssueId = sheet.getRange(i, 1).getValue();
    if (rowIssueId === issueId) {
      sheet.deleteRow(i);
      logSheet.appendRow([new Date(), "Issue closed and row deleted", "Issue ID: " + issueId]);
      break;
    }
  }
}

handleOpenedIssue関数

この関数は、Issueが新しく作成されたときにスプレッドシートに追加します。既存のIssueがあればその内容を更新し、新しいIssueであれば新しい行を追加します。

const handleOpenedIssue = (sheet, lastRow, insertRow, issue, issueId) => {
  const title = issue.title;
  const labels = issue.labels.map(label => label.name).join(", ");
  const description = issue.body;
  const url = issue.html_url;
  let foundRow = false;
  for (let i = 1; i <= lastRow; i++) {
    const rowIssueId = sheet.getRange(i, 1).getValue();
    if (rowIssueId === issueId) {
      sheet.getRange(i, 2).setValue(title);
      sheet.getRange(i, 3).setValue(labels);
      sheet.getRange(i, 4).setValue(description);
      sheet.getRange(i, 9).setValue(url);
      foundRow = true;
      break;
    }
  }
  if (!foundRow && lastRow >= insertRow) {
    sheet.insertRowBefore(insertRow);
    sheet.getRange(insertRow, 1).setValue(issueId);
    sheet.getRange(insertRow, 2).setValue(title);
    sheet.getRange(insertRow, 3).setValue(labels);
    sheet.getRange(insertRow, 4).setValue(description);
    sheet.getRange(insertRow, 9).setValue(url);
  }
}

  const title = issue.title;
  const labels = issue.labels.map(label => label.name).join(", ");
  const description = issue.body;
  const url = issue.html_url;

部分でスプレッドシートに記述する情報を変数に格納している

  for (let i = 1; i <= lastRow; i++) {
    const rowIssueId = sheet.getRange(i, 1).getValue();
    if (rowIssueId === issueId) {
      sheet.getRange(i, 2).setValue(title);
      sheet.getRange(i, 3).setValue(labels);
      sheet.getRange(i, 4).setValue(description);
      sheet.getRange(i, 9).setValue(url);
      foundRow = true;
      break;
    }
  }

で、issueがスプレッドシートに存在するかを確認している。
存在した場合、該当する行の内容を更新する。

if (!foundRow && lastRow >= insertRow) {
  sheet.insertRowBefore(insertRow);
  sheet.getRange(insertRow, 1).setValue(issueId);
  sheet.getRange(insertRow, 2).setValue(title);
  sheet.getRange(insertRow, 3).setValue(labels);
  sheet.getRange(insertRow, 4).setValue(description);
  sheet.getRange(insertRow, 9).setValue(url);
}

シートにissueが存在しなかった場合、新しく行を作って情報を追加する。
sheet.insertRowBefore(insertRow);で挿入する行を指定している。
降順に並べたいため、今回は3行目に挿入指定。

handleCreatedAction関数

この関数は、GitHub Issueに新しいコメントが追加されたときの処理を行います。

const handleCreatedAction = (sheet, lastRow, issueId) => {
  const latestComment = fetchLatestIssueComment(issueId);
  for (let i = 1; i <= lastRow; i++) {
    const rowIssueId = sheet.getRange(i, 1).getValue();
    if (rowIssueId === issueId) {
      sheet.getRange(i, 4).setValue(latestComment);
      break;
    }
  }
}
  • for文で更新されたissueのidと一致するシートの行を探す。
  • 一致した行の4列目（D列）に、コメント内容を追加する。

handleLabeled関数

この関数は、GitHub Issueのラベルが追加・削除されたときの処理を行います。

const handleLabeled = (sheet, lastRow, issueId) => {
  const labels = fetchCurrentIssueLabels(issueId);
  for (let i = 1; i <= lastRow; i++) {
    const rowIssueId = sheet.getRange(i, 1).getValue();
    if (rowIssueId === issueId) {
      sheet.getRange(i, 3).setValue(Array.from(labels).join(", "));
      break;
    }
  }
}

webhookの設定

GitHub Issueが編集された時、自動的にスプレッドシートに反映されるために、Webhookの設定を行います。手順は以下の通りです。

GASをデプロイする

デプロイとは、開発した機能を公開して外部から利用できるようにすることです。

  1. ブラウザ右上のデプロイボタンからデプロイを開始します。
  2. 設定内容は以下の通りです。
  • 種類の選択：ウェブアプリ
  • 次のユーザーとして実行：自分
  • アクセスできるユーザー：全員（GitHubで利用するため）
  1. デプロイ後、ウェブアプリのURLが発行されるので、コピーしておきます。

注意
ウェブアプリのURLは、GASを編集するたびにバージョンを新しくする必要があるので注意してください。

githubのwebhookにGASで発行したURLを登録する

以下の手順で設定を行います。

  1. githubのリポジトリのsettigsタブを選択
  2. サイドバーのwebhookを選択
  3. Add webhookボタンをクリック

上記画像の画面でWebhookの発火条件を指定します。

  1. 「Payload URL」にGASでデプロイしたサイトURLを貼り付けます。
  2. 「Content type」で「application/json」を選択します。
  3. 「Which events would you like to trigger this webhook?」で、Webhookのトリガー条件を指定します。今回は、Issueが作成された時や更新された時にトリガーが発火するように「issues」と「issue comments」を選択します。

おわりに

今回、GASを使って初めて開発を行いました。設計を立てずに実装を始めた結果、後からトリガー関数の存在を知り、手戻りが発生。。。要件設計の重要性を改めて実感しました。

何もわからないところから、一週間前後で実装できたのですが、これはChatGPTさんのおかげです。やりたいことを明確に伝えられなくても、ラリーを重ねる中で作りたいものが作れるのだから、ChatGPTは本当にすごいですね。

最後まで読んでいただき、ありがとうございました！

Discussion