Zarządzanie widocznością danych za pomocą filtrów

Ten dokument zawiera informacje o używaniu filtrów do sortowania i filtrowania danych wyświetlanych w arkuszu kalkulacyjnym.

Filtry umożliwiają sortowanie i filtrowanie danych wyświetlanych w arkuszu kalkulacyjnym. Filtry nie zmieniają wartości danych w arkuszu kalkulacyjnym. Możesz używać filtrów, aby tymczasowo ukrywać lub sortować informacje. Dane, które spełniają określone kryteria filtra, nie są wyświetlane, gdy filtr jest włączony. Dzięki widokom filtra możesz też zapisywać różne nazwane filtry i przełączać się między nimi w dowolnym momencie.

Aby filtrować dane zwracane w żądaniu interfejsu Google Sheets API, użyj obiektu DataFilter. Więcej informacji znajdziesz w artykule Odczytywanie, zapisywanie i wyszukiwanie metadanych.

Filtrowanie przypadków użycia

Oto kilka przykładowych zastosowań filtrów:

  • sortować dane według konkretnej kolumny; Możesz na przykład posortować rekordy użytkowników według nazwiska.
  • Ukrywanie danych spełniających określony warunek. Możesz na przykład ukryć wszystkie rekordy starsze niż 2 lata.
  • Ukrywanie danych, które pasują do określonej wartości. Na przykład ukryj wszystkie problemy o stanie „zamknięty”.

Filtr podstawowy

Obiekt BasicFilter arkusza kalkulacyjnego to domyślny filtr, który jest stosowany za każdym razem, gdy ktoś wyświetla arkusz kalkulacyjny. Arkusze mogą mieć tylko 1 filtr podstawowy na arkusz. Możesz wyłączyć filtr podstawowy, usuwając go. Spowoduje to usunięcie filtra i wszystkich jego ustawień z arkusza kalkulacyjnego. Jeśli chcesz ponownie włączyć ten sam filtr, musisz ponownie ustawić kryteria.

Zarządzanie filtrem podstawowym

Aby ustawić lub wyczyścić filtr podstawowy, użyj metody spreadsheets.batchUpdate z odpowiednim typem żądania:

Aby wyświetlić podstawowy filtr, użyj metody spreadsheets.get i ustaw parametr adresu URL fields na sheets/basicFilter. Poniższy przykładowy kodspreadsheets.get pokazuje adres URL arkusza Google z maską pola:

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

Widoki filtrów

FilterView to nazwany filtr, który możesz włączać i wyłączać w dowolnym momencie. Arkusz może mieć zapisanych wiele widoków filtra, ale w danym momencie możesz zastosować tylko jeden. Arkusz może też zawierać zarówno podstawowy filtr, jak i wiele widoków filtrów, ale nie można zastosować obu jednocześnie w tym samym zakresie danych.

Przypadki użycia widoku filtra

Oto kilka przykładowych zastosowań widoków filtrów:

  • Masz kilka różnych filtrów, między którymi chcesz się przełączać podczas wyświetlania danych.
  • Nie masz uprawnień do edytowania arkusza kalkulacyjnego, ale chcesz zastosować filtr. W takim przypadku możesz utworzyć tymczasowy widok filtra, który jest widoczny tylko dla Ciebie.

  • Chcesz, aby każda osoba, której udostępnisz arkusz kalkulacyjny, wyświetlała dane w inny sposób. Możesz określić widok przefiltrowany, który chcesz zastosować, podając parametry spreadsheetId i filterViewId w adresie URL arkusza kalkulacyjnego. Aby to zrobić, użyj filterViewId zwróconego w odpowiedzi podczas tworzenia widoku filtra.

    Poniższy przykładowy kod pokazuje adres URL Arkuszy z widokiem filtra:

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

Zarządzanie widokami filtra

Aby utworzyć, zduplikować, zmodyfikować lub usunąć widoki przefiltrowane, użyj metody spreadsheets.batchUpdate z odpowiednim typem żądania:

Aby wyświetlić listę wszystkich widoków danych z filtrem, użyj metody spreadsheets.get i ustaw parametr adresu URL fields na sheets/filterViews. Poniższy przykładowy kodspreadsheets.get pokazuje adres URL Arkuszy z maską pola:

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

Zapis JSON filtra

Poniższy przykład kodu przedstawia reprezentację JSON obiektu FilterView. Obiekt BasicFilter jest taki sam, z wyjątkiem tego, że nie zawiera pól filterViewId i title i nie może używać zakresu nazwanego.

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

Przykładowe dane o sprzedaży

W pozostałej części tego dokumentu będziemy odwoływać się do tej przykładowej tabeli danych o sprzedaży:

Tabela 1. Przykładowe dane o sprzedaży
A B C D E F G
1 Kategoria produktu Numer modelu Koszt Ilość Region Pracownik działu sprzedaży Data wysyłki
2 Diabelski młyn W-24 20,50 USD 4 zachód Beth 01.03.2016
3 Drzwi D-01X 15 zł 2 południe Amir 15.03.2016 r.
4 Rama FR-0B1 34,00 USD 8 wschód Hannah 12.03.2016 r.
5 Panel P-034 18 PLN 4 północ Devyn 15.03.2016 r.
6 Panel P-052 11,50 PLN 7 wschód Erik 16.05.2016 r.
7 Diabelski młyn W-24 20,50 USD 11 południe Sheldon 30.04.2016 r.
8 Wyszukiwarka ENG-0161 330,00 PLN 2 północ Jessie 02.07.2016 r.

Specyfikacje sortowania

Filtr może mieć wiele specyfikacji sortowania. Te specyfikacje określają sposób sortowania danych i są stosowane w podanej kolejności. Atrybut SortSpec.dimensionIndex określa indeks kolumny, do której ma być zastosowane sortowanie.

Poniższy przykładowy kod pokazuje specyfikację sortowania:

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

W przypadku zastosowania do przykładowych danych o sprzedaży ta specyfikacja sortuje najpierw według kolumny „Ilość”, a potem, jeśli 2 wiersze mają tę samą ilość, według kolumny „Data wysyłki”.

Tabela 2. Dane o sprzedaży posortowane według 2 kolumn
A B C D E F G
1 Kategoria produktu Numer modelu Koszt Ilość Region Pracownik działu sprzedaży Data wysyłki
2 Drzwi D-01X 15 zł 2 południe Amir 15.03.2016 r.
3 Wyszukiwarka ENG-0161 330,00 PLN 2 północ Jessie 02.07.2016 r.
4 Diabelski młyn W-24 20,50 USD 4 zachód Beth 01.03.2016
5 Panel P-034 18 PLN 4 północ Devyn 15.03.2016 r.
6 Panel P-052 11,50 PLN 7 wschód Erik 16.05.2016 r.
7 Rama FR-0B1 34,00 USD 8 wschód Hannah 12.03.2016 r.
8 Diabelski młyn W-24 20,50 USD 11 południe Sheldon 30.04.2016 r.

Kryteria filtra

Obiekt FilterCriteria określa, które dane arkusza kalkulacyjnego są wyświetlane lub ukrywane w podstawowym filtrze lub widoku filtra. Każde kryterium zależy od wartości w określonej kolumnie. Kryteria filtra podajesz w postaci mapy, w której klucze to indeksy kolumn, a wartości to kryteria.

W przypadku kryteriów określonych za pomocą wartości logicznej condition warunek musi być true, aby wyświetlać wartości. Warunek nie zastępuje hiddenValues. Jeśli wartość jest wymieniona w sekcji hiddenValues, wszystkie pasujące do niej wyniki są nadal ukryte.

Poniższy przykładowy kod pokazuje mapę kryteriów filtra:

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

W przypadku zastosowania do przykładowych danych o sprzedaży to kryterium wyświetla tylko wiersze, w których wartość kolumny „Kategoria produktu” nie jest równa „Panel” i w których wartość kolumny „Data wysyłki” jest wcześniejsza niż „30 kwietnia 2016 r.”.

Tabela 3. Dane o sprzedaży z użyciem kryteriów filtrowania
A B C D E F G
1 Kategoria produktu Numer modelu Koszt Ilość Region Pracownik działu sprzedaży Data wysyłki
2 Diabelski młyn W-24 20,50 USD 4 zachód Beth 01.03.2016
3 Drzwi D-01X 15 zł 2 południe Amir 15.03.2016 r.
4 Rama FR-0B1 34,00 USD 8 wschód Hannah 12.03.2016 r.

Przykładowy kod widoku filtra

Poniższy przykładowy kod pokazuje, jak utworzyć widok filtra, zduplikować go, a następnie zaktualizować zduplikowaną wersję za pomocą przykładowych danych o sprzedaży.

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")