【GAS】チェックしたセルを変身させる方法

ナカムラ。

2023年9月30日 19:51

今回の内容
・チェックすると、そのセルに時間を入れるGASのコードを書いた。
・コードの紹介、応用と使用例の紹介。
・ChatGPT（3.5）を使って書いたので、プロンプトの紹介。

はじめに
私は本職のプログラマでは無いので「コードの良し悪し」について判断できません、見る人からしたら違和感のある記述もあるかもしれませんがご了承ください。
「動けばええやろ」の精神です。

コードの説明

・実際の挙動

挙動に関しては、画像の通りです。
チェックボックスにチェックを入れると「今の時間」を自動で入力してくれます。

「チェックした」ことをトリガーにGASが起動するイメージ。
※あくまでもイメージ。

・使用するコード

// 初期設定
const sName   = "test";         // シート名。
const sRanges = ["A:B", "C:C"]; // 範囲指定。
const Format  = "hh:mm:ss";     // 書式設定。

function onEdit(e) {
  const eSheetName = e.source.getSheetName();
  if (eSheetName === sName) {
    for (const range of sRanges) {
      TimeStamp(e.source.getSheetByName(sName), range);
    }
  }
}
function TimeStamp(sheet, range) {
  const Time   = new Date();
  const sRange = sheet.getRange(range);
  const Data   = sRange.getValues();
  

  //「Data」配列内で「TRUE」のセルを「Time」の値に書換える。
  Data.forEach((row, i) => {
    row.forEach((cellValue, j) => {
      if (cellValue === true) {
        const cell = sRange.getCell(i + 1, j + 1);
        cell
          .clearDataValidations()
          .setValue(Time)
          .setNumberFormat(Format);
      }
    });
  });
}

最初の「初期設定」の部分を変更することで、色々なシートで対応させることが出来ます。

①シート名
実行したいシートの「シート名」を入力してください。
※シートの指定は必ず1枚にして下さい。（「"test01 , test02"」と指定すれば複数指定可能ですがオススメはしません）

②範囲指定
セル範囲を指定してください。
関数で範囲を指定する要領で範囲を指定できます。

③書式設定
省略可能です。
「時間（シリアル値）」を扱う際は、必須になります。

④入力する値
コード中盤の「Time」の中身を弄ってください。
時間だけでなく、好きな文字列や、数式や別セルの参照など、色々と応用や拡張も可能です。

活用例

・Case.1：進捗管理表

「タスク」と書きましたが、議事録作成のメモや、勉強の目標など、色々と応用は利くと思います。

・Case.2：戦績管理ツール

私が実際に使用しているシートの例です。
対人ゲームの戦績を管理しています。

指定した期間内の勝率を振り返ったり、
相手キャラの分布に関して、時間帯によって変化する事を実感できて楽しいです。

車輪の再発明

・便利なショートカットを活用しよう！

既にお気付きの方もいるかと思いますが、
正直なところ「Ctrl +Shift + :」「Ctrl +Shift +Alt + :」のショートカットで事足りる内容です。

「文字列に変身」させたい場合や、「共有シートでフォーマットを固定」させたい場合など、使用用途としては少し限定的になるかもしれません。

でもニッチな需要に応えるのがマクロじゃね？とも思う。

・コード作成の経緯

元々の作成経緯として「キーボード操作をしたくない」「マウス操作で完結させたい」という考えがあります。（Case.2のシートのように）

この需要があるかはわかりませんが、別の動作に集中出来たり、ワンクリックで操作が済むなど、使用感はとても良いです。

コードについては以上になります。
以降は「ChatGPTでプログラミングする方法」になります。
いかにして先のコードを作成したか、お見せします。

後編：ChatGPTでプログラミングする方法

・前提条件

バージョン： GPT-3.5（無料のやつ）
実行日： 2023年9月末

前提知識：何もいらない。
※プログラミングの基礎の基礎知識はあったほうが良いかも。（youtubeの動画1本みる程度）

・プロンプトの例

①こんなコード作って！
②ここ修正して！追加して！
基本的にはこの2つの繰り返しです。

①に関してもう少し具体的に言うと。
・言語名（アプリ名）の宣言
・ザックリとしたコードの説明（書かなくても良い）
・詳細なコードの説明
この順番で書くことを意識してください。

実際の会話内容

履歴が残っていなかったので、０からまた作成しました。
コードが少し変わっていますが、処理の内容自体は同じです。
コード作成の考え方と再現性が伝えられたら、と思います。

・STEP.01

まずはメイン処理部分を作成。
「チェックセル（TRUE）を時間に書き換えてくれ」と要望。

【質問文】
GASのコードを作成してください。
スプレッドシートの指定範囲内にある「TRUE」のチェックボックスすべてに、今の時間を入れるコードです。
【処理内容】
①最初に、以下の変数を宣言してください。
・シート名：今回は「test」という名前のシート。
・適用範囲：今回は「A:A」の範囲。
②指定されたシートの範囲内にある、チェック済みセル全てに、今の時間を書き込む。（シリアル値）

カギ括弧や句読点が、案外重要だったりする。

function updateCheckedCells() {
  var sheetName = "test"; // シート名
  var range = "A:A"; // 適応範囲

  // スプレッドシートを開く
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName(sheetName);
  var data = sheet.getRange(range).getValues();

  // 現在の時間を取得
  var currentTime = new Date();

  // チェックボックスがTRUEのセルに時間を入力
  for (var i = 0; i < data.length; i++) {
    if (data[i][0] === true) {
      sheet.getRange(i + 1, 1).setValue(currentTime);
    }
  }
}

コードを実行してみると…。

時間は入力されたものの、警告文が表示されました。

「入力規則に違反している」とあります。
チェックボックスは「入力規則」なので、この設定が残っているのでしょう。

赤い点が気にならなければここで終了でも大丈夫です。

・STEP.02

入力規則を消すように要望を出します。

【質問文】
入力の前に、そのセルの「入力規則」をクリアにしてから書き込む形にしてください。

「消す」「削除」よりも「クリア」の方がうまく行く。（経験則）

function updateCheckedCells() {
  var sheetName = "test"; // シート名
  var range = "A:A"; // 適応範囲

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var data = sheet.getRange(range).getValues();
  var currentTime = new Date();

  data.forEach(function(row, index) {
    if (row[0] === true) {
      var cell = sheet.getRange(index + 1, 1);
      cell.clearContent();
      var rule = cell.getDataValidation();
      if (rule != null) {
        cell.clearDataValidations(); // 入力規則をクリア
      }
      cell.setValue(currentTime); // 現在の時間を入力
    }
  });
}

警告文が消えました、うまく行ったようです。

・STEP.03

次は表示形式。

時間のみを表示したいのですが、
表示形式を設定しても、コードを起動するたび更新されてしまいます。

こちらもコード内で制御する必要があるようです。
「時間入力後、指定した範囲全てに同じ書式設定をするように」と要望を出します。

【質問文】
先程のコードに、「書式設定」の変数を追加してください。
・今回は「 hh:mm:ss」
また、全ての処置が終了した後、以下の操作をするコードを追加してください。
【追加コード】
最初に指定された範囲内すべてに、この書式を設定する。

function updateCheckedCells() {
  var sheetName = "test"; // シート名
  var range = "A:A"; // 適応範囲
  var format = "hh:mm:ss"; // 書式設定

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var rangeToApplyFormat = sheet.getRange(range);

  var data = rangeToApplyFormat.getValues();
  var currentTime = new Date();

  data.forEach(function(row, index) {
    if (row[0] === true) {
      var cell = rangeToApplyFormat.getCell(index + 1, 1);
      cell.clear({contentsOnly: true, validations: true});
      cell.setValue(currentTime);
    }
  });

  rangeToApplyFormat.setNumberFormat(format); // 指定された書式を設定
}

これでメイン処理部分は完成です。
次は「チェックしたらこのコードを起動させる」コードを書きます。

・STEP.04

貰ったコードをちょっと弄って質問文を作ります。
※ここは少しプログラミングの知識が必要になるかもしれません。

【質問文】
GASのコードを作成して下さい。
トリガーに設定するコードです。
以下のコードがあり、条件を満たした場合に「updateCheckedCells」関数を呼出すコードを作成してください。

【条件】
①編集されたシート名と、指定した「シート名」が同じか判定。
・TRUEなら、「updateCheckedCells」関数を起動。
・FALSEなら、何もせずに終了。

【コード】
var sheetName = "test"; // シート名
var range = "A:A"; // セル範囲
function updateCheckedCells() {}

var sheetName = "test"; // シート名
var range = "A:A"; // セル範囲

function onEdit(e) {
  var editedSheet = e.source.getSheetName();
  
  if (editedSheet === sheetName) {
    updateCheckedCells();
  }
}

function updateCheckedCells() {
  //省略
}

・STEP.05

「STEP.03」と「STEP.04」のコードを合体させましょう。

ただ単純に2つのコードをコピペして質問するだけでOKです。
ただ、どうしてもアバウトな質問になるので、再生成を連打しても上手くいかない可能性があります。

これは自分で修正箇所を探して2つのコードを連結させるのが確実です。

【質問文】
2つのコードを連結させてください。
【コード１】
「STEP.03」のコードを貼付ける。
【コード２】
「STEP.04」のコードを貼付ける。

提示されたコードを少し弄ったコード

var sheetName = "test"; // シート名
var range = "A:A"; // 適応範囲
var format = "hh:mm:ss"; // 書式設定

function onEdit(e) {
  var editedSheet = e.source.getSheetName();
  
  if (editedSheet === sheetName) {
    updateCheckedCells();
  }
}
function updateCheckedCells() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var rangeToApplyFormat = sheet.getRange(range);

  var data = rangeToApplyFormat.getValues();
  var currentTime = new Date();

  data.forEach(function(row, index) {
    if (row[0] === true) {
      var cell = rangeToApplyFormat.getCell(index + 1, 1);
      cell.clear({contentsOnly: true, validations: true});
      cell.setValue(currentTime);
    }
  });

  rangeToApplyFormat.setNumberFormat(format); // 指定された書式を設定
}

コードの大枠はこれで完成になります。

・この先の流れ

後は細かい修正になります。
こちらも同じ要領で「質問」と「修正」を繰り返しになります。

ときには全てを捨てて「STEP1」からやり直した方が早い場合もあります。
臆せずに最初の要件から見直すことも大切です。

今回の内容は以上になります、お疲れさまでした。
こんな記事ですが一読してくださり、ありがとうございました。

それでは。

この記事が気に入ったらサポートをしてみませんか？