Các bảng tổng hợp

Hướng dẫn này mô tả cách và lý do sử dụng API Google Trang tính để tạo bảng tổng hợp trong bảng tính.

Bảng tổng hợp là gì?

Bảng tổng hợp cung cấp một cách để tóm tắt dữ liệu trong bảng tính, tự động tổng hợp, sắp xếp, đếm hoặc tính trung bình dữ liệu trong khi hiển thị kết quả tóm tắt trong một bảng mới. Bảng tổng hợp hoạt động như một loại truy vấn đối với một tập dữ liệu nguồn. Dữ liệu nguồn này tồn tại ở một vị trí khác trong bảng tính và bảng tổng hợp trình bày một chế độ xem đã xử lý của dữ liệu.

Ví dụ: hãy xem xét tập dữ liệu bán hàng sau:

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 4 Tây Beth 1/3/2016
3 Cửa ra vào D-01X $15,00 2 Nam Amir 15/3/2016
4 Công cụ ENG-0134 $100,00 1 Bắc Carmen 20/3/2016
5 Khung FR-0B1 $34,00 8 Đông Hannah 12/3/2016
6 Bảng điều khiển P-034 6 USD 4 Bắc Devyn 2/4/2016
7 Bảng điều khiển P-052 $11,50 7 Đông Erik 16/5/2016
8 Bánh xe W-24 $20,50 11 Nam Sheldon 30/4/2016
9 Công cụ ENG-0161 $330,00 2 Bắc Jessie 2/7/2016
10 Cửa ra vào D-01Y $29,00 6 Tây Armando 13/3/2016
11 Khung FR-0B1 $34,00 9 Nam Yuliana 27/2/2016
12 Bảng điều khiển P-102 3 đô la 15 Tây Carmen 18/4/2016
13 Bảng điều khiển P-105 $8,25 13 Tây Jessie 20/6/2016
14 Công cụ ENG-0211 $283,00 1 Bắc Amir 21/6/2016
15 Cửa ra vào D-01X $15,00 2 Tây Armando 3/7/2016
16 Khung FR-0B1 $34,00 6 Nam Carmen 15/7/2016
17 Bánh xe W-25 $20,00 8 Nam Hannah 2/5/2016
18 Bánh xe W-11 $29,00 13 Đông Erik 19/5/2016
19 Cửa ra vào D-05 $17,70 7 Tây Beth 28/6/2016
20 Khung FR-0B1 $34,00 8 Bắc Sheldon 30/3/2016

Bạn có thể sử dụng bảng tổng hợp để tạo báo cáo cho biết số lượng từng kiểu máy đã bán được ở mỗi khu vực:

ảnh chụp màn hình của một bảng tổng hợp cho thấy số lượng số hiệu mẫu theo khu vực

Để biết mã nguồn dùng để tạo bảng tổng hợp này, hãy xem phần Ví dụ bên dưới.

Sau khi bảng tổng hợp được đặt trong bảng tính, người dùng có thể tương tác để thay đổi cấu trúc và thông tin chi tiết của bản tóm tắt bằng giao diện người dùng Trang tính.

Làm việc với bảng tổng hợp

Định nghĩa bảng tổng hợp được liên kết với một ô duy nhất trên trang tính. Mặc dù giao diện hiển thị của bảng tổng hợp là nhiều ô theo cả chiều cao và chiều rộng, nhưng về mặt lập trình, bảng tổng hợp nằm ở một toạ độ ô duy nhất. Ô này trở thành góc trên cùng bên trái của bảng tổng hợp được hiển thị, với phạm vi ngang và dọc được xác định theo định nghĩa của bảng tổng hợp.

Thêm bảng tổng hợp

Để thêm bảng tổng hợp, hãy sử dụng phương thức batchUpdate, cung cấp một yêu cầu updateCells. Bạn sử dụng yêu cầu này để cung cấp định nghĩa PivotTable làm nội dung của một ô như minh hoạ bên dưới:

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

Thao tác này sẽ đặt bảng tổng hợp do MyPivotTable mô tả trên trang tính đã chỉ định, với góc trên cùng bên trái ở ô A1. (Chiều cao và chiều rộng của bảng tổng hợp là động; bạn chỉ cần chỉ định nguồn gốc.)

Loại PivotTable cho phép bạn chỉ định:

  • Phạm vi dữ liệu nguồn
  • Một hoặc nhiều trường có dữ liệu sẽ tạo thành các hàng của bảng tổng hợp
  • Một hoặc nhiều trường có dữ liệu sẽ tạo thành các cột của bảng tổng hợp
  • Tiêu chí lọc và tổng hợp
  • Bố cục bảng tổng hợp

Sửa đổi và xoá bảng tổng hợp

Không có yêu cầu rõ ràng nào để sửa đổi hoặc xoá bảng tổng hợp. Thay vào đó, hãy sử dụng một updateCells với nội dung ô khác nhau:

  • Để sửa đổi bảng tổng hợp, hãy tạo định nghĩa PivotTable đã sửa đổi và cập nhật ô bằng định nghĩa đó, tương tự như việc thêm một bảng tổng hợp mới.
  • Để xoá bảng tổng hợp, hãy cập nhật ô bằng các giá trị trống. Để biết ví dụ, hãy xem mẫu Xoá bảng tổng hợp bảng.

Trường hợp sử dụng

Có nhiều cách sử dụng bảng tổng hợp, trên nhiều lĩnh vực bao gồm phân tích thống kê, ứng dụng ERP, báo cáo tài chính và các lĩnh vực khác. Các trường hợp sử dụng bảng tổng hợp cổ điển bao gồm các mục như:

  • Tổng doanh số theo khu vực và quý
  • Mức lương trung bình theo chức danh và vị trí
  • Số lượng sự cố theo sản phẩm và thời gian trong ngày

Số lượng ứng dụng tiềm năng của bảng tổng hợp là rất lớn và khả năng tạo bảng tổng hợp theo phương thức lập trình là rất mạnh mẽ. Bạn có thể tạo bảng tổng hợp hỗ trợ khám phá tương tác nhưng được điều chỉnh cho phù hợp với các trường hợp cụ thể, ví dụ:

  • Khám phá dữ liệu sự cố trong khoảng thời gian 24 giờ gần đây nhất
  • Xem hoặc phân tích dữ liệu tổng hợp tương ứng với tài khoản đã chọn
  • Kiểm tra dữ liệu bán hàng cho các lãnh thổ thuộc về người dùng hiện tại

Ví dụ:

Ví dụ này tạo một bảng tổng hợp từ một tập dữ liệu để tạo báo cáo "số kiểu máy theo khu vực" như minh hoạ trong phần giới thiệu của trang này. Để xem thêm ví dụ, hãy xem trang mẫu bảng tổng hợp.

Apps Script

sheets/api/spreadsheet_snippets.gs
/**
 * 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
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
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
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
<?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
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
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, {})