こんにちは、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で書く方法があるようなので次回に試してみたいと思います。
参考
以上
15
