スプレッドシートでセルの更新日時を隣のセルに表示させたい

Question

前提・実現したいこと

スプレッドシートでセルの更新日時を隣のセルに表示させたい

発生している問題・エラーメッセージ

セルを更新した時にはその日時が表示されるが、ブラウザをいったん閉じたり、スマホからそのスプレッドシートにアクセスすると、
更新がかかってしまうのか、開いた日時が自動で入ってしまい、実際の編集日時が上書きされてしまいます

gas

### 該当のソースコード

function getCurrentTime(value) {
  if (value) {
    var ctime = new Date();
    var MM = ctime.getMonth()+1;
    var dd = ctime.getDate();
    var hh = ctime.getHours();
    var mm = ctime.getMinutes();
    var t = MM+"/"+dd+"_"+hh+":"+mm+"発表";
    return t;
  }
}


何か手段はあるでしょうか？

よろしくお願いします

Comments

[papinianus]

どういうトリガを設定しているのか、またトリガから呼ばれる関数がなければ、何ともいえません。ここに提示なさったものは日付計算しかしていないので、適切に呼べば意図どおりになりますし、不適切な呼び出しをすれば常時上書きする処理になります。

[jm3atn]

ご回答ありがとうございます。トリガは例えばセルB1に関数　getCurrentTime(A1)　と入れています。A1に入力があった場合、B1にその時の日時が入るようにしたいのですが、開くたびにＡ１が変更されたと認識しているようです。ほかにいいトリガのかけ方がありますでしょうか？

[jm3atn]

papinianus様　ありがとうございます。　記入してリロードしてみましても時刻が変わることはありませんでした。期待していた動作です！ありがとうございます。おっしゃるようにif文のことなのでさらにお尋ねしたいのですが。例えば、B13、B25、B37、C13、C25、C37のセルに書き換えが生じた場合、それぞれその下の行に入力日時を入れたいと思います。入力したセルの更新日時は下の行のセルに入れるというのは共通しています。この場合if文では難しいような気がします。何かアドバイスいただけませんでしょうか。文字を入れるセルに関数を入れておくと書き入れるときに消えてしまうので、関数を作って更新日時が入るに入れるとかでしょうか？お付き合いいただきましてありがとうございます。

[papinianus]

ifでできると思うのですが、要件を例えばではなく確定してほしいです。隣のセルは右ではなかったのでしょうか？下ですか？また想定でいくつのセルですか？固定ですか？変動しますか？変動の頻度は？

[jm3atn]

お手数をおかけして、ありがとうございます。
今現在やりたいと思っているのはテスト１のリンクの表です。

一週間のカレンダーで、4週分です。
列や行は固定ですが、まちがって挿入すると増えてしまうので厳格に操作する必要があります。

これができれば、テスト２の表にも応用したいと考えています。テスト2の表は固定です

最終はテスト2では更新日時の左にだれが更新したかグーグルアカウントの名前を入れたいと思っています。

いずれの表もグーグルアカウントで共同編集している表なので、視覚的にいつ書き換わったかが分かるようにしたいという希望です。
いずれもまずは更新日時を入れたいです。

テスト1
https://docs.google.com/spreadsheets/d/1ZfxlK5MpSdtMwGaFm91FjK6G7u8i3KBNvt-1EgaT2oQ/edit?usp=sharing

テスト2
https://docs.google.com/spreadsheets/d/1xmuDjnn8yLrSQfuKEDE05e_dfQeZK4uchcv0ZjiP97c/edit?usp=sharing

急いではおりませんので、お時間ありましたらご教授お願いいたします。

ありがとうございます

[papinianus]

1ヶ月くらいたちましたね、おひさしぶりです。テスト1およびテスト2が正確にそのままの姿であればできる方法を(ちゃんと適切な形で)考えました。回答をご確認ください

[jm3atn]

papinianus様ありがとうございます。お金を払っているわけでもないのに、ここまでご親切な対応を頂けるとは思っていませんでした。お盆は仕事で忙しいですので、数日後に試してみたいと思います。また報告いたします。本当にありがとうございます！！

[jm3atn]

あと、もしお時間がありましたら、条件分岐の方法の解説をお願いできませんでしょうか？function getCurrentTime()は時間を取得して表示させる。function onEdit(e)はシート名を取得してそれを引数にしてそれぞれのfunctionに送っているというところでしょうか。あとはどのように文字列が入力されたことを認識し、更新日時をいれるセルを認識しているのでしょうか？

[papinianus]

コードの解説については、コードが書いてある回答のコメント欄に記載します(質問についてのコメントではないので)

Answer 1

セルに=getCurrentTIme(A1)と入れているところは全部消して、スクリプトに下記のように書いて保存してください。
なお、↓はあくまでサンプルであり、A1が編集されても、B1が編集されても、その右隣に時刻を書きます。
(要件が不明なのでこれ以上かきようがないですが、A列が編集されたときだけやりたいなら、cの値でifをとればいいと思われます。また、formから書き込まれるのであればこれでは上手くいきません。onEditが発動しないからです)

function onEdit(e) {
  var r = e.range.getRow();
  var c = e.range.getColumn();
  e.range.getSheet().getRange(r, c+1).setValue(getCurrentTime());
}
function getCurrentTime() {
  var ctime = new Date();
  var MM = ctime.getMonth()+1;
  var dd = ctime.getDate();
  var hh = ctime.getHours();
  var mm = ctime.getMinutes();
  var t = MM+"/"+dd+"_"+hh+":"+mm+"発表";
  return t;
}

--- 追記

//https://teratail.com/questions/132061
function onEdit(e) {
  var sheetName = e.source.getSheetName();
  if(sheetName === "テスト1") { handleType1toSetDate(e.range, e.range.getNextDataCell(SpreadsheetApp.Direction.DOWN)); }
  if(sheetName === "テスト2") { handleType2toSetDate(e.range, e.range.getNextDataCell(SpreadsheetApp.Direction.NEXT)); }
  return;
}
function handleType1toSetDate(currentRange, underneath) {
  if(!currentRange.isPartOfMerge() || !underneath.isPartOfMerge()) { return; }
  underneath.setValue(getCurrentTime());
}
function handleType2toSetDate(currentRange, next) {
  if(currentRange.getColumn() != 6) { return; }
  next.setValue(getCurrentTime());
}

function getCurrentTime() {
  var ctime = new Date();
  var MM = ctime.getMonth()+1;
  var dd = ctime.getDate();
  var hh = ctime.getHours();
  var mm = ctime.getMinutes();
  var t = MM+"/"+dd+"_"+hh+":"+mm+"発表";
  return t;
}

Comments

[papinianus]

・要件の私なりの理解
・・テスト1とテスト2があること
・・特定のセルへの入力を、それに対応するまた別の特定のセルに更新情報として記録すること
・・テスト1については、行の挿入などが起こることから、行数では識別できないこと。
・・テスト2については、列数で特定が可能であること。

[papinianus]

・実装
・・onEdit(e)
GASの仕様として、スプレッドシートに編集をすると、onEdit()が呼ばれます(そういう関数が定義されていれば)
テスト１とテスト2は明らかに違う処理を要求されている、またシートは他にも存在する可能性があるのでシート名によって、処理を呼び分けました。
呼ぶ関数は引数が概念的に同じ作りをしております。第一引数が編集したセル、第二引数が更新情報を入れる可能性のあるセルを指定しています。
・・handleType1toSetDate
テスト1の動作条件を、結合セルの上の段だったら、結合セルの下の段に入れる、と解釈しました。
これなら行の挿入が起こっても、結合セルの上下ペアが出現しない限り、動作可能です
isPartOfMergeはGASが容易している、セルが結合されていますか、という判定関数です
・・handleType2toSetDate
入力セルの列数を見て動作を変えています(左から6列目つまりF列への入力でなかったら中断)
・・getCurrentTime
現在時刻を要件にあう書式にするためのものです(以前から提示済み)

[papinianus]

なお、隣や下を得るためにSpreadsheetApp.Direction.DOWNやSpreadsheetApp.Direction.NEXTを使っています。これはキーボードでCtrl+↓とかCtrl+→で移動する操作を模倣していますので、「ここに更新日が入る」といった文字がないと正常に動作しないと思います(仮想のカーソルがシートの最右端などに移動するため)。
(結合セルなどで下を得るのはそれなりに面倒+GASが用意している機構を利用したほうが動作は安定するのでこのようにしています)

[jm3atn]

丁寧な解説をありがとうございます。
一行ずつ理解することができました
そして長きにわたりお付き合いいただきまして感謝しております。
無事に機能を実装できました。

ありがとうございました。