シートのサンプル　転記対象のシート

こんにちは。どうも、ツバサ（nepia_infinity）です。
今回は、C列に、面接予定日、D列、E列に当日担当する面接官の自動転記を実現するスクリプトです。

HRMOS IDは、本来数字の羅列で、文字列ではないので、A列の表示形式を書式なしテキストにするか、getValues()の代わりに、getDisplayValues()を使ってみてください。

シートのサンプル　氏名とカレンダーIDの対応表

カレンダーIDを、名前に変換する際に使用する対応表です。後述しますが、CalendarAppでは、イベント出席者の名前を上手く取得する事が出来ません。

5等分の花嫁を見ながら、スクリプトを書いていたので、苗字が全員、中野になっています。特に深い意味はありません。

カレンダー登録のサンプル

HRMOS経由で登録したスケジュールには、19桁の数字が応募者ごとに割り当てられます。そのユニークなIDで応募者の面接日程を特定します。職場の環境を使えないため、今回は、適当なサンプルを用意しました。

今回のスクリプトでは、HRMOS IDで検索していますが、ConfluenceのURLとかでも、カレンダーのタイトルや、詳細欄などに含まれる文字列であれば、ほぼ何でも検索可能です。

面接官の名前の取得が、CalendarAppで取得出来ない件

何度かやってみたのですが、CalendarAppだと、面接官の名前を取得出来ません。そのため、一度変換用のシートをかませて、カレンダーIDと名前を照合します。この方法だと、残念ながら、個人情報の取り扱いとかに絡んできてしまいます.....。

getName()　これでしょ！？と思って試してみると上手くいかず....

サンプルコード　：　getName()

function myFunction() {
 const cal  = CalendarApp.getDefaultCalendar();
 const name = cal.getName();

 console.log('カレンダーのオーナーの名前：', name); 

 const date   = new Date();
 const events = cal.getEventsForDay(date);

 console.log('本日の予定：', events.length, '　件');

 for(const event of events){
   const guests = event.getGuestList();
   console.log(event.getTitle(), 'イベントの出席者　', guests.length, '　人');
   
   for(const guest of guests){
     console.log('出席者の名前：', guest.getName());
   }
  }
}

サンプルコードの実行ログ

おそらく、カレンダーに表示されている名前は、ContactsAppに紐づいているものだと思われます。

本題　スクリプトの構成

さて、ここから本題です。スクリプトの構成は以下の通りです。

getHrmosIdFromSheet
       |---getCalEvents_
                       |--- getAttendeesName_
                                      |--- convertEmailToName_
                        |--- setNameToSheet_
                                         |--- generateArray_     

【役割】
getHrmosIdFromSheet　for文で、シート上のHRMOS IDを取得
getCalEvents_　　　　  取得したHRMOS IDでカレンダーを検索
getAttendeesName_　　イベントから出席者の配列を作成
convertEmailToName_      カレンダーIDと名前を変換
setNameToSheet_               シートに配列を転記

実際のスクリプト

function getHrmosIdFromSheet(){
 const spreadsheet  = SpreadsheetApp.getActiveSpreadsheet();
 const sheet        = spreadsheet.getSheetByName('面接日程');
 const values       = sheet.getDataRange().getValues();

 //見出し行から列を特定する。
 const headerRow    = values[0];
 const targetColumn = headerRow.indexOf('HRMOS ID');
 const nameColumn   = headerRow.indexOf('氏名');

 for(let i = 0; i < values.length; i++){
   const hrmosId = values[i][targetColumn];
   const name    = values[i][nameColumn];

   if(!hrmosId)continue

   console.log('HRMOS ID', hrmosId, '氏名', name);

   //カレンダーのイベントを取得して、面接日程を転記
   getCalEvents_(hrmosId);

 }//for
}//end



/*
 * カレンダーをHRMOS IDで検索　応募者を特定する。
 * 
 * @param  {string} HRMOS　ID
 * @return {object [][]} 2次元配列
 *
 */
 
function getCalEvents_(hrmosId){
 const calendar  = CalendarApp.getDefaultCalendar();
 const startTime = new Date();
 const endTime   = new Date();
 endTime.setMonth(endTime.getMonth() + 1);

 const events = calendar.getEvents(startTime, endTime, {search: hrmosId});

 console.log('検索対象の期間　1ヶ月', startTime, endTime);
 console.log(events);

 for (const event of events) {
   if(event.getTitle().includes('面接') === true){

     //イベントの出席者を2次元配列で取得し、転記する。 [['2021/01/29', '氏名', '氏名']]
     getAttendeesName_(hrmosId, event);
     return
　　　
   }//if
 }//for
}//end



/*
 * 書き込む内容を生成する。日付、面接官の名前
 * 
 * @param  {string} HRMOS　ID
 * @param  {object} event カレンダーから取得したイベント
 * @return {object [][]} 2次元配列
 *
 */
 
function getAttendeesName_(hrmosId, event) {
 
 const date   = Utilities.formatDate(event.getStartTime(), 'JST', 'yyyy/MM/dd');
 const title  = event.getTitle();
 const guests = event.getGuestList();

 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('アカウント対応表');

 let guestsArray   = [];

 //Googleアカウントに紐づいているメールアドレスを取り出すための記述
 for(const guest of guests){

   //メールアドレス（カレンダーID）を氏名に変換するスクリプト
   const calId = guest.getEmail();
   const name  = convertEmailToName_(sheet, calId);
   guestsArray.push(name);

 }
 //配列の最初に、日付を追加する。
 //追加後：['2021/1/28', '面接官', '面接官']
 guestsArray.unshift(date);

 console.log(title, date);
 console.log('guestsArray', guestsArray);

 //書き出し先のシートに転記する。
 setNameToSheet_(hrmosId, guestsArray);

}//end



/*
 * HRMOS IDが、シートのどこにあるか検索し、配列を転記する。
 * 
 * @param  {string} HRMOS　ID
 * @param  {object} ['2021/1/28', '面接官', '面接官']
 *
 */

function setNameToSheet_(hrmosId, guestsArray){
 const spreadsheet  = SpreadsheetApp.getActiveSpreadsheet(); 
 const targetSheet  = spreadsheet.getSheetByName('面接日程');
 const values       = targetSheet.getDataRange().getValues();

 //見出し行から、列を特定する。
 const headerRow          = values[0];
 const hrmosIdColumnIndex = headerRow.indexOf('HRMOS ID'); 
 const targetColumn       = headerRow.indexOf('面接日程') + 1; 

 //書き出す行を判定する。
 const hrmosIdsArray = generateArray_(values, hrmosIdColumnIndex);
 const targetRow     = hrmosIdsArray.indexOf(hrmosId) + 1;

 console.log('hrmosIdsArray',hrmosIdsArray);
 console.log('書き込み対象の行', targetRow);

 const range = targetSheet.getRange(targetRow, targetColumn, 1, guestsArray.length);
 console.log('書き出し範囲', range.getA1Notation());
　
 //空白だったら、転記する
 if(range.isBlank() === true){
   range.setValues([guestsArray]);
   console.log('転記完了')
 }
}


/*
 * カレンダーIDを名前に変換する。
 * 
 * @param  {object} sheet
 * @param  {string} 面接官のカレンダーID　（メールアドレス）
 * @return  {string} 面接官の名前
 */
 
function convertEmailToName_(sheet, calId){

 const values    = sheet.getDataRange().getValues();

 //見出し行から列を特定する。
 const headerRow  = values[0];
 const idColumn   = headerRow.indexOf('calender ID');
 const nameColumn = headerRow.indexOf('氏名');

 for(let i = 0; i < values.length; i++){
   //カレンダーIDが、空白であればスキップする。
   if(!values[i][idColumn])continue

   //引数で受け取ったカレンダーIDと一致したら氏名を返す。
   if(values[i][idColumn] === calId){
     return values[i][nameColumn];
   }
 }//for
}//end



function generateArray_(values, column){
　return values.map(record => record[column]).filter(value => value);
}

実行ログ

unshift()

pushメソッドの逆と覚えるといいと思います。

push()       →　配列の最後尾に要素を足していく
unshift()   →     配列の先頭に要素を足していく

isBlank()

if(range.isBlank() === true){
   range.setValues([guestsArray]);
   console.log('転記完了')
 }

もし、空白だったら、〇〇するみたいな感じでよく使います。
1次面接の日程記入欄が空白だったら、C列
最終面接の日程記入欄が空白だったら、H列

とか条件分岐を駆使する事で、応用範囲が広がります！！

Googleカレンダーの検索オプション

function myFunction() {
 // Determines how many events are happening in the next two hours that contain the term
 // "meeting".
 const now = new Date();
 const twoHoursFromNow = new Date(now.getTime() + (2 * 60 * 60 * 1000));
 const events = CalendarApp.getDefaultCalendar().getEvents(now, twoHoursFromNow,
     {search: 'meeting'});

 console.log('Number of events: ' + events.length);
}

上記のリンクのサンプルは、今から2時間以内に、会議が何件あるかを調べるスクリプトのようです。Slackのスラッシュコマンドとかで出来たら便利かもしれません。

編集後記

2020年は、転職をし、新たな職場で新たな自動化ニーズを見つけ、1週間に2本を書くことを目安に頑張ってきたのですが、その自動化も落ち着いて、ネタが無くなってきてしまいました。1年近くで、GASのネタも100本近く溜まりました。

時間は有限なので、未だに割と読まれている需要の高いものから、徐々にリライトを進めています。

こんな自動化もあります。