@kyanny's blog

My thoughts, my life. Views/opinions are my own.

GitHub Projects (classic) でカードがカラム間の移動に要した日数を得る（ための材料）

TL;DR: Webhook のデータをどこかにためてカード毎に updated_at の差分を取る。

Projects (classic) でカードを別の列に移動すると、Webhook が送信される（Webhook を仕込んでいればの話）。Webhook 設定時の対象イベント名は Project cards。

カード移動イベントは Webhook ペイロードの "action":"moved" で判別できる。

カードが issue の場合は↓のような Webhook ペイロードが送信される。どの issue かは project_card.content_url でわかる。その代わりに project_card.note は null になっている。

{
  "action": "moved",
  "changes": {
    "column_id": {
      "from": 18986620
    }
  },
  "project_card": {
    "url": "https://api.github.com/projects/columns/cards/86808873",
    "project_url": "https://api.github.com/projects/14565852",
    "column_url": "https://api.github.com/projects/columns/18986621",
    "column_id": 18986621,
    "id": 86808873,
    "node_id": "PRC_lADOBRMu184A3kHczgUsmSk",
    "note": null,
    "archived": false,
    "creator": {
      "login": "kyanny",
      "id": 10515,
      "node_id": "MDQ6VXNlcjEwNTE1",
      "avatar_url": "https://avatars.githubusercontent.com/u/10515?v=4",
      "gravatar_id": "",
      "url": "https://api.github.com/users/kyanny",
      "html_url": "https://github.com/kyanny",
      "followers_url": "https://api.github.com/users/kyanny/followers",
      "following_url": "https://api.github.com/users/kyanny/following{/other_user}",
      "gists_url": "https://api.github.com/users/kyanny/gists{/gist_id}",
      "starred_url": "https://api.github.com/users/kyanny/starred{/owner}{/repo}",
      "subscriptions_url": "https://api.github.com/users/kyanny/subscriptions",
      "organizations_url": "https://api.github.com/users/kyanny/orgs",
      "repos_url": "https://api.github.com/users/kyanny/repos",
      "events_url": "https://api.github.com/users/kyanny/events{/privacy}",
      "received_events_url": "https://api.github.com/users/kyanny/received_events",
      "type": "User",
      "site_admin": true
    },
    "created_at": "2022-11-17T01:27:19Z",
    "updated_at": "2022-11-17T01:27:25Z",
    "content_url": "https://api.github.com/repos/kyanny-corp-enterprise-cloud-testing/testrepo/issues/43",
    "after_id": null
  },
  "organization": {
    "login": "kyanny-corp-enterprise-cloud-testing",
    "id": 85143255,
    "node_id": "MDEyOk9yZ2FuaXphdGlvbjg1MTQzMjU1",
    "url": "https://api.github.com/orgs/kyanny-corp-enterprise-cloud-testing",
    "repos_url": "https://api.github.com/orgs/kyanny-corp-enterprise-cloud-testing/repos",
    "events_url": "https://api.github.com/orgs/kyanny-corp-enterprise-cloud-testing/events",
    "hooks_url": "https://api.github.com/orgs/kyanny-corp-enterprise-cloud-testing/hooks",
    "issues_url": "https://api.github.com/orgs/kyanny-corp-enterprise-cloud-testing/issues",
    "members_url": "https://api.github.com/orgs/kyanny-corp-enterprise-cloud-testing/members{/member}",
    "public_members_url": "https://api.github.com/orgs/kyanny-corp-enterprise-cloud-testing/public_members{/member}",
    "avatar_url": "https://avatars.githubusercontent.com/u/85143255?v=4",
    "description": null
  },
  "enterprise": {
    "id": 5844,
    "slug": "kyanny-corp",
    "name": "Kyanny Corp.",
    "node_id": "MDEwOkVudGVycHJpc2U1ODQ0",
    "avatar_url": "https://avatars.githubusercontent.com/b/5844?v=4",
    "description": "",
    "website_url": "",
    "html_url": "https://github.com/enterprises/kyanny-corp",
    "created_at": "2021-02-22T06:24:38Z",
    "updated_at": "2021-03-10T07:44:28Z"
  },
  "sender": {
    "login": "kyanny",
    "id": 10515,
    "node_id": "MDQ6VXNlcjEwNTE1",
    "avatar_url": "https://avatars.githubusercontent.com/u/10515?v=4",
    "gravatar_id": "",
    "url": "https://api.github.com/users/kyanny",
    "html_url": "https://github.com/kyanny",
    "followers_url": "https://api.github.com/users/kyanny/followers",
    "following_url": "https://api.github.com/users/kyanny/following{/other_user}",
    "gists_url": "https://api.github.com/users/kyanny/gists{/gist_id}",
    "starred_url": "https://api.github.com/users/kyanny/starred{/owner}{/repo}",
    "subscriptions_url": "https://api.github.com/users/kyanny/subscriptions",
    "organizations_url": "https://api.github.com/users/kyanny/orgs",
    "repos_url": "https://api.github.com/users/kyanny/repos",
    "events_url": "https://api.github.com/users/kyanny/events{/privacy}",
    "received_events_url": "https://api.github.com/users/kyanny/received_events",
    "type": "User",
    "site_admin": true
  }
}

カードが issue ではない場合は↓のような Webhook ペイロードが送信される。project_card.content_url フィールドが存在しない代わりに project_card.note にカード本文が入っている。

{
  "action": "moved",
  "changes": {
    "column_id": {
      "from": 18986621
    }
  },
  "project_card": {
    "url": "https://api.github.com/projects/columns/cards/83770885",
    "project_url": "https://api.github.com/projects/14565852",
    "column_url": "https://api.github.com/projects/columns/18986622",
    "column_id": 18986622,
    "id": 83770885,
    "node_id": "PRC_lADOBRMu184A3kHczgT-PgU",
    "note": "**Cards**\nCards can be added to your board to track the progress of issues and pull requests. You can also add note cards, like this one!\n",
    "archived": false,
    "creator": {
      "login": "kyanny",
      "id": 10515,
      "node_id": "MDQ6VXNlcjEwNTE1",
      "avatar_url": "https://avatars.githubusercontent.com/u/10515?v=4",
      "gravatar_id": "",
      "url": "https://api.github.com/users/kyanny",
      "html_url": "https://github.com/kyanny",
      "followers_url": "https://api.github.com/users/kyanny/followers",
      "following_url": "https://api.github.com/users/kyanny/following{/other_user}",
      "gists_url": "https://api.github.com/users/kyanny/gists{/gist_id}",
      "starred_url": "https://api.github.com/users/kyanny/starred{/owner}{/repo}",
      "subscriptions_url": "https://api.github.com/users/kyanny/subscriptions",
      "organizations_url": "https://api.github.com/users/kyanny/orgs",
      "repos_url": "https://api.github.com/users/kyanny/repos",
      "events_url": "https://api.github.com/users/kyanny/events{/privacy}",
      "received_events_url": "https://api.github.com/users/kyanny/received_events",
      "type": "User",
      "site_admin": true
    },
    "created_at": "2022-07-01T09:33:48Z",
    "updated_at": "2022-11-16T14:59:48Z",
    "after_id": null
  },
  "organization": {
    "login": "kyanny-corp-enterprise-cloud-testing",
    "id": 85143255,
    "node_id": "MDEyOk9yZ2FuaXphdGlvbjg1MTQzMjU1",
    "url": "https://api.github.com/orgs/kyanny-corp-enterprise-cloud-testing",
    "repos_url": "https://api.github.com/orgs/kyanny-corp-enterprise-cloud-testing/repos",
    "events_url": "https://api.github.com/orgs/kyanny-corp-enterprise-cloud-testing/events",
    "hooks_url": "https://api.github.com/orgs/kyanny-corp-enterprise-cloud-testing/hooks",
    "issues_url": "https://api.github.com/orgs/kyanny-corp-enterprise-cloud-testing/issues",
    "members_url": "https://api.github.com/orgs/kyanny-corp-enterprise-cloud-testing/members{/member}",
    "public_members_url": "https://api.github.com/orgs/kyanny-corp-enterprise-cloud-testing/public_members{/member}",
    "avatar_url": "https://avatars.githubusercontent.com/u/85143255?v=4",
    "description": null
  },
  "enterprise": {
    "id": 5844,
    "slug": "kyanny-corp",
    "name": "Kyanny Corp.",
    "node_id": "MDEwOkVudGVycHJpc2U1ODQ0",
    "avatar_url": "https://avatars.githubusercontent.com/b/5844?v=4",
    "description": "",
    "website_url": "",
    "html_url": "https://github.com/enterprises/kyanny-corp",
    "created_at": "2021-02-22T06:24:38Z",
    "updated_at": "2021-03-10T07:44:28Z"
  },
  "sender": {
    "login": "kyanny",
    "id": 10515,
    "node_id": "MDQ6VXNlcjEwNTE1",
    "avatar_url": "https://avatars.githubusercontent.com/u/10515?v=4",
    "gravatar_id": "",
    "url": "https://api.github.com/users/kyanny",
    "html_url": "https://github.com/kyanny",
    "followers_url": "https://api.github.com/users/kyanny/followers",
    "following_url": "https://api.github.com/users/kyanny/following{/other_user}",
    "gists_url": "https://api.github.com/users/kyanny/gists{/gist_id}",
    "starred_url": "https://api.github.com/users/kyanny/starred{/owner}{/repo}",
    "subscriptions_url": "https://api.github.com/users/kyanny/subscriptions",
    "organizations_url": "https://api.github.com/users/kyanny/orgs",
    "repos_url": "https://api.github.com/users/kyanny/repos",
    "events_url": "https://api.github.com/users/kyanny/events{/privacy}",
    "received_events_url": "https://api.github.com/users/kyanny/received_events",
    "type": "User",
    "site_admin": true
  }
}

カラム間の移動に要した日数を得るのに必要な情報は、

  1. project_card.id
  2. project_card.column_id
  3. project_card.updated_at

Webhook レシーバー側でこれらの情報を記録する。Google シートと GAS でやるならこういう感じ。

function doPost(e) {
  var data = JSON.parse(e.postData.getDataAsString());
  if (data.action !== "moved") {
    return;
  }
  var card_id = data.project_card.id;
  var column_id = data.project_card.column_id;
  var updated_at = new Date(data.project_card.updated_at);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.appendRow([card_id, column_id, updated_at]);
  return "OK";
}

card_id でフィルタして updated_at の昇順に並べ替えて DATEDIF 関数で直前の日時との差分を取れば、カラム間の移動に要した日数を得られる。

=DATEDIF("2022/11/17", "2022/11/22", "D")

Projects (classic) のデータは REST API で取得できる。

column_id からカラム名を得るには、Get a project column を使えばよい。あるいはあらかじめ List project columns で列名と ID の一覧をとっておいてもよい（project_id は List (organization|repository|user) projects の結果から拾ってくる必要があるが）。

カード本文 project_card.note は Webhook ペイロード内にも入ってるので、Webhook レシーバー側で保存しておいてもいいし、 https://docs.github.com/en/rest/projects/cards でまとめてとってもいい。カードが issue の場合は issues の API で title をとってくる必要あり。

card_id と本文（タイトル）、column_id とカラム名のリストを得たら、別のシートにマスタデータとして保存しておいて VLOOKUP とかで突合すればよい。

カードの新規追加イベントも Webhook が飛ぶので（"action":"created"）、Webhook レシーバーを作り込めばマスタデータも自動更新できるだろう。

移動日のデータが得られたとして、どういうふうに可視化するのがよいのかは Excel 力不足のため no idea だが、たとえば滝グラフを使ってこんな風にするとか？

問題は、これを各カードごとにやるとなると、作業量が膨大になってしまいそう。これは GitHub Projects (classic) の範疇を超えたデータ分析スキルの領域にあたると思うので、ここいらで筆を置くこととする。