特定の範囲（列）に値が入力された場合、その入力セルの隣に日付を表示し、その範囲の値が削除された場合、その表示された日付も自動で削除して欲しい。

前提・実現したいこと

初めまして
スプレッドシートを用いて業務改善をしたく挑戦しているのですが初心者で知識が乏しく、
コードを書いてもうまく動作しないため投稿させていただきました。

実現したいこととしては以下の通りです。恐れ入りますがご教示いただけますと幸甚です。

実現したいこと
「特定の範囲（列）に値が入力された場合、その入力セルの隣に日付を表示する。また値がない場合は空白。」
追加と言ってはなんですが「入力された値が削除された場合、表示されていた日付も空白になる」とより嬉しいです。

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

エラーメッセージ

該当のソースコード

function getDateTime() {
var ss = SpreadsheetApp.getActiveSpreadsheet();　//現在アクティブなスプレッドシートを取得
var sh = ss.getSheetByName("シート1");　//そのスプレッドシートにある('指定するシート名')のシートを取得
var lastRow = sh.getLastRow();　//アクティブなセルの最終行を取得
var range = sh.getRange("C10:C309").getValues();　//そのシートにある(○○:○○)のセル範囲を取得

for(var i=0 ; i <= lastRow ; i++){ //「11行目」からデータがある「最終行番号」までの範囲でプラス1しながら繰り返す。

if(range[i] ==""){ //もし範囲C10~C309が空白だったら
sh.getRange(i+2.3).setValue(""); //i+2行、3列目がtrue時は空白

} else {
var date = new Date();
sh.getRange(i+2,2).setValue(date);
}

}
}

試したこと

いろいろとネットを渡り歩いてにたコードを貼り付けたりと試してみましたがいずれも動作しませんでした。
どこが問題なのかがわからないためお手上げの状態です。。。

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

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

行動規範の内容に同意します

回答2件

ベストアンサー

これだと全てのシートで動作するので、適宜シート名を見て判定する処理を入れてください。

javascript
1function onEdit(e) {
2    const sourceRange = e.range;
3    const c = sourceRange.getColumn();
4    if (c !== 3) {
5        return;
6    }
7    const r = sourceRange.getRow();
8    if (r < 10 || r > 309) {
9        return;
10    }
11    const targetRange = e.range.offset(0, 1);
12    const dat = sourceRange.getValues().map(function (r) {
13        return r[0] === "" ? [""] : [new Date()];
14    });
15    targetRange.setValues(dat);
16}

投稿2019/11/03 09:01

編集2019/11/04 16:28

papinianus

総合スコア12705

Yam_Fu

2019/11/03 11:37

ご教示ありがとうございます。はい、「スプレッドシートを開き直した際の更新はさせない」が条件の一つでしたので関数は考えておりませんでした。そしていざコードをコピペして動作させてみるとかなり思っていたものに近い感じですが何回か値を削除したり入力したりした時に挙動が安定しないように感じています。そこで以下ご質問をさせてください。質問① 値を入力すると時刻が消えたり、削除すると表示されたり・・・。（スイッチのNo、OFFが逆になったような）これはトリガーが「編集時」ということでだからでしょうか？この場合、「空白」かそうじゃないかで判定させようとするコードに変更しようと思うとどのように修正を加えればよろしいでしょうか？質問② 一度表示された範囲を一括クリアをして再度同じように値を入力すると反応しない場合があったりします。（むしろ全く反応しない！？）これは質問①が解消されればクリアされるものでしょうか？それともコードの前提条件が不足しているからでしょうか？なんとなく意味を拾いながらコードは読めますが挙動からの原因が特定できず・・・。（おそらく条件が読み切れていないだけかと思いますが。）

papinianus

2019/11/04 05:17 編集

質問1 逆転してました。直したつもりです。質問2 一括クリアは当初質問から読みとれていなかったので意図どおり動作しません。全く確認していませんが、1の修正後には、おそらく左上セルがC10:C309の範囲にあれば消去は動作するのではないですかね。複数範囲のoffsetをやったことがないので分かりません。

Yam_Fu

2019/11/04 08:35

ご返答ありがとうございます。回答からすると自分にはハードルが高そうですね・・・。もう少し悩んでみます。

papinianus

2019/11/04 16:40 編集

このコメントは質問2の件ですよね。範囲の仕様は難しいと思います。範囲の一部に空白があった場合や、範囲の一部は変更前後でかわらない場合などを自力で考慮しなければなりません。また縦方向に複数行に対応して動作させてしまうと、誤操作で横方向に複数はりつけした場合（今回の例ではA1:C1にはったときに、うまくD1にだけ書かなければならないのか、それは例外として何もしないのかなど）の望ましい動作とは何かを考える必要があります。そうした仕様への考慮は質問からは読み取れませんでしたし、また仕様そのものも明らかでないです。ハードルが高いというご指摘だったので、とりあえず縦に複数はっても1列だけならなんとなく動くようにしました。コードのハードルは高くないです。仕様のハードルが高いのです。免責この修正では横方向の複数列は一切動作しません。エラーになるか、動作しないかです。また貼り付けした範囲に空白があれば消しますが、貼り付け前と同じ値であっても日付が更新されてしまいます。検証してみましたが複数範囲貼り付けの場合、変更前後のどちらの値もスクリプトで取得できない（ように見えた）ので、複数範囲のときに対応させようとすると、実質的に変更されていない場合に日付更新を抑制するのは難しそうです（他のセルに書いていいならできるかもしれない、できないかもしれない）。複数範囲のときは動作を停止する、ということなら可能です。

Yam_Fu

2019/11/06 12:33

修正案を作っていただき大変ありがとうございます！横方向には想定していませんでしたので今回のコードが一番思っていた内容に近い動作となっています。そうですね、なんとかコードは読めそうです。仕様をもう少し検討しておく必要があったと反省しています。このコードを元に検討を進めていきたいと思います。ありがとうございます！

行動規範の内容に同意します