本日は、スプレッドシートのマクロ＝Google Apps Script＝GASについて備忘録がてら残していく*1。
Java Scriptそんなに触った事は無いが、このGAS、ほぼJava Scriptと思われる。
スプレッドシートがマクロの記録に対応したのは2018年らしいが、スクリプトコード自体は以前から書けたらしい。
記録できるようになり、とっつきやすくなったのが上記の年以降ということになるな。
んで、表題の件である。
今回は、
・編集者を制限したシートA(範囲保護)
・そのシートを参照しているシートB(保護無)
保護をするとフィルタをかけられない状況になる為、閲覧者用にBを作成
といった状況で、
【目的】
アクティブシートAの特定列のセルを編集した場合に非アクティブシートB上のフィルタを自動で更新したい！！！
といった、一見単純そうに見えてGAS初心者的には「ハ？」なこれを、我輩が実現させるまでの奮闘、そして魂の記録である。
つまりはGAS初心者によるGAS初心者のための、壮大(で遠回り)なドキュメンタリー…。
※すっ飛ばしてさっさと完成形に行きたい場合はこちらの目次からどうぞ↓
初心者がやることと言ったらまずは自動記録である。例文無しには何も手を付けられないのだ。
というわけで、Bをアクティブにした状態で、フィルタ操作をマクロで記録してみる。
function myFunction() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('A3').activate();
var criteria = SpreadsheetApp.newFilterCriteria()
.setHiddenValues(['TRUE'])
.build();
spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(1, criteria);
};
こんな感じになった。
すなわち、A3をアクティブにしてアクティブシートの1列目をTRUE非表示(つまりFalse値)でフィルタかけるでござるよ～という意味と思われる。
にしてもA3アクティブにするなんて動きしてないんだがなぁ…(´･ω･`)
varの行では変数を使えるようにするため、中身を代入している。varはvariantのvarか…？ようわからんので、VBAでいうSetみたいなもんということにしておくとしよう(※あとから調べたらVariable(まんま変数)の略だった)。
その次に、シートを切り替える動作を記録してみる。
function myFunction1() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('A3').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('シートB'), true);
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('シートA'), true);
};
…またA3がactivateになっているな。そんなことをした記憶は無いぞ。
多分だが、スプレッドシートでのマクロ記録は開始時点で選択しているセルも含めて記録する為こうなるっぽい。
要らんアクティブは後から消せば良いので置いといて、
まずは記録によるできあがった二つを見比べてみると、
シートを新たに選択し(切り替え)アクティブにするときは、setActivesheet
シートを切替えず現在のアクティブシートを取得する場合は、getActiveSheet
ということが分かる。
切り替え＝セット、現在のものを捕捉＝ゲット
ってことやろ(多分)。
これら二つを組み合わせ、シートBに切替え→シートBのフィルタ反映後、シートAに戻るという動作をさせる場合、
function myFunction() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('シートB'), true);
var criteria = SpreadsheetApp.newFilterCriteria()
.setHiddenValues(['TRUE'])
.build();
spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(1, criteria);
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('シートA'), true);
};
これで可能なはずだが、なんかもうちょい短く出来んもんか、と考えた結果↓
function myFunction() {
var spreadsheet = SpreadsheetApp.getActive();
var criteria = SpreadsheetApp.newFilterCriteria()
.setHiddenValues(['TRUE'])
.build();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('シートB'), true).getFilter().setColumnFilterCriteria(1, criteria);
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('シートA'), true);
};
こうなった。
このスクリプトを編集条件のトリガーで動かすようにすればええやろ！！！
と思いやってみたところ、
一文字打つごとにシートが切り替わってクソだった。
よくよく考えればそりゃそうである、一文字打とうが消そうが、編集は編集。編集したら実行されるわけだから当然なのだ。
このままだと全く使えない。実用性がゼロである。
というわけで、改めて、表題の件をスマートにやる方法を模索。
ちなみに分かりやすかったのはここ↓
GASのgetActiveSpreadsheetでできる機能まとめ – Google Apps Script(GAS)を用いたシステム開発を支援
まず、
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName('シート名');
で非アクティブなシートをわざわざ選択せずに取得できるとの事。これだよこれ！！これを探してたのだ！！(セットしねーでゲットしろってことだな！？)
つまり、
sheetに取得したいシート名を代入する
わけである。
そして、
spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(1, criteria);のspreadsheet.getActiveSheet()部分を、sheetに変更すれば…。
【以下完成形】
実行するコード①↓
function jikkoucode() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName('シートB');
var criteria = SpreadsheetApp.newFilterCriteria()
.setHiddenValues(['TRUE'])
.build();
sheet.getFilter().setColumnFilterCriteria(1, criteria);
}
※function(関数)名をjikkoucode()に変更した。
応用↓
シートBの12列目を「サービス部」以外が表示されるようにフィルタをかけたい、とかいう場合は、
function tes() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName('シートB');
var criteria = SpreadsheetApp.newFilterCriteria()
.setHiddenValues(['サービス部'])
.build()
sheet.getFilter().setColumnFilterCriteria(12, criteria);
}
こう。
ちなみに面倒な事に、GASでフィルタをかけるときには非表示値の指定は出来るが、表示値の指定は出来ない。
(setHiddenValuesがあるなら、setVisibleValuesあるやろ、と思い確認するとあったにはあったのだが、現状サポートされて無いとの事で使えなかった。無いのと変わらんやんけ！！！)
これを、特定シートの特定列セルが編集された場合のみスクリプト実行させたいので、
function onEdit(e) { }で条件分岐するように書いていく。※トリガーを【編集時】にするのと同じだろうが今回は見える形としてコードにすることに。
トリガー用コード②↓
function onEdit(e) {
var acsheet = e.source.getActiveSheet();
var cell = e.source.getActiveRange();
if (acsheet.getName() == 'シートA' && cell.getColumn() == 1) {
jikkoucode();
}
}
これで、
アクティブシートがシートA、且つ、アクティブセルがcolumn1のA列のときに編集された場合(つまりシートAのA列を編集した場合)→jikkoucode()を呼び出し実行する。
というわけだ。
念の為捕捉すると、
①と②をセットで書けばいいわけなので、
最終的な形は、
function jikkoucode() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName('シートB');
var criteria = SpreadsheetApp.newFilterCriteria()
.setHiddenValues(['TRUE'])
.build();
sheet.getFilter().setColumnFilterCriteria(1, criteria);
}
function onEdit(e) {
var acsheet = e.source.getActiveSheet();
var cell = e.source.getActiveRange();
if (acsheet.getName() == 'シートA' && cell.getColumn() == 1) {
jikkoucode();
}
}
となるわけだ。
以上！
GASに関してはまだまだぴよっこ状態だが、今後使いながら徐々にやりたいことだけ習得できれば…と思う。が、次回はGASへの不満を述べていく。
ちょっとVBAと比較したときに気に食わんところがあるんだよな。初心者過ぎてかもしれんが。
*1:記事にするため前でまとめてあったものを改めて読み返すと、マジ何言ってるかわからん部分が多々あってビビった。時間置いて見返すの大事だな。
