仕事効率化

【GAS】セルに入力があった時に自動で日付を入力しメールで通知する

Webサイトを制作していると何度もあるのが、クライアントからの質問や要望を受ける場面。

同時に複数案件をこなしていたりすると、対応した項目と対応していない項目が分からなくなる可能性も高くなります。

そういったクライアントからのコンタクトを、スプレッドシートで一括管理できると便利ですよね。

そこで下記のようなシートを作成し、あると便利な機能をGASで組み込んでいきたいと思います。

 

スプレッドシートへ機能追加

追加する機能を定義しましょう。

まずクライアントが入力したら日付が自動で入力できるようにしたいです。同じようにこちら側で回答したときも自動入力されるように。

また編集時にメールが送信されるようにすれば、より見落としや遅延のリスクも少なくなりそうです。

あとはクライアントが編集できる範囲を限定すれば、機能としては十分な気がします。

 

ということで今回追加する機能は以下の3つ。

 

①日付を自動入力する

②メールを送信する

③列毎に編集権限を設定する

 

上記3STEPで作成していきます。

それでは詳しく解説していきますね。

 

1STEP:特定のセルが編集されたら日付を自動入力

 

今回は

①クライアントがB〜C列を入力したときはA列に日付を自動入力

②自分がE〜F列を入力したときはD列に日付を自動入力

する想定でGASを組みます。

 

GASを組み込むときはメニュー内『Apps Script』をクリック。

 

下記画面にコードを記述することができます。

 

ソースコードは以下のとおりです。

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var col = sheet.getActiveCell().getColumn();
  var currentRow = sheet.getActiveCell().getRow();

  if ((1 < col) && (col < 4)) { // B列からC列まで
    var updateRange = sheet.getRange("A" + currentRow);

    if (currentRow > 1) {
      updateRange.setValue(new Date());
    }
  } else if ((3 < col) && (col < 7)) { // E列からF列まで
    var updateRange = sheet.getRange("D" + currentRow);

    if (currentRow > 1) {
      updateRange.setValue(new Date());
    }
  }
}

 

A列は1、B列は2、C列は3・・・という風に捉えます。

なので範囲取得をB列から始めたい場合は、『 1 < col 』となります。

対象列を変えたい場合は、適宜if分岐の数値を変更すればOKです。

 

STEP2:セル編集時にメールを自動送信する

 

続いてセルが編集されたときにメールを送信するようにします。

コピペで大丈夫ですが、以下2点のみ編集してください。

①メールを送信するGメールアドレス

②変更されたシート名

 

function sendMail(e){
  if (!e) {
    // eに値がない場合(IDEで関数を実行したとき等)は何もせず終了する
    return;
  }

  const subject = 'セルが更新されました';//通知タイトル
  var recipient = 'test@test.com'; //①メールを送信するGメールアドレス
  // ファイル名の取得
  const ss_name = e.source.getName();

  // シート名の取得
  const sheet_name = e.source.getSheetName();

  // 編集されたセル番地の取得
  const edited_cell = e.range.getA1Notation();

  // 編集前後の値を取得
  const value_before = e.oldValue;
  const value_after = e.range.getValue();

  // ファイルのURLを取得
  const url = e.source.getUrl();

  // メール本文を組み立てる
  const mail_body = [];
  mail_body.push('ファイル名: ' + ss_name);
  mail_body.push('シート名: ' + sheet_name);
  mail_body.push('セル: ' + edited_cell);
  mail_body.push('編集前: ' + value_before);
  mail_body.push('編集後: ' + value_after);
  mail_body.push(url);

  var mySheet = SpreadsheetApp.getActiveSheet(); //シートを取得
  var mySheetName= mySheet.getSheetName(); //シート名を取得

  var myCell = mySheet.getActiveCell(); //アクティブセルを取得
  var myCol = myCell.getColumn()
  var myCellValue = myCell.getValue();

  if(mySheetName == 'シート名'){ //②変更されたシート名
    if(myCol == 3){ //変更されたのがC列なら
      GmailApp.sendEmail(recipient, subject, mail_body.join('\n'));//通知メールを送信
    }
  }
}

 

コードを書き終わったあとは、トリガーという発生条件を設定する必要があります。

左メニューの『トリガー』をクリックし、右下の『トリガーを追加』をクリック。

 

ひとつずつしか選択できないので、それぞれの関数が選択されているのを確認した上で、イベントの種類を『編集時』に設定し保存。

 

下記のように2つトリガーが設定されていればOKです。

 

STEP3:列毎に編集権限を設定する

 

最後にクライアントは特定の範囲しか編集できないようにしておきましょう。

これはGASではなくスプレッドシート上の機能で実装できます。

 

編集権限を設定したい列を選択して、左クリックします。

『列での他の操作項目を表示』→『範囲を保護』を選択します。

 

右の『権限を設定』をクリックします。

 

すると以下のポップアップが表示されますので、編集権限をユーザー別に設定することが可能です。

 

以上で設定は終了です。

-仕事効率化

© 2022 サボリン | 公務員からフリーランスになった人のブログ Powered by AFFINGER5