[Google Spread Sheet] 選択した行の値でテーブルをフィルターできる機能をGASで作る

こんにちは、CX事業本部 IoT事業部の若槻です。

今回は、Google Spread Sheetで選択したセルの値でテーブルをフィルターできる機能をGoogle Apps Script（GAS）で作ってみました。

作ってみた

やりたいこととしては、次のようなスプレッドシート上のテーブルで、選択したレコードの特定列（B,C,D）が同じ値のレコードをフィルター表示したいというものです。

GASコード

次のようなGASのコードを作りました。

Code.gs

const sheet = SpreadsheetApp.getActiveSpreadsheet();

const applyFilter = () => {
  const activeRange = sheet.getActiveRange();

  const selectedRow = activeRange.getRowIndex();

  const columnBValue = sheet.getRange("B" + selectedRow).getValue();
  const columnCValue = sheet.getRange("C" + selectedRow).getValue();
  const columnDValue = sheet.getRange("D" + selectedRow).getValue();

  const setBasicFilterRequest = {
    'setBasicFilter': {
      'filter': {
        'range': {
          "sheetId": sheet.getSheetId(),
          "startRowIndex": 0,
          "endRowIndex": 1000,
          "startColumnIndex": 0,
          "endColumnIndex": 4
        },
        'filterSpecs': [
          {
            'filterCriteria': {
              'condition': {
                'type': 'TEXT_EQ',
                'values': {
                  'userEnteredValue': columnBValue
                }
              }
            },
            'columnIndex': 1
          },
          {
            'filterCriteria': {
              'condition': {
                'type': 'TEXT_EQ',
                'values': {
                  'userEnteredValue': columnCValue
                }
              }
            },
            'columnIndex': 2
          },
          {
            'filterCriteria': {
              'condition': {
                'type': 'TEXT_EQ',
                'values': {
                  'userEnteredValue': columnDValue
                }
              }
            },
            'columnIndex': 3
          }
        ]
      }
    }
  };

  Sheets.Spreadsheets.batchUpdate({'requests': [setBasicFilterRequest]}, sheet.getId());
};

const onOpen = () => {
  const menu = [{name: "filter", functionName: "applyFilter"}];
  sheet.addMenu("Custom", menu);
};

サービスのAPIを追加

batchUpdateはスプレッドシートのAPIなので、プロジェクトがサービスとして利用可能にする必要があります。

エディター画面で[Servives]の[+]をクリック。

Google Sheets APIを選択して[Add]をクリック。

追加されました。

動作確認

スプレッドシートを画面更新するとメニューバーにメニューが追加されています。

メニューからスクリプトを実行しようとすると、初回に権限の付与が必要なようなので実施します。

改めてスクリプトを実行します。

同じ値のレコードがフィルターされました！

ちなみにセル選択でもその行の情報で同じようにフィルターが可能です。

複数行のセル範囲を選択した場合は、1番上のセルの行でフィルターが行われます。

遭遇したエラー

今回の機能を作る際に様々なエラーに遭遇しました。

Script function not found

スクリプト実行時に次のエラーが発生しました。

Script function not found: <スクリプトの内容>

スクリプトの構文が誤っている場合に発生するエラーでした。この時は次の箇所の構文が誤っていました。

Code.gs

const onOpen = () => {
  const menu = [{name: "filter", functionName: applyFilter}]; //誤：applyFilter 正："applyFilter"
  sheet.addMenu("Custom", menu);
};

ReferenceError: Sheets is not defined

スクリプト実行時に次のエラーが発生しました。

ReferenceError: Sheets is not defined

サービスのAPIがプロジェクトで未追加だったため、追加したら解消しました。

前述の[サービスのAPIを追加]手順を忘れないようにしましょう。

Requested entity was not found.

スクリプト実行時に次のエラーが発生しました。

GoogleJsonResponseException: API call to sheets.spreadsheets.batchUpdate failed with error: Requested entity was not found.

batchUpdateでの指定が誤っていました。ここではスプレッドシートのID（sheet.getId()により取得できる）を指定する必要がありました。

Code.gs

  Sheets.Spreadsheets.batchUpdate({'requests': [setBasicFilterRequest]}, sheet.getSheetId()); //誤：sheet.getSheetId() 正：sheet.getId()

API call to sheets.spreadsheets.batchUpdate failed with error

スクリプト実行時に次のエラーが発生しました。

GoogleJsonResponseException: API call to sheets.spreadsheets.batchUpdate failed with error: Invalid value at 'requests[0].set_basic_filter.filter.range.sheet_id' (TYPE_INT32), "1bcdtb1sCGvMCe2Go1NBRTe6zDi7jVPWY0vwzjexCAes"

setBasicFilterでの指定が誤っていました。ここではシートのID（sheet.getSheetId()により取得できる）を指定する必要がありました。

Code.gs

    const setBasicFilterRequest = {
    'setBasicFilter': {
      'filter': {
        'range': {
          "sheetId": sheet.getId(), //誤：sheet.getId() 正：sheet.getSheetId()

おわりに

Google Spread Sheetで選択したセルの値でテーブルをフィルターできる機能をGoogle Apps Script（GAS）で作ってみました。

GASを触るのは2年ぶりくらいでしたが、結構ハマりどころがあり苦労しました。

またJavaScriptと同じ構文でしたが、TypeScriptとは異なり型支援がないのも辛かったです。これに関しては次の記事の通りGASをTypeScriptで書く方法があるようなので次回に試してみたいと思います。

• Google Apps ScriptをTypeScriptで書いてみた | DevelopersIO

参考

• PythonでGoogle Spread Sheetのフィルターを操作してみた | DevelopersIO

以上