【GAS】Google Apps Script 活用事例 年間のカジュアル面談の件数を応募者ごとに数えるスクリプト
22卒の募集が充足につき終了し、年間の面談対応数を応募者ごとに算出してほしいという依頼がありました。内定受諾に至るまでの面談回数や内定後フォローが発生しているかを調べたかったようです。
内心、素手で数えるのは、100人以上いるのでやりたくないなぁーと思って、GASを駆使して何とか集計を完了することが出来ました。時間は掛かってしまったものの、手作業での集計よりは早く出来たかなと思います。多分.....。
今回のスクリプトでは、図のようなシート上に記載があるIDをカレンダーで検索して、面談日程を取得しています。ハマったのは、カレンダーを検索する際、数値だと検索出来ないことでした。toStringか、getDisplayValues()で数値を文字列に変換する必要があります。
カレンダーの出席者を取得するスクリプト（サンプル版）
function guestNameList(){
//カレンダーの出席者は配列で取得できる
const guests = ['aaa@sample.co.jp', 'bbb@sample.co.jp', 'ccc@sample.co.jp'];
let array = [];
for(const guest of guests){
const accountId = convertEmailToName_(guest);
array.push([accountId]);
}
//配列を文字列化する
const guestsList = array.join();
console.log(typeof guestsList, guestsList);
return guestsList // string 太郎,花子,ポチ
}
function convertEmailToName_(email){
let name = '';
//メアド → 名前に変換させる
//変換させたい人数を地道に書いていく
switch(email){
case 'aaa@sample.co.jp': name = '太郎';
break;
case 'bbb@sample.co.jp': name = '花子';
break;
case 'ccc@sample.co.jp': name = 'ポチ';
break
default:
name = email;
}
return name
}
同時に、年間で行われたカジュアル面談のうち、人事のみの面談回数とエンジニアが参加した回数、分けてカウントして欲しいとの依頼もあり、それを考慮して上記のようなスクリプトを書きました。
人事のみ → Switch文内のそれぞれのcaseに該当し、漢字で書き出す
エンジニア → defaultに該当し、@を含むカレンダーIDで書き出す
シートに記載されているIDでカレンダーを検索する
/**
* setCalEvents が実施する関数
* tempGenerateTargetArray_(1, 1) これ追加で記載する
*/
function setCalEvents(){
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('面談回数カウント');
let newValues = [];
//シートからIDの列を1次元配列として取得する。
const idArray = tempGenerateTargetArray_(sheet, 2, 1); //@param sheet, row, column
//IDをひとつずつ取り出す
for(let i = 0; i < idArray.length; i++){
if(!idArray[i])continue
const array = getCasualInterviewDate_(idArray[i]);
//1次元配列を追加
newValues.push(array);
console.log(`No: ${i}, ID: ${idArray[i]}`);
console.log(newValues);
// テストで何人か試したい場合、コメントアウトを外す
// if (i > 3){
// console.log('3を超えたので繰り返し処理を抜けます')
// break;
// }
}//for
//シートに書き出す処理
sheet.getRange(2, 4, newValues.length, newValues[0].length).setValues(newValues);
//面談回数が多い順、つまり降順に並べ直す
const range = sheet.getRange('A2:I');
range.sort([
{column: 4, ascending: false}
]);
}
/**
* @param sheet シートオブジェクト
* @param row 取得開始行
* @param column 取得開始列
*/
function tempGenerateTargetArray_(sheet, row, column){
const array = sheet.getRange(row, column, sheet.getLastRow(), 1).getDisplayValues().flat().filter(value => value);
console.log(`空白削除後の1次元配列\n${array}`);
return array;
}
/**
* @param query シート上の応募者のID 文字列
*
*/
function getCasualInterviewDate_(query) {
const startDate = new Date('2020/08/01');
const endDate = new Date();
const cal = CalendarApp.getCalendarById('************');
const options = {search: query};
const events = cal.getEvents(startDate, endDate, options);
let count = 0;
let stringDate = '';
let newValues = [];
for(const event of events){
info = {
title: event.getTitle(),
day: dayOfTheWeek_(event.getStartTime().getDay()),
date: Utilities.formatDate(event.getStartTime(), 'JST', 'MM月dd日'),
start: Utilities.formatDate(event.getStartTime(), 'JST', 'HH:mm'),
end: Utilities.formatDate(event.getEndTime(), 'JST', 'HH:mm'),
name: guestNameList_(event.getGuestList()),
}
if(info.title.includes('面談')){
stringDate += `${info.date}（${info.day}）${info.title} 参加者: ${info.name}\n`;
count += 1;
}
//（例）
//2021/08/5 (木) Wantedly カジュアル面談 参加者: 太郎、花子、ポチ\n
//2021/08/6 (金) Wantedly カジュアル面談 参加者: 花子\n
}//for
//for文を抜けた後で、応募者ごとに、合計何回、面談を実施したかをカウントする
newValues.push(count, stringDate);
console.log(newValues);
return newValues
}//end
/**
* 曜日を返す
*
*/
function dayOfTheWeek_(day) {
let string;
switch(day){
case 0: string = '日';
break;
case 1: string = '月';
break;
case 2: string = '火';
break;
case 3: string = '水';
break;
case 4: string = '木';
break;
case 5: string = '金';
break;
case 6: string = '土';
break;
}
return string
}
/**
* カレンダーの出席者を文字列で返す
*
*/
function guestNameList_(guests){
let array = [];
for(const guest of guests){
const accountId = convertEmailToName_(guest.getEmail());
array.push([accountId]);
}
//配列を文字列化する
const guestsList = array.join();
console.log(typeof guestsList, guestsList);
return guestsList
}
/**
* Googleアカウントに紐付いているメールアドレスを名前で返す。
*
*/
function convertEmailToName_(email){
let name = '';
//メアド → 名前に変換させる
//変換させたい人数を地道に書いていく
switch(email){
case 'aaa@sample.co.jp': name = '太郎';
break;
case 'bbb@sample.co.jp': name = '花子';
break;
case 'ccc@sample.co.jp': name = 'ポチ';
break
default:
name = email;
}
return name
}1次面接前に実施した面談回数、最終面接前に実施した面談回数、最終面談後、内定者フォロー等で面談した回数に分けて数えるということを実務で行いました。
選考フローごとに面談を何回実施したかを数える
function myFunction1() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('面談回数カウント');
const values = sheet.getRange('A2:I').getValues();
let newValues = [];
for(let i = 0; i < values.length; i++){
if(!values[i][0])continue
//console.log(values[i]);
const pageId = values[i][0];
const name = values[i][1];
const date1 = values[i][5];
const date2 = values[i][6];
const countArray = generateCountArray_(pageId, name, date1, date2);//@param (検索キーワード, 名前, 1次面接日時, 最終面接日時)
newValues.push(countArray)
}//for
sheet.getRange(2, 10, newValues.length, newValues[0].length).setValues(newValues);
}
/**
* 面談日程が各選考日程より前にあるかどうかを判定し、配列に入れる
*
*
* @param query シート上の応募者のID 文字列
* @param name シート上の応募者名
* @param date1 1次面接日程 シート上に記載
* @param query 最終面接日程 シート上に記載
*
* @return [0, 1, 2, 1, 0] 各応募者の判定結果、1行のみを返り値として返す
*/
function generateCountArray_(query, name, date1, date2){
const startDate = new Date('2020/09/01');
const endDate = new Date();
//dateオブジェクトを文字列に変換する処理
const stringDate = (date, number, format) => {
date.setDate(date.getDate() + number);
return Utilities.formatDate(date, 'JST', format);
}
const cal = CalendarApp.getCalendarById(********************);
const convertString = String(query);
const options = {search: convertString};
const events = cal.getEvents(startDate, endDate, options);
console.log(convertString, events);
let first = 0; //1次面接前 人事と面談
let engineerIncluded1 = 0; //1次面接前 エンジニア同席
let final = 0; //最終面接前 人事と面談
let engineerIncluded2 = 0; //最終面接前 エンジニア同席
let follow = 0; //内定後フォロー
let newValues = [];
for(const event of events){
info = {
title: event.getTitle(),
date: event.getStartTime(),//面談日
name: guestNameList_(event.getGuestList()),
}
//console.log(`${name}, 面談日： ${info.date}`);
if(date1 === ''){
first = 0;
engineerIncluded1 = 0
}
if(info.title.includes('面談') && !info.name.includes('@') && date1 > info.date){
//1次面接 > 面談日
//2020/12/28 > 2020/11/24
//人事のみ ＠が含まれていない
first += 1;
console.log(`${first} 回目 ${info.title} 人事のみ：${info.name}`);
console.log(`1次面接： ${stringDate(date1, 0, 'yyyy/MM/dd')} > 面談日： ${stringDate(info.date, 0, 'yyyy/MM/dd')}`);
}//if
else if(info.title.includes('面談') && info.name.includes('@') && date1 > info.date){
engineerIncluded1 += 1;
console.log(`${engineerIncluded1} 回目 ${info.title} エンジニアを含む：${info.name}`);
console.log(`1次面接： ${stringDate(date1, 0, 'yyyy/MM/dd')} > 面談日： ${stringDate(info.date, 0, 'yyyy/MM/dd')}`);
}
else if(date2 === ''){
final = 0;
engineerIncluded2 = 0;
if(info.title.includes('面談') && date1 < info.date){
//1次不合格後のフォロー面談など
//最終面接日が空白かつ面談日が、1次面接以降
follow += 1;
console.log(`${follow} 回目 ${info.title}`);
}
}
else if(info.title.includes('面談') && !info.name.includes('@') && date2 > info.date){
final += 1;
console.log(`${final} 回目 ${info.title} 人事のみ：${info.name}`);
console.log(`最終面接： ${stringDate(date2, 0, 'yyyy/MM/dd')} > 面談日： ${stringDate(info.date, 0, 'yyyy/MM/dd')}`);
}
else if(info.title.includes('面談') && info.name.includes('@') && date2 > info.date){
engineerIncluded2 += 1;
console.log(`${engineerIncluded2} 回目 ${info.title} エンジニアを含む：${info.name}`);
console.log(`最終面接： ${stringDate(date2, 0, 'yyyy/MM/dd')} > 面談日： ${stringDate(info.date, 0, 'yyyy/MM/dd')}`);
}
else if(info.title.includes('面談') && date2 < info.date) {
follow += 1;
console.log(`${follow} 回目 ${info.title} 人事：${info.name}`);
console.log(`最終面接： ${stringDate(date2, 0, 'yyyy/MM/dd')} < 面談日： ${stringDate(info.date, 0, 'yyyy/MM/dd')}`);
}
}//for
newValues.push(first, engineerIncluded1, final, engineerIncluded2, follow, name);
console.log(`\n1次前 人事のみ： ${first} 回, 1次前 エンジニア含む：${engineerIncluded1} 回, 最終前 人事のみ： ${final} 回, 最終前 エンジニア含む：${engineerIncluded2} 回`);
console.log(newValues);
return newValues
}//end長くて分かりにくいのですが・・・シート上に記載がある1次面接や最終面接の日程より前か後かをカウントし、振り分ける作業を自動化しています。
エンジニアが同席しているかどうかの判定には、convertEmailToName_(email) の関数で、人事は名前で返し、エンジニアはそのまま@を含むアカウントに紐づくメールアドレスを返すようにしています。
綺麗に、これで一発でいけるかなと思ったのですが、実際には担当者がフリーフォーマットで登録した予定があったり、人事共有カレンダーに登録していない場合があったりして、こちらについては100件超目視で確認しました。めちゃくちゃやりたくない作業でした。
結論、フリーフォーマットはクソ。自動化出来んッ！！
2022/3/25追記
上記の記事を書いた事をきっかけに、こちらのスクリプトを見直したのですが、複雑でやってることがよう分からんってなりました。特にスマホで見ていると長過ぎると追えない。
（未来の自分も含めて）読者に分かりやすく伝える場合に画像は必須。あと関数を細分化して何の処理が行われているのかを直感的に分かりやすくすることが必要ですね。すみません、(・ω<) てへぺろッ
この記事が気に入ったらサポートをしてみませんか？