こんにちは。

弊社では予実管理の1つにGoogleカレンダーを使用しています。

朝出勤したらその日の予定をGoogleカレンダーに登録し、帰る際にそれを実績で更新します。

また、それぞれ出退勤のタイミングでチャットに予実を流しています。

1日にこなす作業が複数あると、Googleカレンダーに予実を登録するだけでそれなりに時間を取ってしまいます。

そこで、これらの作業を簡略化するためのスクリプトをGoogle Apps Scriptでつくりました。

Google Apps Scriptとは

「ブラウザでJava scriptをコーディングできる開発プラットフォーム」です。

今回はじめて使ってみましたが、スプレッドシートをGoogleカレンダーに共有する以外にもいろいろとできそうで、うまく使えればとても便利なものになりそうと感じました。

参考：Google Apps Scriptの概要

要件

大まかに次の通りです。

• 予実はスプレッドシートで管理する
• 予実は同じ一覧で管理する
• スプレッドシートに入力した予実をGooleカレンダーに共有する
• Googleカレンダーに実績を共有するときは、その日のスプレッドシートから共有されている予定は削除する
• Googleカレンダーに個別で設定している予定には影響がないように
• スプレッドシートに入力した予実からチャットに流す用のメッセージをつくる
• つくれるチャットに流す用のメッセージはその日分のみ

スプレッドシートの内容

項目は9つで作業時間列には数式を入れています。

他の項目の入力は次の通りです。

B1セルにGoogleカレンダーのアカウントを入力して、Google Apps Scriptでカレンダーを見るときにそれを参照します。

チャットに流すメッセージはM1セルに出力します。

カレンダー共有機能

Google Apps Scriptでカレンダー共有用の「カレンダー共有」タブを追加します。

一覧に入力されている項目が上記のような場合、Googleカレンダーへは次のように共有されます。

実績を共有するときは、スプレッドシートから共有されている予定をすべて削除します。

カレンダー共有後はカレンダー共有列が「済」に変わり、以後共有対象からは外れます。

メッセージ作成機能

Google Apps Scriptでチャットに流す用のメッセージをつくるタブを追加します。

対象となる項目は基本的に日付列の入力がその日の予定、もしくは実績のみです。

メッセージは大区分列、小区分列、作業時間列の内容からつくります。

コードの全体像

const scheduleTypeSchedule = "予定";
const scheduleTypeResult = "実績";
const shareStatusFinish = "済";
const shareStatusUnfinish = "未";

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  menuEntries.push({name: "共有する", functionName: "shareCalendar"});
  ss.addMenu("カレンダー共有", menuEntries);

  menuEntries = [];
  menuEntries.push({name: scheduleTypeSchedule, functionName: "genMsgSchedule"});
  menuEntries.push(null);
  menuEntries.push({name: scheduleTypeResult, functionName: "genMsgResult"});
  ss.addMenu("メッセージ作成", menuEntries);
}

function getSSPosition( arg ) {
  var row = 4;
  var column = 9;
  var cellItem = 0;
  var cellSubItem = 1;
  var cellDate = 2;
  var cellScheduleType = 7;

  if (arg == "message") {
    var cellTotalTime = 6;
    var cellOutputMsg = "M1";
    return {row, column, cellDate, cellItem, cellSubItem, cellScheduleType, cellTotalTime, cellOutputMsg};
  } else {
    var cellStartTime = 3;
    var cellEndTime = 4;
    var cellShareStatus = 8;
    var cellGmailAddr = "B1";
    return {row, column, cellDate, cellItem, cellSubItem, cellScheduleType, cellShareStatus, cellStartTime, cellEndTime, cellGmailAddr};
  }
}

function getSSData( row, column ) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var values = sheet.getRange(row, 2, lastRow, column).getValues();
  return {sheet, lastRow, values};
}

function genMsg( arg, values, lastRow, row, cellScheduleType, cellDate, cellItem, cellSubItem, cellTotalTime ) {
  var msg = "";

  for (i = 0; i <= lastRow - row; i++) {
    var scheduleType = values[i][cellScheduleType];
    var date = genDateObj(values[i][cellDate]);
    var now = genDateObj();
    var workContent = values[i][cellItem] + " " + values[i][cellSubItem];
    var workTime = values[i][cellTotalTime];
    if (date.toDateString() == now.toDateString() && scheduleType == arg) {
      if (msg == "") {
        if (arg == scheduleTypeResult) {
          msg = "お疲れさまです。本日の業務です。n";
        } else {
          msg = "おはようございます。本日の予定です。n";
        }
      }
      msg = msg + "・" + workContent + "（" + workTime + ")n";
    }
  }

  return msg;
}

function genDateObj( date = "" ) {
  if (date == "") {
    ret = new Date();
  } else {
    ret = new Date(date);
  }
  return ret;
}

function sendMsg( arg ) {
  var {row, column, cellDate, cellItem, cellSubItem, cellScheduleType, cellTotalTime, cellOutputMsg} = getSSPosition("message");
  var {sheet, lastRow, values} = getSSData(row, column);
  var msg = genMsg(arg, values, lastRow, row, cellScheduleType, cellDate, cellItem, cellSubItem, cellTotalTime);
  sheet.getRange(cellOutputMsg).setValue(msg);
}

function genMsgResult() {
  sendMsg(scheduleTypeResult);
}

function genMsgSchedule() {
  sendMsg(scheduleTypeSchedule);
}

function shareCalendar() {
  var {row, column, cellDate, cellItem, cellSubItem, cellScheduleType, cellShareStatus, cellStartTime, cellEndTime, cellGmailAddr} = getSSPosition("calendar");
  var {sheet, lastRow, values} = getSSData(row, column);
  var gmailAddr = sheet.getRange(cellGmailAddr).getValue();
  var calender = CalendarApp.getCalendarById(gmailAddr);

  for (i = 0; i <= lastRow - row; i++) {
    var shareStatus = values[i][cellShareStatus];
    if (shareStatus == shareStatusFinish) {
      continue;
    }

    var date = genDateObj(values[i][cellDate]);
    var startTime = values[i][cellStartTime];
    var endTime = values[i][cellEndTime];
    var workContent = values[i][cellItem] + " " + values[i][cellSubItem];
    var scheduleType = values[i][cellScheduleType];
    var options = {description: scheduleType};

    if (shareStatus == shareStatusUnfinish && scheduleType == scheduleTypeResult) {
      var events = calender.getEventsForDay(date)
      for(let i = 0 ; i < events.length ; i++ ){
        if (events[i].getDescription() == scheduleTypeSchedule) {
          events[i].deleteEvent();
        }
      }
    }

    var startWorkTime = genDateObj(date);
    var endWorkTime = genDateObj(date);
    startWorkTime.setHours(startTime.getHours());
    startWorkTime.setMinutes(startTime.getMinutes());
    endWorkTime.setHours(endTime.getHours());
    endWorkTime.setMinutes(endTime.getMinutes());
    calender.createEvent(workContent, startWorkTime, endWorkTime, options);

    sheet.getRange(row + i, column + 1).setValue(shareStatusFinish);
  }
}

Google Apps Scriptのコードはスプレッドシートの拡張機能から追加できます。

Google Apps Scriptを使うとGoogleのサービスをもっと便利に使えそうですね。