Zenn
😺

Backlogの工数をAPIで自動集計してスプレッドシートに出力する仕組みを作った話

に公開
API
GAS
自動化
Backlog
Google Sheets
tech

1. はじめに

この記事の目的と背景

目的

この記事では、Backlogで各個人がどの課題でどれくらい工数を消化しているのかを、BacklogAPIとGoogleスプレッドシートを利用して集計する方法をまとめます。

背景

請負契約でプロジェクトに参画していたとき、「毎月○時間」というような目標工数が定められており、その達成状況を確認・管理する必要がありました。

しかし、実際の運用では以下のような課題があり、誰がいつ・どれくらいの工数を積み上げているのかが非常に見えづらい状態になっていました。

  • 工数の集計は「完了」ステータスになった課題だけを対象に、月末にまとめて手動で実施していた
  • そのため、月をまたいで進行中の課題については、完了するまで請求対象にならず、どの月にどれだけ作業されたかの把握が困難だった
  • 月をまたがない場合でも課題がたくさんある場合は合計工数がわかりにくくなっていた

このような状況を改善するために、BacklogのAPIを使ってタスクの工数を定期的に自動集計し、スプレッドシートに出力する仕組みを構築しました。

2. 現状の課題

工数入力の流れ

  • Backlogの「実績時間」に各自がタスクの作業時間を記録
  • 管理者が手動でデータを集計し、スプレッドシートにまとめていた

問題点

  • 人力集計でミスが起きる
  • 工数入力が遅れると集計も遅れる
  • メンバーの入力状況の把握が面倒、リアルタイムで確認できない

3. 解決アプローチ

やりたいこと

  • Backlog APIを使ってタスクデータを取得し、スプレッドシートに出力
  • 各メンバーごとにシートを分けて工数を集計
  • 必要なデータは工数が入力された日付、プロジェクト名、課題名、消化工数

担当者ごとに下記のようなフォーマットでスプレッドシートに出力する想定

ID 日付 プロジェクト名 課題名 消化工数
1 yyyy/MM/dd HH:mm:ss プロジェクト1 課題A 8
2 yyyy/MM/dd HH:mm:ss プロジェクト1 課題B 4
3 yyyy/MM/dd HH:mm:ss プロジェクト2 課題C 20

使用技術・言語

BacklogAPI, GAS, Googleスプレッドシート

4. 実装内容の紹介

今回は「ユーザーの最近の活動の取得」APIを使用します。

GET /api/v2/users/:userId/activities?apiKey={apiKey}

「実績時間」の増加分を消化した工数としてログシートに記録します。

1. 「個人設定 > API」からAPIキーを取得

APIキーの取得画面

2. Backlogから取得したデータを記入するためのスプレッドシートを用意

新規のGoogleスプレッドシートを作成し、下記のフォーマットで「ユーザー一覧」シートを作成します。

名前 ユーザーID
Alex 1
Bola 2
Charlie 3

「名前」はログシートの名前に、「ユーザーID」はログの取得に使います。

3. スクリプトを用意

1. 「拡張機能 > Apps Script」からスクリプトの設定画面を開く

2. 工数の集計に必要なスクリプトを設定

const API_KEY = 'XXX';
const SPACE_ID = 'XXX';
const BASE_URL = `https://${SPACE_ID}.backlog.com/api/v2`;

/**
 * ユーザー一覧からユーザーIDを取得し、それぞれのログシートに実績時間の変更履歴を追記する
 */
function fetchAllUsersActualHourLogs() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const userSheet = ss.getSheetByName("ユーザー一覧");
  if (!userSheet) throw new Error("「ユーザー一覧」シートが存在しません。");

  // ユーザー一覧シートからデータを取得（ヘッダーを除く）
  const userData = userSheet.getDataRange().getValues().slice(1);

  userData.forEach(([name, userId]) => {
    if (!name || !userId) return;

    const logSheetName = sanitizeSheetName(`${name}_ログ`);
    const logSheet = prepareLogSheet(ss, logSheetName);

    const maxId = getMaxActivityId(logSheet);
    const activities = getRecentUserActivities(userId, maxId);

    const rows = convertActivitiesToRows(activities);
    rows.forEach(row => logSheet.appendRow(row));
  });
}

/**
 * ログシートを取得、なければ作成し、ヘッダーを設定する
 */
function prepareLogSheet(ss, sheetName) {
  let sheet = ss.getSheetByName(sheetName);
  if (!sheet) {
    sheet = ss.insertSheet(sheetName);
    sheet.appendRow(["ID", "日時", "プロジェクト名", "課題名", "消化工数"]);
  }
  return sheet;
}

/**
 * ログシートから最大のアクティビティIDを取得する（差分取得のため）
 */
function getMaxActivityId(sheet) {
  const lastRow = sheet.getLastRow();
  if (lastRow <= 1) return 0;

  const idColumn = sheet.getRange(2, 1, lastRow - 1, 1).getValues().flat();
  return Math.max(...idColumn.filter(id => typeof id === 'number' || !isNaN(id)).map(Number));
}

/**
 * 指定ユーザーのアクティビティログを Backlog API から取得する
 * minId を基準に差分取得（100件ずつループで取得）
 */
function getRecentUserActivities(userId, minId) {
  const activities = [];
  let currentMinId = minId;

  while (true) {
    const params = {
      apiKey: API_KEY,
      count: 100,
      order: 'asc',
    };

    if (currentMinId > 0) {
      params.minId = currentMinId + 1;
    }

    const query = Object.entries(params)
      .map(([k, v]) => `${encodeURIComponent(k)}=${encodeURIComponent(v)}`)
      .join('&');

    const url = `${BASE_URL}/users/${userId}/activities?${query}`;
    const response = UrlFetchApp.fetch(url);
    const result = JSON.parse(response.getContentText());

    if (!result || result.length === 0) break;

    activities.push(...result);
    currentMinId = Math.max(...result.map(item => item.id));
    if (result.length < 100) break;
  }

  return activities;
}

/**
 * アクティビティデータをログ出力用の行形式に変換する
 */
function convertActivitiesToRows(activities) {
  return activities
    .map(activity => {
      const { id, created, project, content } = activity;
      const projectName = project?.name || "（不明）";
      const summary = content?.summary || "（不明）";

      if (!content?.changes) return null;

      const actualHoursChange = content.changes.find(change => change.field === 'actualHours');
      if (!actualHoursChange) return null;

      const from = parseFloat(actualHoursChange.old_value || '0');
      const to = parseFloat(actualHoursChange.new_value || '0');
      const diff = to - from;

      if (diff === 0) return null;

      return [id, formatDate(created), projectName, summary, diff];
    })
    .filter(row => row !== null);
}

/**
 * ISO形式の日付を「yyyy/MM/dd HH:mm:ss」形式に変換
 */
function formatDate(isoStr) {
  const date = new Date(isoStr);
  return Utilities.formatDate(date, Session.getScriptTimeZone(), "yyyy/MM/dd HH:mm:ss");
}

/**
 * シート名に使えない文字を置換して整形
 */
function sanitizeSheetName(name) {
  return name.replace(/[\\/?*[\]:]/g, "_").substring(0, 100);
}

3. トリガーで定期実行されるように設定

左メニューからトリガーの設定画面に移り、「トリガーの追加」で先ほど設定した関数が定期実行されるように設定します。

4. 集計用のシートを作成

ログシートに日付や課題名を集計できたので、これをもとにして各月ごとや課題ごとの工数を集計します。

例えば4月の工数を集計したい場合は下記のようにセルに入力すれば計算してくれます。

=SUMIFS(E:E, B:B, ">=2025/4/1", B:B, "<=2025/4/30")

5. 実際に使ってみて

良かった点

常にメンバーの消化工数を把握できるため仕事のペースをコントロールしやすくなりました。
また、入力状況も把握できるので入力漏れを検知しやすくなりました。

想定外だったこと

時々謎のエラーが発生して集計ができていないことがありました。再現性がなかったのでおそらくBacklog側の一時的な問題だったのかなと思っていますが、使う場合は注意してください。

6. おわりに

Backlogの実績時間を集計したいというのは割と特殊な状況だと思いますが、もし必要だったら使ってみてください。

今回は最近の活動の内容を取得しただけですが、他にもたくさんAPIが用意されているので色々と触ってみると面白そうです。

Discussion