【GAS】Google Apps Script 活用事例　COUNTIFS関数で算出した数値が正しいかどうかを確かめるスクリプト

その数値は、果たして正しいのか？

今回、ご紹介するのは、COUNTIFS関数で算出した数値が正しいのかどうかを確かめるスクリプトです。早い話、計算結果を電卓で計算し直すような話です。

例えば、自社の求人ページ経由の応募で、その応募者が、1次選考不合格だった場合、

=COUNTIFS(採用数値管理表!C:C,"自社",採用数値管理表
!E:E,"1次選考不合格")

おそらく、こんな感じになるかと思います。こういった数値が正しいかどうかを確かめるためには、手動で行う場合は、フィルターを使って、媒体が自社で、選考ステータスが1次選考不合格...... 次は他社媒体だった場合.....その次は採用イベントだった場合.....こういう作業を延々と繰り返していました。

........めちゃくちゃ時間が掛かる。　＿｜￣｜●

自動化のイメージ

上記のサンプルシートでは、目視で確認出来るのですが、人数が多くなってくると、ね？
大変なのですよ….。

スクリプト全文はこちら。

function confirmNumbers() {
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('選考状況管理');
 const values      = sheet.getDataRange().getValues();

 //見出し行を検索して、対象
 const column     = getSheetHeaderIndex_(values, 0); 
 const typeList   = ['インターン', '1DAY選考会', '本採用'];
 const sourceList = ['リファラル', '自社', 'リクナビ', 'イベント'];

 let count  = 0;  //対象者の人数を確認するための変数
 let name   = ''; //対象者の名前を確認するための変数
 let string = '';

 for(let i = 0; i < typeList.length; i++){
   for(let j = 0; j < sourceList.length; j++){
     for(let k = 1; k < values.length; k++){
       if(!values[k][column.id])continue;

       if( values[k][column.type].includes(typeList[i]) &&
           values[k][column.source].includes(sourceList[j]) && 
           values[k][column.status].includes('1次選考辞退')){

           const person = values[k][column.name];
           count += 1;
           name  += '<p>' + typeList[i] + '　' + person + '</p>';

           console.log(person);
       }//if
     }//for_k

    string += '<p>' + typeList[i] + ' ' + sourceList[j] + '　' + count + '人</p>';
    console.log('対象者の人数',count);

    //kを抜けたら、countを初期化
    count = 0;

   }//for_j
 }//for_i

 console.log(string);

 //確認作業のために名前も表示させる。
 string += '-----------------------------------------<br>' + name;
 const title = '1次選考辞退';
 showHtmlDialog_(title, string, 280, 650);
 
}//end


/**
 * 見出し行の位置を配列番号で取得する
 * 
 * @param  {Array.Array.<string>} values - 2次元配列
 * @param  {number} headerRowIndex - 0,1など数字
 * 
 */
function getSheetHeaderIndex_(values, headerRowIndex){
 const headerRow = values[headerRowIndex];
 const column    = {
   id:     headerRow.indexOf('採用　ID'),
   name:   headerRow.indexOf('名前'),
   source: headerRow.indexOf('応募媒体'),
   type:   headerRow.indexOf('選考タイプ'),
   status: headerRow.indexOf('選考ステータス')
 };

 console.log(column);
 return column
}


/**
 * HTMLで集計結果などを表示する
 * 
 * @param  {title} title - 表題
 * @param  {string} string - HTML pタグなどで囲った文字列
 * 
 */
function showHtmlDialog_(title, string, width, height){
 const ui = SpreadsheetApp.getUi();
 const html = HtmlService.createHtmlOutput(string)
 .setWidth(width)
 .setHeight(height);

 ui.showModelessDialog(html, title);
}


/**
 * スプレッドシートにカスタムメニューを表示させる
 */
function onOpen() {
 SpreadsheetApp.getUi()
 .createMenu('追加メニュー')
 .addItem('COUNTIFの内訳を調べる', 'confirmNumbers')
 .addItem('サブメニュー', 'myFunction2')
 .addToUi();
}

今のスクリプトだと、1次選考しか調べられないので、これを表示画面をクリックして、その値を受け取り、臨機応変に対応できるスクリプトにしたいなぁと考えています。

ざっくり人数だけを把握できればいいケース

2023年2月17日追記

/**
 * 各職種の人数をざっくりと把握する
 * 
 */
function myFunction(){
  const sheet  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート5');
  const values = sheet.getDataRange().getValues();

  //1列目の1次元配列を作成する
  const array  = values.map(row => row[0]);
  const unique = Array.from(new Set(array));

  console.log(array);
  console.log(unique);

  let string = '';

  unique.map(jobName => string += countIf_(array, jobName));
  console.log(string);

  showHtmlDialog_('職種別の人数', string);
}



/**
 * 1次元配列の中に該当する職種がいくつ存在するかを数える
 * 
 * @param  {Array.<string>} array - 1次元配列
 * @param  {string} jobName - 職種名
 * @return {string} （例）<p>iOS: 2名</p>
 * 
 */
function countIf_(array, jobName){
  let count = 0;
  array.map(route => {if(route === jobName){
    count +=1;
  }});

  return `<p>${jobName}: ${count}名</p>`;
}



/**
 * HTMLで集計結果などを表示する
 * 
 * @param  {title} title - 表題
 * @param  {string} string - HTML pタグなどで囲った文字列
 * 
 */
function showHtmlDialog_(title, string){
 const ui = SpreadsheetApp.getUi();
 const html = HtmlService.createHtmlOutput(string)
 .setWidth(300)
 .setHeight(600);

 ui.showModelessDialog(html, title);
}

Switch文を使って、COUNTIF的な挙動をする関数を書く方法

UI操作するためには、HTMLを頑張る他ない。

GASでクライアントJavaScriptを使用する超簡単なプログラム

こんなスクリプトも書いています。

自動化するまで集計元のシートを手動で更新していました。更新し忘れる事で、数値が正しくないという事が頻繁に起こっていました。そこで、できる限り、ステータス更新を自動化することにしました。その取り組みをまとめました。

複雑な数式をどこから持ってきているのかを、見出し行を参照して表示してくれるスクリプトです。数式が大きく表示されるため、目にも優しい業務改善になりました。

採用イベントの参加者が、過去、いつのイベントに参加しているのかを表示してくれるスクリプトです。