Google Sheetsで特定の列の編集を検知し、別の列に自動でタイムスタンプを追加する

# はじめに

Google Sheetsを使用してプロジェクト管理や日々のタスク管理を行う際、特定の列が編集されたときに自動的に別の列にタイムスタンプを追加する機能があると便利です。例えば、タスクの状態が変更されたときに自動的に更新日時を記録したり、新しい項目が追加されたときに作成日時を自動入力したりすることができます。

この記事では、Google Apps Scriptを使用して、この機能を簡単に実装する方法を紹介します。

# 実装方法


## 1. スプレッドシートの準備

まず、以下のような構造の「シート1」を準備します。（例）

| タスク | 状態 | 完了フラグ | 作成日時 | 更新日時 | 完了日時 |
|-------|------|-----------|---------|---------|---------|
|       |      |           |         |         |         |

この表の各列は以下の役割を持ちます：
- タスク：タスクの内容を入力する列
- 状態：タスクの現在の状態を入力する列
- 完了フラグ：タスクが完了したかどうかを示す列（チェックボックスを使用）
- 作成日時：タスクが作成された日時（自動入力）
- 更新日時：タスクの状態が更新された日時（自動入力）
- 完了日時：タスクが完了した日時（自動入力）

## 2. スクリプトエディタを開く

1. Google Sheetsで対象のスプレッドシートを開きます。
2. メニューから「ツール」→「スクリプトエディタ」を選択します。

## 3. コードを追加する

スクリプトエディタに以下のコードを貼り付けます。

```javascript
/**
 * スプレッドシートの編集時に自動的に実行される関数
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e - 編集イベントオブジェクト
 */
function onEdit(e) {
  addTimeStampOnTrigger(e, "シート1", "状態", "更新日時");
  addTimeStampOnTrigger(e, "シート1", "タスク", "作成日時");
  addTimeStampOnTrigger(e, "シート1", "完了フラグ", "完了日時", value => value === true);
}

/**
 * トリガー列が編集されたときに、対象列にタイムスタンプを追加する
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e - 編集イベントオブジェクト
 * @param {string} sheetName - シート名
 * @param {string} triggerHeaderName - トリガーとなる列のヘッダー名
 * @param {string} targetHeaderName - タイムスタンプを追加する列のヘッダー名
 * @param {function} [condition] - オプションの条件関数。指定された場合、この関数がtrueを返すときのみタイムスタンプを追加する
 */
function addTimeStampOnTrigger(e, sheetName, triggerHeaderName, targetHeaderName, condition) {
  try {
    const sheet = e.source.getSheetByName(sheetName);
    if (!sheet) {
      throw new Error(`シート '${sheetName}' が見つかりません。`);
    }

    const triggerColumn = getColumnIndexByHeader(sheet, triggerHeaderName);
    const targetColumn = getColumnIndexByHeader(sheet, targetHeaderName);

    if (triggerColumn === -1) {
      throw new Error(`'${triggerHeaderName}' 列が見つかりません。`);
    }
    if (targetColumn === -1) {
      throw new Error(`'${targetHeaderName}' 列が見つかりません。`);
    }

    const editedColumn = e.range.getColumn();
    const editedRow = e.range.getRow();
    const editedValue = e.range.getValue();
    
    // ヘッダー行（1行目）の編集を無視する
    if (editedRow === 1) {
      return;
    }

    if (editedColumn === triggerColumn) {
      if (!condition || condition(editedValue)) {
        sheet.getRange(editedRow, targetColumn).setValue(new Date());
      }
    }
  } catch (error) {
    Logger.log(`エラーが発生しました: ${error.message}`);
    showErrorAlert(error.message);
  }
}

/**
 * シートのヘッダーから列のインデックスを取得する
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - 対象のシート
 * @param {string} headerName - 探すヘッダー名
 * @returns {number} 列のインデックス（1から始まる）。見つからない場合は-1
 */
function getColumnIndexByHeader(sheet, headerName) {
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const index = headers.indexOf(headerName);
  return index !== -1 ? index + 1 : -1;
}

/**
 * エラーアラートを表示する
 * @param {string} message - エラーメッセージ
 */
function showErrorAlert(message) {
  SpreadsheetApp.getUi().alert('エラー', message, SpreadsheetApp.getUi().ButtonSet.OK);
}
```

## 3. コードの説明

このスクリプトの主な機能は以下の通りです：

- `onEdit(e)`: スプレッドシートが編集されるたびに自動的に実行される関数です。
- `addTimeStampOnTrigger()`: 指定された条件に基づいてタイムスタンプを追加する主要な関数です。
- `getColumnIndexByHeader()`: ヘッダー名から列のインデックスを取得する補助関数です。
- `showErrorAlert()`: エラーメッセージを表示する関数です。

## 4. 使用方法

`onEdit(e)` 関数内で `addTimeStampOnTrigger()` を呼び出すことで、どの列の編集を検知し、どの列にタイムスタンプを追加するかを指定します。

```javascript
function onEdit(e) {
  addTimeStampOnTrigger(e, "シート1", "状態", "更新日時");
  addTimeStampOnTrigger(e, "シート1", "タスク", "作成日時");
  addTimeStampOnTrigger(e, "シート1", "完了フラグ", "完了日時", value => value === true);
}
```

各行の意味は以下の通りです：

1. "状態"列が編集されたら、"更新日時"列にタイムスタンプを追加
2. "タスク"列が編集されたら、"作成日時"列にタイムスタンプを追加
3. "完了フラグ"列が編集され、その値が `true` になったら、"完了日時"列にタイムスタンプを追加

## 5. カスタマイズ

自分のスプレッドシートに合わせて、シート名、列名、条件を変更してください。例えば：

```javascript
function onEdit(e) {
  addTimeStampOnTrigger(e, "プロジェクト管理", "進捗状況", "最終更新日");
  addTimeStampOnTrigger(e, "日報", "業務内容", "記入時刻");
  addTimeStampOnTrigger(e, "在庫管理", "在庫数", "確認日時", value => value < 10);
}
```

# まとめ

この方法を使用することで、Google Sheetsで特定の列の編集を検知し、別の列に自動でタイムスタンプを追加することができます。プロジェクト管理、タスク管理、在庫管理など、様々な用途に応用可能です。

ぜひ自分のスプレッドシートに適用して、作業の効率化を図ってみてください。

# 注意点

- このスクリプトは編集権限のあるユーザーの操作に対してのみ動作します。
- 大量のデータを扱う場合、処理に時間がかかる可能性があります。
- スクリプトのエラーはログに記録され、アラートとして表示されます。

皆様のGoogle Sheets活用にお役立ていただければ幸いです。