スプレッドシートの個人フィルタをGoogle Apps Scriptで作成する方法

はじめに

Googleのスプレッドシートでフィルタを個人で作成してもらう運用をしていると、
フィルタ１などよくわからないフィルタが増えすぎて困ることってよくありますよね。
なので最初からある程度グループなどでまとめたフィルタを作っておいてそれぞれそれを利用してもらえば、
綺麗な運用ができるんじゃないかという発想です。

記事の目的

デフォルトの機能だけではフィルタを大量に作成することはできないので
GAS(Google Apps Script)を利用してフィルタを作成していきます。

必要な前提知識

Spreadsheetのフィルタとフィルタ表示

「フィルタ」と「フィルタ表示(Filter View)」というものがあります。
フィルタは全体にかかるもの、フィルタ表示は全体の表示は邪魔せずに個人単位でフィルタを作成できるものです。

下記の画像の「フィルタを作成」からフィルタが、
「フィルタ表示 > 新しいフィルタ表示を作成」からフィルタ表示を作成することができます。

Google Apps Script でフィルタ表示を作成する

サービスでSheets APIを有効にする

サービスでGoogle Sheets APIを選択しSheets APIを利用できるようにする。
サービスの横の「＋」ボタンから「Google Sheets API」を選択し、サービスを有効化します。

GASでフィルタ表示を作る

/**
 * スプレッドシートの特定のシートに複数のフィルタ表示を作成します。
 * @param {string} ssId - スプレッドシートのID。
 * @param {string} sheetName - フィルタを作成するシートの名前。
 * @param {number} filterColumnNum - フィルタを適用する列の番号（0から始まる）。
 * @param {string[]} filterNameArray - 作成するフィルタの名前の配列。
 */
const main = () => {
  // スプレッドシートの取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // IDの取得
  const ssId = ss.getId();

  // フィルタを作るシート
  const sheetName = 'フィルタを作るシート';
  // 作成するフィルタの配列
  const filterNameArray = ['フィルターA', 'フィルターB']

  // フィルタ削除(重複するとエラー出るのでいったん削除)
  deleteAllFilters(ssId, sheetName);

  // フィルタ作成
  createFilterView(ssId, sheetName, filterNameArray);
}

/**
 * 指定したスプレッドシートIDのシートに対してフィルタ表示を作成します。
 * @param {string} ssId - スプレッドシートのID。
 * @param {string} sheetName - フィルタ表示を作成するシートの名前。
 * @param {string[]} filterNameArray - 作成するフィルタ表示の名前の配列。
 */
const createFilterView = (ssId, sheetName, filterNameArray) => {
  // シートの取得
  const sheet = SpreadsheetApp.openById(ssId).getSheetByName(sheetName);

  // IDの取得
  const sheetIds = getSheetIds(ssId);
  const sheetObj = sheetIds.find(s => s.sheetName === sheetName);
  // シート有無の判定
  if(!sheetObj) throw new Error('シートがないです');
  const sheetId = sheetObj.sheetId; // 実際のsheetIdの取得

  // シートが空かどうかのチェック
  if (sheet.getLastRow() === 0 || sheet.getLastColumn() === 0) {
    throw new Error('指定されたシートは空です。');
  }

  Logger.log(filterNameArray)
  // フィルタ用リクエストの作成
  const batchUpdateRequests = filterNameArray.map((target) => ({
    addFilterView: {
      filter: {
        title: target,
        range: {
          sheetId: sheetId,
          startRowIndex: 0,
          endRowIndex: sheet.getLastRow(),
          startColumnIndex: 0,
          endColumnIndex: sheet.getLastColumn(),
        }
      },
    },
  }));

  const requests = {
    requests: batchUpdateRequests,
  };

  Sheets.Spreadsheets.batchUpdate(requests, ssId);
}

/**
 * 指定したスプレッドシートのすべてのシートIDを取得します。
 * @param {string} spreadsheetId - スプレッドシートのID。
 * @returns {Object[]} シート名とシートIDを含むオブジェクトの配列。
 */
const getSheetIds = (spreadsheetId) => {

  // スプレッドシートのメタデータを取得
  const spreadsheet = Sheets.Spreadsheets.get(spreadsheetId);

  // シートのIDを格納するための配列
  const sheetIds = [];

  // 各シートの情報をループしてIDを抽出
  spreadsheet.sheets.forEach(function(sheet) {
    sheetIds.push({
      sheetName: sheet.properties.title,
      sheetId: sheet.properties.sheetId
    });
  });

  return sheetIds;
}

/**
 * 指定したスプレッドシートの特定のシートからすべてのフィルタ表示を削除します。
 * @param {string} spreadsheetId - スプレッドシートのID。
 * @param {string} sheetName - フィルタを削除するシートの名前。
 */
const deleteAllFilters = (spreadsheetId, sheetName) => {
  const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
  const sheetId = sheet.getSheetId();

  // そのシートのフィルタ表示を取得
  const spreadsheet = Sheets.Spreadsheets.get(spreadsheetId);
  const filters = spreadsheet.sheets.find(s => s.properties.sheetId === sheetId).filterViews;

  if (filters && filters.length > 0) {
    const requests = filters.map(filter => {
      return {
        deleteFilterView: {
          filterId: filter.filterViewId
        }
      };
    });

    Sheets.Spreadsheets.batchUpdate({requests: requests}, spreadsheetId);
  } else {
    Logger.log('フィルタは見つかりませんでした。');
  }
}

コードの概要

実行結果

こんな感じになります。
「フィルターA」、「フィルターB」を選択すると、フィルタ表示が表示されます。

main 関数

• この関数は、スクリプトのメインの実行ポイント
• スプレッドシートのIDを取得し、フィルタを作成するシートの名前と、作成するフィルタの名前の配列を定義
• 既存のすべてのフィルタ表示を削除し、新しいフィルタ表示を作成

createFilterView 関数

• この関数は、指定されたスプレッドシートの指定されたシートにフィルタ表示を作成
• スプレッドシートとシートのオブジェクトを取得し、シートIDを特定
• 指定されたフィルタの名前の配列を使用して、各フィルタ表示のためのリクエストを作成
• 作成したリクエストを使って、バッチ更新操作を実行し、複数のフィルタ表示を一度に作成

getSheetIds 関数

• この関数は、指定されたスプレッドシートのすべてのシートのIDを取得
• スプレッドシートのメタデータを取得し、各シートの名前とIDを含むオブジェクトの配列を返却

deleteAllFilters 関数

• この関数は、指定されたスプレッドシートの特定のシートからすべてのフィルタ表示を削除
• 指定されたシートのフィルタ表示を取得し、それらを一つずつ削除するリクエストを作成
• 作成したリクエストを使って、バッチ更新操作を実行し、すべてのフィルタ表示を削除

コードの使用方法

main関数を実行することで、指定されたスプレッドシートの指定されたシートにフィルタ表示が作成されます。
main関数内で指定されたシート名やフィルタ名を変更することで、異なるシートやフィルタの設定が可能です。

フィルタした状態のフィルタ表示(Filter View)の作成

フィルタを作るだけでなく、最初からフィルタをかけた状態にしたい場合も考えられます。いろいろフィルタのやり方は考えられますが、今回はとある列の値の数だけフィルタを作成し、フィルタの値だけを選択した状態のフィルタ表示を作成するサンプルを作成してみました。

コード

const main2 = () => {
  // スプレッドシートとシートの取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetName = 'フィルタを作るシート';
  const sheet = ss.getSheetByName(sheetName);
  const ssId = ss.getId();

  // B列（2列目）のデータを取得
  const dataRange = sheet.getRange(2, 2, sheet.getLastRow() - 1, 1);
  const dataValues = dataRange.getValues();

  // 重複を排除して一意の値を取得
  const uniqueValues = [...new Set(dataValues.flat())];

  // フィルタ削除(重複するとエラー出るのでいったん削除)
  deleteAllFilters(ssId, sheetName);

  // 一意の値をフィルタ名の配列として使用
  const filterNameArray = uniqueValues;

  // 指定した列（B列）に対してフィルタ表示を作成
  const filterColumnNum = 1; // B列（1として数える）
  createFilterViewWithSpecs(ssId, 'フィルタを作るシート', filterColumnNum, filterNameArray);
};

/**
 * 指定されたスプレッドシートの特定のシートに、指定された列に基づいたフィルタ表示を作成します。
 * @param {string} ssId - スプレッドシートのID。
 * @param {string} sheetName - フィルタ表示を作成するシートの名前。
 * @param {number} filterColumnNum - フィルタを適用する列の番号（0から始まる）。
 * @param {string[]} filterNameArray - 除外する値のリスト。
 */
const createFilterViewWithSpecs = (ssId, sheetName, filterColumnNum, filterNameArray) => {
  // シートの取得
  const sheet = SpreadsheetApp.openById(ssId).getSheetByName(sheetName);
  const sheetId = getSheetIds(ssId).find(s => s.sheetName === sheetName).sheetId;

  // 各フィルタ名に対してフィルタ表示を作成
  filterNameArray.forEach(target => {
    const requests = {
      requests: [{
        addFilterView: {
          filter: {
            title: target,
            range: {
              sheetId: sheetId,
              startRowIndex: 0,
              endRowIndex: sheet.getLastRow(),
              startColumnIndex: 0,
              endColumnIndex: sheet.getLastColumn(),
            },
            filterSpecs: [
              {
                columnIndex: filterColumnNum,
                filterCriteria: {
                  hiddenValues: filterNameArray.filter((item) => item !== target),
                },
              },
            ],
          },
        },
      }],
    };

    Sheets.Spreadsheets.batchUpdate(requests, ssId);
  });
};

コードの概要

実行結果

こんな感じになります。

main2 関数

• スプレッドシートの特定のシートを取得します。
• B列（2列目）からデータを取得し、その列の一意の値を抽出します。
• 既存のフィルタ表示をすべて削除します（重複を避けるため）。
• 抽出した一意の値を使って、B列に基づく複数のフィルタ表示を作成します。

createFilterViewWithSpecs 関数

• 指定されたスプレッドシートの特定のシートに対して、特定の列を基準とするフィルタ表示を作成します。
• フィルタ表示は、指定された列の一意の値ごとに作成されます。
• 各フィルタ表示は、その値を表示し、他の値を非表示にするように設定されます。

注意点

• スプレッドシートには、対象となる列にデータが必要です。
• 指定された列に基づいて、列内の各一意の値ごとに個別のフィルタ表示を作成します。
• このスクリプトは、データを特定の基準で簡単にフィルタリングしたい場合や、スプレッドシート内のデータをさまざまな視点から見たい場合に便利です。

さいごに

データが少ない場合は問題ないですが、増えてくると手動では厳しいですし、
フィルタ条件もいろいろと指定できるので、
見せたい角度などがあれば自動化してフィルタ表示を作成するのもありなのかと思います。