Googleスプレッドシートで自動的に更新されない「タイムスタンプ関数」を自作したい

Question

前提・実現したいこと

#Googleスプレッドシートで自動的に更新されない「タイムスタンプ関数」を自作したい
当方初心者です。
Googleスプレッドシートを使用して業務実施のチェックリストを作成しています。
その中に「実施完了報告」のチェックＢＯＸやプルダウンリストを置いてそこに記入が入った時刻を記録できるようにしたいのです。
書式の中に複数のチェックＢＯＸがあり場所によって隣や下のセルに時刻が出るようにしたいのでネットで調べて（自作関数？の）【ＴＩＭＥＳＴＡＭＰ】をスクリプトエディタに登録して以下の挙動になるようにしました。

• 指定したチェックＢＯＸにチェックを入れる、又はプルダウンリストから選択したテキストが表示されると入力した時刻が隣（又は下）の指定のセルに表示されるようにする。
• タイムスタンプを表示させたいセルに関数と組み合わせて書いています。

[例]「E25でプルダウンリストからテキストを選択」し表示させたら「F25に時刻を表示」させたいときは
「F25のセル」に【=if(istext(E25)=true,timestamp(),"")】としています。

しかしこれで記録すると一旦スプレッドシートを閉じて再度開いたときに関数を設置したセルに「loading」の表示が出て（出ないこともあります）、そのあとでタイムスタンプを入れた全てのセルの日時が勝手に更新されてしまうことがありました。
業務完了の日時を記録したいので後から開いても確実に【自動的に更新されない】タイムスタンプにしたいのですが、現状は「勝手に更新されたり」「されなかったり」と不安定な挙動で困っています。どうかお知恵をお貸しください。
（下記のコードはネットから参照したものなので中身の意味はよくわかっていない初心者です。。。）

該当のソースコード

function onEdit(e) {
  var r = e.range.getRow();
  var c = e.range.getColumn();
  e.range.getSheet().getRange(r, c+1).setValue(getCurrentTime());
}

function TIMESTAMP() {

  var today = new Date();
  var date = (today.getMonth()+1)+'-'+today.getDate()+'-'+today.getFullYear();
  var time = today.getHours() + ":" + today.getMinutes() + ":" + today.getSeconds();
  var dateTime = date+' '+time;
  return dateTime;

}

試したこと

トリガーを設定したら更新されなくなるかと思いトリガーを「編集時」「変更時」それぞれにして試してみましたが、どちらでやっても一時更新されなくなる感じになったのですが時間をおいて再度シートを開くと結局タイムスタンプが上書きされてしまいました。

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

ここにより詳細な情報を記載してください。

Answer 1

昨日の回答は私の認識違いで、どうも new Date() の影響かGASの自作関数だと、シートを開きなおした際に再計算され日時が更新されてしまうようです。失礼しました。

諦めて普通の onEdit でのコードを記載しておきます。
シートに関数を入れる必要はありません。

onEditは編集をトリガーとしていますが、途中のifで該当のシート、列、行範囲以外を除外し、
チェックを外したり、プルダウンを空欄に戻した際は時刻を消すようにしています。

シートの開き直し等では動かないので、勝手に時刻が更新される問題は解消されるはずです。

function onEdit(e) {
  var targetsheet = "シート〇〇" // 対象シート名
  
  //操作されたセルの情報 シート名、行、列を取得
  var sheet = e.range.getSheet().getSheetName();
  var r = e.range.getRow();
  var c = e.range.getColumn();

  if(sheet != targetsheet) return;　//対象シート以外を除外
  if(c != 5 | r <=2) return;　// E列(5列目)以外と、1，2行目を除外
  
  if(!e.value | e.value == 'FALSE'){
    e.range.getSheet().getRange(r, c+1).clearContent();　
　// チェックを外したり空欄とした際に日時を消去
    return;
    }
  e.range.getSheet().getRange(r, c+1).setValue(new Date());
　//シート側で表示設定を日時とする
}

【以下は、誤りです。開きなおした際に再計算されてしまいました。失礼しました。】

単純にタイムスタンプを返す自作関数なら、もっとシンプルでいいです。
GASの自作関数は、参照する引数に動きがあった時のみ再計算されます。
スプレッドシートの開きなおしは影響しません。

あえてx,yと 引数を2つとってるのは、プルダウンで値を変更しても turuのまま変わらないと更新されないので、xの方は 数式でtrue判別用、もう一つのyは直接セルを参照して動きがあれば日時を更新するようにしています。

function timestamp(x,y) {
  if(x === true){
    return new Date();
  }  
}

E25にプルダウンかチェックボックスがあるとして、チェックボックスにチェックが付いた時、もしくはプルダウンでなんらかの値が選択された時刻を返すには F25に以下の式を入れて、表示形式を日時としてください。

=timestamp(NOT(OR(E25=FALSE,E25="")),E25)

参照している セル（上記なE25）に変化があった時のみ更新され、チェックを外したりプルダウンの選択した値を削除（空欄）とした場合は、空欄を返します。

もちろん、この判別処理を GAS側で書いても良いです。

function timestamp2(x) {
  if(x === false | x ==="") return;
    return new Date(); 
}

この場合は単スプレッドシート側は、単純に =timestamp(E25) で動作します。

Comments

[fbps]

ありがとうございます！
私のやり方が悪かったのか、始めにいただいた方のコードを試したところ
一回「loading」が入ってしまいタイムスタンプが上書きされる症状が出てしまったのですが、
二つ目にいただいた方は今のところ「loading」自体が発生しなくなりました。
これでようやく業務報告書をリリースできそうです。
本当にありがとうございました。
余談ですが、ご教授頂いたコードがすごくシンプルで、関数程度しかわからない私でも
このコードが何を指示しているのかがわかったのがなんだか少し嬉しかったです(笑)

[fbps]

これで解決したと思ったのですが、先ほどシートを開いた際に「タイムスタンプ時刻がシートを開いた時刻に上書きされてしまう」症状が再発しました。。
もしかしてこれはコードの問題ではなく使用環境の問題なのでしょうか？
このスプレッドシートファイルは社内で「共有」して使用します。
現在はテスト中なので、PCで作成して私のPCとiPhoneとで「閲覧」「書き込み」を行ってテストしています。
タイムスタンプが書いてあるセルはもちろん「チェックＢＯＸ」「プルダウンリスト」以外の箇所は「セルの保護」をかけて書き込みできなくしています。
説明が不足しており申し訳ありませんでしたが、今一度解決策をご教授願えませんでしょうか？

[sawa]

すいません、もしかすると1つ目の方は シートを開きなおした際に シート関数の方が再計算されて、それに反応してタイムスタンプが更新されちゃうかもです。timestamp2のコードの方をご利用ください。

[fbps]

ご連絡ありがとうございます。
[timestamp2]の方ので一旦はうまくいったのですが再発してしまいまして。。

[sawa]

なるほど。失礼しました。パソコン閉じちゃたので明日の確認とさせてください。

[fbps]

お手数をおかけしますがご教授のほどよろしくお願いいたします。

[sawa]

ガラっと修正した回答を入れましたので、ご確認・検証ください

[fbps]

ありがとうございます！
これはつまり「関数のようにタイムスタンプを使用する」ことは仕様上できない、ということですかね。。
いただいたコードは
【特定の列（ここではE列）に記入があったら】【その隣のセルに（ここではF列）タイムスタンプを表示】させる。
と理解しましたが、同じシート上で「入力の右隣セルにタイムスタンプ」「入力の下のセルにタイムスタンプ」としたい場合はこのコードで可能ですか？
同じ列に「タイムスタンプの処理をしない行」を含む場合は「除外の行」を足せばよいでしょうか？
また、シートが日付ごとに「１日～３１日」まで31シートある場合の【対象のシート名】はどうしたらよいでしょうか？
また「トリガーの設定」は別途に必要でしょうか？
質問ばかりで恐縮ですがご教授いただけましたら幸いです。

[sawa]

>「関数のようにタイムスタンプを使用する」ことは仕様上できない
出来ないと思われます。ネット上にあった再計算されないというコードも、試してみると再計算されてしまったので、もしかしたら仕様が変わったのかもしれません。仕様上できないと断言は出来ませんが、私のスキルでは思いつきません。
Google Apps Script のタグをつけて質問を入れると、ハイレベルな面々からの回答があるかもです。

対象のシート名は、ifでの判別を消せば全シートが対象となります。
「入力の右隣セルに」「入力の下のセルに」これらも コードを修正していただければ可能です。
onEditはトリガーの設定不要です。まずは試してみてください。

[fbps]

ありがとうございます。
いただいたコードをもとに調整してみます！
お付き合いいただき本当にありがとうございました。