GASでシフトを皆のカレンダーに一括登録：第１回（元のシフト表から登録のために読み込むための形に整える）

Google Apps Scriptとはなんぞや

Googleが開発している言語で、Google Appsの機能の拡張や、Google Apps間の連携や、外部サービスとGoogle Appsの連携などが可能

背景

GASをもちいて、Googleスプレッドシートで管理していたシフト表を、それぞれのシフトメンバーのカレンダーに登録できるスクリプトを作成します。背景としては、24時間365日のシフトなのですが、シフト表を作成して共有してもシフトを勘違いして忘れるといった事象がたびたび起こっていたので、Googleカレンダーに登録してシフト確認作業を軽くしたいなという意向です！

今回の記事の範囲

まず、スクリプトは大きく2つにわけて作成します。
1.（読み込むためのシフト表作成）元のシフト表から、Googleカレンダーに読み込むための表を作成する。
2.(カレンダー登録) １で作成した表を、皆のカレンダーに登録します。
今回の記事は、１番について書いていこうと思います！

使用した元のシフト表(Google スプレッドシート)

シフト表から新に作成する表のイメージ

from(開始時間）to(終了時間）name(その時間のシフト担当者の名前）mail（シフト担当者のメアド）

作成した関数

sampleShiftCreate()

➡シフト表のマクロボタンと紐づいていて、カレンダーに読み込む用のシート(readSheet)を作成します。
↓の3つの関数も記述されているので、これを実行するとすべて実行されます。

getandSetDate()

➡元のシフト表から、日付の値を取得し、(readSheet)に書き込みます。

getandSetName()

➡元のシフト表から、日付に対応する人の名前を取得して、(readSheet)書き込みます。

maillist()

➡readhSheetに、名前に紐づいたメールアドレスを書き込んでいきます。

作成したコード

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var shiftSheet = spreadsheet.getSheetByName('シート1');

/** @OnlyCurrentDoc */
function sampleShiftCreate() {
  var shtLength = spreadsheet.getSheets().length;
  Logger.log(shtLength);
  if (shtLength == 2){
    var newSht = spreadsheet.insertSheet();
    newSht.setName("readSheet");
    var readSheet = spreadsheet.getSheetByName("readSheet");
    for(i=3; i <= shiftSheet.getLastColumn()*4;i += 4){
      newSht.getRange(1,i-2).setValue("from");
      newSht.getRange(1,i-1).setValue("to");
      newSht.getRange(1,i).setValue("mail");
      newSht.getRange(1,i+1).setValue("name");

    }

    getandSetDate();
    getandSetName();
    maillist();

  }else{
    //上書き処理
    var readSheet = spreadsheet.getSheetByName("readSheet");
    var column = readSheet.getLastColumn();
    var row = readSheet.getLastRow();
    readSheet.getRange(2,1,row-2,column-1).clear();
    getandSetDate();
    getandSetName();
    maillist();
  }
}

var date;
var dateFrom;
var dateFrom;
var dateTo;
var starTime;
var endTime;
var readSheet = spreadsheet.getSheetByName("readSheet");

function getandSetDate(){
  for(var i = 3; i<=shiftSheet.getLastColumn(); i++) {
    date = new Date(shiftSheet.getRange(5,i).getValue());
    for(j = 6;j <=shiftSheet.getLastRow();j++){
      startTime = new Date(shiftSheet.getRange(j,1).getValue());
      dateFrom = new Date(date.getUTCFullYear(),date.getMonth(),date.getDate(),startTime.getHours(),startTime.getMinutes(),0);


      endTime = new Date(shiftSheet.getRange(j,2).getValue());
      dateTo = new     Date(date.getUTCFullYear(),date.getMonth(),date.getDate(),endTime.getHours(),endTime.getMinutes(),0);
      readSheet.getRange(j-4,1 +4*(i-3)).setValue(dateFrom);
      readSheet.getRange(j-4,2 +4*(i-3)).setValue(dateTo);  
    }    
  }  
}
var name;
function getandSetName(){
  for(j=3; j<= shiftSheet.getLastColumn();j++){
    for(i = 6;i <= shiftSheet.getLastRow();i++){
        name = shiftSheet.getRange(i,j).getValue();

        readSheet.getRange(i-4,4*(j-2)).setValue(name);
    }
  }
}

function maillist(){
  for(i=3;i <=readSheet.getLastColumn();i+=4){
    for(j=2;j <= readSheet.getLastRow();j++){
      Logger.log(i) 
      Logger.log(j);
      switch(readSheet.getRange(j,i+1).getValue()){
        case "Aさん":
        readSheet.getRange(j,i).setValue("メールアドレス");
        break;

        case "Bさん":
        readSheet.getRange(j,i).setValue("メールアドレス");
        break;

        case "Cさん":
        readSheet.getRange(j,i).setValue("メールアドレス");
        break;

        case "Dさん":
        readSheet.getRange(j,i).setValue("メールアドレス");
        break;

        case "Eさん":
        readSheet.getRange(j,i).setValue("メールアドレス");
        break;

        case "Fさん":
        readSheet.getRange(j,i).setValue("メールアドレス");
        break;
      }
    }
  }
}

まとめ・感想・補足

少し細かいコードの書き方など、補足で追加していこうと思います。
ご質問・ご指摘などございましたらお願いいたします。m(__)m

※2021年10月3日追記

➡GASでやらなくても、スプレッドシートの関数でシフト表から読み込むシフト表に変換すればいいことに気づきました( ´艸｀)その方が早い。。。