La API de Google Sheets te permite crear y actualizar tablas dinámicas en hojas de cálculo. En los ejemplos de esta página, se ilustra cómo puedes realizar algunas operaciones comunes de tablas dinámicas con la API de Sheets.
Estos ejemplos se presentan en forma de solicitudes HTTP para que sean independientes del lenguaje. Para obtener información sobre cómo implementar una actualización por lotes en diferentes lenguajes con las bibliotecas cliente de la API de Google, consulta Actualiza hojas de cálculo.
En estos ejemplos, los marcadores de posición SPREADSHEET_ID y
SHEET_ID
indican dónde debes proporcionar esos IDs. Puedes encontrar el ID
de la hoja de cálculo en la URL de la hoja de cálculo.
Puedes obtener el ID de la hoja con
el
spreadsheets.get
método. Los rangos se especifican con la notación
A1. Un rango de ejemplo es Sheet1!A1:D5.
Además, el marcador de posición SOURCE_SHEET_ID indica tu hoja con los datos de origen. En estos ejemplos, esta es la tabla que aparece
en Datos de origen de la tabla dinámica.
Datos de origen de la tabla dinámica
Para estos ejemplos, supongamos que la hoja de cálculo que se usa tiene los siguientes datos de origen de "ventas" en su primera hoja ("Sheet1"). Las cadenas de la primera fila son etiquetas para las columnas individuales. Para ver ejemplos de cómo leer desde otras hojas de tu hoja de cálculo, consulta Notación A1.
| A | B | C | D | E | F | G | |
| 1 | Item Category | Número de modelo | Costo | Cantidad | Región | Vendedor | Fecha de envío |
| 2 | Rueda de la fortuna | W-24 | USD 20.50 | 4 | Oeste | Beth | 1/3/2016 |
| 3 | Puerta | D-01X | USD 15.00 | 2 | Sur | Amir | 15/3/2016 |
| 4 | Motor | ENG-0134 | USD 100.00 | 1 | Norte | Carmen | 20/3/2016 |
| 5 | Marco | FR-0B1 | USD 34.00 | 8 | Este | Hannah | 12/3/2016 |
| 6 | Panel | P-034 | USD 6.00 | 4 | Norte | Devyn | 2/4/2016 |
| 7 | Panel | P-052 | USD 11.50 | 7 | Este | Erik | 16/5/2016 |
| 8 | Rueda de la fortuna | W-24 | USD 20.50 | 11 | Sur | Sheldon | 30/4/2016 |
| 9 | Motor | ENG-0161 | USD 330.00 | 2 | Norte | Jessie | 2/7/2016 |
| 10 | Puerta | D-01Y | USD 29.00 | 6 | Oeste | Armando | 13/3/2016 |
| 11 | Marco | FR-0B1 | USD 34.00 | 9 | Sur | Yuliana | 27/2/2016 |
| 12 | Panel | P-102 | USD 3.00 | 15 | Oeste | Carmen | 18/4/2016 |
| 13 | Panel | P-105 | USD 8.25 | 13 | Oeste | Jessie | 20/6/2016 |
| 14 | Motor | ENG-0211 | USD 283.00 | 1 | Norte | Amir | 21/6/2016 |
| 15 | Puerta | D-01X | USD 15.00 | 2 | Oeste | Armando | 3/7/2016 |
| 16 | Marco | FR-0B1 | USD 34.00 | 6 | Sur | Carmen | 15/7/2016 |
| 17 | Rueda de la fortuna | W-25 | USD 20.00 | 8 | Sur | Hannah | 2/5/2016 |
| 18 | Rueda de la fortuna | W-11 | USD 29.00 | 13 | Este | Erik | 19/5/2016 |
| 19 | Puerta | D-05 | USD 17.70 | 7 | Oeste | Beth | 28/6/2016 |
| 20 | Marco | FR-0B1 | USD 34.00 | 8 | Norte | Sheldon | 30/3/2016 |
Agrega una tabla dinámica
En el siguiente
spreadsheets.batchUpdate
muestra de código, se muestra cómo usar
UpdateCellsRequest
para crear una tabla dinámica a partir de los datos de origen y anclarla en la celda A50 de la
hoja especificada por SHEET_ID.
La solicitud configura la tabla dinámica con las siguientes propiedades:
- Un grupo de valores (Cantidad) que indica la cantidad de ventas. Como solo hay un grupo de valores, los 2 parámetros de configuración posibles son equivalentes.
valueLayout - Dos grupos de filas (Categoría de artículo y Número de modelo). El primero ordena en valor ascendente la Cantidad total de la Región "Oeste". Por lo tanto, "Motor" (sin ventas en el Oeste) aparece sobre "Puerta" (con 15 ventas en el Oeste). El grupo Número de modelo ordena en orden descendente las ventas totales en todas las regiones, por lo que "W-24" (15 ventas) aparece sobre "W-25" (8 ventas). Para ello, se configura el
campo
valueBucketcomo{}. - Un grupo de columnas (Región) que ordena en orden ascendente la mayor cantidad de ventas.
De nuevo,
valueBucketse establece en{}. "Norte" tiene la menor cantidad de ventas totales, por lo que aparece como la primera columna Región.
A continuación, se muestra el protocolo de solicitud.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate{ "requests": [ { "updateCells": { "rows": [ { "values": [ { "pivotTable": { "source": { "sheetId":SOURCE_SHEET_ID, "startRowIndex": 0, "startColumnIndex": 0, "endRowIndex": 20, "endColumnIndex": 7 }, "rows": [ { "sourceColumnOffset": 0, "showTotals": true, "sortOrder": "ASCENDING", "valueBucket": { "buckets": [ { "stringValue": "West" } ] } }, { "sourceColumnOffset": 1, "showTotals": true, "sortOrder": "DESCENDING", "valueBucket": {} } ], "columns": [ { "sourceColumnOffset": 4, "sortOrder": "ASCENDING", "showTotals": true, "valueBucket": {} } ], "values": [ { "summarizeFunction": "SUM", "sourceColumnOffset": 3 } ], "valueLayout": "HORIZONTAL" } } ] } ], "start": { "sheetId":SHEET_ID, "rowIndex": 49, "columnIndex": 0 }, "fields": "pivotTable" } } ] }
La solicitud crea una tabla dinámica como esta:
Agrega una tabla dinámica con valores calculados
En el siguiente
spreadsheets.batchUpdate
muestra de código, se muestra cómo usar
UpdateCellsRequest
para crear una tabla dinámica con un grupo de valores calculados a partir de los datos de origen y
anclarla en la celda A50 de la hoja especificada por SHEET_ID.
La solicitud configura la tabla dinámica con las siguientes propiedades:
- Dos grupos de valores (Cantidad y Precio total). El primero indica la cantidad de ventas. El segundo es un valor calculado basado en el producto del costo de una pieza y su cantidad total de ventas, con la siguiente fórmula:
=Cost*SUM(Quantity). - Tres grupos de filas (Categoría de artículo, Número de modelo y Costo).
- Un grupo de columnas (Región).
- Los grupos de filas y columnas se ordenan por nombre (en lugar de por Cantidad) en cada grupo, lo que alfabetiza la tabla. Para ello, se omite el
valueBucketcampo de laPivotGroup.- Para simplificar la apariencia de la tabla, la solicitud oculta los subtotales de todos los grupos de filas y columnas principales.
- La solicitud establece
valueLayoutenVERTICALpara mejorar la apariencia de la tabla.valueLayoutsolo es importante si hay 2 o más grupos de valores.
A continuación, se muestra el protocolo de solicitud.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate{
"requests": [
{
"updateCells": {
"rows": [
{
"values": [
{
"pivotTable": {
"source": {
"sheetId": SOURCE_SHEET_ID,
"startRowIndex": 0,
"startColumnIndex": 0,
"endRowIndex": 20,
"endColumnIndex": 7
},
"rows": [
{
"sourceColumnOffset": 0,
"showTotals": true,
"sortOrder": "ASCENDING"
},
{
"sourceColumnOffset": 1,
"showTotals": false,
"sortOrder": "ASCENDING",
},
{
"sourceColumnOffset": 2,
"showTotals": false,
"sortOrder": "ASCENDING",
}
],
"columns": [
{
"sourceColumnOffset": 4,
"sortOrder": "ASCENDING",
"showTotals": true
}
],
"values": [
{
"summarizeFunction": "SUM",
"sourceColumnOffset": 3
},
{
"summarizeFunction": "CUSTOM",
"name": "Total Price",
"formula": "=Cost*SUM(Quantity)"
}
],
"valueLayout": "VERTICAL"
}
}
]
}
],
"start": {
"sheetId": SHEET_ID,
"rowIndex": 49,
"columnIndex": 0
},
"fields": "pivotTable"
}
}
]
}La solicitud crea una tabla dinámica como esta:
Borra una tabla dinámica
En el siguiente
spreadsheets.batchUpdate
muestra de código, se muestra cómo usar
UpdateCellsRequest
para borrar una tabla dinámica (si está presente) que está anclada en la celda A50 de la hoja
especificada por SHEET_ID.
Un UpdateCellsRequest puede quitar una tabla dinámica si incluye "pivotTable" en el parámetro fields y, al mismo tiempo, omite el campo pivotTable en la celda de anclaje.
A continuación, se muestra el protocolo de solicitud.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate{
"requests": [
{
"updateCells": {
"rows": [
{
"values": [
{}
]
}
],
"start": {
"sheetId": SHEET_ID,
"rowIndex": 49,
"columnIndex": 0
},
"fields": "pivotTable"
}
}
]
}Edita las columnas y filas de la tabla dinámica
En el siguiente
spreadsheets.batchUpdate
muestra de código, se muestra cómo usar
UpdateCellsRequest
para editar la tabla dinámica creada en Agrega una tabla dinámica.
Los subconjuntos del
pivotTable
campo en el
CellData recurso
no se pueden cambiar de forma individual con el fields parámetro. Para realizar ediciones, se debe proporcionar todo el campo pivotTable. Básicamente, para editar una tabla dinámica, es necesario reemplazarla por una nueva.
La solicitud realiza los siguientes cambios en la tabla dinámica original:
- Quita el segundo grupo de filas de la tabla dinámica original (Número de modelo).
- Agrega un grupo de columnas (Vendedor). Las columnas se ordenan en orden descendente según la cantidad total de ventas de Panel. "Carmen" (15 ventas de Panel) aparece a la izquierda de "Jessie" (13 ventas de Panel).
- Contrae la columna de cada Región, excepto "Oeste", y oculta el
Vendedor grupo para esa región. Para ello, se configura
collapsedcomotruevalueMetadatapara esa columna en el grupo de columnas Región.
A continuación, se muestra el protocolo de solicitud.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate{ "requests": [ { "updateCells": { "rows": [ { "values": [ { "pivotTable": { "source": { "sheetId":SOURCE_SHEET_ID, "startRowIndex": 0, "startColumnIndex": 0, "endRowIndex": 20, "endColumnIndex": 7 }, "rows": [ { "sourceColumnOffset": 0, "showTotals": true, "sortOrder": "ASCENDING", "valueBucket": { "buckets": [ { "stringValue": "West" } ] } } ], "columns": [ { "sourceColumnOffset": 4, "sortOrder": "ASCENDING", "showTotals": true, "valueBucket": {}, "valueMetadata": [ { "value": { "stringValue": "North" }, "collapsed": true }, { "value": { "stringValue": "South" }, "collapsed": true }, { "value": { "stringValue": "East" }, "collapsed": true } ] }, { "sourceColumnOffset": 5, "sortOrder": "DESCENDING", "showTotals": false, "valueBucket": { "buckets": [ { "stringValue": "Panel" } ] }, } ], "values": [ { "summarizeFunction": "SUM", "sourceColumnOffset": 3 } ], "valueLayout": "HORIZONTAL" } } ] } ], "start": { "sheetId":SHEET_ID, "rowIndex": 49, "columnIndex": 0 }, "fields": "pivotTable" } } ] }
La solicitud crea una tabla dinámica como esta:
Lee datos de la tabla dinámica
En el siguiente
spreadsheets.get ejemplo de código
se muestra cómo obtener datos de la tabla dinámica de una hoja de cálculo. El parámetro de búsqueda fields especifica que solo se deben mostrar los datos de la tabla dinámica (en lugar de los datos del valor de la celda).
A continuación, se muestra el protocolo de solicitud.
GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)La respuesta consta de un
Spreadsheet
recurso, que contiene un
Sheet objeto con
SheetProperties
elementos. También hay un array de
GridData
elementos que contiene información sobre
PivotTable.
La información de la tabla dinámica se encuentra dentro del recurso
de la hoja para la celda en la que está anclada la tabla (es decir, la esquina superior izquierda
de la tabla).CellData Si un campo de respuesta se establece en el valor predeterminado, se omite de la respuesta.
En este ejemplo, la primera hoja (SOURCE_SHEET_ID) tiene los datos de origen de la tabla sin procesar, mientras que la segunda hoja (SHEET_ID) tiene la tabla dinámica
, anclada en B3. Las llaves vacías indican hojas o celdas que no contienen datos de la tabla dinámica. Como referencia, esta solicitud también muestra los IDs de la hoja.
{ "sheets": [ { "data": [{}], "properties": { "sheetId":SOURCE_SHEET_ID} }, { "data": [ { "rowData": [ {}, {}, { "values": [ {}, { "pivotTable": { "columns": [ { "showTotals": true, "sortOrder": "ASCENDING", "sourceColumnOffset": 4, "valueBucket": {} } ], "rows": [ { "showTotals": true, "sortOrder": "ASCENDING", "valueBucket": { "buckets": [ { "stringValue": "West" } ] } }, { "showTotals": true, "sortOrder": "DESCENDING", "valueBucket": {}, "sourceColumnOffset": 1 } ], "source": { "sheetId":SOURCE_SHEET_ID, "startColumnIndex": 0, "endColumnIndex": 7, "startRowIndex": 0, "endRowIndex": 20 }, "values": [ { "sourceColumnOffset": 3, "summarizeFunction": "SUM" } ] } } ] } ] } ], "properties": { "sheetId":SHEET_ID} } ], }