営業数字自動メール送信

code.gs

function mailSend() {

//////　初期　//////////////////////////////////////////////

//スプレッドシートのIDを指定
var fileId   = "****************************";

//////　宛先　//////////////////////////////////////////////

var address = "***@sam-ple.net";

//////　件名　//////////////////////////////////////////////

//現在の月を取得
var MM= Utilities.formatDate(new Date(), "Asia/Tokyo", "MM");
//現在の日を取得
var dd = Utilities.formatDate(new Date(), "Asia/Tokyo", "dd");

var subject = "日次ランキング（" + MM + "月" + dd + "日）";
  
//////　本文　//////////////////////////////////////////////

var content = "";

content += "各位\n\n";
content += "" + MM + "月" + dd + "日の日次ランキングです。\n\n";

////////////////  
  
var sheet = "【営業】確定額"
var ss = SpreadsheetApp.getActive().getSheetByName(sheet);

content += "■【営業】確定額ランキング(3Q合計)\n";
content += "\n";
for(var i=4;i<=8;i++){
  content += "" + String(ss.getRange(i,7).getValue()).replace(/(\d)(?=(\d\d\d)+(?!\d))/g,'$1,') + "位\t" + ss.getRange(i,8).getValue() + "\t" +  String(ss.getRange(i,12).getValue()).replace(/(\d)(?=(\d\d\d)+(?!\d))/g,'$1,') + "円\n";
}
content += "\n";

////////////////  

var sheet = "【営業】チーム達成率"
var ss = SpreadsheetApp.getActive().getSheetByName(sheet);

content += "■【営業】チーム達成率ランキング(3Q合計)\n";
content += "\n";
for(var i=4;i<=8;i++){
  content += "" + String(ss.getRange(i,7).getValue()).replace(/(\d)(?=(\d\d\d)+(?!\d))/g,'$1,') + "位\t" + ss.getRange(i,8).getValue() + "\t" +  Math.round(ss.getRange(i,12).getValue()*100*Math.pow(10,2))/Math.pow(10,2) + "％\n";
}
content += "\n";

  ////////////////  

var sheet = "【CS】合説件数"
var ss = SpreadsheetApp.getActive().getSheetByName(sheet);

content += "■【CS】合説件数ランキング(3Q合計)\n";
content += "\n";
for(var i=4;i<=8;i++){
  content += "" + String(ss.getRange(i,7).getValue()).replace(/(\d)(?=(\d\d\d)+(?!\d))/g,'$1,') + "位\t" + ss.getRange(i,8).getValue() + "\t" +  String(ss.getRange(i,12).getValue()).replace(/(\d)(?=(\d\d\d)+(?!\d))/g,'$1,') + "件\n";
}
content += "\n";

////////////////  

var sheet = "【CS】合説確定額"
var ss = SpreadsheetApp.getActive().getSheetByName(sheet);

content += "■【CS】合説確定額ランキング(3Q合計)\n";
content += "\n";
for(var i=4;i<=8;i++){
  content += "" + String(ss.getRange(i,7).getValue()).replace(/(\d)(?=(\d\d\d)+(?!\d))/g,'$1,') + "位\t" + ss.getRange(i,8).getValue() + "\t" +  String(ss.getRange(i,12).getValue()).replace(/(\d)(?=(\d\d\d)+(?!\d))/g,'$1,') + "円\n";
}
content += "\n";

////////////////  

content += "■スプレッドシート\n";
content += "\n";
content += "https://docs.google.com/spreadsheets/d/"+ fileId + "/edit#gid=0" + "\n";
content += "\n";

////////////////  

//Logger.log( content );
  
//////　Excelファイル　//////////////////////////////////////////////

//Excelファイルの名前
var xlsxName = "日次ランキング（" + MM + "月" + dd + "日）" + ".xlsx";
//エクスポート用のURL
var fetchUrl = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + fileId + "&amp;exportFormat=xlsx";
//OAuth2対応
var fetchOpt = {
    "headers" : { Authorization: "Bearer " + ScriptApp.getOAuthToken() },
    "muteHttpExceptions" : true
};
//URLをダウンロード
var xlsxFile = UrlFetchApp.fetch(fetchUrl, fetchOpt).getBlob().setName(xlsxName);

//////　メール送信　//////////////////////////////////////////////

//ダウンロードしたxlsxファイルを添付してメール送信
MailApp.sendEmail(address, subject, content, { attachments:[xlsxFile], name: '日次ランキング' });

//下書き
//GmailApp.createDraft(address,subject,content, { attachments:[xlsxFile], name: '日次ランキング' });

}