ตาราง Pivot

คู่มือนี้อธิบายวิธีและเหตุผลในการใช้ Google Sheets API เพื่อสร้างตาราง Pivot ในสเปรดชีต

ตาราง Pivot คืออะไร

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

ตัวอย่างเช่น ลองพิจารณาชุดข้อมูลการขายต่อไปนี้

	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	เครื่องมือค้นหา	ENG-0134	3,000 บาท	1	เหนือ	Carmen	20/3/2016
5	กรอบ	FR-0B1	$34.00	8	ตะวันออก	Hannah	12/3/2016
6	แผง	P-034	$6.00	4	เหนือ	Devyn	2/4/2016
7	แผง	P-052	$11.50	7	ตะวันออก	อิริค	16/5/2016
8	ตั๋วขึ้นชิงช้าสวรรค์	W-24	$20.50	11	ใต้	Sheldon	30/4/2016
9	เครื่องมือค้นหา	ENG-0161	$330.00	2	เหนือ	Jessie	2/7/2016
10	ประตู	D-01Y	$29.00	6	ตะวันตก	Armando	13/3/2016
11	กรอบ	FR-0B1	$34.00	9	ใต้	Yuliana	27/2/2016
12	แผง	P-102	$3.00	15	ตะวันตก	Carmen	18/4/2016
13	แผง	P-105	$8.25	13	ตะวันตก	Jessie	20/6/2016
14	เครื่องมือค้นหา	ENG-0211	$283.00	1	เหนือ	Amir	21/6/2016
15	ประตู	D-01X	$15.00	2	ตะวันตก	Armando	3/7/2016
16	กรอบ	FR-0B1	$34.00	6	ใต้	Carmen	15/7/2016
17	ตั๋วขึ้นชิงช้าสวรรค์	W-25	$20.00	8	ใต้	Hannah	2/5/2016
18	ตั๋วขึ้นชิงช้าสวรรค์	W-11	$29.00	13	ตะวันออก	อิริค	19/5/2016
19	ประตู	D-05	$17.70	7	ตะวันตก	Beth	28/6/2016
20	กรอบ	FR-0B1	$34.00	8	เหนือ	Sheldon	30/3/2016

คุณสามารถใช้ตาราง Pivot เพื่อสร้างรายงานที่แสดงจำนวนรุ่น หมายเลขที่ขายในแต่ละภูมิภาคได้ดังนี้

ดูซอร์สโค้ดที่ใช้สร้างตาราง Pivot นี้ได้ที่ส่วนตัวอย่างด้านล่าง

เมื่อวางตาราง Pivot ในสเปรดชีตแล้ว ผู้ใช้จะเปลี่ยนโครงสร้างและรายละเอียดของข้อมูลสรุปได้แบบอินเทอร์แอกทีฟโดยใช้ UI ของชีต

การทำงานกับตาราง Pivot

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

การเพิ่มตาราง Pivot

หากต้องการเพิ่มตาราง Pivot ให้ใช้วิธี batchUpdate โดยส่งคำขอ updateCells คุณใช้คำขอนี้เพื่อระบุคำจำกัดความของ PivotTable เป็นเนื้อหาของเซลล์ตามที่แสดงด้านล่าง

{
  "updateCells": {
    "rows": {
      "values": [
        {
          "pivotTable": "MyPivotTable"
        }
      ],
      "start": {
        "sheetId": "sheetId",
        "rowIndex": 0,
        "columnIndex": 0
      },
      "fields": "pivotTable"
    }
  }
}

ซึ่งจะวางตาราง Pivot ที่อธิบายโดย MyPivotTable ในชีตที่ระบุ โดยมีมุมซ้ายบนอยู่ที่เซลล์ A1 (ความสูงและความกว้างของตาราง Pivot table เป็นแบบไดนามิก คุณระบุได้เฉพาะต้นทาง)

ประเภท PivotTable ช่วยให้คุณระบุสิ่งต่อไปนี้ได้

• ช่วงข้อมูลต้นทาง
• ฟิลด์อย่างน้อย 1 รายการซึ่งข้อมูลจะสร้างแถวของตาราง Pivot
• ฟิลด์อย่างน้อย 1 รายการซึ่งข้อมูลจะสร้างเป็นคอลัมน์ของตาราง Pivot
• เกณฑ์การกรองและการรวบรวม
• เลย์เอาต์ตาราง Pivot

การแก้ไขและลบตาราง Pivot

ไม่มีคำขอที่ชัดเจนในการแก้ไขหรือลบตาราง Pivot แต่ให้ใช้คำขอ updateCells ที่มีเนื้อหาในเซลล์ต่างกันแทน ดังนี้

• หากต้องการแก้ไขตาราง Pivot ให้สร้างคำจำกัดความของ PivotTable ที่แก้ไขแล้ว และอัปเดตเซลล์โดยใช้คำจำกัดความดังกล่าว เช่นเดียวกับการเพิ่มตาราง Pivot ใหม่
• หากต้องการลบตาราง Pivot ให้อัปเดตเซลล์ด้วยค่าว่าง ดูตัวอย่างได้ที่ตัวอย่างลบ Pivot Table

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

ตาราง Pivot มีการใช้งานที่หลากหลายในหลายด้าน รวมถึงการวิเคราะห์ทางสถิติ แอปพลิเคชัน ERP การรายงานทางการเงิน และ อื่นๆ กรณีการใช้งานตาราง Pivot แบบคลาสสิก ได้แก่ รายการต่อไปนี้

• ยอดขายทั้งหมดตามภูมิภาคและไตรมาส
• เงินเดือนเฉลี่ยตามตำแหน่งและสถานที่
• จำนวนเหตุการณ์ตามผลิตภัณฑ์และเวลาของวัน

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

• สำรวจข้อมูลเหตุการณ์ในช่วง 24 ชั่วโมงที่ผ่านมา
• ดูหรือวิเคราะห์ข้อมูลรวมที่สอดคล้องกับบัญชีที่เลือก
• ตรวจสอบข้อมูลการขายสำหรับเขตแดนที่เป็นของผู้ใช้ปัจจุบัน

ตัวอย่าง

ตัวอย่างนี้สร้างตาราง Pivot จากชุดข้อมูลเพื่อสร้างรายงาน "หมายเลขรุ่น ตามภูมิภาค" ที่แสดงในส่วนบทนำของหน้านี้ ดูตัวอย่างเพิ่มเติมได้ที่หน้าตัวอย่างตาราง Pivot

Apps Script

sheets/api/spreadsheet_snippets.gs

ดูใน GitHub

/**
 * Create pivot table
 * @param {string} spreadsheetId spreadsheet ID
 * @returns {*} pivot table's spreadsheet
 */
Snippets.prototype.pivotTable = (spreadsheetId) => {
  try {
    const spreadsheet = SpreadsheetApp.openById(spreadsheetId);

    // Create two sheets for our pivot table, assume we have one.
    const sheet = spreadsheet.getSheets()[0];
    sheet.copyTo(spreadsheet);

    const sourceSheetId = spreadsheet.getSheets()[0].getSheetId();
    const targetSheetId = spreadsheet.getSheets()[1].getSheetId();

    // Create pivot table
    const pivotTable = Sheets.newPivotTable();

    const gridRange = Sheets.newGridRange();
    gridRange.sheetId = sourceSheetId;
    gridRange.startRowIndex = 0;
    gridRange.startColumnIndex = 0;
    gridRange.endRowIndex = 20;
    gridRange.endColumnIndex = 7;
    pivotTable.source = gridRange;

    const pivotRows = Sheets.newPivotGroup();
    pivotRows.sourceColumnOffset = 1;
    pivotRows.showTotals = true;
    pivotRows.sortOrder = "ASCENDING";
    pivotTable.rows = pivotRows;

    const pivotColumns = Sheets.newPivotGroup();
    pivotColumns.sourceColumnOffset = 4;
    pivotColumns.sortOrder = "ASCENDING";
    pivotColumns.showTotals = true;
    pivotTable.columns = pivotColumns;

    const pivotValue = Sheets.newPivotValue();
    pivotValue.summarizeFunction = "COUNTA";
    pivotValue.sourceColumnOffset = 4;
    pivotTable.values = [pivotValue];

    // Create other metadata for the updateCellsRequest
    const cellData = Sheets.newCellData();
    cellData.pivotTable = pivotTable;

    const rows = Sheets.newRowData();
    rows.values = cellData;

    const start = Sheets.newGridCoordinate();
    start.sheetId = targetSheetId;
    start.rowIndex = 0;
    start.columnIndex = 0;

    const updateCellsRequest = Sheets.newUpdateCellsRequest();
    updateCellsRequest.rows = rows;
    updateCellsRequest.start = start;
    updateCellsRequest.fields = "pivotTable";

    // Batch update our spreadsheet
    const batchUpdate = Sheets.newBatchUpdateSpreadsheetRequest();
    const updateCellsRawRequest = Sheets.newRequest();
    updateCellsRawRequest.updateCells = updateCellsRequest;
    batchUpdate.requests = [updateCellsRawRequest];
    const response = Sheets.Spreadsheets.batchUpdate(
      batchUpdate,
      spreadsheetId,
    );

    return response;
  } catch (err) {
    // TODO (developer) - Handle exception
    console.log("Failed with error %s", err.message);
  }
};

Java

sheets/snippets/src/main/java/PivotTables.java

ดูใน GitHub

import com.google.api.client.googleapis.json.GoogleJsonError;
import com.google.api.client.googleapis.json.GoogleJsonResponseException;
import com.google.api.client.http.HttpRequestInitializer;
import com.google.api.client.http.javanet.NetHttpTransport;
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.AddSheetRequest;
import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetRequest;
import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetResponse;
import com.google.api.services.sheets.v4.model.CellData;
import com.google.api.services.sheets.v4.model.GridCoordinate;
import com.google.api.services.sheets.v4.model.GridRange;
import com.google.api.services.sheets.v4.model.PivotGroup;
import com.google.api.services.sheets.v4.model.PivotTable;
import com.google.api.services.sheets.v4.model.PivotValue;
import com.google.api.services.sheets.v4.model.Request;
import com.google.api.services.sheets.v4.model.RowData;
import com.google.api.services.sheets.v4.model.UpdateCellsRequest;
import com.google.auth.http.HttpCredentialsAdapter;
import com.google.auth.oauth2.GoogleCredentials;
import com.google.common.collect.Lists;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/* Class to demonstrate the use of Spreadsheet Create Pivot Tables API */
public class PivotTables {
  /**
   * Create pivot table.
   *
   * @param spreadsheetId - Id of the spreadsheet.
   * @return pivot table's spreadsheet
   * @throws IOException - if credentials file not found.
   */
  public static BatchUpdateSpreadsheetResponse pivotTables(String spreadsheetId)
      throws IOException {
        /* Load pre-authorized user credentials from the environment.
           TODO(developer) - See https://developers.google.com/identity for
            guides on implementing OAuth2 for your application. */
    GoogleCredentials credentials = GoogleCredentials.getApplicationDefault()
        .createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS));
    HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter(
        credentials);

    // Create the sheets API client
    Sheets service = new Sheets.Builder(new NetHttpTransport(),
        GsonFactory.getDefaultInstance(),
        requestInitializer)
        .setApplicationName("Sheets samples")
        .build();

    // Create two sheets for our pivot table.
    List<Request> sheetsRequests = new ArrayList<>();
    BatchUpdateSpreadsheetResponse result = null;
    try {
      sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest()));
      sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest()));

      BatchUpdateSpreadsheetRequest createSheetsBody = new BatchUpdateSpreadsheetRequest()
          .setRequests(sheetsRequests);
      BatchUpdateSpreadsheetResponse createSheetsResponse = service.spreadsheets()
          .batchUpdate(spreadsheetId, createSheetsBody).execute();
      int sourceSheetId = createSheetsResponse.getReplies().get(0).getAddSheet().getProperties()
          .getSheetId();
      int targetSheetId = createSheetsResponse.getReplies().get(1).getAddSheet().getProperties()
          .getSheetId();

      PivotTable pivotTable = new PivotTable()
          .setSource(
              new GridRange()
                  .setSheetId(sourceSheetId)
                  .setStartRowIndex(0)
                  .setStartColumnIndex(0)
                  .setEndRowIndex(20)
                  .setEndColumnIndex(7)
          )
          .setRows(Collections.singletonList(
              new PivotGroup()
                  .setSourceColumnOffset(1)
                  .setShowTotals(true)
                  .setSortOrder("ASCENDING")
          ))
          .setColumns(Collections.singletonList(
              new PivotGroup()
                  .setSourceColumnOffset(4)
                  .setShowTotals(true)
                  .setSortOrder("ASCENDING")
          ))
          .setValues(Collections.singletonList(
              new PivotValue()
                  .setSummarizeFunction("COUNTA")
                  .setSourceColumnOffset(4)
          ));
      List<Request> requests = Lists.newArrayList();
      Request updateCellsRequest = new Request().setUpdateCells(new UpdateCellsRequest()
          .setFields("*")
          .setRows(Collections.singletonList(
              new RowData().setValues(
                  Collections.singletonList(
                      new CellData().setPivotTable(pivotTable))
              )
          ))
          .setStart(new GridCoordinate()
              .setSheetId(targetSheetId)
              .setRowIndex(0)
              .setColumnIndex(0)

          ));

      requests.add(updateCellsRequest);
      BatchUpdateSpreadsheetRequest updateCellsBody = new BatchUpdateSpreadsheetRequest()
          .setRequests(requests);
      result = service.spreadsheets().batchUpdate(spreadsheetId, updateCellsBody).execute();
    } catch (GoogleJsonResponseException e) {
      // TODO(developer) - handle error appropriately
      GoogleJsonError error = e.getDetails();
      if (error.getCode() == 404) {
        System.out.printf("Spreadsheet not found with id '%s'.\n", spreadsheetId);
      } else {
        throw e;
      }
    }
    return result;
  }
}

JavaScript

sheets/snippets/sheets_pivot_tables.js

ดูใน GitHub

function pivotTable(spreadsheetId, callback) {
  // Create two sheets for our pivot table
  const requests = [{
    addSheet: {},
  }, {
    addSheet: {},
  }];
  const batchUpdateRequest = {requests: requests};
  try {
    gapi.client.sheets.spreadsheets.batchUpdate({
      spreadsheetId: spreadsheetId,
      resource: batchUpdateRequest,
    }).then((response) => {
      const sourceSheetId = response.result.replies[0].addSheet.properties.sheetId;
      const targetSheetId = response.result.replies[1].addSheet.properties.sheetId;

      const requests = [{
        updateCells: {
          rows: {
            values: [{
              pivotTable: {
                source: {
                  sheetId: sourceSheetId,
                  startRowIndex: 0,
                  startColumnIndex: 0,
                  endRowIndex: 20,
                  endColumnIndex: 7,
                },
                rows: [{
                  sourceColumnOffset: 1,
                  showTotals: true,
                  sortOrder: 'ASCENDING',
                }],
                columns: [{
                  sourceColumnOffset: 4,
                  sortOrder: 'ASCENDING',
                  showTotals: true,
                }],
                values: [{
                  summarizeFunction: 'COUNTA',
                  sourceColumnOffset: 4,
                }],
                valueLayout: 'HORIZONTAL',
              },
            },
            ],
          },
          start: {
            sheetId: targetSheetId,
            rowIndex: 0,
            columnIndex: 0,
          },
          fields: 'pivotTable',
        },
      }];

      const body = {
        requests,
      };
      gapi.client.sheets.spreadsheets.batchUpdate({
        spreadsheetId: spreadsheetId,
        resource: body,
      }).then((response) => {
        if (callback) callback(response);
      });
    });
  } catch (err) {
    document.getElementById('content').innerText = err.message;
    return;
  }
}

Node.js

sheets/snippets/sheets_pivot_table.js

ดูใน GitHub

import {GoogleAuth} from 'google-auth-library';
import {google} from 'googleapis';

/**
 * Creates a pivot table in a spreadsheet.
 * @param {string} spreadsheetId The ID of the spreadsheet.
 * @return {Promise<object>} The response from the batch update.
 */
async function pivotTable(spreadsheetId) {
  // Authenticate with Google and get an authorized client.
  const auth = new GoogleAuth({
    scopes: 'https://www.googleapis.com/auth/spreadsheets',
  });

  const service = google.sheets({version: 'v4', auth});

  // Create two new sheets for the pivot table.
  // One for the source data and one for the pivot table itself.
  let requests = [
    {
      addSheet: {},
    },
    {
      addSheet: {},
    },
  ];
  let resource = {requests};
  let response = await service.spreadsheets.batchUpdate({
    spreadsheetId,
    resource,
  });

  // Get the IDs of the newly created sheets.
  const sourceSheetId = response.data.replies[0].addSheet.properties.sheetId;
  const targetSheetId = response.data.replies[1].addSheet.properties.sheetId;

  // Add a pivot table to the new sheet.
  requests = [
    {
      updateCells: {
        rows: {
          values: [
            {
              pivotTable: {
                // The source data for the pivot table.
                source: {
                  sheetId: sourceSheetId,
                  startRowIndex: 0,
                  startColumnIndex: 0,
                  endRowIndex: 20,
                  endColumnIndex: 7,
                },
                // The rows of the pivot table.
                rows: [
                  {
                    sourceColumnOffset: 1,
                    showTotals: true,
                    sortOrder: 'ASCENDING',
                  },
                ],
                // The columns of the pivot table.
                columns: [
                  {
                    sourceColumnOffset: 4,
                    sortOrder: 'ASCENDING',
                    showTotals: true,
                  },
                ],
                // The values to display in the pivot table.
                values: [
                  {
                    summarizeFunction: 'COUNTA',
                    sourceColumnOffset: 4,
                  },
                ],
                valueLayout: 'HORIZONTAL',
              },
            },
          ],
        },
        // The location to place the pivot table.
        start: {
          sheetId: targetSheetId,
          rowIndex: 0,
          columnIndex: 0,
        },
        fields: 'pivotTable',
      },
    },
  ];
  resource = {
    requests,
  };

  // Send the batch update request to create the pivot table.
  response = service.spreadsheets.batchUpdate({
    spreadsheetId,
    resource,
  });
  return response;
}

PHP

sheets/snippets/src/SpreadsheetPivotTables.php

ดูใน GitHub

<?php
use Google\Client;
use Google\Service\Drive;
use Google\Service\Sheets\BatchUpdateSpreadsheetRequest;


function pivotTables($spreadsheetId)
    {
        /* Load pre-authorized user credentials from the environment.
           TODO(developer) - See https://developers.google.com/identity for
            guides on implementing OAuth2 for your application. */
        $client = new Google\Client();
        $client->useApplicationDefaultCredentials();
        $client->addScope(Google\Service\Drive::DRIVE);
        $service = new Google_Service_Sheets($client);
        try{
            $requests = [
                new Google_Service_Sheets_Request([
                    'addSheet' => [
                        'properties' => [
                            'title' => 'Sheet 1'
                            ]
                ]
            ]),
            new Google_Service_Sheets_Request([
                'addSheet' => [
                    'properties' => [
                        'title' => 'Sheet 2'
                        ]
                        ]
                        ])
                    ];
                    // Create two sheets for our pivot table
                    $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
                        'requests' => $requests
                    ]);
        $batchUpdateResponse = $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);
        $sourceSheetId = $batchUpdateResponse->replies[0]->addSheet->properties->sheetId;
        $targetSheetId = $batchUpdateResponse->replies[1]->addSheet->properties->sheetId;
        $requests = [
            'updateCells' => [
                'rows' => [
                    'values' => [
                        [
                            'pivotTable' => [
                                'source' => [
                                    'sheetId' => $sourceSheetId,
                                    'startRowIndex' => 0,
                                    'startColumnIndex' => 0,
                                    'endRowIndex' => 20,
                                    'endColumnIndex' => 7
                                ],
                                'rows' => [
                                    [
                                        'sourceColumnOffset' => 1,
                                        'showTotals' => true,
                                        'sortOrder' => 'ASCENDING',
                                    ],
                                ],
                                'columns' => [
                                    [
                                        'sourceColumnOffset' => 4,
                                        'sortOrder' => 'ASCENDING',
                                        'showTotals' => true,
                                    ]
                                ],
                                'values' => [
                                    [
                                        'summarizeFunction' => 'COUNTA',
                                        'sourceColumnOffset' => 4
                                        ]
                                    ],
                                'valueLayout' => 'HORIZONTAL'
                            ]
                        ]
                        ]
                ],
                'start' => [
                    'sheetId' => $targetSheetId,
                    'rowIndex' => 0,
                    'columnIndex' => 0
                ],
                'fields' => 'pivotTable'
            ]
        ];
        return $batchUpdateResponse;
    }
        catch(Exception $e) {
            // TODO(developer) - handle error appropriately
            echo 'Message: ' .$e->getMessage();
        }
    }

Python

sheets/snippets/sheets_pivot_tables.py

ดูใน GitHub

import google.auth
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


def pivot_tables(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)
    # Create two sheets for our pivot table.
    body = {"requests": [{"addSheet": {}}, {"addSheet": {}}]}
    batch_update_response = (
        service.spreadsheets()
        .batchUpdate(spreadsheetId=spreadsheet_id, body=body)
        .execute()
    )
    source_sheet_id = (
        batch_update_response.get("replies")[0]
        .get("addSheet")
        .get("properties")
        .get("sheetId")
    )
    target_sheet_id = (
        batch_update_response.get("replies")[1]
        .get("addSheet")
        .get("properties")
        .get("sheetId")
    )
    requests = []
    requests.append(
        {
            "updateCells": {
                "rows": {
                    "values": [
                        {
                            "pivotTable": {
                                "source": {
                                    "sheetId": source_sheet_id,
                                    "startRowIndex": 0,
                                    "startColumnIndex": 0,
                                    "endRowIndex": 20,
                                    "endColumnIndex": 7,
                                },
                                "rows": [
                                    {
                                        "sourceColumnOffset": 1,
                                        "showTotals": True,
                                        "sortOrder": "ASCENDING",
                                    },
                                ],
                                "columns": [{
                                    "sourceColumnOffset": 4,
                                    "sortOrder": "ASCENDING",
                                    "showTotals": True,
                                }],
                                "values": [{
                                    "summarizeFunction": "COUNTA",
                                    "sourceColumnOffset": 4,
                                }],
                                "valueLayout": "HORIZONTAL",
                            }
                        }
                    ]
                },
                "start": {
                    "sheetId": target_sheet_id,
                    "rowIndex": 0,
                    "columnIndex": 0,
                },
                "fields": "pivotTable",
            }
        }
    )
    body = {"requests": requests}
    response = (
        service.spreadsheets()
        .batchUpdate(spreadsheetId=spreadsheet_id, body=body)
        .execute()
    )
    return response

  except HttpError as error:
    print(f"An error occurred: {error}")
    return error


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

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb

ดูใน GitHub

requests = [{
  update_cells: {
    rows:   {
      values: [
        {
          pivot_table: {
            source:       {
              sheet_id:           source_sheet_id,
              start_row_index:    0,
              start_column_index: 0,
              end_row_index:      20,
              end_column_index:   7
            },
            rows:         [
              {
                source_column_offset: 1,
                show_totals:          true,
                sort_order:           'ASCENDING',
              },
            ],
            columns:      [
              {
                source_column_offset: 4,
                sort_order:           'ASCENDING',
                show_totals:          true,
              }
            ],
            values:       [
              {
                summarize_function:   'COUNTA',
                source_column_offset: 4
              }
            ],
            value_layout: 'HORIZONTAL'
          }
        }
      ]
    },
    start:  {
      sheet_id:     target_sheet_id,
      row_index:    0,
      column_index: 0
    },
    fields: 'pivotTable'
  }
}]
result = service.batch_update_spreadsheet(spreadsheet_id, body, {})