前提

処理は Google App Script で行いました。

エクセルもスプレッドシートに変換して処理するので、任意のドライブにシフト表を配置すれば元のファイルはエクセル、スプレッドシートのどちらでも可能です。

シフト表のフォーマットは以下なので、フォーマットが違うときは合わせるための処理が別途必要です。

 

スクリプト詳細

新しいプロジェクトを作成

プロジェクト名は何でも良いですが、「シフト表からカレンダー自動登録」にしました。

 

スクリプトプロパティにIDを追加

GASではセキュリティの観点から、ID、パスワード、APIキーなどをスクリプトプロパティに保管して利用します。

完全に自分しか使わないとか、GitHub管理もしないのであればスクリプト内に直書きでも良いかもしれませんが、スクリプトプロパティの利用を癖にしたほうが良いと思います。

ということで、GASスクリプト内の設定画面を開き、下部にある「スクリプトプロパティ」の欄からプロパティ(Key)と値(value)を設定します。

作成するプロパティは「GoogleカレンダーのID」と「シフト表を配置したドライブのID」です。

それぞれ、「MyCalenderID」と「ShiftFolder」でプロパティ名を設定し、IDを保存します。

ドライブIDはフォルダURLの、https://drive.google.com/drive/folders/XXXXX の XXXXX の部分です。

カレンダーIDはGoogleカレンダーの設定画面に記載されてます。

 

シフト表のIDを取得

スクリプトプロパティからシフト表が配置してあるフォルダを取得し、シフト表のIDを取得します。

//GASフォルダ(シフト表配置先のフォルダ)を取得
function getShiftFolder(){
  const folderID = PropertiesService.getScriptProperties().getProperty('ShiftFolder');
  const folder = DriveApp.getFolderById(folderID);
  return folder
}

//GASフォルダ内のシフト表のIDを取得
function getShiftSheet(){
  const folder = getShiftFolder();
  const query = 'title contains "シフト表"';  
  
  let searchFile = folder.searchFiles(query);
  let shiftSheetID = ""
  let shiftSheetName = ""
  let shiftSheet = ""

  //返り値はイテレータなのでwhileを回す。
  while(searchFile.hasNext()){
    const sheet = searchFile.next();
    shiftSheetID = sheet.getId();
    shiftSheetName = sheet.getName();
    if (shiftSheetName.match(/xlsx$/)){
      shiftSheet = xlsxToSpreadsheet(shiftSheetID)
    }else {
      shiftSheet = SpreadsheetApp.openById(shiftSheetID).getActiveSheet();
    }
  }
  return shiftSheet
}

 

PropertiesService.getScriptProperties().getProperty('ShiftFolder') でスクリプトプロパティにあるプロパティ名:ShiftFolder の値を取得しています。

const query = 'title contains "シフト表"' でシフト表を取得していますが、ファイル名で検索しているので、contains の後に続く部分は配置したシフト表の名前に合わせて編集すればOKです。

なお、このスクリプトは1フォルダに1つのシフト表のみが置いてあることを想定しているので、月が替わった際などは前月のシフト表を削除して最新のファイルのみが配置されている必要があります。

エクセルファイルの場合は if (shiftSheetName.match(/xlsx$/)) で拡張子部分の文字列でエクセルファイルを判定し、次に説明するスプレッドシート形式への変換用の関数を流しています。

 

エクセルファイルからスプレッドシートへの変換

エクセルファイルの場合、GASの処理を実行するためにスプレッドシート形式への変換が必要です。

変換のために、まずプロジェクト内の「サービス」の「＋」ボタンから「Drive API」サービスを追加しています。

※元からスプレッドシートの場合はこの手順は不要。

 

エクセルからスプレッドシートに変換する関数は以下です。

//.xlsx をスプレッドシートとして保存
function xlsxToSpreadsheet(shiftSheetID){
  let fileID = shiftSheetID;
  let xlsxFile = DriveApp.getFileById(fileID); //SpreadsheetAppではなくDriveApp
  let xlsxFileName = xlsxFile.getName();
  const folder = getShiftFolder();
  const folderID = folder.getId();

  let option =  {
    mimeType:MimeType.GOOGLE_SHEETS,  
    parents:[{id:folderID}],
    title:xlsxFileName,
  }
  let fileObj = Drive.Files.insert(option,xlsxFile);
  let shiftSheet = SpreadsheetApp.openById(fileObj.id).getActiveSheet();

  return shiftSheet
}

 

エクセルファイルは SpreadsheetApp.openById() ではなく DriveApp.getFileById(xlsxFileID) で読み込みます。

その後、option で mimeType などをあらかじめ定義し、Drive.Files.insert(option,xlsxFile) で変換を行っています。

 

シフトを取得

シフト表から自分のシフトを配列で取得します。自分の名前は「菊池」にしてみる。

function getShift(){
  const shiftSheet = getShiftSheet();
  const memberName = '菊池'; //自分の名前を定義
  
  //シフト表から自分の名前が存在する行を取得
  const memberNameRange = shiftSheet.getRange('B1:B'); //名前列の範囲
  const searchMember = memberNameRange.createTextFinder(memberName);
  let memberCell = searchMember.findAll();
  let memberRow = ""
  let memberColumn = ""

  for (let i=0; i<memberCell.length; i++){
    memberRow = memberCell[i].getRow();
    memberColumn = memberCell[i].getColumn();
  }

  const lastCol = shiftSheet.getLastColumn();

  //自分のシフトを配列で取得
  let shiftRange = shiftSheet.getRange(memberRow, memberColumn + 1, 1, lastCol -1);
  let shiftList = shiftRange.getValues().flat();

  return shiftList
}

以下はそれぞれ自分の名前と、名前が存在する列範囲に置き換えてください。

const memberName = '菊池';

const memberNameRange = shiftSheet.getRange('B1:B');

 

名前列の次の列～最終列までが日付の範囲なので、その範囲にある自分の名前行の各値=シフトを取得して一次元配列にしています。

let shiftRange = shiftSheet.getRange(memberRow, memberColumn + 1, 1, lastCol -1);
let shiftList = shiftRange.getValues().flat();

 

返り値の shiftList は次のようになります。「菊池」のシフトがとれています。

[勤, 勤, 勤, 勤, 勤, 休, 休, 勤, 勤, 勤, 勤, 勤, 休, 休, 勤, 勤, 勤, 勤, 勤, 休, 休, 勤, 勤, 勤, 勤, 勤, 休, 休, 勤, 勤, 勤, ]

 

もし日付列の後に「備考列」などがある場合は以下のような方法で「備考列」の位置を特定して、名前～日付の間にある値を取得します。

 //備考列の列番号を取得
 const remarksRange = shiftSheet.getRange('2:2'); //備考列がある行範囲
 const searchRemarks = remarksRange.createTextFinder('備考'); //「備考」列の位置取得
 let remarksCell = searchRemarks.findAll();
 let remarksColumn = ""

 for (let j=0; j<remarksCell.length; j++){
   remarksColumn = remarksCell[j].getColumn();
 }

//名前列～備考列の間にあるシフトを取得
let shiftRange = shiftSheet.getRange(memberRow, memberColumn + 1, 1, remarksColumn -1);

 

Googleカレンダーの予定を作成

スクリプトプロパティからカレンダーのIDを取得し、getShift()で呼び出したシフトからカレンダーの予定を作成します。

function shiftToCalender(){
  const shift = getShift()
  //スクリプトプロパティからカレンダーIDを取得
  const calenderID = PropertiesService.getScriptProperties().getProperty('MyCalenderID');
  const calender = CalendarApp.getCalendarById(calenderID);

  let i = 1;
  for (item of shift){
    //値が空の場合はスキップ
    if (item === ''){
      ;
    } else {
      //実行月の翌月に登録する
      const now = new Date()
      const date = new Date(now.getFullYear(), now.getMonth()+1, i);
      let event = calender.createAllDayEvent(item, date);
      event.setTag('key','shift');
      i = i + 1;
    }
  }
}

 

GAS の画面から shiftToCalender を実行すると実際にカレンダーにイベントが作成されます。

イベントのタイトルはセルの値をそのまま出しているので、「勤」か「休」が入ります。連想配列で値と作成名を紐づければもっといい感じになりそう。

また、このスクリプトは now.getMonth()+1 としているので、実行した月の翌月に予定が作成されます。(4月に実行した場合、5月の予定として作成されます。)

5月の予定は5月になってからじゃなくて4月中に入れるよなぁという考え。

getMonth()+1 を getMonth() にすれば当月分に作成されるように変更できます。

 

スクリプト実行後、以下の通り予定が作成されています。

 

重複判定等は実装していないので、スクリプトを複数回起動すると起動した分だけ同じ予定が作成されます。

予定作成時に event.setTag('key','shift'); で 'shift' というタグをつけているので、スクリプトによって作成したイベントを一括で削除したい場合は以下の clearCalender() を実行すればOK。

//指定日付内のカレンダーイベント全削除
function clearCalender(){
  const calenderID = PropertiesService.getScriptProperties().getProperty('MyCalenderID');
  const calender = CalendarApp.getCalendarById(calenderID);

  const now = new Date();
  const startDate = new Date(now.getFullYear(), now.getMonth(), 01)
  const endDate = new Date(now.getFullYear(), now.getMonth(), 32)

  const events = calender.getEvents(startDate,endDate);
  //Logger.log(events)
  
  for (let event of events){
    if(event.getTag('key') === 'shift'){
      event.deleteEvent();
    }
  }
}

 

スクリプト全体

getShift.gs

//GASフォルダ(シフト表配置先のフォルダ)を取得
function getShiftFolder(){
  const folderID = PropertiesService.getScriptProperties().getProperty('ShiftFolder');
  const folder = DriveApp.getFolderById(folderID);
  return folder
}

//GASフォルダ内のシフト表のIDを取得
function getShiftSheet(){
  const folder = getShiftFolder();
  const query = 'title contains "シフト表"';  
  
  let searchFile = folder.searchFiles(query);
  let shiftSheetID = ""
  let shiftSheetName = ""
  let shiftSheet = ""

  //返り値はイテレータなのでwhileを回す。
  while(searchFile.hasNext()){
    const sheet = searchFile.next();
    shiftSheetID = sheet.getId();
    shiftSheetName = sheet.getName();
    if (shiftSheetName.match(/xlsx$/)){
      shiftSheet = xlsxToSpreadsheet(shiftSheetID)
    }else {
      shiftSheet = SpreadsheetApp.openById(shiftSheetID).getActiveSheet();
    }
  }
  return shiftSheet
}

//.xlsx をスプレッドシートとして保存
function xlsxToSpreadsheet(shiftSheetID){
  let fileID = shiftSheetID;
  let xlsxFile = DriveApp.getFileById(fileID); //SpreadsheetAppではなくDriveApp
  let xlsxFileName = xlsxFile.getName();
  const folder = getShiftFolder();
  const folderID = folder.getId();

  let option =  {
    mimeType:MimeType.GOOGLE_SHEETS,  
    parents:[{id:folderID}],
    title:xlsxFileName,
  }
  let fileObj = Drive.Files.insert(option,xlsxFile);
  let shiftSheet = SpreadsheetApp.openById(fileObj.id).getActiveSheet();

  return shiftSheet
}

function getShift(){
  const shiftSheet = getShiftSheet();
  const memberName = '菊池';
  
  //抽出する名前が存在する行を取得
  const memberNameRange = shiftSheet.getRange('B1:B');
  const searchMember = memberNameRange.createTextFinder(memberName);
  let memberCell = searchMember.findAll();
  let memberRow = ""
  let memberColumn = ""

  for (let i=0; i<memberCell.length; i++){
    memberRow = memberCell[i].getRow();
    memberColumn = memberCell[i].getColumn();
  }

  const lastCol = shiftSheet.getLastColumn();

  //該当メンバーの名前列～備考列の間にあるシフトを取得
  let shiftRange = shiftSheet.getRange(memberRow, memberColumn + 1, 1, lastCol -1);
  let shiftList = shiftRange.getValues().flat();
  Logger.log(shiftList)
  return shiftList
}

 

shiftToCalender.gs

/**
  毎月GASフォルダ内のシフト表を置き換えてからスクリプト実行。(フォルダ内にシフト表は常に1つだけの想定)
 */


function shiftToCalender(){
  let shift = getShift()
  //登録カレンダー(自分)
  const calenderID = PropertiesService.getScriptProperties().getProperty('MyCalenderID');
  const calender = CalendarApp.getCalendarById(calenderID);

  let i = 1;
  for (item of shift){
    if (item === ''){
      ;
    } else {
      //実行時の翌月に登録する。
      const now = new Date()
      const date = new Date(now.getFullYear(), now.getMonth()+1, i);
      let event = calender.createAllDayEvent(item, date);
      event.setTag('key','shift');
      i = i + 1;
    }
  }
}

//指定日付内のカレンダーイベント全削除
function clearCalender(){
  const calenderID = PropertiesService.getScriptProperties().getProperty('MyCalenderID');
  const calender = CalendarApp.getCalendarById(calenderID);

  //月は0～11なので注意
  const now = new Date();
  const startDate = new Date(now.getFullYear(), now.getMonth()+1, 01)
  const endDate = new Date(now.getFullYear(), now.getMonth()+1, 32)

  const events = calender.getEvents(startDate,endDate);
  //Logger.log(events)
  
  for (let event of events){
    if(event.getTag('key') === 'shift'){
    //if(event.getTag('key') !== 'shift'){
      event.deleteEvent();
    }
  }
}

 

まとめ

自分用のスクリプトを汎用的にも使えるようにしましたが、結局シフト表のフォーマットとイベント作成時のタイトルとか諸々の部分が人によって異なるのでこのまま流用というわけにはいかなそうですね。形さえ整えば同じようなことはできるという感じです。

予定作成時に付与したタグはGoogleカレンダーの画面からは見当たらなかったが、GAS専用の内部的な機能なんだろうか。

そしてすっかり忘れてましたが 12月だけはこのスクリプトそのままじゃ機能しませんね…。。