【GAS】Google Apps Script 活用事例 HRMOS新卒エディションの説明会CSVから情報を取得して既存シートに情報を反映するスクリプト
シートに合わせてCSVを整形する派です。
昨今の採用管理ツールでは、CSVなどでデータを落とす事が可能になっていると思うのですが、
シートをCSVに合わせるか
シートに合わせてCSVを整形するか
ある程度関数を組める人がチームにいる場合は後者になるケースが多いと思います。それでもCSVの加工って結構面倒だったりするので、スクリプトで自動化しました。
大体の流れ
CSVを特定のフォルダにアップロード
アップロードされたCSVから必要な列のみ取得
フルネーム、候補者IDなどの成形
既存シートに転記済みかどうかをチェック
貼り付け
大まかに上記のことを実行しています。
スクリプトはこんな感じ
function setHrmosCsvValues(){
const html = HtmlService.createHtmlOutputFromFile('dialog');
SpreadsheetApp.getUi().showModalDialog(html, "CSVアップロード");
//Javascriptのonclickで、CSVのアップローダが作動する
//実際に動く関数は、uploadCsvで、dialog.htmlに記載
}
/**
* 指定したドライブのフォルダにCSVファイルをアップロードするスクリプト
* 更新日時：2022/10/12
* アップロード先
* https://drive.google.com/drive/folders/************
*
*/
function uploadCsv(e) {
//CSVアップロード先フォルダを取得する
const folderId = '****************' ;
const folder = DriveApp.getFolderById(folderId);
const file = folder.createFile(
Utilities.newBlob(
e.file[0].files[0].bytes,
e.file[0].files[0].mimeType,
e.file[0].files[0].filename
)
);
const fileId = file.getId();
console.log(`fileName: ${file.getName()}`);
console.log(`fileId: ${fileId}`);
SpreadsheetApp.getUi().alert("アップロード完了");
//取得したCSVから不要な情報を省き、2次元配列を取得する
const values = decodeCsv_(fileId);
//2次元配列を転記済みかどうかを確認してシートに張り付ける
appendHrmosInfo_(values);
}
/**
* uploadCsvの処理途中で呼び出される関数
* 取得したCSVから特定の列のみを配列として取得する
*
* @param {string} fileId - CSVファイルのID
* @return {Array.<Array.<string>>}
*
*/
function decodeCsv_(fileId) {
const blob = DriveApp.getFileById(fileId).getBlob();
const csv = blob.getDataAsString('UTF-8');
const values = Utilities.parseCsv(csv);
console.log(values);
const header = values[0];
const column = {
lastName: header.indexOf('姓'),
firstName: header.indexOf('名'),
candidateId: header.indexOf('候補者ID'),
stringDate: header.indexOf('イベント日時'),
isAbsent : header.indexOf('出席'),
}
console.log(column);
const selectedColumns = selectColumn_(values, column);
//オブジェクトの中身を入れ替える
const newColumn = sortInsideObject_(column);
const newValues = modifyHrmosCsvValues_(selectedColumns, newColumn);
return newValues
}
/**
*
* 1-1. 苗字と名前を繋げる
* 1-2. HRMOSのURLを生成 https://n-ats.hrmos.co/candidates/
* 1-3. 候補者IDにハイフンが含まれているため、置換が必要
*
*/
function modifyHrmosCsvValues_(values, column){
let newValues = [];
values.shift();
for(let i = 0; i < values.length; i++){
if(!values[i][0])continue
//console.log(values[i]);
//姓名を連結する
const fullName = values[i][column.lastName]+values[i][column.firstName];
console.log(`姓名連結後の名前：${fullName}`);
//ハイフンを消す
const replacedString = values[i][column.candidateId].replace(/-/g, '');
const candidateId = `https://n-ats.hrmos.co/candidates/${replacedString}`;
console.log(`候補者ID: ${candidateId}`);
//日付と日時に分離する
//例：['2022/11/21','11:05']
const array = values[i][column.stringDate].split(' ');
console.log(array);
let isAbsent = '';
if(values[i][column.isAbsent] === '○'){
isAbsent = '出席'
}
array.unshift(fullName, candidateId, isAbsent);
newValues.push(array);
}//for
console.log(newValues);
return newValues
}
/**
* CSVにあるHRMOSの候補者IDが、説明会管理シートに転記済みかどうかを確認し、見当たらない場合のみ張り付ける
*
* 説明会管理シートのURL
* https://docs.google.com/spreadsheets/d/***********
*
* values --- 引数で受け取る2次元配列
* original --- 転記先（説明会管理シート）の2次元配列
*
* @param {Array.<Array.<string>>} CSVから抽出した2次元配列
* @return
*
*/
function appendHrmosInfo_(values){
const sheetUrl = 'https://docs.google.com/spreadsheets/d/******************';
const sheetName = getSheetByUrl_(sheetUrl, 'sheetName');
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
const original = sheet.getDataRange().getDisplayValues();
const column = {
hrmos: original[0].indexOf('HRMOS'),
isAbsent: original[0].indexOf('出欠確認')
}
const ui = SpreadsheetApp.getUi();
const response = ui.alert('CSVの内容を最終行に張り付けます。よろしいでしょうか？', ui.ButtonSet.YES_NO);
switch (response){
case ui.Button.YES:
console.log('“はい” のボタンが押されました。');
//シートに転記済みのHRMOS IDを取得する
const hrmosIdArray = generateArray_(original, column.hrmos);
console.log(hrmosIdArray);
let range;
for(let i = 0; i < values.length; i++){
//シートに転記済みのHRMOS IDが転記済みかどうかを確認する
//テストアカウントを省くための処理
if(hrmosIdArray.indexOf(values[i][1]) === -1 && !values[i][0].includes('テスト')){
const lastRow = sheet.getRange('A1:A1000').getValues().filter(String).length +1;
console.log(`最終行： ${lastRow}`);
if(values[i].includes('')){
//['野比のび太','https:...', '','2022/11/21','11:05' ]
range = sheet.getRange(lastRow, 1, 1, values[0].length);
console.log(`転記先範囲：${range.getA1Notation()}`);
console.log(values[i]);
//setValues()は2次元配列しか対応していない。
range.setValues([values[i]]);
}else if(values[i].indexOf('出席') !== -1){
//['野比のび太', 'https:...', '出席','2022/11/21','11:05' ]
//splice(抽出開始位置, 取り出す要素数, 削除箇所に挿入する要素, …)
values[i].splice(2, 1, '');
sheet.getRange(lastRow, column.isAbsent +1).setValue('出席');
range = sheet.getRange(lastRow, 1, 1, values[i].length);
console.log(`転記先範囲：${range.getA1Notation()}`);
console.log(values[i]);
range.setValues([values[i]]);
}
}
}//for
break;
case ui.Button.NO:
console.log('“いいえ” のボタンが押されました。');
ui.alert('処理が中断されました。');
break;
default:
console.log('処理が中断されました。');
}//switch
}
/**
* SpreadsheetのURLからsheetオブジェクトを取得する。
* シート名が取得したい場合は、2番目の引数に、'sheetName' と指定する
*
* @param {string} url - スプレッドシートのURL
* @param {string} keyWord - 引数の省略可。'sheetName' と指定する
* @return {SpreadsheetApp.Sheet|string} オブジェクトかシート名を返す。
*
*
*/
function getSheetByUrl_(url, keyWord) {
const spreadsheet = SpreadsheetApp.openByUrl(url);
const sheets = spreadsheet.getSheets();
console.log(url.split('#gid='));
//シートIDを、文字列から数値に変換する
const sheetId = Number(url.split('#gid=')[1]);
//前述のsheetIdが、型も含めて完全一致したときに、sheetをオブジェクトとして返す。
//keyWord、2番目の引数が省略されており、定義されていない場合
for (const sheet of sheets) {
if (sheetId === sheet.getSheetId() && !keyWord){
console.log(`シート名： ${sheet.getName()}`);
console.log(`型： ${typeof sheet}`);
return sheet;
}else if(sheetId === sheet.getSheetId() && keyWord === 'sheetName'){
const sheetName = sheet.getName();
console.log(`シート名： ${sheetName}`);
console.warn(`型： ${typeof sheetName}`);
return sheetName;
}
}//for
}//end
/**
* getDataRange()などで取得した2次元配列から必要な列だけを抽出し、新しい2次元配列を作成する
*
* @param {Array.<Array.<string|number>>} values - 元の2次元配列
* @param {Object.<number>} column - 見出し行のオブジェクト {id: 0, name: 1}
* @param {string} keyWord - 2次元配列から情報を取捨選択するためのキーワード
* @return {Array.<Array.<string|number>>} 新しい2次元配列
*/
function selectColumn_(values, column, keyWord){
const keys = Object.keys(column);
const numbers = keys.map(key => column[key]);
console.log(numbers);//1次元配列
//indexに該当する列だけを残して2次元配列を作成する
const newValues = values.map(array => array.reduce((accumulator, current, index) =>{
if(numbers.includes(index)){
accumulator.push(current);
}
return accumulator
}, [])//reduce
);//map
//console.log(newValues);
if(!keyWord){
//keyWordが省略されており、定義されていない場合、空白行の配列を取り除く
const filtered = newValues.filter(row => row[0] !== '');
console.log(filtered);
return filtered
}else if(keyWord){
//newValuesから、さらに特定の単語が含まれている配列のみを残す
const filtered = newValues.filter(row => row.indexOf(keyWord) !== -1);
console.log(filtered);
return filtered
}
}
function sortInsideObject_(column) {
const values = Object.entries(column);
console.log(`Object.entriesの結果`, values);
const newValues = values.sort((a, b) => (a[1] < b[1]) ? -1 : 1);
console.log(`ソート後の2次元配列`, newValues);
const keys = newValues.map(record => record[0]).filter(value => value);
console.log(keys);
const object = new Object();
keys.map((key, index) => object[`${key}`] = index);
console.log(object);//{ name: 0, step: 1, date: 2 }
return object
}普段ライブラリ的に使っているスクリプトを含めると長いですねぇ…..。
dialog.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<!-- V8対応ライブラリ -->
<script src="https://cdn.jsdelivr.net/gh/tanaikech/HtmlFormObjectParserForGoogleAppsScript_js@master/htmlFormObjectParserForGoogleAppsScript_js.min.js"></script>
</head>
<body>
<form>
<input type="file" name="file" />
<input
type="button"
value="OK"
class="action"
onclick="ParseFormObjectForGAS(this.parentNode).then(obj => google.script.run.uploadCsv(obj))"
/>
</form>
</body>
</html>CSV関連のメソッドにはV8でバグが改善されないまま残っている
ローカルのCSVをアップするスクリプトが、どうもV8で動かないみたいなんですが、どなたか回避策をご存知ありませんか？？https://t.co/2B0e7WzOWw
— ⅡBASA (@nepia_infinity) October 12, 2022