概要

この投稿では、Filter view（フィルタ表示）でフィルタされたシートから Google Apps Script を使用してフィルタされた行を取得する方法について紹介させていただきます。

Google Spreadsheet のフィルタは次の 3 種類が用意されています。

Basic filter（フィルタ（メニューでは「フィルタを作成」として表示））: すべてのユーザーの画面に反映されるようにデータをフィルタする
- フィルタされた行を取得する方法
- このフィルタを設定すると、他のユーザも同様の結果を見ることができます。
Filter view（フィルタ表示）: 共同編集者からの見え方を変えずにデータをフィルタする
- フィルタされた行を取得する方法: この投稿で紹介します。英語版はこちらです。
- このフィルタは、ユーザ毎に設定できます。例えば、3 つのフィルタを用意し、状況に合わせて各ユーザでフィルタを使い分けることができます。
Slicer（スライサー）: スライサーを使用してグラフと表をフィルタする
- フィルタされた行を取得する方法
- このフィルタを設定すると、他のユーザも同様の結果を見ることができます。

これまで Basic filter と Slice については、フィルタされた行の取得は用意されたメソッドから得ることができることを確認してきました。しかしながら、Filter view の場合、クライアント毎に設定が可能なためにこのようなメソッドが存在せず、Filter view でフィルタされた行の取得ができませんでした。最近、このための回避策を見つけましたので、こちらでも紹介させていただきます。これについての問い合わせが多く届いていたことから、他のユーザの方々のお役に立てばと、こちらでも紹介させていただきました。

流れ

この方法では、Sheets API を使用します。そのため、下記のサンプルスクリプトを使用する前に Advanced Google services で Sheets API を有効にしてください。この方法の流れは次の通りです。

Sheets API の spreadsheets.get メソッドを使って Filter view の設定を取得します。
- この場合、各 Filter view の設定は、JSON オブジェクトとして取得することができます。
Sheets API の spreadsheets.batchUpdate メソッドを使って、取得した Filter view の設定オブジェクトから使用したい Filter view を選んで Basic filter として設定します。
- このとき、Filter view の設定内容は Basic filter のそれと同じです。これにより、スムースな変換が可能になります。
設定した Basic filter からフィルタされた行を取得します。
- この場合は、既に報告しているこちらの方法を使用することができます。
設定した Basic filter を削除します。

上記の流れで Filter view でフィルタされた行の取得が可能になります。

また、この方法では、Basic filter を上書きすることになりますので、既に Basic filter を設定している場合は、ご注意ください。

サンプルスクリプト

上記の流れをスクリプトにすると、次のようになります。サンプルスクリプトを実行する前に Advanced Google services で Sheets API を有効にしてください。

function myFunction() {
  const spreadsheetId = "###"; // Please set the Spreadsheet ID.
  const sheetName = "Sheet1"; // Please set the sheet name.
  const filterViewName = "sampleFilter1"; // Please set the filter view name.

  // 1. Retrieve the settings of the filter view (`filterViews`) you want to use.
  const res1 = Sheets.Spreadsheets.get(spreadsheetId, { ranges: [sheetName] });
  const sheetId = res1.sheets[0].properties.sheetId;
  const filterViews = res1.sheets[0].filterViews.filter(
    (e) => e.title == filterViewName
  );
  if (filterViews.length == 0) {
    throw new Error("Filter view cannot be found.");
  }

  // 2. Create new basic filter to the sheet you want to use using the retrieved settings of the filter view.
  const obj = filterViews[0];
  obj.range.sheetId = sheetId;
  const reqs = [
    { clearBasicFilter: { sheetId: sheetId } },
    {
      setBasicFilter: {
        filter: {
          criteria: obj.criteria,
          filterSpecs: obj.filterSpecs,
          range: obj.range,
          sortSpecs: obj.sortSpecs,
        },
      },
    },
  ];
  Sheets.Spreadsheets.batchUpdate({ requests: reqs }, spreadsheetId);

  // 3. Retrieve the values of `rowMetadata` of the sheet.
  const res2 = Sheets.Spreadsheets.get(spreadsheetId, {
    ranges: [sheetName],
    fields: "sheets",
  });
  const values = res2.sheets[0].data[0].rowMetadata.reduce(
    (o, r, i) => {
      if (r.hiddenByFilter && r.hiddenByFilter === true) {
        o.hiddenRows.push(i + 1);
      } else {
        o.showingRows.push(i + 1);
      }
      return o;
    },
    { hiddenRows: [], showingRows: [] }
  );

  // 4. Delete the created basic filter.
  Sheets.Spreadsheets.batchUpdate(
    { requests: [{ clearBasicFilter: { sheetId: sheetId } }] },
    spreadsheetId
  );

  console.log(values);
}

結果

次のようなスプレッドシートがあり、

次のように Filter view が設定されている場合、

上記のスクリプトを実行すると、次のような結果を得ることができます。

{
  "hiddenRows": [2, 3, 5, 6, 8, 9],
  "showingRows": [1, 4, 7, 10, 11, 12, 13, 14, 15]
}

ここで、hiddenRowsとshowingRowsは、それぞれ、Filter view によって隠された行、表示されている行です。

この方法では、Filter view がアクティブでない場合でも、設定さえしていると、Filter view の結果を取得することができます。そのため、例えば、いくつかの Filter view を設定しておき、スクリプトを使って必要な時に必要な Filter view でフィルタされた値を取得することが可能です。

Google Apps Script を使って Google Spreadsheet のフィルタ表示でフィルタリングされた行を取得したい

概要

流れ

サンプルスクリプト

結果