【GAS】Google Apps Script 活用事例　重複判定を上手く利用して、採用イベントに参加した学生と、いつのイベントで接触したかを調べて書き出すスクリプト

採用イベントに出席予定の学生が、過去のイベントで接触しているかを調べ、その合否結果を調べて書き出すという業務の自動化の備忘録です。

今回のサンプルシートでは、C列のステータス欄と、D列の重複判定に、合否結果が自動入力されます。

過去に実施した場合　→　今回は面接を実施しないという意味でclose
過去に実施していない場合　→　今回、面接実施予定という意味でactive

例えば、12/08ミドルクラスでは、　桑野さん、さおりさん、瑛二さん　が該当します。過去に応募が合った応募者は、青で色付けされています。
ちなみに、重複した値に色を付けるのは、条件付き書式のカスタム数式を使うと出来ます。

=COUNTIF(B:B,B:B)>1

シート公開しています。

今回、サンプルとして作ったシートを公開します。実務で使っているシートを模して簡易的に、アレンジしたものになります。応募者名は、僕の好きなドラマの「結婚できない男」に出てくるキャラの名前を拝借しています。採用イベントも架空のものです。

【ブログ公開用】重複判定

main.gs

実行する関数、今回は、処理を一つの関数の中に書いてしまうと、ゴチャついてしまうため、関数を切り分けました。

function findSameRecords() {
 const spreadsheet      = SpreadsheetApp.getActiveSpreadsheet();
 const sheet            = spreadsheet.getSheetByName('DB');
 const values           = sheet.getDataRange().getValues();
 
 //見出し行の検索で、列を特定する。
 const eventColumnIndex = getColumnIndex_(values, 'イベント名'); //@return {number}　見出し行の配列番号
 const nameColumnIndex  = getColumnIndex_(values, '応募者名');
 
 //イベント名の1次元配列と、最新のイベント名の取得
 const eventNameArray = generateArray_(values, eventColumnIndex);
 const latestEvent    = getLatestEvent_(eventNameArray); //@return {string}　12/08ミドルクラス
 
  /*
  * シート上の過去開催分も含めた参加者   allParticipantNameArray
  * 最新イベントの出席者のみの1次元配列 書き込みの対象者の選定   latestParticipantNameArray
  * シート上の過去開催分も含めた参加者の重複 repetitiveParticipant
  * 最新のイベント参加者かつ、重複している人　targetParticipants_
  */
  
 const allParticipants        = generateArray_(values, nameColumnIndex); //@return {object []}
 const latestParticipants     = latestEventParticipants_(values, latestEvent, nameColumnIndex); //@return {object []}
 const repetitiveParticipants = repetitiveValue_(allParticipants); //@return {object []}
 const targetParticipants     = targetParticipants_(latestParticipants, repetitiveParticipants); //@return {object []}
 
 
 //対象者の過去の合否結果を取得する。
 const results = getResults_(sheet, allParticipants, targetParticipants);
 
 //getResultsで取得した結果を書き込む
 setResults_(sheet, allParticipants, results);
 
 return
 
}//end

common.gs

整理するために別の関数に分離

/*
* シートの見出し行から配列の列数を取得する
* 
* @param  {Object [][]} 二次元配列
* @param  {string} 見出し行の名前
* @return {number} 配列の列数
*
*/
function getColumnIndex_(values, query){
 const headerRow   = values[0];
 const columnIndex = headerRow.indexOf(query);
 return columnIndex
}


/*
* 配列を逆にして、最新のイベント名を取得する。
* 
* @param  {array} １次元配列
* @return {string} イベント名
*
*/

function getLatestEvent_(array){
 const reverseArray = array.reverse();
 const latestEvent  = reverseArray[0];
 
 console.log('順序を逆にした配列', reverseArray);
 console.log('最新のイベント名', latestEvent);
 
 return latestEvent
}


/*
* 二次元配列から、指定の列のデータを抽出し、一次元配列を生成する
*
* @param {Object[][]} 二次元配列
* @param {number} 配列の列数（0以上のインデックス）
* @return {Object[]} 特定列の、一次元配列
*
*/

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

/*
* 2次元配列から、最新のイベント名のみを抽出し、1次元配列を作成する。
*
* @param {Object[][]} 2次元配列
* @param {string} 最新のイベント名
* @param {number} 配列の列数（0以上のインデックス）
* @return {Object[]} 1次元配列
*
*/

function latestEventParticipants_(values, query, column) {
 
 //2次元配列の中から、最新のイベント名を含む1次元配列のみを残す
 const result = values.filter(array => { if(array.indexOf(query) !== -1){
   return array;
 }});

//console.log('最新のイベント名が含まれる2次元配列を生成',result);

const nameArray = generateArray_(result, column);
const length    = nameArray.length + '人';

console.log('最新のイベントの参加者', nameArray, length);

return nameArray
}

/*
* 1次元配列から重複している値のみを抽出する。
*
* @param {Object[]} 1次元配列
* @return {Object[]} 重複のみ抽出した、1次元配列
*
*/

function repetitiveValue_(array) {

 //重複している人を抽出する。
 const repetitiveArray = array.filter((value, index) => {return array.indexOf(value) !== index});
 
 //採用イベントに2回以上、参加している人を省く
 const newArray = Array.from(new Set(repetitiveArray));
 const length   = newArray.length + '人';

 console.log('シート全体の重複者', newArray, length);

 return newArray
}


/*
* 最新のイベント参加者かつ、重複している、2つの条件を同時に満たす人を抽出する
*
* @param {Object[]} 最新のイベント参加者　1次元配列
* @param {Object[]} シート全体の重複者　1次元配列
* @return {Object[]} 2つの条件を同時に満たす人の、1次元配列
*
*/

function targetParticipants_(latestParticipants, repetitive) {
 let newArray = [];
 
 for(let i = 0; i < repetitive.length; i++){
   const person = repetitive[i];
   
   if(latestParticipants.indexOf(person) !== -1){
     newArray.push(person);
     
   }//if
 }//for
 
 const length = newArray.length + '人';
 console.log('最新のイベント参加者かつ、重複している人', newArray, length);
 
 return newArray
}

/*
* 過去の採用イベントで接触した人の合否を取得
*
* @param {Object[]} シートオブジェクト　1次元配列
* @param {Object[]} シートの名簿のみの配列　1次元配列
* @param {Object[]} 重複・最新イベントの参加者　2つの条件を同時に満たす人の、1次元配列
* @return {Object[][]} 対象者の合否結果、参加したイベント名
*
*/

function getResults_(sheet, originalNameArray, targetParticipants) {
 
 //見出し行の検索
 const values          = sheet.getDataRange().getValues();
 const eventNameColumn = getColumnIndex_(values, 'イベント名') + 1;
 const resultColumn    = getColumnIndex_(values, '合否結果') + 1;
 
 let newArray = [];
 let status   = '';
 let result   = '';
 
 for( let i = 0; i < targetParticipants.length; i++){
   const person = targetParticipants[i];
   
   if(originalNameArray.indexOf(person) !== -1){
     const row         = originalNameArray.indexOf(person) + 1;
     const targetRange = sheet.getRange(row, resultColumn);
     
     //合否欄が空白の場合は、面接を実施していない。
     if(targetRange.isBlank() === true){
       status = 'active';
       result = '　接触なし'
     }
     //合否欄が空白以外の場合は、過去に面接を実施済み
     else if(targetRange.isBlank() === false){
       status = 'close';
       result = '　' + targetRange.getValue();
     }
     
     const eventName   = sheet.getRange(row, eventNameColumn).getValue();
     newArray.push([person, status, eventName, result]);
     
   }//if
 }//for
 
 console.log('過去イベントの合否結果', newArray);
 return newArray
 
}//end


/*
* 前述の関数、getResults_　で取得した結果を任意の場所に書き込む
* 列の挿入などが考えられるため、getValue()で対応する。
*
* @param {Object[]} シートオブジェクト　1次元配列
* @param {Object[]} 応募者の名前の配列　1次元配列
* @param {Object[][]} getResults_で、取得した　2次元配列
* @return なし、シートに値が書き込まれる。
*
*/

function setResults_(sheet, originalNameArray, results) {
 
 //見出し行の検索
 const values       = sheet.getDataRange().getValues();
 const nameColumn   = getColumnIndex_(values, '応募者名') + 1;
 const targetColumn = getColumnIndex_(values, '重複判定') + 1;
 const statusColumn = getColumnIndex_(values, 'ステータス') + 1;
 
 for( let i = 0; i < results.length; i++){
 
   //results [['参加者の名前', 'close', '参加したイベント名', '合否結果']]
   const person = results[i][0];
   const status = results[i][1];
   
   //重複した値があるため、書き込みを行いたい最新の値を取得
   if(originalNameArray.lastIndexOf(person) !== -1){
     const row         = originalNameArray.lastIndexOf(person) + 1;
     const eventResult = results[i][2] + results[i][3];
     
     const range       = sheet.getRange(row, targetColumn);
     range.setValue(eventResult);
     sheet.getRange(row, statusColumn).setValue(status);
     
     console.log(person, range.getA1Notation(), eventResult);
     
   }//if
 }//for
}//end

どんな事をやっているか？

1.　イベント名を全て、1次元配列で取得
2.　イベント名の配列を逆にして、0番目、つまり、最新のイベントを取得
3.　values（2次元配列）から、最新のイベント名が含まれる1次元配列を
　　取得し、新たな2次元配列を作成
4.　3で作成した2次元配列から、最新のイベントに参加する人のみの1次元
　　配列を作成
5.　全イベントの参加者の1次元配列を作成し、重複している人を取得
6.　4と5で作成した配列をindexOfで、精査して、最新のイベントに出席
　　かつ、重複している人を取得
7.　6で取得した対象者の合否結果、ステータス等を取得
8.　7で取得した値を特定のセルに書き込むという事をしています。

実務で使用しているスクリプトでは、手順6のステップが無いため、実行する度に重複している全員の合否を検索し、書き込みがある場合は、スキップするみたいな感じで対応していました。

1回の採用イベントに30名から40名近くいるため、シート全体の重複者も結構増えて、処理が重くなっていました。明らかに、このスクリプトの方が負担が軽く、速いので、実務で使っている本家も直さないといけないですね。

2次元配列も、filterで処理できる。

function test() {
 const values = [['のび太', '5年生'],['ケータ', '5年生'],['ドラえもん', '年齢不明']];
 
 //2次元配列の中から、最新のイベント名を含む1次元配列のみを残して、新たな2次元配列を作成
 const result = values.filter(array => { if(array.indexOf('5年生') !== -1){
   return array;
 }});
 
 console.log(result); //[['のび太', '5年生'],['ケータ', '5年生']]
}

分かりやすくするために、別途、サンプルを用意しました。2次元配列から、5年生が含まれている1次元配列のみを残し、2次元配列を新たに生成します。

Array.prototype.filter() - JavaScript | MDN

重複している値のみを抽出する関数

function test2() {
 const array = ['キャラクター紹介', '加藤', '玄野', '西', '玄野', '下平', '風', '加藤', '玄野'];
 const repetitiveArray = array.filter((value, index) => {return array.indexOf(value) !== index});
 
 //2回以上、登場するキャラクターを省く
 const newArray = Array.from(new Set(repetitiveArray));
 const length   = newArray.length + '人';

 console.log('重複削除前', repetitiveArray);
 console.log('重複削除後', newArray, length);

 return newArray　//['加藤', '玄野']
}

filterは、3つ引数が使えます。

element　配列内で処理中の現在の要素。
index　　配列内で処理中の現在の要素の位置。（省略可）
array　　filter メソッドが実行されている配列。（省略可）

加藤は、0から数えて、配列の1番目に登場します。配列番号が、1ではないため、不成立。

array.indexOf(value) !== index
//array.indexOf('加藤') !== 1

しかし、配列の最後に登場する時は、下記の条件が成り立ちます。その結果、2回目以降の重複した値のみを取り出す事が出来ます。ただし、3回目、4回目の登場の際にも配列に追加されています。

array.indexOf(value) !== index
//array.indexOf('加藤') !== 1

2回以上、登場する値を省く必要がある。

JavaScriptで重複排除を自分で実装してはいけない（Setを使う） - Qiita

const array1 = [1, 5, 3, 1, 5, 3];
const array2 = Array.from(new Set(array1))
console.log(array2); // [ 1, 5, 3 ];

横にかなりスクロールしないと、必要な情報が見えない

実務で使っているシートは、サンプルで作成したシートよりも、さらに横に長く、CC列とかに合否が書いてあるのですが、激しくスクロールしないと見えません.....（泣）率直に言って、かなり見辛いです。

control + F で応募者名を検索、合否結果の列までスクロールして、ひたすらコピペってのを延々に繰り返さなければならなかったので、自動化しました。しかし、急いで実装したあまりコメントも少なく、あとで、振り返った際、自分でも何をやっているのか解読が必要なコードになってしまい、そのまま、ブログでは紹介できないなぁ・・・・と思っていました。

そこで、本日、かなりの時間を掛けて、丁寧にリライトしました。実は、ノンプロ研のアドベントカレンダー用に残していたネタだったんです。慣れないmapやfilterなんかも使い、出来るだけ短く分かりやすいコードにすべく頑張ったんですが、それでも長くなってしまいますね.....。

仕事が、秒で片付くとは、こういう事

#ノンプロ研を一旦休止しました 。

転職してから、忙しさに波があるものの、ノンプロ研を覗く時間が減ったのは事実です。それから、あまりのコンテンツ量に追えなくなってきたなぁと感じたのもあります。また聞かなくてもある程度、一人でGASを書けるようになった自分自身の成長もあり、一旦、ノンプロ研を休止する事にしました。

GASだけではなく、PythonやVBAもあるのですが、Pythonは、今の仕事では、そこまで必要ではなく出来たら悪くないかもね....みたいな感じです。

人事業務の自動化特集

【GAS】人事業務の自動化特集｜nepia_infinity｜note