【GAS】Google Apps Script 活用事例　複数箇所にまたがる備考欄をカテゴリーごとにメール本文に書き出す。

同じ管理会社に各シェアハウスの修繕内容をまとめてメールしたい。

SpreadsheetでToDo管理の続きです。実務で、日本語が話せないスタッフに代わって、不動産会社にシェアハウスの不具合箇所を伝えるという業務があり、担当者から、不動産会社に不具合箇所をまとめて伝えたい（メールしたい）という要望を受けたので作ってみました。

今回は、要望は出来そうだけど・・・難しいなぁという事で、自分で書いてみたけど上手くいかなくて、ノンプロ研のメンバーに相談し、結果的に答えを教えてもらう形になってしまったのですが、一人だったら無理。と諦めていた気がします。そして正解の型は、どんどん蓄積されていっています。

/*管理会社ごとに、各シェアハウスの修繕箇所をメールに書き出す。*/

function integrateContents() {
 
 var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
 var sheet       = spreadSheet.getSheetByName('ALL_DATA');
 var values      = sheet.getDataRange().getValues();
 //Logger.log(values);
 
 //各列の3行目から取得　一次元配列化
 //1行目、見出し　2行目、ARRAYFORMULA関数が入っているため、3行目から範囲を取得すること
 
 //columnG 管理会社
 var targetRow      = sheet.getRange(3, 7, sheet.getLastRow()-2).getValues().filter(String).length;
 var columnG        = sheet.getRange(3, 7, targetRow).getValues();
 columnG = Array.prototype.concat.apply([], columnG); // 一次元配列化
 
 Logger.log(targetRow);
 Logger.log(sheet.getRange(3, 7, sheet.getLastRow()-2).getA1Notation());//ここ注意が必要、3行目から始まっているので、-2をする
 Logger.log(sheet.getRange(3, 7, targetRow).getA1Notation());
 
 //管理会社　配列の重複を省く
 var newArray = columnG.filter(function (value, i, array) {
   return array.indexOf(value) === i;
 });
 Logger.log(newArray);
 
 for(var j = 0; j < newArray.length; j++){ // uniqueCompanyName
   Logger.log('newArray[%s]: %s', j, newArray[j]);
   var contents = [];  //空の配列 
   var count = 0;
   var targetCompany = ''; // ここに対象となる管理会社が入る
   var targetAddress = ''; // ここに対象となる管理会社のメールアドレスが入る
   var person        = ''; //担当者
   
   for(var k = 1; k < values.length; k++){ // dataRange.values()
   //kが１から始まるのは、見出しを省くため
     
     var shareHouse        = values[k][4];//シェアハウス
     var realEstateCompany = values[k][6];//管理会社
     var brokenPart        = values[k][7];//不具合・内容
     var status            = values[k][8];//未対応
     var eMail             = values[k][13];//メールアドレス
     var inCharge          = values[k][14];//担当者

     if (status == '未対応' && realEstateCompany === newArray[j]) {
     
//シートを検索して、未対応かつ、4つの不動産会社　
//['管理会社A','管理会社B','管理会社C','管理会社D']
//のいずれかに一致するか確認する。一致したら、新たな変数に代入する。

       count ++;
       targetCompany = realEstateCompany;
       targetAddress = eMail;
       person        = inCharge;
       
       //改行を入れる
      shareHouse = shareHouse + '\n';
      brokenPart = brokenPart + '\n\n';
       
       contents.push(shareHouse, brokenPart);//シェアハウス、不具合・内容
     }
   }//k
   //Logger.log('管理会社：%s,メール：%s %s件：%s', targetCompany,  targetAddress, count, contents);
   
   Logger.log(contents);
   
   var mailAddress = targetAddress
   var mailCc = '*******@gmail.com';
   var mailTitle = '各シェアハウスの修繕箇所について';
   var mailBody = '';
   
   
   //if文を噛ませないと、未対応 0件が出力してしまう
   if(count > 0){
   
     mailBody += targetCompany + '\n';
     mailBody += person + '　様' + '\n\n';
     mailBody += 'お世話になっております。'+ '\n';
     mailBody += '株式会社******　******課　＊＊＊＊です。' + '\n\n';
     mailBody += '修繕対応依頼箇所：　' + count + ' 件' + '\n\n';
     mailBody += contents.join('');
     var mailArgs = { cc: mailCc }
     GmailApp.createDraft(mailAddress, mailTitle, mailBody, mailArgs);
     
   }//if
 }//j
 Logger.log(contents);
}

シートの役割の切り分けが難しい......。

このシートは入力、データ処理、閲覧の機能を一緒くたにしてしまったために、1行目が見出し、2行目がARRAYFORMULAを含んだ関数、3行目からデータが始まっています。そのため、getRange(3, 7, sheet.getLastRow()-2)のようになっています。

スタッフIDを入力したら、該当スタッフの名前、所属している支店を自動で出力するVLOOKUP関数は、効率化のために、どうしても組みたくなるのでデータ処理をしやすくするために、どう機能を切り分けるか？それは今後の課題だと考えています。

For文の威力は凄まじい

上記の場合だと管理会社はG列の2社、なので2通の下書きメールを作成し、複数箇所にまたがる修繕箇所は管理会社ごとにまとめたい。

管理会社が、テレビ東京だったら、懐中時計が止まった、秋葉原駅が封鎖されている、シャイニングスターダスト.....
みたいな感じで3行の修繕箇所を1通のメール文面の中にまとめたい.....

これ書いた人、天才か！？

何事もまず、順番がありますね。管理会社の配列を作成する必要があります。そのためには、G列をそのまま取得すると重複分が発生してしまいます。そこで重複を削除します。

//columnG 管理会社
 var targetRow      = sheet.getRange(3, 7, sheet.getLastRow()-2).getValues().filter(String).length;
 var columnG        = sheet.getRange(3, 7, targetRow).getValues();
 columnG = Array.prototype.concat.apply([], columnG); // 一次元配列化
 
 Logger.log(targetRow);
 Logger.log(sheet.getRange(3, 7, sheet.getLastRow()-2).getA1Notation());//ここ注意が必要、3行目から始まっているので、-2をする
 Logger.log(sheet.getRange(3, 7, targetRow).getA1Notation());
 
 //管理会社　配列の重複を省く
 var newArray = columnG.filter(function (value, i, array) {
   return array.indexOf(value) === i;
 });
 Logger.log(newArray);

var newArray = columnG.filter(function (value, i, array) {
   return array.indexOf(value) === i;
 });

上記は、indexOfの結果を上手く利用して、重複を省いています。indexOfは一次元配列か文字列でしか使えないので、2次元配列を1次元配列に変換するという作業をしています。

VLOOKUP関数などにも同じ事が言えるのですが、検索対象の中に、IDが複数箇所にまたがって存在していても、一番上のデータを返します。

G列の場合、[ 'テレビ東京', 'テレビ東京', 'テレビ東京', '天王寺家', '天王寺家']
※1行目見出し、2行目ARRAYFORMULA関数（非表示）、3行目からデータ

sheet.getRange(3, 7, sheet.getLastRow()-2)

array.indexOf(value) === i　valueには配列の要素が入ります。つまり、テレビ東京と天王寺家です。テレビ東京が一番初めに登場するのは、配列番号 0 番目です。天王寺家が一番最初に登場するのは、配列番号3番目　0 === 0, 　0 === 1,　 0 === 2,　3 === 3 ........

右辺と左辺が完全一致した時のみ、newArrayという配列に加えられます。これ考えた人、めっちゃ頭が良い！！配列の重複削除は分からなかったので、ググってパクりました。

これで重複を省いた配列が完成しました。getLastRow()は、1行目から数えた、最終行の値を返してくれます。3行目から始まるのでは -2 しています。-2をしないと空白セルが配列の中に入ってしまいます。そこだけ注意が必要です。

for文の1周目で、管理会社の配列、newArray。for文の2周目で表の取得内容を走査していきます。そして、I列管理会社への連絡ステータスが未対応かつ、newArrayの要素と、G列の取得内容が一致するかを調べます。

if (status == '未対応' && realEstateCompany === newArray[j]){

//具体的な処理を書く

}

そのあとは、作成した配列をメールにべたっと貼り付けるだけです。ただし、カンマが入ってしまうので、それを取り除いて、文字列にするために、
mailBody += contents.join(''); の記述を加えています。

var eMail             = values[k][13];//メールアドレス
var inCharge          = values[k][14];//担当者

サンプルシートでは、14列目、15列目が面倒で作らなかったのですが、入力すればundefindや空白にならずに、作成できます！！5000字、書くのも大変でしたが、最後まで読んで頂きありがとうございます。