ChatGPT ✕ Googleスプレッドシート ～セルの更新日時を自動入力したい編～

こんにちは。ディレクターの栗原です。

この記事では、僕が個人的にかなり革命的だと感じた「Googleスプレッドシートで、隣のセルの更新日時を自動で入力＋更新させる方法を」紹介したいと思います。 VBAがわからなくても大丈夫。記事中に出てくる「Google Apps Script（GAS）」というGoogleが提供する拡張機能、一見難しそうに思えますが、ChatGPTを駆使すれば簡単で便利なツールになっちゃいます。

実際に僕がプロジェクトで使用した例を元に「一体何が便利なんだい」というところを経緯とともに説明していくので、お付き合いください。

そもそも何がしたかったのか

そもそもの発端としては、数カ月にわたるプロジェクトの終盤に待っている「受け入れテスト」という工程でした。社内でのテストが完了後、お客様にもテストを行っていただくこの工程に際して、共同編集用のドキュメントとしてGoogleスプレッドシートを使うことがあります。

便利なツールではあるのですが、「ここを直してほしい」「こうでしょうか？これなら対応できます」「じゃあこれなら？」などの双方のコメントをセル上に残していくうちに、コメントがいつ更新されたものなのかわからなくなってしまうことが頻発していました。

添付のキャプチャのように日付が入っていれば、更新されたことが多少はわかるものの、やり取りが長くなればなるほど、さらに確認項目数が数十個に積み重なれば積み重なるほど、コメントの更新を追いかけづらくなってしまいます。
セルごとに右クリックして「セルの編集履歴を表示」で確認する、という手段もありますが、それだとちょっと手間がかかる。日付の記載を徹底しても、長引くやり取りや書き忘れには対処しきれない。 そこで、なんとかしてパッと見で更新日時を確認できる方法はないだろうかと、方法を探し始めることにしました。

調べてもちょうどいいものが出てこない

まず初めに、各コメントを入力したセルの隣に自動で更新日時を出力させるようにすれば、ソートしたりフィルターをかけたりすることでコメントの更新を追いかけやすくなるのではと考えました。

そして、セルの更新日時を出す方法はあるのか、というところから調べ始めたものの、どうやら関数を使う方法では対処しきれないらしいことが判明。さらにいろいろなサイトを確認した結果、Google Apps Script（GAS）なるツールを使えばおそらく僕のやりたいことを実現できることがわかったのです（Excelでも同様にVBAを使うことができれば同じような処理が実現できるみたいです）。

ただ、エンジニアでもなければExcelのマクロとVBAの違いすらわかっていない僕のような人間からすると、親切にやり方を教えてくれているサイトがあったとしても、サンプルとして提示されているスクリプトを器用に編集して使えるかたちに落とし込むことは非常に難易度が高い。スクリプトの記述内容は難解過ぎてわけがわからないし、自分が埋め込みたいスプレッドシートの現在の構造にも合致していない。どうすればいいのかと思い悩んでいる中で、あることに気付きました。最近流行りのAIに頼ればいいじゃないか、と。

ChatGPTがあるじゃないか

ここからは解決編なのでだいぶ端折りますが、結論から言えば「Googleスプレッドシートで、隣のセルの更新日時を自動で入力＋更新させる方法」が知りたい場合は、最初からChatGPTに聞いてしまえばいいです。1回目の質問の時点でAIくんが「GASを使えばいいよ。スクリプトはこれだよ」って教えてくれます。すぐ解決します。

なんやかんやで今回ChatGPTが作ってくれたサンプルコードはこちら。

function onEdit(e) {
  // 更新を監視するシートを指定
  var sheet = e.source.getActiveSheet();
  
  // 指定されたシートでない場合は処理しない
  if (sheet.getName() != "YourSheetName") {
    return;
  }
  
  // 更新されたセルの範囲を取得
  var editedCell = e.range;
  // 更新されたセルの行と列を取得
  var editedRow = editedCell.getRow();
  var editedColumn = editedCell.getColumn();
  
  // 3行目以降のみ処理を行う
  if (editedRow < 3) {
    return;
  }
  
  // 日時のフォーマットを設定
  var dateFormat = "M/d HH:mm";
  
  // L列が更新された場合、その行のK列に日時を記録
  if (editedColumn == 12) { // L列
    var timestampCellK = sheet.getRange(editedRow, 11); // K列
    if (editedCell.isBlank()) {
      timestampCellK.clearContent(); // L列が空欄の場合、K列を空欄にする
    } else {
      timestampCellK.setValue(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), dateFormat));
    }
  }
  
  // N列が更新された場合、その行のM列に日時を記録
  if (editedColumn == 14) { // N列
    var timestampCellM = sheet.getRange(editedRow, 13); // M列
    if (editedCell.isBlank()) {
      timestampCellM.clearContent(); // N列が空欄の場合、M列を空欄にする
    } else {
      timestampCellM.setValue(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), dateFormat));
    }
  }
}

ChatGPTに投げかけた依頼は以下の通りです。

• 対象はGoogleスプレッドシートの特定のシート（タブ）の中
• 範囲は3行目から下の行
• L列が更新された日時をK列に出力し、同様にN列が更新された日時をM列に自動出力したい
• K列、M列は既に値が入力されていても、上書きされるようにしたい
• 日時の表記は短くしたいので、3/5 18:00 といった形式を希望する
• L列とN列が値のある状態から空欄に変わった場合、対応するK列とM列がそれぞれ空欄になる

L列とN列はコメントを記入するセル、K列、M列が更新日時が出力されるセルです。コメント欄にメモ書きだけ残してあとから削除する場合がたまにあったので、L列、N列が空欄になった場合には更新日時が残らないように調整してもらいました。

ChatGPTが出力するスクリプトはコメントアウトにきちんと説明まで入れてくれてるので、どの記述がどう作用しているのか素人にもわかりやすいです。違和感がある箇所については何度もやり取りを重ねれば修正してくれますし、本当に超便利でした。

あとは設定するのみ

あとはGoogleスプレッドシートに今回作ったGoogle Apps Script（GAS）を設定するのみ。

手順は次の通りです。

1. メニュー内の「拡張機能」から「Apps Script」を選択。
2. コード.gsのエディタ部分にChatGPTが作ったスクリプトを貼り付けて、Office系ソフトの保存ボタンに似た形状のアイコンを押して保存。
3. 左側のメニューから目覚ましアイコンみたいな「トリガー」を選択。
4. 右下の「トリガーを追加」を選択。
5. 「イベントの種類を選択」の中から「編集時」の項目に変更して「保存」を押す。
6. ポップアップが開いてサインインを求められるので、サインインしてそのまま進み、「Allow」を押せばOK。※サインイン後に「Google hasn’t verified this app」みたいな警告が出るかもしれないですが、左下の「Advanced」を押して「Go to 【Apps Scriptのプロジェクト名】 (unsafe)」を躊躇なく押していけば進めます。
7. 設定完了！

便利なのでぜひお試しを。
また仕事で使えるスクリプトを作ったら紹介したいと思います。