チームメンバーの予定をSpreadSheetにぶっこ抜く！

#【まえがき】
チームメンバーの今日から先、2週間の予定をわかりやすく表示できないかと思って作ってみたものです。
元々、管理職の人がメンバーの状況がわかるように作ったものなので、
ためしに使ってみて、使いやすく修正していただければと思います！
⇒SpreadSheetをサイト上に表示できるので、そうしとくと"より"喜ばれました。

#【ポイント】
・複数人で同一予定のものを集約して１行にする
⇒本来ならEventIDでやればよかったと今になっておもっていますが、
　同じ予定を別々に予定入れてたりするので、今回は件名で集約

・処理するIDの「他のカレンダー」に取得元カレンダーが表示されていること）
⇒これやっておかないとエラーになります。
　都度、チェックして追加する処理もできるのですが、今回は省いてます。

#【作成したもの】
####★SpreadSheet ３シート/１ファイル
・dataシート
A列：開始日
B列：曜日
C列：開始時間
D列：終了時間
E列：件名
F列以降：共通予定の参加者

・dispシート
A列：開始日
B列：曜日
C列：開始時間
D列：終了時間
E列：件名
F列：作った時のミスなのか何も使ってなさそう…
G列：抽出元カレンダー名
H列移行：disp処理にて使用

・masterシート
A列：アドレス（取得元カレンダーID：Emailアドレス形式）
B列：氏名（一覧表示用の氏名を記載）

####★GAS ６本 　①と②はまとめちゃってもよかったかも・・・
処理する順番に記載
①Clearsheet_data：dataシートのデータクリア
②ClearSheet_disp：dispシートのデータクリア
③getCal：予定抽出
④sort：抽出した予定のソート
⑤disp：表示用にデータを整える（ポイント）
⑥dispcopy：dataシートにデータコピー

日次で下記のようにトリガーを仕掛けてます。
①、②：23:00~24:00　－　前日に取ってきたデータをお掃除して、
③：0:00~1:00　－　きれいにしたとこにデータ持ってきて、
④：1:00~2:00　－　ソートして、
⑤：2:00~3:00　－　重複データを整えて、
⑥：3:00~4:00　－　表示用にコピーする！

#【参考Script】

①Clearsheet_data.gas

function ClearSheet_data() {
  try{
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var mstsheet = ss.getSheetByName('data');
    mstsheet.getRange('A2:EI').clearContent();
  }catch(e){
    Browser.msgBox(e);
  }
}

②Clearsheet_data.gas

function ClearSheet_disp() {
  try{
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var dispsheet = ss.getSheetByName('disp');
    dispsheet.getRange('A2:U').clearContent();
  }catch(e){
    Browser.msgBox(e);
  }
}

③getCal.gs

function getCal(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mstsheet = ss.getSheetByName('master');
  var mstvalues  = mstsheet.getDataRange().getValues();
  var batsheet = ss.getSheetByName('data');  
  var batvalues  = batsheet.getDataRange().getValues();
  var StartDay =   new Date();
  var StartDate = Utilities.formatDate(StartDay, "Asia/Tokyo", "yyyy/MM/dd")
  var EndDay = new Date();
  var EndDay =     new Date(EndDay.setDate(EndDay.getDate()+14));     
  var EndDate = Utilities.formatDate(EndDay, "Asia/Tokyo", "yyyy/MM/dd")
  var index = 2; 
  var range = mstsheet.getDataRange(); 
  var j = 1
  var i = 1
  for (var i = range.getRow(); i < range.getLastRow(); ){
    var i =  i + 1;
    var j =  j + 1;
    var caladdress =  mstvalues[j-1][0];    
    var cal = CalendarApp.getCalendarById(caladdress);
    var evts = cal.getEvents(
      new Date(StartDay), 
      new Date(EndDay));
    var calnm =  mstsheet.getRange(j,1);
    var calname =  calnm.getValues();     
    if (evts.length > 0){
      for (var k in evts){
        var evt = evts[k];
        var startday = Utilities.formatDate(evt.getStartTime(), "Asia/Tokyo", "MM/dd")
        var starttime = Utilities.formatDate(evt.getStartTime(), "Asia/Tokyo", "HH:mm")
        var endtime = Utilities.formatDate(evt.getEndTime(), "Asia/Tokyo", "HH:mm")
        batsheet.getRange(index, 1).setValue(startday);
        batsheet.getRange(index, 2).setFormula('=A'+index);
        batsheet.getRange(index, 3).setValue(starttime);      
        batsheet.getRange(index, 4).setValue(endtime);      
        batsheet.getRange(index, 5).setValue(evt.getTitle());
        batsheet.getRange(index, 6).setValue(caladdress);  
        batsheet.getRange(index, 7).setFormula('=vlookup(F'+index+',master!A:B,2,false)');        
        index++;
      }
    }
  }
}

④sort.gs

function sort() {  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('data');    
  var range = sheet.getRange('A2:G');
  range.sort([1, 3, 4, 5, 7]);
}

⑤disp.gs

function disp() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var batsheet = ss.getSheetByName('data');  
  var batvalues  = batsheet.getDataRange().getValues();
  var lastRow = batsheet.getLastRow();
  var string3 = "";
  for(var i = 1; i+1 < lastRow;i++){
    var string = batvalues[i][4];
    if(string3 !== string){
      var date =  batvalues[i][0];
      var date = Utilities.formatDate(date, "Asia/Tokyo", "yyyy/MM/dd");
      var starttime =  batvalues[i][2];
      var starttime = Utilities.formatDate(starttime, "Asia/Tokyo", "HH:mm");
      var string = batvalues[i][4];
      var j = i+1;
      var k = 9;
      for(var j = 1;j<lastRow;j++){
        var date2 =  batvalues[j][0];
        var date2 = Utilities.formatDate(date2, "Asia/Tokyo", "yyyy/MM/dd");
        var starttime2 =  batvalues[j][2];
        var starttime2 = Utilities.formatDate(starttime2, "Asia/Tokyo", "HH:mm");
        var string2 = batvalues[j][4];
        var member = batvalues[j][5];
        if(i !== j){
          if(date === date2){
            if(starttime === starttime2){
              if(string === string2){ //ここから↓　汚くて反省中
                if(k==9){
                  var l= "I";
                }else if(k==11){
                  var l= "K";
                }else if(k==13){
                  var l= "M";
                }else if(k==15){
                  var l= "O";
                }else if(k==17){
                  var l= "Q";
                }else if(k==19){
                  var l= "S";
                }else if(k==21){
                  var l= "U";
                }else if(k==23){
                  var l= "W";
                }else if(k==25){
                  var l= "Y";
                }else if(k==27){
                  var l= "AA";                  
                }else if(k==29){
                  var l= "AC";
                }else if(k==31){
                  var l= "AE";
                }else if(k==33){
                  var l= "AG";
                }else if(k==35){
                  var l= "AI";
                }else if(k==37){
                  var l= "AL";
                }else if(k==39){
                  var l= "AN";
                }
                batsheet.getRange(i+1, k).setValue(member);  
                
                batsheet.getRange(i+1, k+1).setFormula('=vlookup('+l+(i+1)+',master!A:B,2,false)'); //masterシートから氏名を取得
                k = k+2;
                var string3 = string2;
              }
            }
          }    
          batsheet.getRange(i+1, 8).setValue("1");  
        }
      }
    }
  }
}

dispcopy.gs

function dispcopy() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var batsheet = ss.getSheetByName('data');  
  var batvalues  = batsheet.getDataRange().getValues();
  var lastRow = batsheet.getLastRow();
  var targetsheet = ss.getSheetByName('disp');    
  var j = 2;
  for(var i = 1; i+1 < lastRow;i++){
    var flg = batvalues[i][7];
    if(flg == 1){
      var date =  batvalues[i][0];
      var date = Utilities.formatDate(date, "Asia/Tokyo", "yyyy/MM/dd");
      var starttime =  batvalues[i][2];
      var starttime = Utilities.formatDate(starttime, "Asia/Tokyo", "HH:mm");   
      var endtime =  batvalues[i][3];
      var endtime = Utilities.formatDate(endtime, "Asia/Tokyo", "HH:mm");   
      var string = batvalues[i][4]; //ここから↓　汚くて反省中
      targetsheet.getRange(j, 1).setValue(date);
      targetsheet.getRange(j, 2).setValue(batvalues[i][1]);
      targetsheet.getRange(j, 3).setValue(starttime);
      targetsheet.getRange(j, 4).setValue(endtime);
      targetsheet.getRange(j, 5).setValue(batvalues[i][4]);
      targetsheet.getRange(j, 6).setValue(batvalues[i][6]);
      targetsheet.getRange(j, 7).setValue(batvalues[i][9]);
      targetsheet.getRange(j, 8).setValue(batvalues[i][11]);
      targetsheet.getRange(j, 9).setValue(batvalues[i][13]);
      targetsheet.getRange(j, 10).setValue(batvalues[i][15]);
      targetsheet.getRange(j, 11).setValue(batvalues[i][17]);
      targetsheet.getRange(j, 12).setValue(batvalues[i][19]);
      targetsheet.getRange(j, 13).setValue(batvalues[i][21]);
      targetsheet.getRange(j, 14).setValue(batvalues[i][23]);
      targetsheet.getRange(j, 15).setValue(batvalues[i][25]);
      targetsheet.getRange(j, 16).setValue(batvalues[i][27]);
      targetsheet.getRange(j, 17).setValue(batvalues[i][29]);
      targetsheet.getRange(j, 18).setValue(batvalues[i][31]);
      targetsheet.getRange(j, 19).setValue(batvalues[i][33]);
      targetsheet.getRange(j, 20).setValue(batvalues[i][35]);
      targetsheet.getRange(j, 21).setValue(batvalues[i][37]);
      var j = j+1;
    }
  }
}