gasja2.js

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet()
    .addMenu('gasja2', [{name: '勤怠入力補助', functionName: 'timesheet'}]);
}

function timesheet() {
  try {
    SpreadsheetApp.getActiveSpreadsheet().show(_createUI());
  } catch(e) {
    Logger.log(e);
    Browser.msgBox(e);
  }
}

/**
 * 年月入力ダイアログでokボタンがクリックされた時のハンドラ。
 * @param event {object}
 * @return {UiInstance}
 */
function okHandler(event) {
  Logger.log('okHandler. parameter=' + event.parameter);
  var app = UiApp.getActiveApplication();

  var yearMonth = event.parameter.yearMonth;
  if (!yearMonth) { return app; }
  
  var startTime = event.parameter.startTime;
  var endTime = event.parameter.endTime;
  var year = yearMonth.substring(0, yearMonth.indexOf('/'));
  var month = yearMonth.substring(yearMonth.indexOf('/')+1, yearMonth.length);
  var startDate = new Date();
  var endDate = new Date();
  startDate.setFullYear(year, month-1, 1);
  endDate.setFullYear(year, month-1, 31);

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // 指定された月の日本の祝日、ユーザの予定を取得する。
  var holidays =
    CalendarApp.getCalendarById("ja.japanese#[email protected]")
      .getEvents(startDate, endDate);
  var ownerEvents =
    CalendarApp.getOwnedCalendarById(ss.getOwner().getEmail())
      .getEvents(startDate, endDate);

  var sheet = ss.getActiveSheet();
  sheet.getRange('A1:D32').clear();
  sheet.setColumnWidth(1, 100).setColumnWidth(2, 350).setColumnWidth(3, 50).setColumnWidth(4, 50);
  sheet.getRange('C2:D32').setNumberFormat('H:mm');
  sheet.getRange('A1:D1').setValues([['日','予定','開始','終了']]);
  for (var day =1; day<=31; day++) {
    var today = new Date();
    today.setFullYear(year, month-1, day);
    if (today.getMonth() != month-1) { break; }
    var rowNum = day+1;
    _day(sheet, rowNum, today, startTime, endTime, holidays, ownerEvents);
  }

  app.close();
  return app;
}

function _day(sheet, rowNum, today, startTime, endTime, holidays, ownerEvents) {
  var range, i, event, isHoliday, title;
  range = sheet.getRange('A'+rowNum);
  var tz = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
  range.setValue(Utilities.formatDate(today, tz, 'M月d日(EE)'));

  // 日本の祝日を検索し、祝日だった場合はA列のコメントに設定する。
  isHoliday = false;
  for (i = 0; i < holidays.length; i++) {
    event = holidays[i];
    if (event.getStartTime().getYear() == today.getYear()
        && event.getStartTime().getMonth() == today.getMonth()
        && event.getStartTime().getDate() == today.getDate()) {
      isHoliday = true;
      range = sheet.getRange('A'+rowNum);
      range.setComment(event.getTitle());
      break;
    }
  }
  // 曜日ごとの処理の振り分け
  if (isHoliday === true || today.getDay() === 0/*Sunday*/) {
    range = sheet.getRange('A'+rowNum);
    range.setBackgroundColor('#ff0000');
  } else if (today.getDay() === 6/*Saturday*/) {
    range = sheet.getRange('A'+rowNum);
    range.setBackgroundColor('#0000ff');
  } else {
    range = sheet.getRange('C'+rowNum+':D'+rowNum);
    range.setValues([[startTime, endTime]]);
  }
  // ユーザの予定を取得し、予定が存在する場合はB列にその内容を記述する。
  for (i = 0; i < ownerEvents.length; i++) {
    event = ownerEvents[i];
    if (event.getStartTime().getYear() == today.getYear()
        && event.getStartTime().getMonth() == today.getMonth()
        && event.getStartTime().getDate() == today.getDate()) {
      range = sheet.getRange('B'+rowNum);
      title = range.getValue();
      if (title) { title+= '\n'; }
      title+= _getEventTitle(event);
      range.setValue(title);
    }
  }
}

function _getEventTitle(event) {
  if (event.isAllDayEvent()) {
    return '終日 ' + event.getTitle();
  }
  var tz = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
  var title = '';
  title += Utilities.formatDate(event.getStartTime(), tz, 'HH:mm');
  title += '-'+Utilities.formatDate(event.getEndTime(), tz, 'HH:mm');
  title += ' '+event.getTitle();
  return title;
}

/**
 * 年月入力ダイアログを作成する。
 * @return {UiInstance}
 */
function _createUI() {
  Logger.log('_createUI');
  var ui = UiApp.createApplication().setTitle('出力対象の年月を入力してください');

  var yearMonthText = ui.createTextBox().setName('yearMonth');
  ui.add(ui.createLabel('年月(yyyy/MM)')).add(yearMonthText);

  var startTimeText = ui.createTextBox().setName('startTime').setText('10:00');
  ui.add(ui.createLabel('業務開始時刻(HH:mm)')).add(startTimeText);

  var endTimeText = ui.createTextBox().setName('endTime').setText('19:00');
  ui.add(ui.createLabel('業務終了時刻(HH:mm)')).add(endTimeText);

  var okButton = ui.createButton('ok');
  var okHandler = ui.createServerClickHandler('okHandler')
    .addCallbackElement(yearMonthText)
    .addCallbackElement(startTimeText)
    .addCallbackElement(endTimeText);
  okButton.addClickHandler(okHandler);
  ui.add(okButton);

  return ui;
}