コピペで更新したら、onEditが動かない

Question

こんにちは。先週からはじめてのプログラミングを勉強中です。
手始めに研修管理シートを作成していますが、下記がうまくいきません。

【やりたいこと】
スプレッドシートの「受講予定者一覧」というシートに、研修を受ける必要のある人の名簿が記録されています。
このシートのF列に、研修の受講状況（受講済,未受講）を記録していきます。

このF列の値が「受講済」に更新された場合、
・更新されたセルの右隣のセル（G列）に、「受講済」へと更新した日時を、
・更新されたセルの2つ右のセル（Ｈ列）に、更新した人のメールアドレスを
それぞれ取得して記録したいです。

Ｆ列を更新する際に、一つ一つのセルに「受講済」と入力していけば、書いてみたコードはうまく動くのですが、「受講済」の値をどこかのセルからコピーしてきて、まとめて更新した場合、どちらの操作も動きません。
Ｆ列の更新をひとつひとつした場合だけでなく、コピペでまとめて更新した場合にも動作するようにはできないのでしょうか。

書いたコードはこちらです。

function onEdit(e) {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getActiveRange();
var row = e.range.getRow();
var column = e.range.getColumn();
var ssName = sheet.getSheetName();
var cellvalue = e.value;
var oldcellvalue = e.oldValue;

console.log('onEdit');
console.log('ssName:' + ssName);
console.log('row:' + row);
console.log('column:' + column);
console.log('cellvalue:' + cellvalue);
console.log('oldcellvalue:' + oldcellvalue);

if(ssName != '受講予定者一覧') return;

// 「受講予定者一覧」シートで処理する内容
if (column == 6 && cellvalue == '受講済' && oldcellvalue == '未受講') {
console.log('before xxx');
var str = Utilities.formatDate(new Date(), "JST", "yyyy-MM-dd HH:mm:ss");
var user = Session.getActiveUser().getUserLoginId();

console.log('str:' + str);
console.log('user:' + user);

sheet.getRange(row,7).setValue(str);
sheet.getRange(row,8).setValue(user);

}

}

Comments

[shimamaru]

すみません。なんか変なところでコードが切れてしまっています。

[kikukiku]

https://qiita.com/STSHISHO/items/8cec8041951d182c937e

Answer 1

function onEdit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  var range = sheet.getActiveRange();
  var row = e.range.getRow();
  var column = e.range.getColumn();
  var ssName = sheet.getSheetName();
  var cellvalue = e.value;
  var oldcellvalue = e.oldValue;

  if (ssName != '受講予定者一覧') return;

  // 複数セルを編集した場合「F2:F4」と取れるので、「:」で区切り、配列化
  let ranges = range.getA1Notation().split(":")
  
  // 配列の長さが2なら、複数セルの編集処理
  if (ranges.length == 2) {
    // 開始セル行と終了セル行
    let startNum = ranges[0].replace(/[A-Z]/, "")
    let endNum = ranges[1].replace(/[A-Z]/, "")
    // 入力した複数セルの値を配列化
    let rangesValues = range.getValues().flat()

    // 入力はF列か
    if (column == 6) {
      // 開始セル行から終了セル行まで繰り返す
      for (let i = startNum, j = 0; i <= endNum; i++, j++) {
        if (rangesValues[j] == '受講済') {
          let str = Utilities.formatDate(new Date(), "JST", "yyyy-MM-dd HH:mm:ss");
          let user = Session.getActiveUser().getUserLoginId();

          sheet.getRange(i, 7).setValue(str)
          sheet.getRange(i, 8).setValue(user)
        }
      }
    }
  }

  // 「受講予定者一覧」シートで処理する内容
  if (column == 6 && cellvalue == '受講済' && oldcellvalue == '未受講') {
    console.log('before xxx');
    var str = Utilities.formatDate(new Date(), "JST", "yyyy-MM-dd HH:mm:ss");
    var user = Session.getActiveUser().getUserLoginId();

    console.log('str:' + str);
    console.log('user:' + user);

    sheet.getRange(row, 7).setValue(str);
    sheet.getRange(row, 8).setValue(user);
  }

}

このような感じでどうでしょうか？
複数セル入力した場合、繰り返し処理を用いて値を設定させれば、実現可能ではないかと思います。

Comments

[shimamaru]

ご回答ありがとうございます！いろいろやっていてお返事が遅れてしまいましたが、理想の操作ができるようになりました！うれしいです。ありがとうございました。