【GAS】Google Apps Script 活用事例　1年間に対応した採用面接・カジュアル面談回数をカウントするスクリプト

去年は、22卒の面談回数のカウントするためにカレンダーからの書き出しについてまとめました。今年は、集計結果をフィードバック用のシート原本に差し込み、複製する方法についてまとめてみました。こっちの方が需要がありそうな気がします。

集計結果を原本に差し込むスクリプト全文

/**
* 1. 面接回数集計表から値を取得する。
* 2. 原本を複製して、面接官ごとの集計を差し込むする
*
* getNamesFromSheet 集計表から面接官の名前と面接回数を取得
* duplicateSheet_(info)　原本を複製し、引数として受け取った値を差し込みしていく
*
*/

function getNamesFromSheet() {
 const url    = '*****************';
 const sheet  = getSheetByUrl_(url);
 const values = sheet.getDataRange().getDisplayValues();
 const header = values.shift();

 const column ={
   job:       header.indexOf('担当'),
   name:      header.indexOf('名前'),
   casual:    header.indexOf('面談回数'),
   interview: header.indexOf('面接回数'),
   coffee:    header.indexOf('コーヒー・ご飯'),
   event:     header.indexOf('イベント'),
   note:      header.indexOf('特記事項'),
   flag:      header.indexOf('FB対象外'),

 }

 console.log(values);

 let count = 0;

 for(let i = 0; i < values.length; i++){
   if(!values[i][0] || values[i][column.flag] === '対象外')continue

   const info = {
     job:       values[i][column.job],
     name:      values[i][column.name],
     casual:    values[i][column.casual],
     interview: values[i][column.interview],
     coffee:    values[i][column.coffee],
     event:     values[i][column.event],
     note:      values[i][column.note],
     flag:      values[i][column.flag]
   }

   console.log(info);
   duplicateSheet_(info);

   count +=1;

 }//for
 console.log(`対象者：　${count} 件`);
}//end



/**
* 
* ファイル内に存在する原本を複製し差し込みしていく
*
* @param {object} オブジェクト
* @return 
*
*/
function duplicateSheet_(info){
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const url         = '********************';
 const original    = getSheetByUrl_(url);
 const sheet       = original.copyTo(spreadsheet);

 console.log(`コピー後のシート： ${sheet.getSheetId()}`);
 sheet.setName(`${info.name}さん`);

 const keys = Object.keys(info);
 console.log('keys', keys);
 
 for(const key of keys){
   const query   = `${key.replace(/'/g, '')}`;
   console.log(query);

   const finder  = spreadsheet.createTextFinder(query).useRegularExpression(true);
   const results = finder.findAll();
   const length  = results.length;

   console.log(`検索語句：　${query} , 検索結果：　${length} 件`);

   const range = sheet.getRange(results[0].getRow(), results[0].getColumn());
   console.log(`該当箇所：　${range.getA1Notation()}`);

   range.setValue(info[key]);
 }
}


/**
* スプレッドシートのURLから、シートオブジェクトを取得する
*
* @param  {string} シートのURL
* @return {object} シートオブジェクト
*
*/
function getSheetByUrl_(url) {
 const spreadsheet = SpreadsheetApp.openByUrl(url);
 const sheets      = spreadsheet.getSheets();

 console.log(url.split('#gid='));

 //シートIDを、文字列から数値に変換する
 const sheetId = Number(url.split('#gid=')[1]);

 //前述のsheetIdが、型も含めて完全一致したときに、sheetをオブジェクトとして返す。
 for (const sheet of sheets) {
  if (sheetId === sheet.getSheetId()){
    console.log(sheet.getName());
    return sheet
   };
  }//for
 }//end

シートをコピーする　copyToメソッド

Class Range  |  Apps Script  |  Google Developers

今回は、同じファイル内に原本を複製するため、下記のように書いています。

const sheet = original.copyTo(spreadsheet);

特定の範囲の場合は、下記のようにすればOK

const copyToRange = original.copyTo(tagetRange); //コピーしたい範囲
copyToRange.activate(); //コピーした範囲をアクティブにする

サンプルスクリプト_その1

function myFunction() {
 const info = {
   name: 'ベル・クラネル',
   job:  'ヘスティア・ファミリア'
 }

 const keys = Object.keys(info);
 console.log(keys); //[ 'name', 'job' ]

 for(const key of keys){
   const query = `${key.replace(/'/g, '')}`; //シングルクオーテーションを削除する
   console.log(`key: ${query}, value: ${info[query]}`);

 }
}

こんな感じで、key と value が取得できます。
シートから値を取得、差し込む準備が出来ましたッ！！

サンプルスクリプト_その2

function myFunction2(){
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getActiveSheet();
 const query       = 'test';

 const finder  = spreadsheet.createTextFinder(query).useRegularExpression(true);
 const results = finder.findAll();
 const length  = results.length;

 console.log(`検索語句：　${query} , 検索結果：　${length} 件`);

 const range = sheet.getRange(results[0].getRow(), results[0].getColumn());
 console.log(`該当箇所：　${range.getA1Notation()}`);
} 

今回は、面接官は集計表に重複せずに、存在する想定でスクリプトを書いています。

後から大量の修正依頼？......えっ.....やめてよ(小声)

複製した後で、35枚ものシートの特定箇所のみ修正して欲しいとの依頼がありました。35枚ってエグいな.....。その時に使用したスクリプトがこちらです。

function getSheets(){
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheets      = spreadsheet.getSheets();

 let count = 0;

 for(const sheet of sheets){
   const sheetName = sheet.getName();
   
   //シート名に面接官の名前　+ さん
   // (例)　野比のび太さん　のように、シート名にさんが付くのだけを対象とする。
   if(sheetName.includes('さん')){
     console.log(sheetName)
     count += 1;

     const targetValue = sheet.getRange('A2').getValue();
     console.log(`A2：　${targetValue}`);

     if(targetValue === '2021'){
       sheet.getRange('A2').setValue('2022');
       console.log('書き換え完了');
     }
   }
 }
 console.log(`該当：${count} 件`);
}

去年のシートをコピペして今年のものを作成した際、日付が去年のままだったとか....そういうときに使えるかなと思っています。

目次を作成するのに便利なスクリプトはこちら

ドキュメントへの差し込みはこちら