GASでスプレッドシートのセル編集回数をカウントできるスクリプト

Question

前提

GASでスプレッドシートのセル編集回数をカウントできるスクリプト

実現したいこと

スプレッドシートのセルにフィルタで3種類の値を選択できるようになっており、
3回編集したらセルを黒で塗りつぶしをしたい。
もしくは編集回数をカウントして1〜3を他のセルに出力できるスクリプトがあればご教示頂きたいです。

試したこと

関数ではできなかった。

補足情報（FW/ツールのバージョンなど）

Answer 1

手順

• スプシを開いたら、メニューバーに［カスタムメニュー］→［編集回数カウント用シートを作成］を生成する。
• ↑をクリックすることで、編集回数をカウントしたい対象のシートを複製する（そしてそれをカウント用シートとする）。
• 対象シートが編集されたら、対応するカウント用シートに、そのセルの編集回数を+1する。
• カウント用シートの数字が3以上なら、対象シートのセルの背景を黒色にする。

コード例

function onOpen() {
  // スプシを開く時、カスタムメニュを追加
  const customMenu = SpreadsheetApp.getUi();
  customMenu.createMenu('カスタムメニュー')　// カスタムメニュー名（[ファイル][編集][表示]...[カスタムメニュー]）
    .addItem('編集回数カウント用シートを作成', 'createCountSheet')　// [カスタムメニュー]をクリックしたら出てくるメニューアイテム
    .addToUi()
}

/**
 * 編集回数カウント用シートを新規作成する関数
 */
function createCountSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 対象シートを取得
  const originalSheet = ss.getActiveSheet();

  // 対象シートのシート名を取得
  const originalSheetName = originalSheet.getName();
  // カウント用のシート名は、『【カウント用】対象のシート名』とする
  const countSheetName = `【カウント用】${originalSheetName}`;
  // カウント用シートを取得
  const countSheet = ss.getSheetByName(countSheetName);

  // ui生成（あとでアラート表示に使うため）
  const ui = SpreadsheetApp.getUi();

  // カウント用シートが既に存在する場合
  if (countSheet) {
    // カウント用シート新規作成処理を中断して、お知らせアラート表示する
    ui.alert('⚠️ 作成中止', `『${originalSheetName}』の編集回数カウント用シートはすでに作成済みのため、これ以上作成できません。`, ui.ButtonSet.OK);

  // カウント用シートが存在しない場合
  } else {
    // 対象シートを複製
    const newSheet = originalSheet.copyTo(ss);
    // シート名を『【カウント用】対象のシート名』にする
    newSheet.setName(countSheetName);
    // 背景色をグレーにし、さらに、ドロップダウンリストを削除
    newSheet.getDataRange().setBackground('#cccccc').clearDataValidations();
    // シートに保護をかけ、編集しようとすると警告がでるようにする
    newSheet.protect().setDescription('GASにより保護されたシート').setWarningOnly(true);

    // シート複製官僚のお知らせアラート表示する
    ui.alert('✅ 作成完了', `『${countSheetName}』を新規作成しました。`, ui.ButtonSet.OK);
  }
}

/**
 * 編集回数をカウントする関数
 * @param {object} e - イベントオブジェクト
 */
function countEdit(e) {
  // 編集されたスプシを取得
  const ss = e.source;

  // 編集されたシートを取得
  const originalSheet = ss.getActiveSheet();
  // 編集されたシートのシート名を取得
  const originalSheetName = originalSheet.getName();

  // カウント用のシート名は、『【カウント用】対象のシート名』である
  const countSheetName = `【カウント用】${originalSheetName}`;
  // カウント用シートを取得
  const countSheet = ss.getSheetByName(countSheetName);

  // カウント用シートが存在する場合のみ以下の処理をする
  if (countSheet) {
    // 編集されたセルの行番号を取得
    const editedRow = e.range.getRow();
    // 編集されたセルの列番号を取得
    const editedCol = e.range.getColumn();

    // カウント用シートの、編集されたセルと同じセルを取得
    const range = countSheet.getRange(editedRow, editedCol);
    // 値に+1する（編集回数を+1する）
    const count = range.getValue() + 1;
    range.setValue(count);

    // カウントが3以上だったら
    if (count >= 3) {
      // 編集されたセルの背景を黒色にする
      originalSheet.getRange(editedRow, editedCol).setBackground('#333333');
    }
  }
}

Answer 2

対象範囲や「編集回数」の"編集”について詳細な定義が一切書かれていないので
一番単純に"すべてのシート・すべてのセル範囲において"「編集回数　＝ onEditトリガーが呼び出される回数」かつ、「複数のセルが同時に編集された場合であっても、rowStart と columnStart のみ、黒塗りする対象セルとする」と見なしました。
また、シート名には「^」記号が含まれていないものと見なしています。

この場合、下記のようなコードになると思われます。

（PropertiesService の DocumentProperties に、「キー＝"編集"されたシート名・セルのアドレス、値＝"編集"回数」として書き込む）

function onEdit(e) {
  const props = PropertiesService.getDocumentProperties();
  const sh = SpreadsheetApp.getActive().getActiveSheet();
  const shid = sh.getSheetId();
  const address = [shid, e.range.rowStart, e.range.columnStart].join("^");
  let count = props.getProperty(address);

  if (count == null) {
    props.setProperty(address, 1);
    return;
  }

  if (count >= 3) return;

  props.setProperty(address, ++count);
  if (count === 3) {
    sh.getRange(e.range.rowStart, e.range.columnStart, 1, 1).setBackground("black");
  }
}

※
※ 編集回数があまりに多いと、onEditの呼び出し回数制限や、SpreadSheetAppsのAPI回数制限、PropertiesServiceの書き込みサイズ制限等に引っ掛かって動作しなくなりますが、GASとは「そういうもの」ですので、事前に御了承ください。

※ もちろん、PropertiesService ではなく、別の回数記録専用シートを作っておいてそれに回数を記録するという手段も取れます(コードは割愛）
。（「すべてのシート」という要件を満たさなくなってしまうため回答には採用しませんでしたが）