Quản lý chế độ hiển thị dữ liệu bằng bộ lọc

Tài liệu này trình bày về cách sử dụng bộ lọc để sắp xếp và lọc dữ liệu xuất hiện trong a bảng tính.

Bộ lọc cho phép bạn sắp xếp và lọc dữ liệu mà bạn thấy khi xem bảng tính. Bộ lọc không thay đổi giá trị dữ liệu trong bảng tính. Bạn có thể sử dụng bộ lọc để tạm thời ẩn hoặc sắp xếp thông tin. Dữ liệu khớp với tiêu chí bộ lọc đã chỉ định sẽ không xuất hiện khi bộ lọc đang bật. Với chế độ xem bộ lọc, bạn cũng có thể lưu nhiều bộ lọc có tên và chuyển đổi giữa các bộ lọc đó bất cứ khi nào bạn muốn.

Để lọc dữ liệu được trả về trong yêu cầu API Google Trang tính, hãy sử dụng đối tượng DataFilter. Để biết thêm thông tin, hãy xem bài viết Đọc, ghi và tìm kiếm siêu dữ liệu.

Trường hợp sử dụng bộ lọc

Dưới đây là một số trường hợp sử dụng bộ lọc:

  • Sắp xếp dữ liệu theo một cột cụ thể. Ví dụ: sắp xếp bản ghi người dùng theo họ.
  • Ẩn dữ liệu đáp ứng một điều kiện cụ thể. Ví dụ: ẩn tất cả bản ghi cũ hơn 2 năm.
  • Ẩn dữ liệu khớp với một giá trị nhất định. Ví dụ: ẩn tất cả vấn đề có trạng thái "đã đóng".

Bộ lọc cơ bản

Đối tượng BasicFilter cho bảng tính là bộ lọc mặc định được áp dụng bất cứ khi nào có người xem bảng tính. Một bảng tính chỉ có thể có một bộ lọc cơ bản cho mỗi trang tính. Bạn có thể tắt bộ lọc cơ bản bằng cách xoá bộ lọc đó. Thao tác này sẽ xoá bộ lọc và tất cả chế độ cài đặt của bộ lọc đó khỏi bảng tính. Nếu muốn bật lại cùng một bộ lọc, bạn phải đặt lại tiêu chí.

Quản lý bộ lọc cơ bản

Để đặt hoặc xoá bộ lọc cơ bản, hãy sử dụng phương thức spreadsheets.batchUpdate với loại yêu cầu thích hợp:

Để liệt kê bộ lọc cơ bản, hãy sử dụng spreadsheets.get phương thức và đặt tham số URL fields thành sheets/basicFilter. Mã mẫu spreadsheets.get sau đây cho thấy một URL Google Trang tính có mặt nạ trường:

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

Chế độ xem dạng lọc

A FilterView là một bộ lọc có tên mà bạn có thể bật và tắt bất cứ khi nào bạn muốn. Một trang tính có thể lưu nhiều chế độ xem bộ lọc, nhưng bạn chỉ có thể áp dụng một chế độ xem bộ lọc tại một thời điểm. Một trang tính cũng có thể chứa cả bộ lọc cơ bản và nhiều chế độ xem bộ lọc, nhưng bạn không thể áp dụng cả hai cùng lúc trên cùng một phạm vi dữ liệu.

Trường hợp sử dụng chế độ xem bộ lọc

Dưới đây là một số trường hợp sử dụng chế độ xem bộ lọc:

  • Bạn có một số bộ lọc khác nhau mà bạn muốn chuyển đổi giữa các bộ lọc đó khi xem dữ liệu.
  • Bạn không có quyền chỉnh sửa đối với một bảng tính nhưng vẫn muốn áp dụng bộ lọc. Trong trường hợp này, bạn có thể tạo một chế độ xem bộ lọc tạm thời mà chỉ bạn mới nhìn thấy.

  • Bạn muốn mỗi người mà bạn chia sẻ bảng tính đều xem dữ liệu theo cách khác nhau. Bạn có thể chỉ định chế độ xem bộ lọc mà bạn muốn áp dụng bằng cách cung cấp the spreadsheetId and filterViewId trong URL bảng tính. Để thực hiện việc này, hãy sử dụng filterViewId được trả về trong phản hồi khi bạn tạo chế độ xem bộ lọc.

    Mã mẫu sau đây cho thấy một URL Sheets có chế độ xem bộ lọc:

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

Quản lý chế độ xem bộ lọc

Để tạo, sao chép, sửa đổi hoặc xoá chế độ xem bộ lọc, hãy sử dụng phương thức spreadsheets.batchUpdate với loại yêu cầu thích hợp:

Để liệt kê tất cả chế độ xem bộ lọc, hãy sử dụng spreadsheets.get phương thức và đặt tham số URL fields thành sheets/filterViews. Mã mẫu spreadsheets.get sau đây cho thấy một URL Sheets có mặt nạ trường:

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

Biểu diễn bộ lọc dưới dạng JSON

Mã mẫu sau đây cho thấy biểu diễn JSON cho đối tượng FilterView. Đối tượng BasicFilter cũng tương tự, ngoại trừ việc đối tượng này thiếu các trường filterViewId và title, đồng thời không thể sử dụng dải ô được đặt tên.

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

Dữ liệu bán hàng mẫu

Phần còn lại của tài liệu này tham chiếu đến bảng dữ liệu bán hàng mẫu sau đây:

Bảng 1. Dữ liệu bán hàng mẫu
A B C D E F G
1 Danh mục mặt hàng Số kiểu máy Chi phí Số lượng Vùng Nhân viên bán hàng Ngày giao hàng
2 Bánh xe W-24 20,50 USD 4 Tây Beth 1/3/2016
3 Cửa ra vào D-01X 15,00 USD 2 Nam Amir 15/3/2016
4 Khung FR-0B1 34,00 USD 8 Đông Hannah 12/3/2016
5 Bảng điều khiển P-034 6 USD 4 Bắc Devyn 15/3/2016
6 Bảng điều khiển P-052 11,50 USD 7 Đông Erik 16/5/2016
7 Bánh xe W-24 20,50 USD 11 Nam Sheldon 30/4/2016
8 Công cụ ENG-0161 330,00 USD 2 Bắc Jessie 2/7/2016

Thông số sắp xếp

Một bộ lọc có thể có nhiều thông số sắp xếp. Các thông số này xác định cách sắp xếp dữ liệu và được áp dụng theo thứ tự đã chỉ định. Thuộc tính SortSpec.dimensionIndex chỉ định chỉ mục cột mà thao tác sắp xếp sẽ được áp dụng.

Mã mẫu sau đây cho thấy một thông số sắp xếp:

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

Khi áp dụng cho dữ liệu bán hàng mẫu, thông số này sẽ sắp xếp theo cột "Số lượng" trước, sau đó, nếu 2 hàng có cùng số lượng, thì sẽ sắp xếp theo "Ngày giao hàng".

Bảng 2. Dữ liệu bán hàng được sắp xếp theo 2 cột
A B C D E F G
1 Danh mục mặt hàng Số kiểu máy Chi phí Số lượng Vùng Nhân viên bán hàng Ngày giao hàng
2 Cửa ra vào D-01X 15,00 USD 2 Nam Amir 15/3/2016
3 Công cụ ENG-0161 330,00 USD 2 Bắc Jessie 2/7/2016
4 Bánh xe W-24 20,50 USD 4 Tây Beth 1/3/2016
5 Bảng điều khiển P-034 6 USD 4 Bắc Devyn 15/3/2016
6 Bảng điều khiển P-052 11,50 USD 7 Đông Erik 16/5/2016
7 Khung FR-0B1 34,00 USD 8 Đông Hannah 12/3/2016
8 Bánh xe W-24 20,50 USD 11 Nam Sheldon 30/4/2016

Tiêu chí bộ lọc

Đối tượng FilterCriteria xác định dữ liệu bảng tính nào được hiển thị hoặc ẩn trong bộ lọc cơ bản hoặc chế độ xem bộ lọc. Mỗi tiêu chí phụ thuộc vào các giá trị trong một cột cụ thể. Bạn cung cấp tiêu chí bộ lọc dưới dạng bản đồ, trong đó các khoá là chỉ mục cột và các giá trị là tiêu chí.

Đối với tiêu chí được chỉ định bằng cách sử dụng boolean condition, điều kiện phải là true để các giá trị được hiển thị. Điều kiện không ghi đè hiddenValues. Nếu một giá trị được liệt kê trong hiddenValues, thì tất cả kết quả khớp cho một giá trị vẫn bị ẩn.

Mã mẫu sau đây cho thấy bản đồ tiêu chí bộ lọc:

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

Khi áp dụng cho dữ liệu bán hàng mẫu, tiêu chí này chỉ hiển thị các hàng có giá trị cột "Danh mục mặt hàng" không phải là "Bảng điều khiển" và giá trị cột "Ngày giao hàng" là trước "Ngày 30 tháng 4 năm 2016".

Bảng 3. Dữ liệu bán hàng sử dụng tiêu chí bộ lọc
A B C D E F G
1 Danh mục mặt hàng Số kiểu máy Chi phí Số lượng Vùng Nhân viên bán hàng Ngày giao hàng
2 Bánh xe W-24 20,50 USD 4 Tây Beth 1/3/2016
3 Cửa ra vào D-01X 15,00 USD 2 Nam Amir 15/3/2016
4 Khung FR-0B1 34,00 USD 8 Đông Hannah 12/3/2016

Mã mẫu chế độ xem bộ lọc

Mã mẫu sau đây cho thấy cách tạo chế độ xem bộ lọc, sao chép chế độ xem đó, và sau đó cập nhật phiên bản đã sao chép bằng dữ liệu bán hàng mẫu.

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