Zenn
🐈

[GAS] スプレッドシートのフィルタでランダムな列を抽出

2022/07/20に公開
GAS
Google Apps Script
Google Sheets
tech

これはなに？

スプレッドシートのフィルタを操作し、ランダムな列を抽出・表示するスクリプトを作ったので、共有します。

概説

私は語学の勉強が好きで、なるべく効率よく弱点が克服出来るように、学んだ単語のうち、苦手なものをスプレッドシートにまとめて管理したりしています。

日々本やアプリなどを行ったり来たりして勉強する内に、「スプレッドシートがそのまま復習ツールとして使えたら便利なのでは…？」と思い至り、GASを使ったランダム出題機能を実装してみました[1]。

スクリプトの内容としては、フィルタでランダムな列を抽出するだけの汎用的なものです。

個人的には乱数を使ったフィルタは需要がありそうだと思うのですが、英語圏の方を含め、検索した限りでは実装している人がほとんどおらず、せっかくなので共有することにしました。

コード

早速、以下にコードを載せます。効率が悪い処理をしている箇所もあると思いますが、コードの後で解説します。

なお、載せているコードでは、A列に入っている整数IDをキーにして、フィルタリングをします。

[BEFORE] こんな感じの構造のスプレッドシートのデータから…


[AFTER] 一瞬でランダムな列を抽出します

なお、スクリプトの中でA列を指定している箇所が14行目と42行目にあるので、他の列の値を使ってフィルタリングをしたければ、そこをいじればOKです。

function onOpen() {
  let ui = SpreadsheetApp.getUi();
  let menu = ui.createMenu('フィルタ');
  menu.addItem('ランダムフィルタを設定', 'setRandomFilter');
  menu.addToUi(); 
}


function setRandomFilter() {
  let random_amount = 10; // フィルタの表示列数を指定

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let last_col = sheet.getLastColumn();
  let vals = sheet.getRange("A1:A").getValues(); // A列に入っている値を抽出
  let last_row = vals.filter(String).length; // A列の空でないセルのうち、一番最後の列番号を取得

  let random_numbers = [];

  // 重複のない乱数をrandom_amountに代入された個数分、配列に格納
  for(let i=0; i < random_amount; i++){
    let new_number = Math.floor(Math.random() * last_row) + 1;
    while (random_numbers.indexOf(new_number)+1){
      new_number = Math.floor(Math.random() * last_row) + 1;
    }
    random_numbers.push(new_number);
  }

  // 全部の列から、表示される分を抜いた [非表示ID配列] を作成
  let list_of_hidden_rows = [];
  for (let i=0; i < last_row; i++){
    if (random_numbers.indexOf(i)+1){
      // indexOfでtrueだった場合の処理
      // 今回は空白
    }
    else{
      list_of_hidden_rows.push(i);
    }
  }
  
  let criteria = SpreadsheetApp.newFilterCriteria()
                              .setHiddenValues(list_of_hidden_rows)
                              .whenCellNotEmpty()
                              .build();
  sheet.getFilter().setColumnFilterCriteria(1, criteria); // 数字の'1'でA列を指定 / '2'を代入すればB列がフィルタの対象になります
}

躓きポイントと、非効率な処理について

GASは非常にパワフルなのですが、ネット上で見つかる情報が多いわけでもなく、パーミッション関係や関数の仕様などで、躓くことが多かったです。以下で少し言及します。

(1) onOpen()では何をしてるの？

スプレッドシートの画面には、カスタムのメニューを追加することができます。

実はGASの機能には、こういったメニューからしか呼び出せないものも多いようで、今回使っているsetColumnFilterCriteria関数もそれに当てはまります。

そのため、ここではカスタムメニューを実装する処理をしています。

こんな感じのメニューが表示されるようになります

(2) setHiddenValuesじゃなくて、setVisibleValues使えばいいのでは？

表示したい行が10行とか20行なのにsetHiddenValuesを使うのは非効率ですよね。

本当であれば、表示する列だけを配列に代入してsetVisibleValues関数に渡せば良いと思うのですが、setHiddenValues関数はピポットテーブルでしか使えないようです。

ですので、非表示にする列をsetHiddenValues関数で指定しています。非表示列が膨大な数になり、なかなか非効率だと思うのですが仕様上仕方がありません。

(3) パーミッション関連でエラーが出る

GASではスクリプトエディタで表示されるエラーと、スプレッドシートの画面で表示されるエラーが違ってたりして、エラーの原因がわかりづらいことがままあります。

😡
you do not have permission to call setColumnFilterCriteria()

みたいなエラーに延々と悩まされました[2]。

なお、パーミッションについてエラーが出る際は、Oauthスコープの問題だったりすることがあり、その場合は以下の記事に倣って権限の設定をすればエラーが解消されます。

https://zenn.dev/seya/articles/709659242512a7

今回のスクリプトでは、以下のスコープを設定すれば動くはずです[3]。

  "oauthScopes" : [
    "https://www.googleapis.com/auth/spreadsheets", 
    "https://www.googleapis.com/auth/spreadsheets.readonly",
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
    ]

おわり

スプレッドシートのフィルタは非常に便利ですが、今回のように要素を個別に指定して表示・非表示を切り替えるのは、列数が多くなると結構面倒だったりします。GASを使って、そこらへんの処理が効率的にできそうだと知れたのが収穫でした。

普段GASはほとんど使わないので、もっと効率よく書ける箇所もあるとは思います。改善点等あれば、コメントをいただければ幸いです。

脚注

  1. 元々はスプレッドシートの乱数関数とセル参照で同じような機能を再現していたのですが、セル参照だと出題された問題にそのまま書き込みなどができず、勉強も兼ねてGASを使うことにしました。 ↩︎

  2. GASでpermissionについて言及されている場合、ユーザに権限がない場合と、関数の仕様上使えない場所で使おうとしている場合があり、どちらにあたるはスクリプトエディタで実行して調べるのがベストです。…が、それでも分からないこともあります。ちなみにこのエラーは、セルから関数としてスクリプトを呼び出したために表示されたもので、カスタムメニューから関数にアクセスするようにしたら解決しました。GASのエラーメッセージは、不親切に感じられる場合がありますね。 ↩︎

  3. https://developers.google.com/apps-script/reference/spreadsheet/slicer#setcolumnfiltercriteriacolumnposition,-filtercriteria ↩︎

Discussion