【スプレッドシート】範囲保護しつつフィルタを利用する方法

こんにちは、ちーかまです！
初めてブログ担当になってフレッシュな記事を書いてから早7ヶ月、少しづつエンジニアとしての自信がついてきた今日このごろです。

(ちなみに過去記事はこちら！) aimstogeek.hatenablog.com
最近業務で大勢の人とスプレッドシートを共有しながら使う機会が多いのですが、その中で悩んでいることがあります。
それは編集されたくない箇所が他の人によって編集されてしまうこと！
ならば範囲保護をしようという考えに至ったのですが、今度は範囲保護の編集権限を持たない人がフィルタが使えなくなってしまい作業効率が落ちる...

そこで今回は、スプレッドシートで範囲保護しつつフィルタを利用する方法を考えてみました。

今回使用するスプレッドシート
1.データ保持用のシートと作業用シートを分ける
2.フィルタ表示を用意してあげる
- フィルタ表示作成をGASで行う
まとめ

今回使用するスプレッドシート

サンプルとして、下記のようなスプレッドシートを用意しました。

f:id:aimstogeek:20210304114345p:plain

B列・C列は入力が行われる作業用の列、A列・D列・E列は問い合わせに関する情報が入っているため編集されたくない列、という構成になっています。
それではさっそく解決方法を紹介していきます！

1.データ保持用のシートと作業用シートを分ける

1つ目に紹介するのは、データ保持用のシートと作業用シートを分けて、作業用シートにデータを読み込ませるという方法です！以下で手順を説明します。

Step1 データ保持用のシートを作成する

まずサンプルのA列・D列・E列のみが記載されている、データ保持用のシートを作成します。今回このシート名を「マスタ」に設定しました。

f:id:aimstogeek:20210304161852p:plain

Step2 作業用のシートを作成する

次にサンプルのB列・C列のみが記載されている、作業用のシートを作成します。

f:id:aimstogeek:20210304161911p:plain

Step3 作業用のシートにデータを読み込ませる

最後に、ARRAYFORMULA関数を使用してデータを読み込ませて完了です！

f:id:aimstogeek:20210304164937p:plain

注意点

とても簡単なこの方法ですが、実は注意しなければならない点があります。それは並び替えを使うとデータと入力内容がリンクしなくなるということです。
実際に担当者の昇順で並び替えた後のサンプルを見てみましょう。

f:id:aimstogeek:20210304165512p:plain

B列・C列が並び替えられているのに対し、A列・D列・E列が並び替えられていないことがわかるでしょうか？A列・D列・E列は別シートを読み込んでいるため、並び替えを行った後も再度読み込みが行われ順番が元に戻ってしまうのです。
データ保持用のシートと作業用シートを分けて運用する場合は、並び替え禁止というルールを周知させることを忘れないようにしてください。

2.フィルタ表示を用意してあげる

2つ目に紹介するのは、範囲保護の編集権限を持つ人が代わりにフィルタ表示を作成してあげるという方法です！
...と言っても、みんなが必要な絞り込みのパターン分フィルタ表示を作成してたら業務どころではないですよね？ということでGASを使ってフィルタ表示作成を自動化してみました。

フィルタ表示作成をGASで行う

以下で手順を説明していきます。
同じ形式のシートを作成してスクリプト内にあるシート名称を変更するだけで使えるようになっているため、もしよろしければ試してみてください！

Step1 絞り込み条件を入力するシートを作成する

まず追加してほしい絞り込み条件を入力するためのシートを作成します。
A列に「フィルタ名」と記載し、B列以降にフィルタを追加したいシートのヘッダーをそのままコピペすれば完了です。

f:id:aimstogeek:20210304174722p:plain

あわせて入力方法も説明していきます。
A列はフィルタ表示の名称を登録する列です。未記入の場合はデフォルト名である「フィルタ(数字)」という名称で追加されます。
B列以降に単語を入力すると、「その単語が含まれる」という条件で絞り込みが作成されます。「該当列になにも記入されていない」という条件で絞り込みを行いたい場合は、「空欄」と記入するようにしてください。

f:id:aimstogeek:20210304174028p:plain

Step2 Google Sheets APIを有効にする

GASでフィルタ表示を操作するためには、Google Sheets APIを使用する必要があります。メニューよりツール > スクリプトエディタを選択した後、下記のように操作してGoogle Sheets APIを有効にしてください。

f:id:aimstogeek:20210304185713g:plain

Step3 スクリプトを書く

フィルタ表示を追加するためのスクリプトを書いていきます。
下記が実際に書いてみたスクリプトです。

function addFilterView() {
  // アクティブなスプレッドシートのIDを取得
const ss = SpreadsheetApp.getActiveSpreadsheet()
const ssId = ss.getId()
  // フィルタービューを追加するシートのIDを取得
const sh = ss.getSheetByName("フィルタ表示を追加したいシートの名称");
const shId = sh.getSheetId()
  // 絞り込み条件を入力するシートを取得
const iSh = ss.getSheetByName("絞り込み条件を入力するシートの名称");
  // 依頼シートの最終行と最終列を取得
const lastRow = iSh.getLastRow()
const lastColumn = iSh.getLastColumn()
const requests = [] // フィルタ追加のリクエストデータを登録する配列

 // フィルタビュー追加用のJSONデータを生成する
for(let row = 2; row <= lastRow; row++) {
  const title = iSh.getRange(row, 1).getValue(); // フィルタ名取得
  var request = '[{"addFilterView": {"filter": {"range": {"sheetId": ' + shId + '}, "title": "' + title + '", "criteria": {'
  const conditions = [] // 絞り込み条件を作成するための配列
  for(let column = 2; column <= lastColumn; column++) {
  　const condition = iSh.getRange(row, column).getValue();
    if(condition !== '') {
      if(condition === '空欄') {
        conditions.push('"' + (column-2) + '": {"condition": {"type": "BLANK"}}')
      } else {
        conditions.push('"' + (column-2) + '": {"condition": {"type": "TEXT_CONTAINS", "values":[{"userEnteredValue": "' + condition + '"}]}}')
      }
    }
  }
  if(conditions.length !== 0) {
    request += conditions.join(', ')
  }
  request += '}}}}]'
  requests.push(JSON.parse(request))
}

　// フィルタビューを追加
if (requests.length > 0) {
  Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId)
   // 追加済みの依頼内容をシートから削除
  iSh.getRange(2, 1, lastRow, lastColumn).clearContent();
} else {
  return
}
};

よくわからない...という場合は上記をコピペして、「フィルタ表示を追加したいシートの名称」と「絞り込み条件を入力するシートの名称」を書き換えれば動きます！

Step4 実際にフィルタ表示を追加してみる

それではスクリプトを実行して、フィルタ表示を追加してみましょう！

f:id:aimstogeek:20210304185507g:plain

やったー、無事追加できていますね！！この後確認しましたが絞り込みの条件も問題ありませんでした！
何度も同じフィルタ表示を追加しないように、絞り込み条件を入力するシートから入力内容を消す処理もきちんと動いてます。