Help us understand the problem. What is going on with this article?

Google Spreadsheet API v4 Pivot Table (ピボットテーブル)のサンプルコードをコピペしても動かないし、エラーも吐かない件

Pivot Tables

下の方にあるコードサンプル
Pivot_Tables_ _ _Sheets_API_ _ _Google_Developers.png

問題

適当なデータを用意して、適当なsource範囲なりで設定して実行すると

Success - #<Google::Apis::SheetsV4::BatchUpdateSpreadsheetResponse:0x00005567c9a9e490

などと返ってくるが、実際にはPivotテーブルが作られていない。

原因

Pivotテーブルを作るにはbatchUpdateupdateCellsを使って更新するが、パラメータのネストが深い。そしてサンプルコードが間違っている。

UpdateCellsRequest

  • rows <= ここが深い
  • fields
  • start / range (union fieldという事で、どちらかいずれか)

rows

まず、配列だ

rows: [RowData, RowData, RowData ...]

RowData

valuesが配列

values: [CellData, CellData, CellData ...]

CellData

pivotTable要素がある

総合すると

requests = []

requests.push(
  {
    update_cells: {
      # 配列
      rows: [
        {
          # 配列
          values: [
            {
              # PivotTableの内容
              pivot_table: {
                source: {
                  ...
                  ...
                },
                rows: [
                  ...
                  ...
                ],
                values: [
                  ...
                  ...
                ]
              }
            }
          ]
        }
      ],
      # pivotTableを指定
      # https://developers.google.com/slides/how-tos/field-masks?utm_campaign=gsuite_series_slidesapi_041317&utm_source=gdev&utm_medium=yt-desc#updating_with_a_field_mask
      fields: "pivotTable",
      start: { 
        ...
      }
    }
  }
)

request_object = Google::Apis::SheetsV4::BatchUpdateSpreadsheetRequest.new
request_object.requests = requests

# request_objectを {requests: requests} で渡すとなぜかエラー
# ArgumentError: unknown keyword: requests
response = GoogleApi.sheet_service.batch_update_spreadsheet(
  your_spreadsheet_id,
  request_object
)

サンプルコードはrowsが配列になっていないので(invalid)、Pivotテーブルは作られない(が、エラーも出ない)。

Method: spreadsheets.batchUpdate

Each request is validated before being applied. If any request is not valid then the entire request will fail and nothing will be applied.

1個でも変なのがあれば、全部取り止めって書いてあるように読めるが...

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした