一言まとめ

勤務シフトを自動でGoogleカレンダーに入力するようにした。
使用した技術：GAS、VBA

作成に至った経緯

これまで

ユーザーのデメリット

自分のシフトを探すのが困難 (送られてくるシフトには約40人のシフトが書かれている)
自分の予定を失念することがある

これから

PDFでのメール配信を継続しつつ、
各個人の予定に関してはGoogleカレンダーに自動記入する用にしました。
シフト表には会議の開始~終了時刻や会議場所も書かれているので
それらのデータをそっくりそのままGoogleカレンダーに
記入することにします。

ユーザーのメリット

自分の予定把握が容易
各会議のリマインダーが自動でセットされる
Googleカレンダーアプリの導入でマルチデバイスでも閲覧が容易

作成したソフト

概要

3つのソフトを作成しました。
①CSV形式のシフト表を後のプログラムが読みやすい形に書き換えるソフト(CSV-CSV変換)
②①で書き換えられたCSVファイルをパースしてGoogleスプレッドシートの
　マスターシートに書き込むソフト
③マスターシートからユーザーシートにデータを取り込んで、
　Googleカレンダーに書き込むソフト

①CSV形式のシフト表を後のプログラムが読みやすい形に書き換えるソフト(CSV-CSV変換)

こちらはVBAで作成しました。CSV-CSV変換処理終了後、Googleドライブにアップします。
Googleドライブにアップする方法はいくつかあると思いますが、
今回はシフト発行者のPCに『ドライブファイルストリーム』をインストールしました。
これはGoogleが公開しているソフトで、
Googleドライブをまるでローカルのドライブの様にマウントできるソフトです。

本ソフトのソースコードは載せませんが、
シフト表の構造の複雑さから作成に多くの時間を要しました。
また、とにかく力技でゴリゴリ回していくソフトになってしまったので、
隙を見てもう少しスマートなものに改修したいと思っています。

② ①で書き換えられたCSVファイルをパースしてGoogleスプレッドシートのマスターシートに書き込むソフト

こちらはGASで作成しました。
パースするにあたってはGAS標準のメソッドを使いました。
特段つまづいた点はありませんでした。

③マスターシートからユーザーシートにデータを取り込んで、Googleカレンダーに書き込むソフト

こちらはGASで作成しました。
ソースコードのコメントが全てを物語ってくれるはずです。

データの入ったスプレッドシート

この様な並びでデータが入力されたシートを基にGoogleカレンダーに予定を書き込んでいます。
イベントID以外は②ソフト実行後に埋められています。
イベントID欄は各予定を作成した後、その予定のユニークコードが入ります。
次回③ソフトが呼び出された時に、イベントIDを用いて一度全予定を
削除してから新しい予定を書き込む動作をしています。

スプレッドシートから予定を作成するプログラム

ShiftToGoogleCalnedar.gs

//*****ユーザー依存の設定*****//
function getConfig() {
  return {
    from_spreadSheetId: '',
    from_spreadSheetTabName: '',
    to_spreadSheetId: '',
    spreadSheetTabName: '',
    username: '',
    usermail: '',
  }
}


//*****メイン関数*****//
function main() {
  deleteAll();
  copyValues();
  registerAll();
}


//*****eventIdを基に作成済イベントを削除*****//
function deleteAll() {
  var range       = 'G1:G300';
  var config      = getConfig();
  var userMail = config.usermail;
  var spreadSheet = SpreadsheetApp.openById(config.to_spreadSheetId);
  var sheet       = spreadSheet.getSheetByName(config.spreadSheetTabName);
  //G列に書かれたイベントIDと同一のイベントを削除
  sheet
    .getRange(range)
    .getValues()
    .filter(function (eventId) {
      return eventId[0] !== '';
    }).forEach(function (id) {
      CalendarApp.getCalendarById(userMail).getEventById(id).deleteEvent();
    });
  sheet.getRange(range).clear();
}


//*****マスターシートからシート内容をコピー*****//
function copyValues() {
  var config      = getConfig();
  var userName    = config.username
  var from_spreadSheet = SpreadsheetApp.openById(config.from_spreadSheetId);
  var from_sheet       = from_spreadSheet.getSheetByName(config.from_spreadSheetTabName);
  var to_spreadSheet = SpreadsheetApp.openById(config.to_spreadSheetId);
  var to_sheet       = to_spreadSheet.getSheetByName(config.spreadSheetTabName);
  to_sheet.getDataRange().clearContent();

  var from_events = from_sheet
    .getDataRange()
    .getValues()
  　　　　//自分の名前と一致する予定のみをコピー
    .filter(function(e) {
      if ( e[0] == userName ) return e;
    })

  to_sheet.getRange( 1, 1, from_events.length, 6 ).setValues(from_events)
}


//*****スプレッドシートに記録された予定をGoogleカレンダーに登録*****//
function registerAll() {
  var config      = getConfig();
  var spreadSheet = SpreadsheetApp.openById(config.to_spreadSheetId);
  var sheet       = spreadSheet.getSheetByName(config.spreadSheetTabName);
  var userName    = config.username
  var userCalendar = CalendarApp.getCalendarById(config.usermail);
  var eventIds = sheet
    .getDataRange()
    .getValues()
    .map(function (e, i, a) {

    //YYYY@MM#DDをYYYY/MM/DDにReplace
    a[i][1] = e[1].replace( "@", "/" )
    a[i][1] = a[i][1].replace( "#", "/" )
    var date = a[i][1];
    var shift = a[i][2];
    var startTime = Utilities.formatDate( a[i][3], "JST", "HH:mm")
    var endTime = Utilities.formatDate( a[i][4], "JST", "HH:mm")
    var place = a[i][5];

    //特定のシフトの場合、終日イベントとして作成。イベントの色は赤。
    if(shift == "shift1" || shift == "shift2" || shift == "shift3"){
      var calendarEvent = userCalendar.createAllDayEvent(
        shift, new Date(date),
        {
          location: place,
        }
      )
      calendarEvent.setColor(CalendarApp.EventColor.RED);
      //作成したイベントのイベントナンバーを配列で保管
      return [calendarEvent.getId()];
      Utilities.sleep(1000);

    //特定のplaceもしくは、startTime及びendTimeが特定の値の場合、終日イベントとして作成。イベントの色は青。
  }else if(place == "place1" || place == "place2" || place == "place3" ||
             startTime == "00:00" && endTime== "23:59"){
      var calendarEvent = userCalendar.createAllDayEvent(
        shift, new Date(date),
        {
          location: place,
        }
      )
      calendarEvent.setColor(CalendarApp.EventColor.BLUE);
      //作成したイベントのイベントナンバーを配列で保管
      return [calendarEvent.getId()];
      Utilities.sleep(1000);

    //上記条件にあてはまらない場合、スプレッドシートに書かれたstartTIme ~ endTimeの予定を作成して。イベントの色は青。
    }else{
      var calendarEvent = userCalendar.createEvent(
        shift, new Date(date + " " + startTime), new Date(date + " " + endTime),
        {
          location: place,
        }
      )
      calendarEvent.setColor(CalendarApp.EventColor.BLUE);
      //作成したイベントのイベントナンバーを配列で保管
      return [calendarEvent.getId()];
      Utilities.sleep(1000);
    }
    return a
})
  //全てのイベントを追加し終えたらG列に当該イベントのイベントIDを記録
  sheet.getRange('G1:G'+ (eventIds.length)).setValues(eventIds);
 }

まとめ

ソフト完成後、部署の皆様に仮リリースをお伝えしたところ、
多くの方が登録を申し出てくれました。
仮リリース期間中はいくつかの要望を頂くことができました。
例えば

あるシフトの時は終日イベントとして欲しい
イベントの色を指定したい

といったものです。
頂いた要望は検討した後、即時ソースコードに反映しました。
ユーザーファーストで作ったこともあり、本運用中の現在も利用してくれている方がいます。

ポエム

つい最近、弊社にもG Suiteが導入されました。
私は会社からG Suiteインフルエンサーに任命されて、
G Suiteへの移行案内(主にメール)と各種サービスのアピールといった、
導入時のショック緩衝材の役割をしていました。
そしていざG Suiteに乗り換える...といったタイミングで
これまでの不便をGoogleのサービスと上記ソフトの組み合わせで解決することができました。
G Suiteインフルエンサーとして部署に新たなテクノロジーの風を
送りこめたと思います。

[GAS]シフトをGoogleカレンダーに自動入力する仕組みを作った話