稼働報告作成.gs

var Confing;
(function (Confing) {
    Confing.sourceFile = '打刻 2020-07-01T00_00_00Z to 2020-07-30T00_00_00Z';
    Confing.targetFile = '稼働報告書-2020-07';
    Confing.workStartLabel = '稼働開始';
})(Confing = Confing || (Confing = {}));

function onOpen(e) {
    createMenu();
}
function onEdit(e) {
    createMenu();
}

function createMenu() {
    SpreadsheetApp.getUi()
        .createMenu('打刻集計')
        .addItem('チェック', 'checkWorkDays')
        .addItem('集計', 'setWorkDays')
        .addItem('日付変換', 'calculateDate')
        .addToUi();
}

function calculateDate() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt('変換したい日付');
  
  if (response.getSelectedButton() == ui.Button.OK) {
    var date = Moment.moment(response.getResponseText());
    ui.alert(date.format("YYYY-MM-DDTHH:mm:ssZ"));
  }
}

function checkWorkDays() {
  const _ = Underscore.load();

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const source_sheet = ss.getSheetByName(Confing.sourceFile);
  const values = source_sheet.getDataRange().getValues();
  
  const invalidRecords = _.compact(values.map(function(element, index, array) {
    if (index+1 < array.length) {
      if (element[4] == array[index+1][4]) {
        return [index+1, 6];
      }
    }
  }));
  
  _.each(invalidRecords, function(element, index) {
    source_sheet.getRange(element[0], element[1]).setValue('打刻が次の行と重複しています').setBackground('#ffb6c1').setFontColor("red");
  });
}

function setWorkDays() {
  const _ = Underscore.load();

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const source_sheet = ss.getSheetByName(Confing.sourceFile);
  const values = source_sheet.getDataRange().getValues();
  
  const workDays = values.map(function(element, index) {
    var date = Moment.moment(element[0]);
    return date.format("YYYY/MM/DD (ddd)");
  });

  // [Invalid date, 2020/07/01 (Wed), 2020/07/02 (Thu), ... , 2020/07/28 (Tue), 2020/07/30 (Thu)]
  const uniqWorkDays = _.uniq(workDays);

  // [[稼働日], [2020/07/01 (Wed)], [2020/07/02 (Thu)] , ... , [2020/07/28 (Tue)], [2020/07/30 (Thu)]]
  const arrayedUniqWorkDays = uniqWorkDays.map(function(element, index) {
    if (index==0) {
      return ["稼働日"];
    } else {
      return [element];
    }
  });  
  
  // [[date, 稼働開始], [date, 稼働終了], ... ,[date, 稼働開始], [date, 稼働終了]]
  const workTimes = values.map(function(element, index) {
    var date = Moment.moment(element[0]);
    return [date, element[4]];
  });

  // [
  //   [2020/07/01 (Wed), 158.0, 07/01 09:10:00, 07/01 11:48:24],
  //   [2020/07/01 (Wed), 347.0, 07/01 12:48:09, 07/01 18:35:45],
  //   ... ,
  //   [2020/07/30 (Thu), 184.0, 07/30 09:37:32, 07/30 12:41:56],
  //   [2020/07/30 (Thu), 259.0, 07/30 13:49:29, 07/30 18:08:52]
  // ]
  const calcuratedWorkTimes = _.compact(workTimes.map(function(element, index, array) {
    if (element[1] == Confing.workStartLabel) {
      return [
        element[0].format("YYYY/MM/DD (ddd)")
        , array[index+1][0].diff(element[0], "minutes")
        , element[0].format("MM/DD HH:mm:ss")
        , array[index+1][0].format("MM/DD HH:mm:ss")
      ];
    }
  }));
  
  // [
  //   [稼働日, 開始・終了時刻, 稼働時間],
  //   [[2020/07/01 (Wed)], 07/01 09:10:00 ~ 07/01 11:48:24, 07/01 12:48:09 ~ 07/01 18:35:45, 8時間25分],
  //   [[2020/07/02 (Thu)], 07/02 08:59:48 ~ 07/02 12:05:48, 07/02 13:22:58 ~ 07/02 18:06:23, 7時間49分],
  //   ... ,
  //   [[2020/07/28 (Tue)], 07/28 09:02:00 ~ 07/28 12:13:29, 07/28 13:34:40 ~ 07/28 18:35:58, 8時間12分],
  //   [[2020/07/30 (Thu)], 07/30 09:37:32 ~ 07/30 12:41:56, 07/30 13:49:29 ~ 07/30 18:08:52, 7時間23分]
  // ]
  const arrayedUniqWorkDaysAndWorkTime = arrayedUniqWorkDays.map(function(element, index) {
    if (index==0) {
      return ['稼働日', '開始・終了時刻', '稼働時間'];
    } else {
      var thisDateWorkFromTo = calcuratedWorkTimes.filter(function(e, i) {
        return (element[0] == e[0]);
      }).map(function(e, i) {
        return e[2] + " ~ " + e[3];
      });
      var thisDateWorkTime = calcuratedWorkTimes.filter(function(e, i) {
        return (element[0] == e[0]);
      }).map(function(e, i) {
        return e[1];
      });
      return [element, thisDateWorkFromTo.join(', '), min_to_hour_min(_.reduce(thisDateWorkTime, function(memo, num){ return memo + num; }, 0))];
    }
  });

  // [
  //   [稼働日, 開始・終了時刻, 稼働時間],
  //   [[2020/07/01 (Wed)], 07/01 09:10:00 ~ 07/01 11:48:24, 07/01 12:48:09 ~ 07/01 18:35:45, 8時間25分],
  //   [[2020/07/02 (Thu)], 07/02 08:59:48 ~ 07/02 12:05:48, 07/02 13:22:58 ~ 07/02 18:06:23, 7時間49分],
  //   ... ,
  //   [[2020/07/28 (Tue)], 07/28 09:02:00 ~ 07/28 12:13:29, 07/28 13:34:40 ~ 07/28 18:35:58, 8時間12分],
  //   [[2020/07/30 (Thu)], 07/30 09:37:32 ~ 07/30 12:41:56, 07/30 13:49:29 ~ 07/30 18:08:52, 7時間23分],
  //   [合計, , 121時間13分]
  // ]
  arrayedUniqWorkDaysAndWorkTime.push(['合計', '', min_to_hour_min(_.reduce(calcuratedWorkTimes, function(memo, e){ return memo + e[1]; }, 0))]);
  
  const target_sheet = ss.getSheetByName(Confing.targetFile);
  target_sheet.clearContents();
  target_sheet.getRange(1, 1, arrayedUniqWorkDaysAndWorkTime.length, arrayedUniqWorkDaysAndWorkTime[0].length).setValues(arrayedUniqWorkDaysAndWorkTime);
}

function min_to_hour_min(mins) {
  const h = mins / 60 | 0;
  const m = mins % 60 | 0;
  
  return h + "時間" + m + "分";
}