【GAS】Google Apps Script 活用事例 応募者ごとに、To Doリストの項目が変わるチェックリストを作成しよう
採用補助の事務仕事をするようになってから、「これは、発達障害の俺にとって、鬼門のマルチタスクじゃないか.....」と、感じています。
例えば、書類選考中の応募者Aと、面接参加前の応募者B......それぞれ、やるべき事が、変わってきます。それで、取りこぼしや抜け漏れが多かったので、表題の通り、応募者ごとに、やるべき項目が変わるチェックリストを作成しました。
今回のスクリプトでは、DBとして使っているシートから応募者情報を取得して、それを行と列を入れ替えて、チェックシートに反映しています。ちょうど、transpose関数を使っているようなイメージです。横に（無駄に）長いシートを見やすくするのによく使っています。
隣にITの、こちらの記事を参考にしました。
完成系のスクリプト
function transposeValues_(array) {
const url = '*****************';
const spreadsheet = SpreadsheetApp.openByUrl(url);
const sheet = spreadsheet.getSheetByName('DB');
const values = sheet.getDataRange().getValues();
//見出し行
const headerRow = values[0];
console.log(headerRow);
//console.log(values);
//見出し行の項目、通常は、1行目
const COLUMN_NAME ={
ID: 'ID',
NAME: '氏名',
HRMOS: 'HRMOSURL',
CONFLU:'コンフルURL',
STATUS:'ステータス',
}
const idColumnIdx = headerRow.indexOf(COLUMN_NAME.ID);
const nameColumnIdx = headerRow.indexOf(COLUMN_NAME.NAME);
const confluColumnIdx = headerRow.indexOf(COLUMN_NAME.CONFLU);
const HRMOSColumnIdx = headerRow.indexOf(COLUMN_NAME.HRMOS);
const statusColumnIdx = headerRow.indexOf(COLUMN_NAME.STATUS);
let newValues = [];
for(let i = 0; i < values.length; i++){
if(!values[i][idColumnIdx]){continue};
//チェックリストにHRMOSのURLが、転記済みだったら、配列に加えない。
if(array.indexOf(values[i][HRMOSColumnIdx]) !== -1){continue};
if(values[i][statusColumnIdx] === 'active'){
//昨今、厳しい個人情報の扱いに対応して、苗字のみを抽出。大体、佐藤とか高橋とか2文字の苗字が多いため。
const person = values[i][nameColumnIdx].slice(0, 2);
const id = values[i][idColumnIdx];
const conflu = values[i][confluColumnIdx];
const HRMOS = values[i][HRMOSColumnIdx];
//空白行を入れる
newValues.push(['', '', '', ''],[id, person, conflu, HRMOS]);
}//if
}//for
//console.log(newValues);
//配列の行と列を入れ替えるライブラリを使用する。
const _ = Underscore.load();
const transposeArray = _.zip.apply(_, newValues);
console.log(transposeArray);
return transposeArray
}//end
//行と縦を入れ替えた、配列を張り付け
function setTransposeAttay() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('チェックリスト');
const originalValues = sheet.getDataRange().getValues();
//空白を削除
const urlRow = originalValues[3];
const HRMOS_URL_Row = urlRow.filter(element => element);
console.log(`HRMOS_URL_Row ${HRMOS_URL_Row}`);
//配列を突き合わせて、チェックリストにIDがある人を配列に入れる事をスキップする。
const values = transposeValues_(HRMOS_URL_Row);
const targetColumn = sheet.getLastColumn() + 1;
if(0 < values.length){
sheet.getRange(1, targetColumn, values.length, values[0].length)
.setValues(values)
.setFontLine(null);
//6行目以降の塗りつぶしを消す
sheet.getRange(6, targetColumn, values.length, values[0].length)
.setBackground(null);
}//if
//列の幅を調整する。
adjustColumnWidth();
}//end
//列の幅を調整する。
function adjustColumnWidth() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('チェックリスト');
const sheetColumn = sheet.getLastColumn();
console.log(sheetColumn);
for(let i = 0; i < sheetColumn; i++){
//2で割り切れなかったら、つまり奇数だったら、
if(i % 2 !== 0 ) {
console.log(i);
sheet.setColumnWidth(i, 55);//列番号と、サイズ55px
sheet.getRange(6, i, sheet.getLastRow()-5, 1)
.insertCheckboxes('yes');
}//if
}//for
}//end
function getTasks(activeValue) {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('項目');
const values = sheet.getDataRange().getValues();
//console.log(values);
let tasks = [];
const status = activeValue;
for(let i = 0; i < values.length; i++){
if(!values[i][0]){continue};
if(values[i][2] === status){
tasks.push([values[i][1]]);
}//if
}//for
console.log(tasks);
return tasks
}//end
function setCheckList() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('チェックリスト');
const targetSheetName = sheet.getSheetName();
const activeSheet = spreadsheet.getActiveSheet();
const activeSheetName = activeSheet.getSheetName();
const values = sheet.getDataRange().getValues();
if(targetSheetName !== activeSheetName){return};
console.log(`指定したシート： ${targetSheetName}`);
console.log(`アクティブなシート: ${activeSheetName}`);
//アクティブセルを取得
const activeCell = activeSheet.getActiveCell();
const activeRow = activeCell.getRow();
const activeColumn = activeCell.getColumn();
const activeValue = activeCell.getValue();
console.log(activeCell.getA1Notation());
//条件にマッチした場合のみ、これ以降の処理を行う
if (targetSheetName === activeSheetName
&& activeRow === 5
&& 0 < activeColumn
&& activeValue !== ''){
console.log(activeValue);
const newValues = getTasks(activeValue);//1次元配列を取得
const targetRow = activeRow + 1;
sheet.getRange(targetRow, activeColumn, sheet.getLastRow(), 1)
.clearContent();
sheet.getRange(targetRow, activeColumn, newValues.length, newValues[0].length)
.setValues(newValues);
}//if
}//end
function seLineThrough() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('チェックリスト');
const targetSheetName = sheet.getSheetName();
const activeSheet = spreadsheet.getActiveSheet();
const activeSheetName = activeSheet.getSheetName();
const values = sheet.getDataRange().getValues();
if(targetSheetName !== activeSheetName){return};
console.log(`指定したシート： ${targetSheetName}`);
console.log(`アクティブなシート: ${activeSheetName}`);
//アクティブセルを取得
const activeCell = activeSheet.getActiveCell();
const activeRow = activeCell.getRow();
const activeColumn = activeCell.getColumn();
const status = activeCell.isChecked();
console.log(activeCell.getA1Notation());
//条件にマッチした場合のみ、これ以降の処理を行う
if (targetSheetName === activeSheetName
&& 5 < activeRow
&& 0 < activeColumn
&& status === true){
//チェックをつけたら、グレーアウトと取消線
const targetColumn = activeColumn + 1;
sheet.getRange(activeRow, targetColumn)
.setFontLine('line-through')
.setBackground('#b7b7b7');
}else if (targetSheetName === activeSheetName
&& 5 < activeRow
&& 0 < activeColumn
&& status === false){
//チェックを外したら、塗り潰しを元に戻す
const targetColumn = activeColumn + 1;
sheet.getRange(activeRow, targetColumn)
.setFontLine(null)
.setBackground(null);
}//else if
else{return}
}//endポイントは、ココ
if(array.indexOf(values[i][18]) !== -1){continue};今回のスクリプトのポイントはココで、下記のスクリーンショットのチェックシートに応募者のHRMOS URLがあれば、DBからコピーしないという処理を加えています。
この条件分岐がないと、DBから全部、コピーされてしまうため、手作業で重複を削除する手間が発生します。行と列を入れ替えているため、重複の削除などが、少し面倒なのです。
チェックをつけると、項目名に取り消し線が入ります。
アクティブセルを取得して、取り消し線を入れたりする処理を実行しています。この手のスクリプトは、共通して使えそうな部分がありそうなものの、IF文、条件分岐が変わってくるので、少し扱いづらい印象があります。
2020/10/21 追記
書き出す際、列の幅をチェックマークがある部分だけ、キュッと小さくコンパクトにしたいと思ったので、修正しました。sheet.gatLastColumn()で、最終列を取得。for文を回して、奇数の列だったら、幅を55pxにしています。
また、追加時に、取り消し線や、塗り潰しが残ってしまう事があったので、それを貼り付け時にクリアにするスクリプトも書き足しました。
ノーミス、完璧である事を求められ続けると、ちょっと辛い。
脳に障害かぁ......今でも自分は普通だと思っていても、どうしてもミスが発生してしまいやすい体質のようです。目が悪い人は、メガネを使う事で、不自由なく生活出来るように、ちょっとの工夫で、働きやすい生き方を実現する事が出来るかな.....なんて思っています。
発達障害とも上手く付き合いながら、仕事も長く続けたいなと思っています。仕事が長く続けば、仕事でストレスが溜まっても、息抜きが出来るし、趣味にもお金が使えるし、ある程度、何とかやっていけそうな気がします。
アクティブセルの処理を活用した他のスクリプト
この記事が気に入ったらサポートをしてみませんか？