【Gas】スプレッドシートでカレンダーを取り込んでみた【詳細Ver】

お世話になります。

対顧客用にグーグルカレンダーは共有したくないけど、開いている日付、時間を共有したいという要望から生まれました。
当方プログラミングなんて学生時代にCとVBAをちょっとかじった程度の知識しかない状態で無理やり作っているので、
Javaはほとんど触ったことがありません。
なのですごく無理くりにやっている個所、VBAチックな部分があるのはご容赦ください。
むしろもっとこのほうがスマートだよとかやり方があったら是非教えてほしいです。

というかこれ先方がグーグルカレンダーを使用していたら予定ありと表示するように設定してあげればこれいらなくね？？？？

見た目

注意点

スプレッドシートに関数を埋め込み、スクリプトを簡易化している部分があります。
下記の内容をシートに書き込んでからお使いください。
A2　=$A$1
A3 =if(A2="","",vlookup(weekday(A2),'シート2'!$A:$B,2,false))
※非表示のシートに下記の曜日対応表を作成してありますのでこちらの作成もお忘れずに

B2　=if(A2="","",if(eomonth($A$1,0)<A2+1,"",A2+1))
B3　==if(B2="","",vlookup(weekday(B2),'シート2'!$A:$B,2,false))
あとは右端まで数式のコピーをしてください。

ざっくりとした仕様

1．日曜日はピンク、土曜日は青色、祝日はピンク色になる
2．更新を押下するとグーグルカレンダーより再度スケジュールを引き込む
3．新規作成を押すと日付を入力できるInputBoxが出現し、例に沿って日付を入力すると新しい月のカレンダーを自動生成する。

※もっと予定がぎっちぎちの人は下に行数を増やしてあげて先頭のunderRowsの行数を増やしてあげてください。

コード

var underRows = 15;

function getEvent(){
  valueclear();
  var getsheet = SpreadsheetApp.getActiveSheet();
  var calid1 = CalendarApp.getCalendarById("メールアドレスをここに");
  var place = ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","AA","AB","AC","AD","AE"];
  for(var i = 0; i < 31; i++){
    if(getsheet.getRange(place[i] + "2").getValue() !== ""){
      var todayevents = calid1.getEventsForDay(new Date(getsheet.getRange(place[i] + 2).getValue()));  
      if(todayevents.length>0){
        for(var i2 = 1;todayevents.length + 1 > i2;i2++){
          getsheet.getRange(place[i]+(i2+3)).setValue(Utilities.formatDate(todayevents[i2-1].getStartTime(),"Asia/Tokyo","HH") + "時\n" + Utilities.formatDate(todayevents[i2-1].getEndTime(),"Asia/Tokyo","HH") + "時\n" + "予定あり");
        }
      }
    }
  }
  var nowdate = new Date();
  getsheet.getRange("F1").setValue(Utilities.formatDate(nowdate,"Asia/Tokyo","MM月dd日: hh時mm分更新"));
};

function valueclear() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A4:AE15').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange("A1").activate();
};

function CreateCalendar(){
  var createmonth = Browser.inputBox("作成したい日付を例に沿って入力してください。日付は1日を指定してください。\\n例:2018/09/01");
  if(createmonth !== "cancel"){ 
    SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet();
    var getsheet = SpreadsheetApp.getActiveSheet();
    getsheet.getRange("A1").setValue(createmonth);
    setcollar();
    valueclear();
    getEvent();
  var getsheet2 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var sn = getsheet2.setName(Utilities.formatDate(getsheet.getRange("A1").getValue(),"Asia/Tokyo","MM")+"月");
  getsheet.getRange("A1").activate();
  }
};

function setcollar(){
  var place = ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","AA","AB","AC","AD","AE"];
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var weekstring = ["日","月","火","水","木","金","土"];
  var getsheet = SpreadsheetApp.getActiveSheet();
  var calid2 = CalendarApp.getCalendarById("ja.japanese#[e-mail]");
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();
  spreadsheet.getActiveRangeList().setBackground('#ffffff');
  spreadsheet.getRange('A1:E1').activate();

  for(var i = 0; i < 31; i++){
    if(getsheet.getRange(place[i]　+　"2").getValue() !== ""){
      var todayevents2 = calid2.getEventsForDay(new Date(getsheet.getRange(place[i] + 2).getValue()));
      var day = getsheet.getRange(place[i] + "2").getValue();
      var week = weekstring[new Date(day).getDay()];
      if(week === "日"){
        getsheet.getRange(place[i] + "2:" + place[i] + underRows).activate();
        getsheet.getActiveRangeList().setBackground('#ffdefe');
      }
      if(week === "土"){
        getsheet.getRange(place[i] + "2:" + place[i] + underRows).activate();
        getsheet.getActiveRangeList().setBackground('#cfe2f3');
      }
      if(todayevents2.length>0){
        getsheet.getRange(place[i] + "2" + ":" + place[i] + underRows).activate();
        getsheet.getActiveRangeList().setBackground('#ffdefe');
      }
    }
  }
  getsheet.getRange("A1").activate();
};

コードの解説コーナー

まず作った本人がどうなってるのか若干あやふやです。すみません!!!!!!

getEvent()

varの部分は適時ググってもらえれば何となくわかるはず。
placeについてはoffsetがほんまわけわからんかったので配列で無理やり代用してしまいました。

getEventsForDayにてその日に登録されているイベントを配列のタイプでtodayeventsに格納します。
ただその日何も予定がないと次のsetValueあたりでエラーが発生するのでif文で回避してます。
あとは配列に格納された分だけfor文で回して書き出します。
※ここはsetValuesを使えば高速化が図れるはず・・・。あやつの扱いがすごく難しいので今回はfor文でsetValueで回してます・・・。
誰か書き方教えてほちぃ・・・

valueclear

読んで字のごとく中身をきれーにクリアするだけです。
最近のスプレッドシートについたマクロ機能をそのままこぴってきただけです。

CreateCalendar

新しい月のカレンダー作成用です。
InputBoxにて"2018/09/01"と入力してもらうと9月分のカレンダーを自動生成します。
実はここで行ってるといえば新しいシートを作成してA1に値をセットしているだけなんです・・・。
月の日数とかとるなんて僕には無理でした許してください。なんでもしますから！（なんでもするとは言っていない）

setcollar

単純に土曜か日曜か祝日かでシートに色を付けているだけです。
"ja.japanese#[e-mail]"をgetEventsForDayで日付指定で配列にぶち込むと見事に祝日の時だけ配列の大きさが1になるのでそれを利用しています。
あとはweekstringに日付を格納しておき、getValueで日付を取得し、その日が何曜日化をnew Date(day).getDay()　で取得して曜日変換しています。
この辺は詳しくあまり理解できていないのでリファレンスを気になる方は見ることをお勧めします。
なぜかここではoffsetを使っていたけどなんでだろう。

あとがき

わからないことがあったらコメントいただければできるだけ答えますので！！！！どうぞよろしくお願いします。
というかfunctionにつける名前が小文字だったり大文字だったり統一されてねぇなこれ。今更修正するのも面倒・・・
あとセットカラーのとこでplace[i] + "2:"と2をあえて文字列扱いしてしまったけどこれはjava的には問題のかあるのか・・・
でも動いてるからいいよね

というか終日イベントの処理してなかった。忘れてた。
終日イベント化どうか判断するのにはおそらくgetEventsForDayのgetStartTimeとgetEndTime？だっけかで
そのイベントの開始と終わりが同時刻っていう判断をすればいいってどっかに書いてあった。
要望があるようでしたらそれとなく実装してみます。

ちなみに全く無の状態からリファレンスとにらめっこして作成に2日程かかりました死にたい。

後日簡易バージョンのカレンダーもアップしますのでどうぞよろしくお願いします。