[GAS]Spreadsheetから値を取得して自動送信メールを作成するメモ

Last updated at 2024-02-29Posted at 2024-02-29

作成したいもの

GoogleFormの回答内容をメール文面に挿入し、自動送信する

スプレッドシート側での設定

GoogleForm回答とスプレッドシートを連携させる
テンプレート文面を作成する
文字列置換対象はGoogleForm回答列と一致させる
置換対象の文字列は[]で囲む

▼テーブル例

#	1
カテゴリ	定期進捗報告
件名	【定期報告】[氏名]_プロジェクト進捗状況
本文	皆様お疲れ様です。[氏名]です。プロジェクトの進捗状況をお知らせいたします。 [進捗状況] 以上となります。引き続きよろしくお願いいたします。

▼出力されるメールの例

【宛先】
To: exapmle-to@mail.com
CC: exapmle-cc@mail.com
【件名】【定期報告】[氏名]_プロジェクト進捗状況
【本文】
皆様

お疲れ様です。[氏名]です。
プロジェクトの進捗状況をお知らせいたします。

[進捗状況]

以上となります。
引き続きよろしくお願いいたします。

GAS

GASトリガー

GoogleFormとリンクしたSpreadsheetが更新された際、メール送信スクリプトを実行する

1日のGAS実行回数制限・メール送信回数制限に注意する

GASで行う処理

フォームから置換対象・置換文字列の取得
取得した文字列をテンプレート・置換対象とした文字列の置換
メール件名・本文の生成
メール送信先（To/CC）の設定
メール作成および送信の処理

1-1. フォームシートの最新行を取得する

今回はフォーム回答シートに関数などは入力せず、空欄は必ずBlankである。そのため、getLastRow()で問題なく最新行を取得できる
フォーム回答シートに関数を設定する場合など、getLastRow()で取得できないことがある

  //sheetFormはフォーム回答シートを指定する
  // フォーム回答の最新行を取得
  var lastRow = sheetForm.getLastRow();

1-2. カラムタイトルと最新行を全件取得する

※このオブジェクト連想配列の値がreplacementText[]となる

/**
 * シート内一行目カラムタイトルと最新の値を取得
 * @param {sheet} sheet
 * @param {number} lastRow
 * @return {string[]} columnValue タイトルと値の配列
 */
function getColumnValues(sheet,lastRow) {
  var firstRowValue = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  var lastRowValue = sheet.getRange(lastRow, 1, lastRow, sheet.getLastColumn()).getValues()[0];
  var columnValue = [];
  
  for (var i = 0; i < firstRowValue.length; i++) {
    if (firstRowValue[i] !== "") {
      //オブジェクト連想配列を作成
      columnValue[firstRowValue[i]] =  lastRowValue[i];
    }
  }
  Logger.log('column: '+ columnValue);
  return columnValue;
}

2-1. 置換対象文字列の取得

1-2で取得したカラムタイトル（Key）を置換対象としてカラム値（Value）でテンプレートテキスト（Sheetで設定）から検出する文字列におく
必要であればオブジェクト連想配列の結合を行う

2-1 補記：オブジェクト連想配列の結合（例）

ここでは、連想配列に件名・本文・送信先を含めることでデータを一括に扱えるよう設定している

サンプルコード

/**
   * 置換用連想配列の取得
   */
  //フォームから取得した値を代入
  const replacementText1 = getColumnValues(sheetForm,lastRow); 

  //勤怠用日付形式を変換
  if(replacementText1['勤怠年月日'] != ''){
    var date = new Date(replacementText1['勤怠年月日']);

    // フォーマットオプションを設定
    var option1 = { year: 'numeric', month: '2-digit', day: '2-digit' }; // YYYYMMDD形式
    var option2 = { year: 'numeric', month: '2-digit', day: '2-digit', weekday: 'short' }; // YYYY/MM/DD(ddd)形式

    // フォーマットされた日付文字列を取得
    var formattedDate1 = date.toLocaleDateString('ja-JP', option1).replace(/\//g,""); //YYYYMMDD形式
    var formattedDate2 = date.toLocaleDateString('ja-JP', option2); //YYYY/DD/DD(ddd)形式

    Logger.log('日付：' + date);
    Logger.log('変換後日付: ' + formattedDate1+ ',' + formattedDate2);

  } else {
    var formattedDate = null;
  }

  //置換用追加Key, Values
  var replacementText2 = {
    "課名": sectionName,
    "MailTo" : mailAddressTo, 
    "MailCC" : mailAddressCC,
    "勤怠年月日（YYYYMMDD）" : formattedDate1,
    "勤怠年月日（YYYY/MM/DD/(ddd)）" : formattedDate2
  }; 

  //オブジェクト連想配列を結合
  const replacementText = Object.assign(replacementText1, replacementText2); 
  Logger.log('merged replacementText: ' + JSON.stringify(replacementText)); // なぜか表示されない

2-2. 文字配列の置換用関数

※テンプレートテキスト（配列）と置換対象文字列のKey（オブジェクト連想配列）は一致している必要がある

/**
 * 文章の置換
 * @param {string} targetText - テンプレートテキスト
 * @param {string[]} replacementText - 置換対象文字列の配列
 * @return {string} replacedText - 置換後の文字列
 */
function replaceText(targetText, replacementText){
  var replacedText = targetText.replace(/\[(.*?)\]/g, function(match, p1) {
    // ブラケット内の文字列に対応する置換テキストを取得して返す
    return replacementText[p1.trim()]; 
  });

  return replacedText; // 置換されたテキストを返す
}

function(match, p1)では、ブラケット[aaa]内部の文字列aaaを検出している。
このとき、replaceの対象はブラケットを含む文字列[aaa]である。

3. 件名・本文の作成

2-2の文字列置換を双方に適用することで、件名および本文が生成できる

4. メール送信先（To/CC）の設定

メール送信先は、下記sendNotificationEmail関数の引数に配列または単一の文字列・カンマ区切りの文字列として代入する形式で設定することができる
スプレッドシートに予め送信先を設定しておき、それを参照する形式でも送信可能

5. メール作成および送信の処理

/**
 * 通知メールを送信する関数
 * @param {string} subject - 送信するメールの件名
 * @param {string} body - 送信するメールの本文
 * @param {string} recipientEmail - 送信先のメールアドレス
 * @param {string} ccEmail - CC送信先のメールアドレス
 */
function sendNotificationEmail(subject, body, recipientEmail, ccEmail) {
  MailApp.sendEmail({
    to: recipientEmail,
    cc: ccEmail,
    subject: subject,
    body: body
  });
}

GASサンプルコード（全文）

実行時間：約1.3～1.6秒

サンプルコード

/**
 * 通知メールの内容を指定する関数
 */
function createNotificationEmail() {
  // スプレッドシートを開く
  var ss = SpreadsheetApp.openById('spreadsheetId'); //スプレッドシートのIDを入力
  
  // シートを取得
  var sheetForm = ss.getSheetByName('form'); //フォーム回答結果シート
  var sheetMailFormat = ss.getSheetByName('mailtemp'); //メール文面テンプレートシート
  var sheetAddressMaster = ss.getSheetByName('address'); //宛先一覧シート
  
  // フォーム回答の最新行を取得
  var lastRow = sheetForm.getLastRow();


  //MailTo, MailCCをシート（sheetAddressMaster）から取得する処理など
  //直接指定の例：mailAddressTo = 'exapmle-to@mail.com';
  //省略
  

  /**
   * 置換用連想配列の取得
   */
  //フォームから取得した値を代入
  const replacementText1 = getColumnValues(sheetForm,lastRow); 

  //置換用日付形式を変換
  if(replacementText1['年月日'] != ''){
    var date = new Date(replacementText1['年月日']);

    // フォーマットオプションを設定
    var option1 = { year: 'numeric', month: '2-digit', day: '2-digit' }; // YYYYMMDD形式
    var option2 = { year: 'numeric', month: '2-digit', day: '2-digit', weekday: 'short' }; // YYYY/MM/DD(ddd)形式

    // フォーマットされた日付文字列を取得
    var formattedDate1 = date.toLocaleDateString('ja-JP', option1).replace(/\//g,""); //YYYYMMDD形式
    var formattedDate2 = date.toLocaleDateString('ja-JP', option2); //YYYY/DD/DD(ddd)形式

    Logger.log('日付：' + date);
    Logger.log('変換後日付: ' + formattedDate1+ ',' + formattedDate2);

  } else {
    var formattedDate = null;
  }

  //置換用追加Key, Values
  var replacementText2 = {
    "MailTo" : mailAddressTo,
    "MailCC" : mailAddressCC,
    "年月日（YYYYMMDD）" : formattedDate1,
    "年月日（YYYY/MM/DD/(ddd)）" : formattedDate2
  }; 

  //オブジェクト連想配列を結合
  const replacementText = Object.assign(replacementText1, replacementText2); 
  Logger.log('merged replacementText: ' + JSON.stringify(replacementText)); // なぜか表示されない



  /**
   * 勤怠項目別の分岐
   * ・進捗報告
   * ・宛先追加処理
   */
  // 勤怠項目を取得
  var item = replacementText['項目']; //オブジェクト連想配列から項目を参照

  // フォームのアイテムに応じて処理を分岐
  switch(item) {
    case '進捗報告':
      //タイトル・本文テンプレートを取得
      //後述処理のため、1つ目のセルを件名・2つ目のセルを本文として取得する
      var templateMaill = sheetMailFormat.getRange('範囲').getValues(); 
      break;
    case '宛先追加処理':
      //タイトル・本文テンプレートを取得
      var templateMaill = sheetMailFormat.getRange('範囲').getValues(); 

      //宛先Toに新たなメールアドレスを追加し、CCの最初にデフォルトのToアドレスを追加する
      var addMailAddress = 'example-add@mail.com' //追加メールアドレス
      replacementText['MailCC'] = replacementText['MailTo'] + replacementText['MailCC']; //CCを上書き
      replacementText['MailTo'] = addMailAddress; //Toを置換
      break;
    default:
      //該当処理がなければreturnで処理中断
      Logger.log('メール送信処理：該当処理なし');
      return;
  }

  //メール送信処理
  Logger.log('メール送信先: ' + replacementText['MailTo'] + ' /CC: '+ replacementText['MailCC']);
  var mailSubject = createMailTitle(templateMaill[0][0], replacementText); //タイトルの置換処理
  var mailBody = createMailBody(templateMaill[0][1], replacementText); //本文の置換処理
 
  sendNotificationEmail(mailSubject, mailBody, replacementText['MailTo'], replacementText['MailCC']); //メールを送信 
  Logger.log('メール送信処理完了: ' + replacementText['項目']);
  return;
}

/**
 * 通知メールを送信する関数
 * @param {string} subject - 送信するメールの件名
 * @param {string} body - 送信するメールの本文
 * @param {string} recipientEmail - 送信先のメールアドレス
 * @param {string} ccEmail - CC送信先のメールアドレス
 */
function sendNotificationEmail(subject, body, recipientEmail, ccEmail) {
  MailApp.sendEmail({
    to: recipientEmail,
    cc: ccEmail,
    subject: subject,
    body: body
  });
}



/**============================
 * 本文作成処理
   ============================*/

//タイトル作成処理
function createMailTitle(maillSubject, replacementText){
  var mailSubject = replaceText(maillSubject, replacementText);
  Logger.log('メール件名: ' +mailSubject);

  return mailSubject;
}

//メール本文作成処理
function createMailBody(mailBody, replacementText){
  var mailBody = replaceText(mailBody, replacementText);
  Logger.log('メール本文: ' +mailBody);

  return mailBody;
}

/**
 * 文章の置換
 * @param {string} targetText - テンプレートテキスト
 * @param {string[]} replacementText - 置換対象文字列の配列
 * @return {string} replacedText - 置換後の文字列
 */
function replaceText(targetText, replacementText){
  var templateText = targetText; // テンプレートテキストを最初に設定する

  var replacedText = templateText.replace(/\[(.*?)\]/g, function(match, p1) {
    // ブラケット内の文字列に対応する置換テキストを取得して返す
    return replacementText[p1.trim()]; 
  });

  return replacedText; // 置換されたテキストを返す
}

/**
 * シート内一行目カラムタイトルと最新の値を取得
 * @param {sheet} sheet
 * @param {number} lastRow
 * @return {string[]} columnValue タイトルと値の配列
 */
function getColumnValues(sheet,lastRow) {
  var firstRowValue = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  var lastRowValue = sheet.getRange(lastRow, 1, lastRow, sheet.getLastColumn()).getValues()[0];
  var columnValue = [];
  
  for (var i = 0; i < firstRowValue.length; i++) {
    if (firstRowValue[i] !== "") {
      //オブジェクト連想配列を作成
      columnValue[firstRowValue[i]] =  lastRowValue[i];
    }
  }
  Logger.log('column: '+ columnValue);
  return columnValue;
}

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up