見出し画像

【GAS】Google Apps Script 活用事例　登録済のスケジュールにスプレッドシートの情報を追記するスクリプト

nepia_infinity

1日で内定が出る、1DAY選考会なるものがあります。厄介なのが、当日になるまで最終選考に誰が進むか、準備段階では分からないのです。そこで、仮登録したスケジュールに、後からスプレッドシートの情報を流し込みたいというニーズが生じて自動化するスクリプトを書きました。

処理の流れ

1.　spreadsheetから、for文で登録済スケジュールのイベントIDを取得
2.　取得したイベントIDで、カレンダーの詳細欄を取得。
3.　取得したイベント詳細をスプレッドシートから取得した値で置換
4.　置換済の新しいイベント詳細を貼り付け

置換対象のカレンダー詳細欄

スクリーンショット_2021-02-20_8_38_02

テンプレートに沿って、面接官に必要な情報が網羅されています。手作業でやると、1人のスケジュールを登録するために、何回もコピペしなければならず大変です。この仮登録されたスケジュールに、スプレッドシートの情報を流し込む事で登録作業の効率化を図ります。

HRMOSやコンフル は、個人では使用していないため、スクリーンショットは割愛します。

カレンダー詳細欄をスプレッドシートの情報で置換するスクリプト

function editCalDetail(){

 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('カレンダー登録用シート');
 const values      = sheet.getDataRange().getDisplayValues();


 //見出し行から列を特定する。　これは元シートの列の挿入対策
 const headerRow   = values[0];
 const column      = {
   id:         headerRow.indexOf('HRMOS ID'),
   type:       headerRow.indexOf('選考タイプ'),
   hrmos:      headerRow.indexOf('HRMOS URL'),
   confluence: headerRow.indexOf('コンフル'),
   zoomUrl:    headerRow.indexOf('Zoom URL'),
   passCode:   headerRow.indexOf('パスコード'),
   meetingId:  headerRow.indexOf('ミーティングID'),
   job:        headerRow.indexOf('希望職種'),
 };
 
 for(let i = 0; i < values.length; i++){
   const info = {
     eventId:    values[i][column.id],
     title  :    values[i][column.type],
     hrmos  :    values[i][column.hrmos],
     confluence: values[i][column.confluence],
     zoomUrl:    values[i][column.zoomUrl],
     passCode:   values[i][column.passCode],
     meetingId:  values[i][column.meetingId],
     job:        values[i][column.job],
   }

   //eventIdがない場合は処理をスキップ
   if(!info.eventId) continue;

   //最終選考の場合のみ処理を続行
   if(info.title.includes('最終選考') === true){
     console.log(info);

     //最終選考のカレンダー詳細欄を書き換え
     replaceCalDescription_(info);

   }//if
 }//for
}//end




function replaceCalDescription_(info) {
 const cal   = CalendarApp.getCalendarById('**************');
 const event = cal.getEventById(info.eventId);
 const oldDescription = event.getDescription();

 // [置き換えたい文字列(正規表現), 置き換える値]
 let replaceLists = [
   [/【 希望職種 】/, '【 希望職種 】　' + info.job],
   [/【 ルームID 】/, '【 ミーティング ID 】　' + info.meetingId],
   [/【 パスコード 】/, '【 パスコード 】　' + info.passCode],
   [/【 Zoom URL 】/, '【 Zoom URL 】\n' + info.zoomUrl],
   [/【 コンフルURL 】/, '【 コンフルURL 】\n' + info.confluence],
   [/【 HRMOS URL 】/, '【 HRMOS URL 】\n' + info.hrmos]
 ];

 // replaceListsを使って置換
 const reducer        = (accumulator, list) => accumulator.replace(...list);
 const formatedString = replaceLists.reduce(reducer, oldDescription);
 console.log(formatedString);

 event.setDescription(formatedString);
 return
}

reduce()

reducer 関数は 4 つの引数を取ります。

1. 　アキュムレーター (acc)
2. 　現在値 (cur)
3. 　現在の添字 (idx)
4. 　元の配列 (src)

reducer 関数の返値はアキュムレーターに代入され、配列内の各反復に対してこの値を記憶します。最終的に単一の結果値になります。

今回、下記のブログで紹介されていたスクリプトをほぼ丸パクリさせてもらいました。

このreducerの仮引数で出てくる登場人物2つだけを基本として抑えておくようにします。

acc（accumulator、蓄積者とか累算器みたいな意味）：
これまでの処理の返り値を蓄積しているもの

cur（currentValue、現在値と言う意味）：
今処理している配列の値

配列の要素を順番にcurとして取り出しながら、これまでの処理結果accに処理を加えて結果を次に渡していく、という感じかなと思います。
なんとなく、伝言ゲームのように順番に次に伝えていく感じをイメージしました。

伝言ゲームというフレーズで、ピンときました。

スプレッド構文

下記の部分が分からなかったのですが、エトー先生から教えてもらいました。おそらく、2次元配列の変数名が、replaceLists　となっていたので、listは、1次元配列を指しているのだろうという推測は付いていて、挙動も理解したものの、腑に落ちない感覚、なぜそうなるかよく分からない感覚が残っていました。

エトー先生、ありがとうございます！！

const reducer        = (accumulator, list) => accumulator.replace(...list);
const formatedString = replaceLists.reduce(reducer, oldDescription);

引数の中に、値ではなく配列を入れる事が出来る。

カレンダーから、最終選考のイベントIDを取得して、スプレッドシートに転記するスクリプト

//空白行をすべて省いた上で、1次元配列を生成する。
function generateArray_(values, column){
 return values.map(record => record[column]).filter(value => value);
}



//最終選考のZoom等のリンク転記を簡略するためにイベントIDを取得する。
function getEventIds_(stringDate) {
 const cal    = CalendarApp.getCalendarById('*******************');
 const date   = new Date(stringDate)
 const events = cal.getEventsForDay(date);

 console.log('取得カレンダーの名前：　', cal.getName());

 let newValues = [];

 for(const event of events){
   const info = {
     title: event.getTitle(),
     id:    event.getId(),
     start: event.getStartTime()
   }
   if(info.title.includes('最終選考') === true){
     console.log('イベントID',info.id, '\nタイトル', info.title);
     newValues.push([info.id]);
   }
 }
 console.log('配列の要素数',newValues.length);
 console.log(newValues);

 return newValues
}



//イベントIDの転記を始める行と列を取得する。
function setEventIdsToSheet(){
 const spreadsheet  = SpreadsheetApp.getActiveSpreadsheet();
 const sheet        = spreadsheet.getSheetByName('カレンダー登録用シート');
 const values       = sheet.getDataRange().getDisplayValues();
 const headerRow    = values[0];

 
 //最終選考のデータがどこにあるかを調べる
 const column = headerRow.indexOf('タイプ');
 const array  = generateArray_(values, column);
 const row    = array.indexOf('最終選考 1') + 1;

 console.log(array);
 console.log('最終選考の記載がある行',row);


 //2行目に日付データがあるシート構成
 const dateColumn = headerRow.indexOf('日付');
 const stringDate = values[1][dateColumn];

 console.log('1DAY選考の開催日(文字列)：', stringDate);


 //最終選考のイベントIDの2次元配列
 const ids = getEventIds_(stringDate);
 
 //1列目に最終選考のイベントIDを書き出す
 sheet.getRange(row, 1, ids.length, ids[0].length)
 .setValues(ids)
 .activate();

}

おまけ：　シートからカレンダーへ登録するスクリプト

function registerCal() {
 const calendar    = CalendarApp.getDefaultCalendar();
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('スケジュール登録シート');
 const values      = sheet.getDataRange().getValues();


 //見出し行から列を特定する。シートに列の挿入などがあっても対応するための記述
 const headerRow = values[0];
 const column    = {
   id:     headerRow.indexOf('ID'),
   title:  headerRow.indexOf('イベント名'),
   date:   headerRow.indexOf('開始日'),
   start:  headerRow.indexOf('開始時刻'),
   end:    headerRow.indexOf('終了時刻'),
   status: headerRow.indexOf('登録ステータス'),
 }   


 //見出し行を除外する。
 for(let i = 1; i < values.length; i++){
   //ステータスが登録済だった場合はスキップ
   if(values[i][column.status] === '登録済'){continue}

   //開始時刻
   const startTime = new Date(values[i][column.date]);
   startTime.setHours(values[i][column.start].getHours());
   startTime.setMinutes(values[i][column.start].getMinutes());
    
   //終了時刻
   const endTime = new Date(values[i][column.date]);
   endTime.setHours(values[i][column.end].getHours());
   endTime.setMinutes(values[i][column.end].getMinutes());

   const event   = calendar.createEvent(values[i][column.title], startTime, endTime);

   //イベントIDを書き込む getRange()用に +1をしている。
   const row          = i + 1;
   const idColumn     = column.id + 1;
   const statusColumn = column.status + 1;

   //イベントIDを書き込む
   sheet.getRange(row, idColumn).setValue(event.getId());

   //登録ステータスを登録済にする。
   sheet.getRange(row, statusColumn).setValue('登録済');
 }
}

おまけ：　スクリプトの実行後

シート構成は、メインで紹介したスクリプトのシートとは異なります。あくまで登録さえ出来ればOKな人向きです。

スクリーンショット 2021-02-19 20.36.51

スクリーンショット 2021-02-19 20.39.44

カレンダーの詳細欄に細々と入力する必要がある場合は、過去エントリーのいずれかが参考になるかもしれません。

過去エントリーはこちら

シートからカレンダー登録をするスクリプトの複雑版です。色々、カレンダー詳細欄にメモしたい人向けです。Map Serviceを併用する事により、自宅から会社所在地までの所要時間の把握などが出来るようになっています。

上記の記事では、Googleドキュメントで、入力画面を表示させて、そこにHRMOSのURLを入力するだけで、カレンダーの詳細欄に、シートに記載されているコンフルのURL やら志望職種を自動で転記してくれるスクリプトです。今でも愛用しています。

上記の記事では、シートに記載されたZoomの情報を、Gmailのメール本文に流し込むスクリプトを紹介しています。コレのおかげで1DAY選考は、だいぶ楽になりました。

この記事が参加している募集

#最近の学び

185,855件

この記事が気に入ったらサポートをしてみませんか？