【GAS】Google Apps Script 活用事例 2つ以上のシートを横断的に検索し、出力するスクリプト
先日、スニペット集を作成している途中で、採用イベントの参加者をまとめたシートと、通常選考をまとめたシートを同時に検索して、それぞれの値がどこにあるか把握できれば、めっちゃ便利じゃない？と思って書きました。Ctrl + F の強化版っぽい感じです。
採用イベントの参加者が、過去の採用イベントに参加していたかを調べて、自動入力する仕組みを、こちらで紹介しました。こちらのスクリプト、非常にイイ働きをしてくれるのですが、欠点があります。同じ応募者が2回以上、出席していた場合には、上手くいかないことがあるのです。例えば.....。
1回目の時はイベントを欠席。
2回目の参加時に面接。
3回目にも他社の面接のために参加
のような場合に、臨機応変で、2回目のイベント結果を取得する事ができません。そこまでやろうとすると、if文の条件分岐などが複雑になり過ぎてしまうため、実装を見送りました。
実行時のイメージ
ちなみに、tabボタンを押すと、マウスを使わず、OKボタンを押すことが出来ます。
牧瀬と検索した時に、牧瀬紅莉栖がどのシートのどこに情報が記載されているのかが表示されます。入力フォームで打ち込んだ情報のスペースなどの空白も自動で削除してくれます。
スクリプト全文
今回のスクリプトでは、見出し行の単語が一致していないと、各シートから情報が取得できません。見出し行の単語さえ一致していれば、シート構造が異なる場合でも情報の取得が可能です。
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('追加メニュー')
.addItem('シートを検索する', 'searchAllSheet')
.addItem('サブメニュー', 'myFunction2')
.addToUi();
}
function searchAllSheet() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('採用イベント参加者');
const ui = SpreadsheetApp.getUi();
const originalInput = showPrompt_(ui, '全シート検索', '検索したい語句を入力してください。');
const newInput = originalInput
.replace(/\s/,'')
.replace(' ','')//全角スペース
.replace(' ','');//半角スペース
//[正規表現を使用した検索]有効
const finder = spreadsheet.createTextFinder(newInput).useRegularExpression(true);
const results = finder.findAll();
console.log(`検索結果： ${results.length}`);
//resultsが多過ぎて、処理に時間が掛かり過ぎる場合は検索結果を10件以上を表示させないようにする
//results.splice(9);
if(0 < results.length){
const info = results.map(result => ({
sheetName: result.getSheet().getName(),
value: result.getValue(),
cell : result.getA1Notation(),
eventName: getTargetValue_(result.getSheet().getName(),result.getRow(),'イベント名'),
confluence: getTargetValue_(result.getSheet().getName(),result.getRow(), 'コンフル'),
result: getTargetValue_(result.getSheet().getName(),result.getRow(), '合否'),
})
);
console.log(info);
let string = '';
//オブジェクトを文字列に直す
for(let i = 0; i < results.length; i++){
for(const [key, value] of Object.entries(info[i])) {
string += `${key}: ${value}\n`;
}
string += `-------------------------------------------\n`;
}
console.log(string);
ui.alert(string);
}//if
}//end
function showPrompt_(ui, title, sample) {
const response = ui.prompt(title, sample, ui.ButtonSet.OK);
const input = response.getResponseText();
console.log(input);
switch (response.getSelectedButton()){
case ui.Button.OK:
console.log('%s と入力され、OKが押されました。',input);
break;
case ui.Button.CLOSE:
console.log('閉じるボタンが押されました。');
break;
default:
console.log('処理が中断されました。');
}//switch
return input
}
function getTargetValue_(sheetName, row, keyWord) {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName(sheetName);
const headerRow = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
const targetColumn = headerRow.indexOf(keyWord) + 1;
//indexOfの結果が、不一致だったら、-1 + 1 で 0 になる。
if(targetColumn === 0){
return '';
}else{
console.log('headerRow：', headerRow);
console.log(targetColumn);
const targetValue = sheet.getRange(row, targetColumn).getValue();
console.log('targetValue：', targetValue);
return targetValue
}
}createTextFinder
//[正規表現を使用した検索]有効
const finder = spreadsheet.createTextFinder(newInput).useRegularExpression(true);
const results = finder.findAll();
console.log(`検索結果： ${results.length}`);Windowsだと、Ctrl + H で出現するアレです。
1. spreadsheet.createTextFinder(newInput)
2. sheet.createTextFinder(newInput)
3. range.createTextFinder(newInput)
上記のように範囲を絞る事が出来ます。
今回、記事の執筆にあたって、こちらを参考にしました。
連想配列、ログが見やすくて、重宝するのですが、シートに貼り付けたりする場合には、2次元配列だったり、ボットなどの場合には文字列に変換する必要があります。
Object.entries
function myFunction() {
const object1 = {
yesterday: 3150,
today: 3176
};
//keyとvalue がセットになった配列に変換する
const entries = Object.entries(object1);
console.log(entries); //[ [ 'yesterday', 3150 ], [ 'today', 3176 ] ]
for (const [key, value] of entries) {
console.log(`${key}: ${value}`);
}
}keyとvalueがセットになった配列に変換する。keyが不要な場合は、spliceメソッドで処理する事が可能です。
function myFunction12() {
const obj = {
"key-1": "value-1",
"key-2": "value-2",
"key-3": "value-3"
}
//1次元配列の最初の要素keyを削除する。
const originalValues = Object.entries(obj);//[ [ 'key-1', 'value-1' ],['key-2', 'value-2' ],['key-3', 'value-3' ]]
console.log('originalValues\n\n', originalValues);
originalValues.map(array => array.splice(0, 1));
//1次元配列に変換
const array = originalValues.flat();
let newValues = [];
newValues.push(array);
console.log('array： ', array);
console.log('newValues： ', newValues);
}Object.keys
function myFunction() {
const object1 = {
a: 'somestring',
b: 42,
c: false
};
const keys = Object.keys(object1);
console.log(keys); //["a", "b", "c"];
let newArray = [];
for(const key of keys){
newArray.push(object1[key]);
}
console.log(newArray); //[ 'somestring', 42, false ]
}keysの方が分かりやすいかな。
スクリプトの別バージョン 連想配列の要素の削除
実務で実装した環境だと、姓と名の2つの列があり、フルネームを生成している列があるシートのため、結果が重複して返ってきてしまう事がありました。そこで連想配列の重複を、削除するコードを加えました。
function myFunction457683() {
//createTextFinder(newInput).useRegularExpression(true).findAll(); の結果
const results =
[ { name: 'バカ', cell: 'N656', eventName: '2/14合説', result: '' },
{ name: 'バカ', cell: 'L656', eventName: '2/14合説', result: '' },
{ name: 'マジメ', cell: 'N578', eventName: '2/6合説', result: '合格' },
{ name: 'マジメ', cell: 'L578', eventName: '2/6合説', result: '合格' },
{ name: 'ロリ', cell: 'N500', eventName: '12/13ハッカソン', result: '不合格' },
{ name: 'ロリ', cell: 'L500', eventName: '12/13ハッカソン', result: '不合格' },
{ name: 'ヲタ', cell: 'N495', eventName: '12/12合説', result: '' },
{ name: 'ヲタ', cell: 'L495', eventName: '12/12合説', result: '' },
{ name: 'ヤマイ', cell: 'N435', eventName: '12/5ハッカソン', result: '不合格' },
{ name: 'ヤマイ', cell: 'L435', eventName: '12/5ハッカソン', result: '不合格' } ];
const reducer = (accumulator, current) => {
console.log(`accumulator, ${accumulator}`);
console.log(`current, ${current}`);
//分割代入 new lineは、文字列化した際の改行に使用する
const {name, eventName, result, cell} = current;
const newLine = '';
//同じ応募者名と、イベント名がなく、合否が付いている配列に加える
if (!accumulator.includes(name) && !accumulator.includes(eventName) && result){
accumulator.push(name, eventName, result, cell, newLine);
}
return accumulator
};
const newResult = results.map(result => result).reduce(reducer, []);
//見にくいので、文字列に変換する
const string = newResult.join('\n');
console.log(string);
return string
}スクリプト実行時
オブジェクトのキーは消えてしまうものの.....必要な情報を抽出出来ました。
