【GAS】Google Apps Script 活用事例　行と列の一致で任意の値を取得するスクリプト

行と列の一致で、任意の値を取得したいケースってありませんか？

実務では、採用イベントに出席するエンジニアが日付によって異なるケースがあり、シートから、10月のこの日に出席するエンジニアを特定したいということがあり、書いてみました。

サンプルは、掃除当番にしてみました。10月の窓掃除の当番は、のび太、翌月は、ジャイアンです。

コードはこんな感じ

function myFunction () {
//10月の窓掃除当番を知りたい場合
 const person = getPersonName_('2020/10/01', '窓');
 console.log(person);

}

//日付と掃除場所から担当者名を取得
function getPersonName_(date, job) {
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('掃除当番表');
 const values      = sheet.getDataRange().getDisplayValues();
 
 //見出し行、横方向から、特定列を検出
 //getRangeで取得するため　+1
 const headerRow   = values[0];
 const jobColIndex = headerRow.indexOf(job) + 1;
 
 console.log(jobColIndex);
 
 //縦方向の列 特定行の検出
 const targetRow    = generateArray_(values, 0);
 const dateRowIndex = targetRow.indexOf(date) + 1;
 
 console.log(targetRow);
 console.log(dateRowIndex);
 
 const range  = sheet.getRange(dateRowIndex, jobColIndex);
 const person = range.getValue();
 
 console.log(range.getA1Notation());
 console.log(person);
 
 return person
}//end


//空白行をすべて省いた上で、1次元配列を生成する。
function generateArray_(values, column){
return values.map(record => record[column]).filter(value => value);
}

下記のスクリプトが本当に便利です。

//空白行をすべて省いた上で、1次元配列を生成する。
function generateArray(values, column){
return values.map(record => record[column]).filter(value => value);
}

普段、コピペで使っているのですが、関数として書くと、下記みたいな感じかな。

function test() {
const array  = ['', '', '', 'a', 'b', 'c'];
let newArray = array.filter(function(value, index, array){

  // true を返した要素は残され、false を返した要素は取り除かれる。
  // 要素が、空白以外だったら、trueとなり、配列に残るが、空白だったら、falseとなり、配列から削除される。
  return value !== '';
})
console.log(newArray)//['a','b','c'];;
}

map() メソッドは、与えられた関数を配列のすべての要素に対して呼び出し、その結果からなる新しい配列を生成します。

valuesが、2次元配列
recordが、1次元配列
最初の1次元配列では、record[0]が、日付。
次の1次元配列では、'2020/10/01'みたく、結果として縦方向に処理をしていきます。これ考えた人凄いと思います。本当に。

[ [ '日付', '廊下', '窓', '床', 'デスク', '掃除機' ],
 [ '2020/10/01', 'スネ夫', 'のび太', 'ジャイアン', 'しずか', 'ドラえもん' ],
 [ '2020/11/01', 'のび太', 'ジャイアン', 'しずか', 'ドラえもん', 'スネ夫' ],
 [ '2020/12/01', 'ジャイアン', 'しずか', 'ドラえもん', 'スネ夫', 'のび太' ],
 [ '2021/01/01', 'ドラえもん', 'スネ夫', 'のび太', 'ジャイアン', 'しずか' ] ]

Array.prototype.map() - JavaScript | MDN

今回のスクリプトは、アクティブセルが、書類選考合格になったら、希望職種ごとに異なる面接官の名前を自動入力する機能として使いました。あるシートから、Ctrl +Fで検索して、コピーペーストのような作業が減ったので、だいぶ楽になりました。