GASでドメイン/SSL証明書の有効期限を確認

checkExpireDate.gs

/**
 * 日付をフォーマットする
 * @param  {Date}   date     日付
 * @param  {String} [format] フォーマット
 * @return {String}          フォーマット済み日付
 */
var formatDate = function (date, format) {
  if (!format) format = 'YYYY-MM-DD hh:mm:ss.SSS';
  format = format.replace(/YYYY/g, date.getFullYear());
  format = format.replace(/MM/g, ('0' + (date.getMonth() + 1)).slice(-2));
  format = format.replace(/DD/g, ('0' + date.getDate()).slice(-2));
  format = format.replace(/hh/g, ('0' + date.getHours()).slice(-2));
  format = format.replace(/mm/g, ('0' + date.getMinutes()).slice(-2));
  format = format.replace(/ss/g, ('0' + date.getSeconds()).slice(-2));
  if (format.match(/S/g)) {
    var milliSeconds = ('00' + date.getMilliseconds()).slice(-3);
    var length = format.match(/S/g).length;
    for (var i = 0; i < length; i++) format = format.replace(/S/, milliSeconds.substring(i, i + 1));
  }
  return format;
};

/**
 * @see https://developers.google.com/apps-script/reference/mail/mail-app
 */
function sendMail(row) {
  
  var recipients = row[4].split(',');
  var subject;
  var body;
  if (row[1] == 'domain') {
    subject = "ドメイン失効警告";
    body = "ドメイン失効期限が迫っています!\n";
  } else {
    subject = "SSL証明書失効警告";
    body = "SSL証明書失効期限が迫っています!\n";
  }
  subject += "[" + row[0] + "]" + "残り" + row[5] + "日";
  body += "Site: " + row[0] + "\n";
  body += "Expire: " + 
    formatDate(row[2], 'YYYY-MM-DD') + "\n";

  //var body = doc.getUrl();
  
  MailApp.sendEmail(recipients, subject, body, {
    noReply: true
  });
}

function checkExpiredDate() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  for (var i = 1; i <= numRows - 1; i++) {
    var row = values[i];
    
    if (row[5] == 30 || row[5] == 45) {
      sendMail(row);
    } else if (row[5] < 8) {
      sendMail(row); 
    }
  }
};

/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the readRows() function specified above.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Check Expired Date",
    functionName : "checkExpiredDate"
  }];
  spreadsheet.addMenu("Expired", entries);
};