Mengelola visibilitas data dengan filter

Dokumen ini membahas cara menggunakan filter untuk mengurutkan dan memfilter data yang ditampilkan dalam spreadsheet.

Filter memungkinkan Anda mengurutkan dan memfilter data yang Anda lihat saat melihat spreadsheet. Filter tidak mengubah nilai data dalam spreadsheet Anda. Anda dapat menggunakan filter untuk menyembunyikan atau mengurutkan informasi untuk sementara. Data yang cocok dengan kriteria filter yang ditentukan tidak muncul saat filter diaktifkan. Dengan tampilan filter, Anda juga dapat menyimpan berbagai filter bernama dan beralih di antaranya kapan pun Anda mau.

Untuk memfilter data yang ditampilkan dalam permintaan Google Sheets API, gunakan objek DataFilter. Untuk mengetahui informasi selengkapnya, lihat Membaca, menulis, dan menelusuri metadata.

Filter kasus penggunaan

Berikut adalah beberapa contoh kasus penggunaan filter:

  • Urutkan data menurut kolom tertentu. Misalnya, urutkan data pengguna berdasarkan nama belakang.
  • Menyembunyikan data yang memenuhi kondisi tertentu. Misalnya, sembunyikan semua data yang lebih lama dari dua tahun.
  • Menyembunyikan data yang cocok dengan nilai tertentu. Misalnya, sembunyikan semua masalah dengan status "ditutup".

Filter dasar

Objek BasicFilter untuk spreadsheet adalah filter default yang diterapkan setiap kali seseorang melihat spreadsheet. Spreadsheet hanya dapat memiliki satu filter dasar per sheet. Anda dapat menonaktifkan filter dasar dengan menghapusnya. Tindakan ini akan menghapus filter dan semua setelannya dari spreadsheet. Jika ingin mengaktifkan kembali filter yang sama, Anda harus menetapkan kriteria lagi.

Mengelola filter dasar

Untuk menyetel atau menghapus filter dasar, gunakan metode spreadsheets.batchUpdate dengan jenis permintaan yang sesuai:

Untuk mencantumkan filter dasar, gunakan metode spreadsheets.get dan tetapkan parameter URL fields ke sheets/basicFilter. Contoh kode spreadsheets.get berikut menunjukkan URL Google Spreadsheet dengan masker kolom:

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets/basicFilter

Filter tampilan

FilterView adalah filter bernama yang dapat Anda nonaktifkan dan aktifkan kapan saja. Sheet dapat menyimpan beberapa tampilan filter, tetapi Anda hanya dapat menerapkan satu tampilan filter dalam satu waktu. Sheet juga dapat berisi filter dasar dan beberapa tampilan filter, tetapi Anda tidak dapat menerapkan keduanya secara bersamaan pada rentang data yang sama.

Kasus penggunaan tampilan filter

Berikut adalah beberapa contoh kasus penggunaan untuk tampilan filter:

  • Anda memiliki beberapa filter berbeda yang ingin Anda gunakan saat melihat data.
  • Anda tidak memiliki akses edit ke spreadsheet, tetapi Anda masih ingin menerapkan filter. Dalam hal ini, Anda dapat membuat tampilan filter sementara yang hanya dapat dilihat oleh Anda.

  • Anda ingin setiap orang yang Anda ajak berbagi spreadsheet melihat data secara berbeda. Anda dapat menentukan tampilan filter yang ingin diterapkan dengan memberikan spreadsheetId dan filterViewId dalam URL spreadsheet. Untuk melakukannya, gunakan filterViewId yang ditampilkan dalam respons saat Anda membuat tampilan filter.

    Contoh kode berikut menunjukkan URL Spreadsheet dengan tampilan filter:

    https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit#gid=0&fvid=FILTER_VIEW_ID

Mengelola tampilan filter

Untuk membuat, menduplikasi, mengubah, atau menghapus tampilan filter, gunakan metode spreadsheets.batchUpdate dengan jenis permintaan yang sesuai:

Untuk mencantumkan semua tampilan filter, gunakan metode spreadsheets.get dan tetapkan parameter URL fields ke sheets/filterViews. Contoh kode spreadsheets.get berikut menunjukkan URL Spreadsheet dengan masker kolom:

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets/filterViews

Representasi JSON filter

Contoh kode berikut menunjukkan representasi JSON untuk objek FilterView. Objek BasicFilter sama, kecuali tidak memiliki kolom filterViewId dan title, dan tidak dapat menggunakan rentang bernama.

{
  "filterViewId": number,
  "title": string,
  "range": {
    object(GridRange)
  },
  "namedRangeId": string,
  "sortSpecs": [
    {
      object(SortSpec)
    }
  ],
  "criteria": {
    string: {
      object(FilterCriteria)
    },
    ...
  }
}

Contoh data penjualan

Bagian selebihnya dari dokumen ini merujuk pada tabel data penjualan contoh berikut:

Tabel 1. Data penjualan sampel
A B C D E F G
1 Kategori Item Nomor Model Biaya Kuantitas Wilayah Staf Penjualan Tanggal Pengiriman
2 Wheel W-24 $20,50 4 Barat Beth 1/3/2016
3 Pintu D-01X $15,00 2 Selatan Amir 15/3/2016
4 Bingkai FR-0B1 $34,00 8 Timur Hannah 12/3/2016
5 Panel P-034 $6,00 4 Utara Devyn 15/3/2016
6 Panel P-052 $11,50 7 Timur Erik 16/5/2016
7 Wheel W-24 $20,50 11 Selatan Sheldon 30/4/2016
8 Mesin ENG-0161 $330,00 2 Utara Jessie 2/7/2016

Spesifikasi pengurutan

Filter dapat memiliki beberapa spesifikasi pengurutan. Spesifikasi ini menentukan cara mengurutkan data dan diterapkan dalam urutan yang ditentukan. Atribut SortSpec.dimensionIndex menentukan indeks kolom yang akan diterapkan pengurutan.

Contoh kode berikut menunjukkan spesifikasi pengurutan:

[
  {
    "dimensionIndex": 3,
    "sortOrder": "ASCENDING"
  },
  {
    "dimensionIndex": 6,
    "sortOrder": "ASCENDING"
  }
]

Jika diterapkan ke data penjualan contoh, spesifikasi ini akan mengurutkan menurut kolom "Jumlah" terlebih dahulu, lalu, jika dua baris memiliki jumlah yang sama, menurut "Tanggal Pengiriman".

Tabel 2. Data penjualan diurutkan menurut dua kolom
A B C D E F G
1 Kategori Item Nomor Model Biaya Kuantitas Wilayah Staf Penjualan Tanggal Pengiriman
2 Pintu D-01X $15,00 2 Selatan Amir 15/3/2016
3 Mesin ENG-0161 $330,00 2 Utara Jessie 2/7/2016
4 Wheel W-24 $20,50 4 Barat Beth 1/3/2016
5 Panel P-034 $6,00 4 Utara Devyn 15/3/2016
6 Panel P-052 $11,50 7 Timur Erik 16/5/2016
7 Bingkai FR-0B1 $34,00 8 Timur Hannah 12/3/2016
8 Wheel W-24 $20,50 11 Selatan Sheldon 30/4/2016

Kriteria filter

Objek FilterCriteria menentukan data spreadsheet mana yang ditampilkan atau disembunyikan dalam filter dasar atau tampilan filter. Setiap kriteria bergantung pada nilai dalam kolom tertentu. Anda memberikan kriteria filter sebagai peta dengan kunci berupa indeks kolom, dan nilai berupa kriteria.

Untuk kriteria yang ditentukan menggunakan boolean condition, kondisinya harus true agar nilai dapat ditampilkan. Kondisi tidak menggantikan hiddenValues. Jika nilai tercantum di hiddenValues, semua kecocokan untuk nilai tetap disembunyikan.

Contoh kode berikut menunjukkan peta kriteria filter:

{
  0: {
    'hiddenValues': ['Panel']
  },
  6: {
    'condition': {
      'type': 'DATE_BEFORE',
      'values': {
        'userEnteredValue': '4/30/2016'
      }
    }
  }
}

Jika diterapkan ke data penjualan contoh, kriteria ini hanya menampilkan baris yang nilai kolom "Kategori Item"-nya bukan "Panel", dan nilai kolom "Tanggal Pengiriman"-nya sebelum "30 April 2016".

Tabel 3. Data penjualan menggunakan kriteria filter
A B C D E F G
1 Kategori Item Nomor Model Biaya Kuantitas Wilayah Staf Penjualan Tanggal Pengiriman
2 Wheel W-24 $20,50 4 Barat Beth 1/3/2016
3 Pintu D-01X $15,00 2 Selatan Amir 15/3/2016
4 Bingkai FR-0B1 $34,00 8 Timur Hannah 12/3/2016

Contoh kode tampilan filter

Contoh kode berikut menunjukkan cara membuat tampilan filter, menduplikasinya, lalu memperbarui versi duplikat menggunakan data penjualan contoh.

Java

sheets/snippets/src/main/java/SheetsFilterViews.java
/*
 * Dependencies (Maven):
 * com.google.apis:google-api-services-sheets:v4-rev20220927-2.0.0
 * com.google.auth:google-auth-library-oauth2-http:1.19.0
 */

import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport;
import com.google.api.client.json.gson.GsonFactory;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.*;
import com.google.auth.http.HttpCredentialsAdapter;
import com.google.auth.oauth2.GoogleCredentials;

import java.io.IOException;
import java.security.GeneralSecurityException;
import java.util.*;

public class SheetsFilterViews {

    public static void main(String... args) {
        filterViews("1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k");
    }

    public static void filterViews(String spreadsheetId) {
        try {
            // Load pre-authorized user credentials from the environment.
            // TODO(developer) - See https://developers.google.com/identity for guides on implementing OAuth2.
            GoogleCredentials credentials = GoogleCredentials.getApplicationDefault()
                    .createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS));

            Sheets service = new Sheets.Builder(
                    GoogleNetHttpTransport.newTrustedTransport(),
                    GsonFactory.getDefaultInstance(),
                    new HttpCredentialsAdapter(credentials))
                    .setApplicationName("Sheets Filter Views Sample")
                    .build();

            // --- Step 1: Add Filter View ---
            GridRange myRange = new GridRange()
                    .setSheetId(0)
                    .setStartRowIndex(0)
                    .setStartColumnIndex(0);

            // Construct Criteria for Column 0 (Hidden Values)
            FilterCriteria criteria0 = new FilterCriteria()
                    .setHiddenValues(Collections.singletonList("Panel"));

            // Construct Criteria for Column 6 (Date Condition)
            ConditionValue dateValue = new ConditionValue().setUserEnteredValue("4/30/2016");
            BooleanCondition dateCondition = new BooleanCondition()
                    .setType("DATE_BEFORE")
                    .setValues(Collections.singletonList(dateValue));
            FilterCriteria criteria6 = new FilterCriteria().setCondition(dateCondition);

            // Map criteria to column indices (Note: keys are Strings in Java map)
            Map<String, FilterCriteria> criteriaMap = new HashMap<>();
            criteriaMap.put("0", criteria0);
            criteriaMap.put("6", criteria6);

            FilterView filterView = new FilterView()
                    .setTitle("Sample Filter")
                    .setRange(myRange)
                    .setSortSpecs(Collections.singletonList(
                            new SortSpec().setDimensionIndex(3).setSortOrder("DESCENDING")
                    ))
                    .setCriteria(criteriaMap);

            // --- Step 1: Add Filter View ---
            // (Request construction remains the same)
            // ...
            AddFilterViewRequest addFilterViewRequest = new AddFilterViewRequest().setFilter(filterView);

            BatchUpdateSpreadsheetRequest batchRequest1 = new BatchUpdateSpreadsheetRequest()
                    .setRequests(Collections.singletonList(new Request().setAddFilterView(addFilterViewRequest)));

            BatchUpdateSpreadsheetResponse response1 = service.spreadsheets()
                    .batchUpdate(spreadsheetId, batchRequest1)
                    .execute();

            if (response1.getReplies() == null || response1.getReplies().isEmpty()) {
                System.err.println("Error: No replies returned from AddFilterView request.");
                return;
            }

            Response reply1 = response1.getReplies().get(0);
            if (reply1.getAddFilterView() == null || reply1.getAddFilterView().getFilter() == null) {
                 System.err.println("Error: Response did not contain AddFilterView data.");
                 return;
            }

            int filterId = reply1.getAddFilterView().getFilter().getFilterViewId();

            // --- Step 2: Duplicate Filter View ---
            DuplicateFilterViewRequest duplicateRequest = new DuplicateFilterViewRequest()
                    .setFilterId(filterId);

            BatchUpdateSpreadsheetRequest batchRequest2 = new BatchUpdateSpreadsheetRequest()
                    .setRequests(Collections.singletonList(new Request().setDuplicateFilterView(duplicateRequest)));

            BatchUpdateSpreadsheetResponse response2 = service.spreadsheets()
                    .batchUpdate(spreadsheetId, batchRequest2)
                    .execute();

            if (response2.getReplies() == null || response2.getReplies().isEmpty()) {
                 System.err.println("Error: No replies returned from DuplicateFilterView request.");
                 return;
            }

            Response reply2 = response2.getReplies().get(0);
            if (reply2.getDuplicateFilterView() == null || reply2.getDuplicateFilterView().getFilter() == null) {
                System.err.println("Error: Response did not contain DuplicateFilterView data.");
                return;
            }

            int newFilterId = reply2.getDuplicateFilterView().getFilter().getFilterViewId();

            // --- Step 3: Update Filter View ---
            // Extract the new ID from the duplicate response
            int newFilterId = response2.getReplies().get(0)
                    .getDuplicateFilterView().getFilter().getFilterViewId();

            // Create update criteria
            Map<String, FilterCriteria> updateCriteriaMap = new HashMap<>();
            updateCriteriaMap.put("0", new FilterCriteria()); // Empty criteria

            ConditionValue numValue = new ConditionValue().setUserEnteredValue("5");
            BooleanCondition numCondition = new BooleanCondition()
                    .setType("NUMBER_GREATER")
                    .setValues(Collections.singletonList(numValue));
            updateCriteriaMap.put("3", new FilterCriteria().setCondition(numCondition));

            FilterView updateFilterView = new FilterView()
                    .setFilterViewId(newFilterId)
                    .setTitle("Updated Filter")
                    .setCriteria(updateCriteriaMap);

            UpdateFilterViewRequest updateRequest = new UpdateFilterViewRequest()
                    .setFilter(updateFilterView)
                    .setFields("criteria,title");

            BatchUpdateSpreadsheetRequest batchRequest3 = new BatchUpdateSpreadsheetRequest()
                    .setRequests(Collections.singletonList(new Request().setUpdateFilterView(updateRequest)));

            BatchUpdateSpreadsheetResponse response3 = service.spreadsheets()
                    .batchUpdate(spreadsheetId, batchRequest3)
                    .execute();

            System.out.println(response3.toPrettyString());

        } catch (IOException | GeneralSecurityException e) {
            System.err.println("An error occurred: " + e);
        }
    }
}

Python

sheets/snippets/sheets_filter_views.py
import google.auth
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


def filter_views(spreadsheet_id):
  """
  Creates the batch_update the user has access to.
  Load pre-authorized user credentials from the environment.
  TODO(developer) - See https://developers.google.com/identity
  for guides on implementing OAuth2 for the application.
  """
  creds, _ = google.auth.default()
  # pylint: disable=maybe-no-member
  try:
    service = build("sheets", "v4", credentials=creds)

    my_range = {
        "sheetId": 0,
        "startRowIndex": 0,
        "startColumnIndex": 0,
    }
    addfilterviewrequest = {
        "addFilterView": {
            "filter": {
                "title": "Sample Filter",
                "range": my_range,
                "sortSpecs": [{
                    "dimensionIndex": 3,
                    "sortOrder": "DESCENDING",
                }],
                "criteria": {
                    0: {"hiddenValues": ["Panel"]},
                    6: {
                        "condition": {
                            "type": "DATE_BEFORE",
                            "values": {"userEnteredValue": "4/30/2016"},
                        }
                    },
                },
            }
        }
    }

    body = {"requests": [addfilterviewrequest]}
    addfilterviewresponse = (
        service.spreadsheets()
        .batchUpdate(spreadsheetId=spreadsheet_id, body=body)
        .execute()
    )

    duplicatefilterviewrequest = {
        "duplicateFilterView": {
            "filterId": addfilterviewresponse["replies"][0]["addFilterView"][
                "filter"
            ]["filterViewId"]
        }
    }

    body = {"requests": [duplicatefilterviewrequest]}
    duplicatefilterviewresponse = (
        service.spreadsheets()
        .batchUpdate(spreadsheetId=spreadsheet_id, body=body)
        .execute()
    )

    updatefilterviewrequest = {
        "updateFilterView": {
            "filter": {
                "filterViewId": duplicatefilterviewresponse["replies"][0][
                    "duplicateFilterView"
                ]["filter"]["filterViewId"],
                "title": "Updated Filter",
                "criteria": {
                    0: {},
                    3: {
                        "condition": {
                            "type": "NUMBER_GREATER",
                            "values": {"userEnteredValue": "5"},
                        }
                    },
                },
            },
            "fields": {"paths": ["criteria", "title"]},
        }
    }

    body = {"requests": [updatefilterviewrequest]}
    updatefilterviewresponse = (
        service.spreadsheets()
        .batchUpdate(spreadsheetId=spreadsheet_id, body=body)
        .execute()
    )
    print(str(updatefilterviewresponse))
  except HttpError as error:
    print(f"An error occurred: {error}")


if __name__ == "__main__":
  # Pass: spreadsheet_id
  filter_views("1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k")