Google Apps Script

Google Apps Scriptを使って1案件複数区間の日付をカレンダーに連携する

Google Apps Scriptを使って1案件複数区間の日付をカレンダーに連携する

 

業務の特性上1案件の日付区間が1~4つに分かれています。

スクリプトを組む際に単一の時間指定や複数日にまたがるイベントはよく目にしたのですが、複数期間に分かれているものがなかったので今回作成してみました。

 

今回作成に当たって個人的に勉強になった点です。

  • 二次元配列で取得した値を分割代入する。
  • テンプレートリテラルを使用。
  • スプレッドシート側を見てわかるように連携後はチェックボックスにtrueを入れる。
  • イベントIDを区間ごとにスプレッドシートに出力。(ID認識で日付変更に対応できるため)

 

 

全体のスクリプト文

 

ソースコード

 

今回作成したregistCalendarの全文はこちらです。

function registCalendar(){
//カレンダーを入手
  var calendar = CalendarApp.getCalendarById("カレンダーID(メールアドレス)を入力");
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
//二次元配列で記載されている値を取得
  var contents = sheet.getRange(`A2:AB${lastRow}`).getValues();
  
  for(var i = 0 ; i < contents.length ; i++ ){
//contentsで取得した値を分割代入、各列に合わせて変数をつけます
    var [id, mailDay, agency, storeName, landPage, adCost, firstStatus, firstStart, firstEnd, firstBanner, firstDesign, secondStatus,secondStart, secondEnd, secondBanner, secondDesign, thirdStatus,thirdStart, thirdEnd, thirdBanner, thirdDesign, fourthStatus,fourthStart, fourthEnd, fourthBanner, fourthDesign, report, note] = contents[i];
//第一区間処理
    var firstTitle = id + "(第一区間): " + storeName;
//連携部分がtrueまたは第一区間開始日が空欄だった場合は処理なし
    if(firstStatus == true || firstStart == ''){
    }else{
//Dateオブジェクトは第一区間開始日と終了日を指定
      var firstStartDate = new Date(firstStart);
      var firstEndDate = new Date(firstEnd);
//カレンダー連携の際は終了日の0:00で予定が切れてしまうため、+1日の処理をして終了日を伸ばす。
      firstEndDate.setDate(firstEndDate.getDate() + 1);
//カレンダー転記
      var firstEvent = calendar.createAllDayEvent(firstTitle, firstStartDate, firstEndDate);
//転記されたイベントIDを取得
      var firstEventID = firstEvent.getId();
//スプレッドシートの連携済みにチェックを入れる(G列)
      sheet.getRange(`G${i + 2}`).setValue(true);
//第一区間id書き込み(AD列)
      sheet.getRange(`AD${i + 2}`).setValue(firstEventID);
    }
    
//第二区間処理(以下第一区間と同じ)
    var secondTitle = id + "(第二区間): " + storeName;
    if(secondStatus == true || secondStart == ''){
    }else{
      var secondStartDate = new Date(secondStart);
      var secondEndDate = new Date(secondEnd);
      secondEndDate.setDate(secondEndDate.getDate() + 1);
      var secondEvent = calendar.createAllDayEvent(secondTitle, secondStartDate, secondEndDate);
      var secondEventID = secondEvent.getId();
      //第二区間連携済みチェック(L列)
      sheet.getRange(`L${i + 2}`).setValue(true); 
      //第二区間id書き込み(AE列)
      sheet.getRange(`AE${i + 2}`).setValue(secondEventID);
    }
//第三区間処理(第一区間に同じ)
    var thirdTitle = id + "(第三区間): " + storeName;
    if(thirdStatus == true || thirdStart == ''){
    }else{
      var thirdStartDate = new Date(thirdStart);
      var thirdEndDate = new Date(thirdEnd);
      thirdEndDate.setDate(thirdEndDate.getDate() + 1);
      var thirdEvent = calendar.createAllDayEvent(thirdTitle, thirdStartDate, thirdEndDate);
      var thirdEventID = thirdEvent.getId();
      //第三区間連携済みチェック
      sheet.getRange(`Q${i + 2}`).setValue(true);
      sheet.getRange(`AF${i + 2}`).setValue(thirdEventID);
      
    }
//第四区間処理(第一区間に同じ)
    var fourthTitle = id + "(第四区間): " + storeName;
    if(fourthStatus == true || fourthStart == ''){
    }else{
      var fourthStartDate = new Date(fourthStart);
      var fourthEndDate = new Date(fourthEnd);
      fourthEndDate.setDate(fourthEndDate.getDate() + 1);
      var fourthEvent = calendar.createAllDayEvent(fourthTitle, fourthStartDate, fourthEndDate);
      var fourthEventID = fourthEvent.getId();
      //第四区間連携済みチェック
      sheet.getRange(`V${i + 2}`).setValue(true);
      sheet.getRange(`AG${i + 2}`).setValue(fourthEventID);
    }
  }
}

 

コードがもっと短くなれば理想なんですが、私の力ではこれが限界でした!

追記で、第二区間以降

 if(secondStatus == true || secondStart == ''){}

 

secondStart == ”
(念のためシングルクォーテーション2つです!)

を入れないと、実行した時に「Exception: 予定の開始日は終了日より前にする必要があります。」とエラーが出ます。

空欄だったら開始日も終了日も無いよな〜と思いながら、このエラーggっても出てこないので解決に3日位かけました。

スプレッドシート

 

スプレッドシートは以下の形です。
設計書Noの数が案件の数としています。
(初期だと1501行と処理に時間がかかってしまうので、10行位に削りました。)

 

処理実行前

 

GAS複数日程カレンダー登録1

処理実行後

 

GASカレンダー登録処理後

処理前後でG,L,Q,V各区間連携部分にチェックが入りました。

また、AD,AE,AF,AGにはカレンダーに連携された各イベントのIDが記入されています。
(きっと別シートに書いた方が見栄えは良いんですが、シートを入れ替える処理を書く前に気力がサヨナラしました)

 

Googleカレンダー

 

カレンダーには

var firstTitle = id + “(第一区間): ” + storeName;

の形式で記載されるようにしましたが無事反映されています。
(第一区間は月またぎの関係上スクリーンショットが取れませんでしたが、きちんと反映されています。)

 

GASカレンダー連携

 

まとめ

 

カレンダー連携が可能になったあとは、インストーラブルトリガーで時間主導型のトリガーを設定すると毎回スクリプトを手動実行しなくてもgoogle側が自動で実行してくれます。

 

参考リンク一覧

 

今回機能作成にあたって以下のページを参考にさせていただきました。

【業務自動化をゼロからマスター】Google Apps Scriptの仕事効率化・自動化をプログラミングで実現証明書を取得する

Google Apps Scriptを使って締め切りを通知する方法