はじめに

みなさん、こんにちは！
現在、株式会社ワールドワイドベースでインターン生として働いている大野皓平です。
今回は、Google社が提供している「Googleスプレッドシート」・「Googleカレンダー」・
「Google App Script」通称「GAS」を用いて、シフト自動化ツールを開発してみました。
Googleアカウントさえあれば、無料で誰でも使えるツールですので、ぜひGASで様々なツールを開発していただけたらと思います！

ーどんなツール？ー

今回開発したツールは、スプレッドシートにある日時を特定のGoogleカレンダーに反映させるというものです。
今までは、上司の方が一つ一つ Googleカレンダーにシフトを打ち込んでいました。
しかし、今回のツールを使用することによって、ワンクリックでGoogleカレンダーに反映させることができます。

上記のような流れでシフトを組んでいます。
ツールを使うことによって、大幅な時間短縮をすることができました！
それでは、早速開発をしていきましょう！

ー開発工程ー

①スプレッドシート編

まずは、スプレッドシートを作成していきます！

こちらが実際に使用しているスプレッドシートになっています。
それぞれのシートごとに人の名前をつけてあげると良いと思います。
実際に入力しやすいように「日付」の列にはデータ入力規則から日付を選択し、カレンダーが出るようになっています。また、開始時間と終了時間にプルダウンリストを設けて時刻を選びやすいようにしています。

経過時間は、E2のセルに「=sum(D2-C2)」の関数を使い、時間を求めてます。

実働時間は、休憩時間を差し引いた時間です。F2のセルに
「=IFS(E2>TIME(8,0,0), E2-TIME(1,0,0), TIME(8,0,0)=E2, E2-TIME(0,45,0), E2 > TIME(6,0,0), E2-TIME(0,45,0), TRUE, E2-TIME(0,0,0))」　の関数を使います。
少し、複雑ですね...(笑)
こちらの関数は「8時間を超えて働くと１時間休憩、６時間を超え８時間以下だったら45分休憩、それ以外は休憩がない」という処理をしてくれています。TIME()の引数が左から時間、分、秒を表しています。

合計日数は、G2のセルに「=COUNTA(A2:A22)」で日数をカウントしています。

合計実働時間は、H2のセルに「=sum(F2:F22)」で合計時間を出しており、同様に合計勤務時間は、I2のセルに「=sum(E2:E22)」で合計時間を出しています。
これらの関数は、列で一気にコピペするとそれぞれの行の数値に合わせてくれます。　
本当に便利ですね！

先ほどのスプレッドシートのもう半分です。会社のロゴと操作方法が記載されています。
実際にGoogleカレンダーに反映させるには、この会社ロゴ（画像）をクリックします。
後ほど詳しく解説いたします。

②GAS構築編

続いて、GAS構築編に入ります！
このGASは、スプレッドシートの拡張機能から「App Script」を選択します。
そうすると、以下のような画面が出てきます。

こちらの左側のファイルの＋ボタンから、スクリプトをクリックします。
私のGASはすでに構築済みですが、みなさんはまだ何もないと思いますので、新規でスクリプトを作りましょう！
ファイル名は、実際の人の名前が良いと思います。このスクリプトは１人に対して作成します。
この画像では、Aさん.gs・Bさん.gs・Cさん.gs のようにしています。
仮に10人いたら、10個のスクリプトを作成するということです。スクリプトは１つ作ればコピー可能ですので、そこまで面倒ではないかと思います。

こちらが実際のソースコードです。

function　registerCalender1() {
  var calender = CalendarApp.getCalendarById('xxxxxxxxx@group.calendar.google.com');
  //xxxxxxxxxには特定のGoogleカレンダーの「カレンダーID」をコピペする
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  
  var contents = sheet.getRange(`A2:D${lastRow}`).getValues();

  for (var i=0; i < contents.length; i++) {
    var[day, title, startTime,endTime] = contents[i];

    var date = new Date(day);
    if (startTime == "" || endTime == ""){
      calender.createAllDayEvent(title,date)
    } else {
      var startDateObj = new Date(day);
      startDateObj.setHours(startTime.getHours());
      startDateObj.setMinutes(startTime.getMinutes());

      var endDateObj = new Date(day);
      endDateObj.setHours(endTime.getHours());
      endDateObj.setMinutes(endTime.getMinutes());

      calender.createEvent(title,startDateObj,endDateObj);
    }
　}
}

function registerCalender1()は関数名です。
関数名はなんでも大丈夫ですが、小文字からスタートします。
この関数名は後ほど使用します。

var calender = CalendarApp.getCalendarById('');はどのカレンダーに反映させるかを決めるコードです。('')のクオーテーションの中にGoogleカレンダーのカレンダーIDをコピペします。
そのやり方は、③GASとGoogleカレンダー連携で説明します。
後のコードは、説明を省略します。
大まかに言うと、スプレッドシードにある情報を一つずつ抽出して、カレンダーに反映させるという処理をしています。気になる方は、コードをコピペしてGoogle検索すると良いかもしれません。

このコードで一つ注意してほしいのが、var[day, title, startTime,endTime] = contents[i];の部分です。こちらは[day, title, startTime,endTime] なっていますが、
これはスプレッドシードの日付、内容、開始時間、終了時間の順番と対応しています。
順番を変える、新しく要素を追加するという方は、スプレッドシード、GASの両方を一致させないと処理ができない、もしくは思っているものと違う処理になる可能性があります。

③GASとGoogleカレンダーの連携

ここまでお疲れ様でした！　
残り半分弱でツールが完成します！
ここで、Googleカレンダーを開きます。Googleカレンダーでシフト用にカレンダーを新規で作成（既存のものでも構いません）します。

GASのスクリプト名と同様にAさん・Bさん・Cさんのカレンダーを作成しました。
続いて、Aさんのカレンダーから「設定と共有」を開きます。
そこから、カレンダーの統合までスクロールし、カレンダーIDをコピペします。

そこから、GASでAさんのスクリプトを開き、先ほどのvar calender = CalendarApp.getCalendarById('');の('')のクオーテーションの中にGoogleカレンダーのカレンダーIDをコピペします。@group.calendar.google.comまでコピぺしてください。
これで、GASとGoogleカレンダーの連携が完了です！
同様にBさん、Cさんにも同じようにコピペしていきます。

④ボタンの作成

さて、これが最後のチャプターとなります！
スプレッドシートにボタンを作成することで、全ての作業をスプレッドシートだけで完結することができます。 なので、より業務効率化につながります。
それでは、スプレッドシートを開きます。
今回紹介しているシフト自動化ツールは会社のロゴ画像をボタンにしています。

まずは画像をスプレッドシートに代入します。
画像はなんでも大丈夫です。　ただし、セル上に画像を挿入を選んでください。
任意のところに配置した後、画像を右クリックして、・・・をクリックすると、スクリプトを割り当てるという項目があるので、そこをクリックしてください。

すると、以下のような画面が出てきます。

このスクリプトの割り当てにコードの１行目の function registerCalender1() { のregisterCalender1を入力します。
そうすることによって、画像をクリックすると、GASのコードが実行されます。

registerCalender1はAさんのスクリプトの関数名です。そのため、Aさんのスプレッドシートの画像にスクリプトを割り当てます。　同様にBさん、Cさんにもスクリプトを割り当てます。
ただし、関数名は被らないようにしてください。私の場合は、registerCalender1,2,3.......というように数字で関数名を区別するようにしています。
これでボタンの完成です！

これで、ツールの開発は終了です。
それでは、実際に動かしてみましょう！

このようなシフトを組みました。
それでは、会社のロゴをクリック！

正しく、入力されていますね！
スプレッドシートに時間を入力しないと終日の扱いになります！

このツールに新しい人を追加したいとなったら、

① スプレッドシートをコピーする。
② GASを開き、スクリプトのコピーを作成する。
　その際に、関数名を他のスクリプトと被らないように変更する。
③ Googleカレンダーを開き、新規のカレンダーを作成し、カレンダーIDをコピーして作成したスクリプト
にペーストする。
④ スプレッドシートの画像にスクリプトを割り当てる。該当するGASの関数名を入力。

以上で追加完了です。慣れれば、５分以内で作れるようになります。

ここまでご精読いただき、ありがとうございます！
このツールを使って業務の生産性を高めていただけると嬉しいです！
GASを使うことによって、まだまだ便利なツールを開発できると思いますので この記事を通してGAS開発に興味を持っていただけたらと思います！