今日の白土家保守

sendDairy.gs

function sendDaily() {
  var MAIL_TO = "メールアドレス";
 
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("フォームの回答 1");
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  
  var today = sheet.getRange("E1").getValue();
  var formattedToday = Utilities.formatDate(new Date(today), "JST", "yyyy-MM-dd");
  
  var array = new Array();
 
  for (var i = 1; i <= numRows - 1; i++) {
    var row = values[i];
    var formattedDate = Utilities.formatDate(new Date(row[0]), "JST", "yyyy-MM-dd");
    
    if ( formattedDate != formattedToday ) {
      continue;
    }
    
    var who = new String(row[1]);
    var houseWork = new String(row[2]);
    array.push( who + " が " + houseWork + " をしました。" );
  }
  
  var message = array.sort().join("\n");
  
  MailApp.sendEmail(MAIL_TO, "今日の白土家保守" + "(" + formattedDate + ")",
                    "今日も1日、お家のためにありがとうございます。今日した家事は、以下のとおりです。" + "\n\n" + message);

}

sendSummary.gs

function sendSummary() {
  var MAIL_TO = "メールアドレス";
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("週ごとの集計");
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  
  var today = sheet.getRange("AB1").getValue();
  var formattedToday = Utilities.formatDate(new Date(today), "JST", "yyyy-MM-dd");
  
  for (var i = 1; i <= numRows - 1; i++) {
    var row = values[i];
    var formattedMonday = Utilities.formatDate(new Date(row[0]), "JST", "yyyy-MM-dd");
    var formattedSunday = Utilities.formatDate(new Date(row[1]), "JST", "yyyy-MM-dd");
    
    if ( formattedSunday != formattedToday ) {
      continue;
    }
    
    var washSum = row[18];
    var quickleSum = row[19];
    var bathSum = row[20];
    var sinkSum = row[21];
    var laundrySum = row[22];
    var maintenanceSum = row[23];
    var cookSum = row[24];
    var dustSum = row[25];
    
    var messageSum = "食洗機は" + washSum + "回\n" +
                  "クイックルワイパーは" + quickleSum + "回\n" +
                  "お風呂洗いは" + bathSum + "回\n" + 
                  "水回りの掃除は" + sinkSum + "回\n" +
                  "洗濯は" + laundrySum + "回\n" +
                  "家電メンテナンスは" + maintenanceSum + "回\n" +
                  "お料理は" + cookSum + "回でした。\n" +
                  "ゴミ出しは" + dustSum + "回でした。\n" ;
    
    var frequencySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("理想の頻度表");
    var frequencyRows = frequencySheet.getDataRange();
    var frequencyValues = frequencyRows.getValues();
    var messageAlert = "";
    if (washSum < frequencyValues[1][1]) {
      messageAlert = messageAlert + frequencyValues[1][2] + "\n";
    }
    if (quickleSum < frequencyValues[2][1]) {
      messageAlert = messageAlert + frequencyValues[2][2] + "\n";
    }
    if (bathSum < frequencyValues[3][1]) {
      messageAlert = messageAlert + frequencyValues[3][2] + "\n";
    }
    if (sinkSum < frequencyValues[4][1]) {
      messageAlert = messageAlert + frequencyValues[4][2] + "\n";
    }
    if (laundrySum < frequencyValues[5][1]) {
      messageAlert = messageAlert + frequencyValues[5][2] + "\n";
    }
    if (maintenanceSum < frequencyValues[6][1]) {
      messageAlert = messageAlert + frequencyValues[6][2] + "\n";
    }
    if (cookSum < frequencyValues[7][1]) {
      messageAlert = messageAlert + frequencyValues[7][2] + "\n";
    }
    if (dustSum < frequencyValues[8][1]) {
      messageAlert = messageAlert + frequencyValues[8][2] + "\n";
    }
    
    MailApp.sendEmail(MAIL_TO, "今週の白土家保守" + "(" + formattedMonday + " 〜 " + formattedSunday + ")",
                       "今週も、お家のためにありがとうございました。今週行なった家事の集計は、以下のとおりです。" + "\n\n" + messageSum + "\n" + messageAlert);
  }
}