シートの更新に沿ってメール自動送信

Question

スプレッドシートのシートの内容が更新されたら
その内容を指定のメールアドレスにそれぞれ送るというシステムを作りたいです。

以下のサイトを参考にし、とりあえずやりたいことは実現したのですが、
シートが更新されたら上から順に全ての行に対して都度実行されてしまうので、
新しく更新された行にだけメールの送信がかかるように変えたいです。

ご教示くださいませ。

参考サイト↓
https://tonari-it.com/gas-mail-magazine/

使用中のコード↓

function sendMail(){

  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();;
  
  const DOC_URL = '*****ドキュメントのURL*****'; 
  const doc = DocumentApp.openByUrl(DOC_URL);
  const docText = doc.getBody().getText();

  const subject = 'メルマガ送信テスト';
  const options = {name: 'いつも隣にITのお仕事'};
   
  for(let i = 2; i <= lastRow; i++){

    const company = sheet.getRange(i, 1).getValue();
    const lastName = sheet.getRange(i, 2).getValue();　//姓
    const firstName = sheet.getRange(i, 3).getValue();　//名
    const recipient = sheet.getRange(i, 4).getValue();
    
    const body = docText
      .replace('{社名}',company)
      .replace('{姓}',lastName)
      .replace('{名}',firstName);
    
    GmailApp.sendEmail(recipient, subject, body, options);
  }
}

Answer 1

「新しく追加した行のみ」ではなく、 「チェックボックスで選択した行のみ」 にメール送信するようにしてみました。

というのも今後の実務を考えた時に、例えば、

• 一人のお客様からメールを再送してと頼まれた時、「新しく追加した行に送信」だと、同じ宛先を最終行にかかなければならないのか？
• 宛先を追加したもののその時は送信せず、別の機会にさらに追加して、前回追加した分と一緒に送りたいときはどうするのか？

など疑問点がでてきたからです。

画面の様子

• 「全て選択」ボタンで、全ての宛先に一括でチェックをいれられるようにしました。
  • もちろん1つずつチェックいれはずしもできます！
• 「選択をリセット」ボタンで、全ての宛先のチェックを外すようにしました。
• 「メールを送信する」ボタンで、
  • メールを送信しますか？の確認画面で「OK」を押したら、
  • メールが送信されるようにしました。

↓表の1行目を固定化しているので、スクロールしても各項目名とボタンはずっと1行目に見えています。

　

コード例

• チェックボックスの挿入方法：https://www.yukibnb.com/entry/spreadsheet_checkbox
• 1行目のみ固定する方法：https://boxil.jp/beyond/a5396/
• ボタンを設置して、スクリプトを割り当てる方法はこちら：https://auto-worker.com/blog/?p=1346

/*
 * メールを送信する関数
 */
function sendMail() {
  // メール送信確認のメッセージボックスを画面に表示
  const response = Browser.msgBox(
    '送信確認',
    '選択した宛先にメールを送信しますか？',
    Browser.Buttons.OK_CANCEL
  );

  // 「キャンセル」なら、ここで処理を終了
  if (response === 'cancel') return;

  // ドキュメントの文章を取得
  const DOC_URL = 'ドキュメントのURL';
  const doc = DocumentApp.openByUrl(DOC_URL);
  const docText = doc.getBody().getText();

  // スプレッドシートを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('宛先リスト'); // シート名でシートを取得
  const lastRow = sheet.getLastRow();
  const range = sheet.getRange(2, 1, lastRow, 5);

  // チェックボックスに✅がはいっているレコードのみに絞る
  const checkedRecords = range.getValues().filter(row => row[0] === true);

  const subject = 'メルマガ送信テスト';
  const options = { name: 'いつも隣にITのお仕事' };

  // メールを送信
  for (let data of checkedRecords) {
    const company = data[1];
    const lastName = data[2];
    const firstName = data[3];
    const recipient = data[4];

    const body = docText
      .replace('{社名}', company)
      .replace('{姓}', lastName)
      .replace('{名}', firstName);

    GmailApp.sendEmail(recipient, subject, body, options);
  }

  // 送信完了のメッセージボックスを画面に表示
  Browser.msgBox('メールの送信が完了しました。');
}

/*
 * 全てを選択する関数（全てに✅をいれる）
 */
function checkAll() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('宛先リスト'); // シート名でシートを取得
  const lastRow = sheet.getLastRow();
  const range = sheet.getRange(2, 1, lastRow);
  range.check(); // チェックをいれる
}

/*
 * 選択をリセット選択する関数（全ての✅をはずす）
 */
function uncheckAll() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('宛先リスト'); // シート名でシートを取得
  const lastRow = sheet.getLastRow();
  const range = sheet.getRange(2, 1, lastRow);
  range.uncheck(); // チェックをはずす
}

Comments

[shiyuh]

チェックボックス選択式は送信を手作業で行う手間はありますが、
一件一件メールを作成するよりは遥かに楽＆自動すぎないことで誤作動による送り間違えもおさえられる
という点からベスト回答にさせていただきました。

お力添えいただきありがとうございます！！

Answer 2

新しく更新された行にだけ

これを判別するものを作成する必要があります。
onEdit()を使用すると、シートの変更を検知することができます。

function onEdit(e) {

  // 編集された行数
  let editRow = e.range.getRow()
  let editCol = e.range.getColumn()

  // ヘッダ行を除いた、2行目から下のセル
  if (editRow >= 2) {
    // さらに特定の列だけ、D列(4列目)までのセルが変更された
    if (editCol <= 4) {
      let sheet = e.source.getActiveSheet()
      // 行の内容が更新されたので、「更新」をセット
      sheet.getRange(editRow, 5).setValue("更新")
    }
  }
}

さらにE列に「行が更新されたか？」の状態を保持しておく必要があります。

メール送信処理のforループ内で、E列セルを取得し「更新」されている場合に
メール送信を行うようにすれば問題は解消できるのではないかと思います。

  for (let i = 2; i <= lastRow; i++) {

    const company = sheet.getRange(i, 1).getValue();
    const lastName = sheet.getRange(i, 2).getValue();　//姓
    const firstName = sheet.getRange(i, 3).getValue();　//名
    const recipient = sheet.getRange(i, 4).getValue();

    // 更新されたかフラグ
    let updateRange = sheet.getRange(i, 5)

    // 行の更新があった場合のみ、メールを送信
    if (updateRange.getValue() === "更新") {
      const body = docText
        .replace('{社名}', company)
        .replace('{姓}', lastName)
        .replace('{名}', firstName);

      console.log(`${i}行目 更新されているのでメール送信`)
      GmailApp.sendEmail(recipient, subject, body, options);

      // 更新されるまでの再送信を防ぐため、空文字をセット
      updateRange.setValue("")
    }
  }

実施前	処理実施後	再実施前	再実施後

Comments

[shiyuh]

ご回答いただきありがとうございます。
今回製作しているシステムが、一度に３行のデータが追加されて、そのうち２つの列は前行と同じデータだけど１列のみデータが変わっていて・・・みたいな複雑な構成だったので、適用したところ少し用途と当てはまらない点が出てきてしまいました。しかし、今後十分に使用できるコードですので、ぜひ活用させていただきます。

お力添えいただきありがとうございました！！