【GAS基礎講座 23.】セル更新時にタイムスタンプを表示(Gスプレッドシート)

基礎講座

GAS基礎講座一覧

ご訪問ありがとうございます。

Googleシートマスターのひろしです。

前回

【GAS】特定のフォルダに新規シートを作成する方法
GASを使用して特定のフォルダに新たにスプレッドシートを作成することができます。

今回は、とっても価値の高い方法をお伝えします。


これを知ることであなたは、
こんなことが実現できます。

  • セルを更新したタイミングでデータを更新する方法がわかります。
  • セルを更新した日時を自動的に表示させることができます。

なので、サクッとマスターして

と答えてあげてください。

 

動画はこちら

【GAS基礎講座 23 】セル更新時にタイムスタンプを表示させる方法 更新イベントの取得(onEdit)

 

 

スクリプト

function onEdit(e) {

  const ws = "timestamp"

  let row = e.range.getRow()
  let col = e.range.getColumn()
  let currnetDate = new Date()  //現在の日時

  if (row >= 2 && col == 1 && e.source.getActiveSheet().getName() === ws) { //シートがwsでかつ1列目で2行目以降か?
    e.source.getActiveSheet().getRange(row, 4).setValue(currnetDate)
    if (e.source.getActiveSheet().getRange(row, 3).getValue() === "") { //Date Enterdが空のとき
      e.source.getActiveSheet().getRange(row, 3).setValue(currnetDate)  //Date Enterdに日時を入れる
    }
  }
}

const ws = "timestamp"

シート名は、timestamp にしているのであなたの環境に合わせて
変更してください。

シートはこちら

※クリックするとシートが開きます。

 

ポイント

onEdit関数は、スプレッドシートの編集時に飛んでくる関数です。

引数は、なんでもいいのですが、通常(e)を指定します。


e.range.getRow()
e.range.getColumn()

で、現在の入力位置を取得します。

onEdit関数は、スプレッドシートを編集する度に飛んでくるので、
処理が不要な場合は早めに返して(return)あげましょう。

上記スクリプトの場合、いつでも
new Date() //現在の日時
してしまっていますが、setValueする場合のみ
new Date()したほうがいいですね^^

 

さらに

 

 

どのシートでも対応する場合

 

 

 

どのシートでも対応したいとのご質問をいただきました。

 

 

動画

【GAS】Googleスプレッドシートが変更された際にタイムスタンプを表示させる方法(OnEdit)

スクリプト

function onEdit(e) {


  // const ws = "timestamp"


  let row = e.range.getRow()
  let col = e.range.getColumn()


  // if (row >= 2 && col == 1 && e.source.getActiveSheet().getName() === ws) { //シートがwsでかつ1列目で2行目以降か?
  if (row >= 2 && col == 1) { //1列目で2行目以降か?
    let currnetDate = new Date()  //現在の日時
    e.source.getActiveSheet().getRange(row, 4).setValue(currnetDate)
    if (e.source.getActiveSheet().getRange(row, 3).getValue() === "") { //Date Enterdが空のとき
      e.source.getActiveSheet().getRange(row, 3).setValue(currnetDate)  //Date Enterdに日時を入れる
    }
  }
}

 

ポイント

シートを比較している箇所(&& e.source.getActiveSheet().getName() === ws)を
削除すればOKです。

別件ですが、日時の取得( let currnetDate = new Date()  //現在の日時)
位置をずらし、1列目で2行目以降の場合のみ日時を取得するようにしました。

 

 

最後までご覧いただきありがとうございます。

つぎはこちら

【GAS基礎講座 25.】チェックされた行のメールを送信する方法(Gスプレッドシート)
  • GASでデータを一気に取得する方法がわかります。
  • チェックボックスでチェックされた行のデータを取得することができます。
  • GAS基礎講座一覧

    コメント

    1. AK より:

      https://forms.gle/qcZGgCu5rSYSShhv7

      https://docs.google.com/spreadsheets/d/1axKhCARAoED1cS1Bvnmtpb4HBa3Y-tIfTvlKpPfQhLs/edit?usp=sharing

      在庫管理集計の入荷が書き換わった時に、最終入荷日の日付がその都度更新されるようにできますか?
      よろしくおねがいします。

    2. Ak. より:

      コメントできているのでしょうか?
      何度も送られていたらすいません。
      https://forms.gle/qcZGgCu5rSYSShhv7

    3. Ak. より:

      https://docs.google.com/spreadsheets/d/1axKhCARAoED1cS1Bvnmtpb4HBa3Y-tIfTvlKpPfQhLs/edit?usp=sharing

      在庫管理集計の入荷が書き換わった時に、最終入荷日の日付がその都度更新されるようにできますか?
      よろしくおねがいします。

    4. hiroshi より:

      シートのご提供ありがとうございます。
      あとできれば、仕様を明確にするために
      入力と出力におけるシート名とセルを明記していただけると助かります。

      「フォームの回答1」シートの「状態」(D列)が変更された際に
      「在庫管理表」シートの「最終入荷日」(H列)にYYYYMMDD形式で日付を入力する
      的な感じでピンポイントで明確に指定してください。

      • Ak. より:

        googleフォームから送信されたものが「フォームの回答1」シートに入り、「在庫管理集計」シートに反映されます。
        「在庫管理集計」シートのB列(入荷)のみ更新された場合、その都度H列(最終入荷日)にYYYYMMDD形式で上書きされる。

        こういう感じで宜しいでしょうか?

        • hiroshi より:

          返信ありがとうございます。シートを見てやりたいことが理解できました。

          すみません。ちょっとやったのですが、確かにイベントが飛んでこないですね。
          onEdit()は、直に編集しないと発火しないので、別の方法を考える必要があります。

          期待しないでお待ちください。

    5. saka より:

      作成されているGASのタイムスタンプの対象範囲について質問です。

      A列2行目以降を「row >= 2 && col == 1」と指定されていますが、
      例えばA列2行目~B列20行目までと範囲指定をしたときはどの様な書き方になりますか?

      ご教示願います。

      • hiroshi より:

        2行目から20行目 row >=2 && row <=20
        A列からB列  col >=1 && col <=2
         
        すべて満たす必要があるので 

        (row >=2 && row <=20) && (col >=1 && col <=2) 
         
        となります。
        ※カッコは読みやすいようにつけただけです。

      • hiroshi より:

        2行目から20行目 row >=2 && row <=20 A列からB列 col >=1 && col <=2

      • hiroshi より:

        2行目から20行目 row >=2 && row <=20 A列からB列 col >=1 && col <=2

    6. Md より:

      初めまして。GAS初心者です。更新時にタイムスタンプを表示する方法を探していてこちらにたどり着きました。
      動画の序盤に出てくる「すべてのシート」や「どのセルでも対象」の状態で実行させたかったので、見よう見まねで以下のようにしてみました。

      1 function onEdit(e){
      2
      3 const row = e.range.getRow()
      4
      5 if(row > 3){ //●行目は対象外
      6 e.source.getActiveSheet().getRange(row,2).setValue(new Date()) //●列目
      7 if( e.source.getActiveSheet().getRange(row,1).getValue() === “”){ //●列目が空のとき
      8 e.source.getActiveSheet().getRange(row,1).setValue(new Date()) //
      9
      10 }
      11 }
      12 }

      として保存しました。
      実際稼働しているようではありますが、実行ログに以下のようにエラーが出ます。

      TypeError: Cannot read property ‘range’ of undefined
      onEdit @ コード.gs:3

      これはどうすれば良いのでしょうか。

      また、セルに表示される日時を日付と時間で改行したいのですが、
      併せて出来るのでしょうか。

      2022/03/20 13:53:37
       ↓
      2022/03/20
      13:53:37

      教えて頂きたいです。宜しくお願い致します。

    7. kyhusky より:

      GAS初心者です。
      タイムスタンプ機能を使って、50m走のタイムを計測し、自動で記録できないか検討中です。
      1列目の氏名は予め入力しておくこととして、2列目に計測したタイムが入力されるようにしたいのですが、画面のタッチだと誤操作の恐れがあるので、例えばキーボードのスペースが押されたら計測を開始し、キーボードの1を押したら計測が終了するようなプログラムは可能でしょうか。

      • hiroshi より:

        kyhusky さん質問ありがとうございます。
        結論としてちょっと難しいです。何より機能的に実装できたとしても
        0コンマ何秒での正確な時間は取得できないと思います。

        さらにOnEditでは、変更の確定で発火するので単一キーの場合スペースの
        後のEnterで始めてイベントが発生します。
        なので、Ctrl+1とかチェックボックスのOn/Offで発火させ多少の誤差を
        許容できれば可能だと思います。

        参考サイト
        https://qiita.com/yumehavtuber/items/e57076aa5f00ac5166c9
        https://note.com/genkiman_no_note/n/n1392e8d3f2fb

    タイトルとURLをコピーしました