จัดการระดับการเข้าถึงข้อมูลด้วยตัวกรอง

เอกสารนี้เกี่ยวกับการใช้ตัวกรองเพื่อจัดเรียงและกรองข้อมูลที่แสดงในสเปรดชีต

ตัวกรองช่วยให้คุณจัดเรียงและกรองข้อมูลที่เห็นเมื่อดู สเปรดชีตได้ ตัวกรองจะไม่เปลี่ยนค่าข้อมูลในสเปรดชีต คุณ ใช้ตัวกรองเพื่อซ่อนหรือจัดเรียงข้อมูลชั่วคราวได้ ข้อมูลที่ตรงกับเกณฑ์ตัวกรองที่ระบุจะไม่ปรากฏขณะที่ตัวกรอง เปิดอยู่ มุมมองตัวกรองยังช่วยให้คุณบันทึกตัวกรองต่างๆ ที่ตั้งชื่อไว้และสลับไปมาระหว่างตัวกรองเหล่านั้นได้ทุกเมื่อ

หากต้องการกรองข้อมูลที่แสดงผลในคำขอ Google Sheets API ให้ใช้ออบเจ็กต์ DataFilter ดูข้อมูลเพิ่มเติมได้ที่อ่าน เขียน และค้นหา ข้อมูลเมตา

กรองกรณีการใช้งาน

ตัวอย่างกรณีการใช้งานตัวกรองมีดังนี้

• จัดเรียงข้อมูลตามคอลัมน์ใดคอลัมน์หนึ่ง เช่น จัดเรียงระเบียนผู้ใช้ตามนามสกุล
• ซ่อนข้อมูลที่ตรงกับเงื่อนไขที่เฉพาะเจาะจง เช่น ซ่อนระเบียนทั้งหมด ที่มีอายุกว่า 2 ปี
• ซ่อนข้อมูลที่ตรงกับค่าหนึ่งๆ เช่น ซ่อนปัญหาทั้งหมดที่มี สถานะ "ปิดแล้ว"

ตัวกรองพื้นฐาน

ออบเจ็กต์ BasicFilter สำหรับสเปรดชีตคือตัวกรองเริ่มต้นที่จะใช้เมื่อใดก็ตามที่มีใคร ดูสเปรดชีต สเปรดชีตจะมีตัวกรองพื้นฐานได้เพียงตัวเดียวต่อชีต คุณปิดตัวกรองพื้นฐานได้โดยล้างตัวกรอง การดำเนินการนี้จะนำตัวกรองและการตั้งค่าทั้งหมดออกจาก สเปรดชีต หากต้องการเปิดตัวกรองเดิมอีกครั้ง คุณต้องตั้งค่า เกณฑ์อีกครั้ง

จัดการตัวกรองพื้นฐาน

หากต้องการตั้งค่าหรือล้างตัวกรองพื้นฐาน ให้ใช้วิธี spreadsheets.batchUpdate กับประเภทคำขอที่เหมาะสม

• หากต้องการตั้งค่าตัวกรองพื้นฐาน ให้ใช้วิธี SetBasicFilterRequest
• หากต้องการล้างตัวกรองพื้นฐาน ให้ใช้วิธี ClearBasicFilterRequest

หากต้องการแสดงตัวกรองพื้นฐาน ให้ใช้เมธอด spreadsheets.get และตั้งค่าพารามิเตอร์ fields ของ URL เป็น sheets/basicFilter ตัวอย่างโค้ดต่อไปนี้ spreadsheets.getแสดง URL ของ Google ชีตที่มีฟิลด์ มาสก์

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

มุมมองตัวกรอง

FilterView คือตัวกรองที่มีชื่อซึ่งคุณเปิดและปิดได้ทุกเมื่อที่ต้องการ ชีตสามารถมีมุมมองตัวกรองที่บันทึกไว้หลายรายการ แต่คุณจะใช้ได้ครั้งละ 1 รายการเท่านั้น ชีต ยังมีทั้งตัวกรองพื้นฐานและมุมมองตัวกรองหลายรายการได้ด้วย แต่คุณจะ ใช้ทั้ง 2 อย่างพร้อมกันในข้อมูลช่วงเดียวกันไม่ได้

กรณีการใช้งานมุมมองตัวกรอง

ตัวอย่างกรณีการใช้งานมุมมองตัวกรองมีดังนี้

• คุณมีตัวกรองหลายรายการที่ต้องการสลับไปมาระหว่างดูข้อมูล
• คุณไม่มีสิทธิ์แก้ไขสเปรดชีต แต่ยังต้องการใช้ตัวกรอง ในกรณีนี้ คุณสามารถสร้างมุมมองตัวกรองชั่วคราวที่ มองเห็นได้เฉพาะคุณ

• คุณต้องการให้แต่ละคนที่คุณแชร์สเปรดชีตด้วยดูข้อมูล แตกต่างกัน คุณระบุมุมมองตัวกรองที่ต้องการใช้ได้โดยระบุ spreadsheetId และ filterViewId ใน URL ของสเปรดชีต โดยใช้ filterViewId ที่แสดงใน การตอบกลับเมื่อสร้างมุมมองตัวกรอง

  ตัวอย่างโค้ดต่อไปนี้แสดง URL ของชีตที่มีมุมมองตัวกรอง

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

จัดการมุมมองตัวกรอง

หากต้องการสร้าง ทำซ้ำ แก้ไข หรือลบมุมมองตัวกรอง ให้ใช้วิธี spreadsheets.batchUpdate กับประเภทคำขอที่เหมาะสม

• หากต้องการสร้างมุมมองตัวกรอง ให้ใช้เมธอด AddFilterViewRequest
• หากต้องการทำสำเนามุมมองตัวกรอง ให้ใช้วิธี DuplicateFilterViewRequest
• หากต้องการแก้ไขพร็อพเพอร์ตี้ของมุมมองตัวกรอง ให้ใช้เมธอด UpdateFilterViewRequest
• หากต้องการลบมุมมองตัวกรอง ให้ใช้วิธี DeleteFilterViewRequest

หากต้องการแสดงข้อมูลพร็อพเพอร์ตี้ที่กรองทั้งหมด ให้ใช้เมธอด spreadsheets.get และตั้งค่าพารามิเตอร์ของ URL fields เป็น sheets/filterViews spreadsheets.getตัวอย่างโค้ดต่อไปนี้แสดง URL ของชีตที่มีฟิลด์ มาสก์

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

การแสดงตัวกรองเป็น JSON

ตัวอย่างโค้ดต่อไปนี้แสดงการแสดง JSON สำหรับออบเจ็กต์ FilterView ออบเจ็กต์ BasicFilter จะเหมือนกัน ยกเว้นว่าไม่มีฟิลด์ filterViewId และ title และใช้ช่วงที่ตั้งชื่อไม่ได้

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

ข้อมูลยอดขายตัวอย่าง

ส่วนที่เหลือของเอกสารนี้อ้างอิงตารางข้อมูลการขายตัวอย่างต่อไปนี้

ตารางที่ 1 ข้อมูลยอดขายตัวอย่าง

	A	ข	C	D	E	F	G
1	หมวดหมู่รายการ	หมายเลขรุ่น	ค่าใช้จ่าย	จำนวน	ภูมิภาค	พนักงานขาย	วันที่จัดส่ง
2	ตั๋วขึ้นชิงช้าสวรรค์	W-24	$20.50	4	ตะวันตก	Beth	1/3/2016
3	ประตู	D-01X	$15.00	2	ใต้	Amir	15/3/2016
4	กรอบ	FR-0B1	$34.00	8	ตะวันออก	Hannah	12/3/2016
5	แผง	P-034	$6.00	4	เหนือ	Devyn	15/3/2016
6	แผง	P-052	$11.50	7	ตะวันออก	อิริค	16/5/2016
7	ตั๋วขึ้นชิงช้าสวรรค์	W-24	$20.50	11	ใต้	Sheldon	30/4/2016
8	เครื่องมือค้นหา	ENG-0161	$330.00	2	เหนือ	Jessie	2/7/2016

ข้อกำหนดการจัดเรียง

ตัวกรองมีข้อกำหนดการจัดเรียงได้หลายรายการ ข้อกำหนดเหล่านี้จะกำหนด วิธีจัดเรียงข้อมูลและจะใช้ตามลำดับที่ระบุ แอตทริบิวต์ SortSpec.dimensionIndex ระบุดัชนีคอลัมน์ที่ควรใช้การจัดเรียง

ตัวอย่างโค้ดต่อไปนี้แสดงข้อกำหนดการจัดเรียง

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

เมื่อใช้กับข้อมูลการขายตัวอย่าง ข้อกำหนดนี้จะจัดเรียงตามคอลัมน์ "จำนวน" ก่อน แล้วจึงจัดเรียงตาม "วันที่จัดส่ง" หาก 2 แถวมีจำนวนเท่ากัน

ตารางที่ 2 ข้อมูลยอดขายที่จัดเรียงตาม 2 คอลัมน์

	A	ข	C	D	E	F	G
1	หมวดหมู่รายการ	หมายเลขรุ่น	ค่าใช้จ่าย	จำนวน	ภูมิภาค	พนักงานขาย	วันที่จัดส่ง
2	ประตู	D-01X	$15.00	2	ใต้	Amir	15/3/2016
3	เครื่องมือค้นหา	ENG-0161	$330.00	2	เหนือ	Jessie	2/7/2016
4	ตั๋วขึ้นชิงช้าสวรรค์	W-24	$20.50	4	ตะวันตก	Beth	1/3/2016
5	แผง	P-034	$6.00	4	เหนือ	Devyn	15/3/2016
6	แผง	P-052	$11.50	7	ตะวันออก	อิริค	16/5/2016
7	กรอบ	FR-0B1	$34.00	8	ตะวันออก	Hannah	12/3/2016
8	ตั๋วขึ้นชิงช้าสวรรค์	W-24	$20.50	11	ใต้	Sheldon	30/4/2016

เกณฑ์การกรอง

ออบเจ็กต์ FilterCriteria จะกำหนดว่าข้อมูลสเปรดชีตใดจะแสดงหรือซ่อนในตัวกรองพื้นฐานหรือมุมมองตัวกรอง แต่ละเกณฑ์จะขึ้นอยู่กับค่าในคอลัมน์ที่เฉพาะเจาะจง คุณระบุเกณฑ์ตัวกรองเป็นแผนที่ซึ่งคีย์คือดัชนีคอลัมน์ และ ค่าคือเกณฑ์

สำหรับเกณฑ์ที่ระบุโดยใช้บูลีน condition เงื่อนไขต้องเป็น true เพื่อให้ค่าแสดง เงื่อนไขไม่ ลบล้าง hiddenValues หากค่าแสดงอยู่ในส่วน hiddenValues ระบบจะยังคงซ่อนรายการที่ตรงกันทั้งหมดสำหรับค่านั้น

ตัวอย่างโค้ดต่อไปนี้แสดงแผนที่เกณฑ์ตัวกรอง

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

เมื่อใช้กับข้อมูลยอดขายตัวอย่าง เกณฑ์นี้จะแสดงเฉพาะ แถวที่ค่าในคอลัมน์ "หมวดหมู่สินค้า" ไม่ใช่ "Panel" และค่าในคอลัมน์ "วันที่จัดส่ง" อยู่ก่อน "30 เมษายน 2016"

ตารางที่ 3 ข้อมูลการขายโดยใช้เกณฑ์ตัวกรอง

	A	ข	C	D	E	F	G
1	หมวดหมู่รายการ	หมายเลขรุ่น	ค่าใช้จ่าย	จำนวน	ภูมิภาค	พนักงานขาย	วันที่จัดส่ง
2	ตั๋วขึ้นชิงช้าสวรรค์	W-24	$20.50	4	ตะวันตก	Beth	1/3/2016
3	ประตู	D-01X	$15.00	2	ใต้	Amir	15/3/2016
4	กรอบ	FR-0B1	$34.00	8	ตะวันออก	Hannah	12/3/2016

ตัวอย่างโค้ดมุมมองตัวกรอง

ตัวอย่างโค้ดต่อไปนี้แสดงวิธีสร้างมุมมองตัวกรอง ทำซ้ำ และ อัปเดตเวอร์ชันที่ทำซ้ำโดยใช้ข้อมูลยอดขายตัวอย่าง

Java

sheets/snippets/src/main/java/SheetsFilterViews.java

ดูใน GitHub

/*
 * 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

ดูใน GitHub

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

หัวข้อที่เกี่ยวข้อง

• ใช้ตัวกรองกับข้อมูล Google ชีต
• อ่าน เขียน และค้นหาข้อมูลเมตา