【GAS】Google Apps Script 活用事例　データの入力規則と、FILTER関数を組み合わせて、欲しい月のデータだけを取得できるシートを作ろう。

Photo by timJ on Unsplash

データの入力規則を生成して、その選択内容に応じて、動的にスプレッドシートの内容を変えるという事を実務で実現したので、備忘録として残しておきたいと思います。

メインのスクリプト

function buildRule() {
 const spreadsheet  = SpreadsheetApp.getActiveSpreadsheet();
 const sheet        = spreadsheet.getSheetByName('FILTER');
 const monthList    = dateArray();//他の関数を呼び出し
 
 
 sheet.getRange('A2')
 .setDataValidation(SpreadsheetApp.newDataValidation()
 .setAllowInvalid(true)
 .requireValueInList(monthList, true)
 .build());

 
 spreadsheet.setActiveSheet(spreadsheet.getSheetByName('FILTER'), true);
 
}//end

日付の連番データを作るスクリプト

function dateArray() {
 const startDate = new Date('2019/06/01');
 const endDate  　　= new Date();
 
 console.log(startDate);
 console.log(endDate);
 
 let dateArray = [];
 
 for(let d = startDate; d < endDate; d.setMonth(d.getMonth()+1)) {
 
   console.log('iの内容 %s',d);
   dateArray.push(Utilities.formatDate(d, 'Asia/Tokyo', 'yyyy/MM/dd'));
   
 }//for
 console.log('配列の内容',dateArray);
 return dateArray
}//end

このスクリプトは、日付の連番や時刻の連番を使うのに便利です。上記の例だと、2019年6月1日から、1ヶ月ごとに増えていき、現在の日付まで、増えていてきます。

FILTER関数

=FILTER('年次集計'!$A$2:$S,'年次集計'!A2:A=A2)

年次集計のシートの日付の列が、FILTER関数の入っているシートのA列と同じ日付の身を抽出してコンテンツを動的に変える事が出来ます。

この記事執筆当時は、filterメソッドを使いこなして、全ての処理をGASで書くことができなかったため、上記のように一部の処理を数式を使って書いています。

GASで書くとしたら、こんな感じでしょうか？

/**
* @param {string} '2019/01/01' のように日付、型は文字列
* @return {object} [[]] valuesからその引数を含む行のみを抽出した2次元配列
* 
*/
function filteredValues_(targetDate) {
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('目次');
 const values      = sheet.getDataRange().getDisplayValues();
 const newValues   = values.filter(row => row.indexOf(targetDate) !== -1);

 console.log(newValues);
 return newValues;

}