La API de Hojas de cálculo de Google te permite crear y actualizar tablas dinámicas dentro de hojas de cálculo. En los ejemplos de esta página, se muestra cómo puedes realizar algunas operaciones comunes de tablas dinámicas con la API de Hojas de cálculo.
Estos ejemplos se presentan en forma de solicitudes HTTP para que tengan lenguaje neutro. Para aprender a implementar una actualización por lotes en diferentes lenguajes con las bibliotecas cliente de la API de Google, consulta Cómo actualizar hojas de cálculo.
En estos ejemplos, los marcadores de posición SPREADSHEET_ID y SHEET_ID indican dónde deberías proporcionar esos ID. Puedes encontrar el ID de la hoja de cálculo en su URL. Puedes obtener el ID de la hoja con el método spreadsheets.get. 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, supón que la hoja de cálculo en uso 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 en 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 | Betina | 1/3/2016 |
| 3 | Puerta | D-01X | USD 15.00 | 2 | Sur | Eduardo | 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 | $6.00 | 4 | Norte | Devyn | 2/4/2016 |
| 7 | Panel | P-052 | USD 11.50 | 7 | Este | Enrique | 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 | Eduardo | 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 | Enrique | 19/5/2016 |
| 19 | Puerta | D-05 | USD 17.70 | 7 | Oeste | Betina | 28/6/2016 |
| 20 | Marco | FR-0B1 | USD 34.00 | 8 | Norte | Sheldon | 30/3/2016 |
Cómo agregar una tabla dinámica
En la siguiente muestra de código de spreadsheets.batchUpdate, se muestra cómo usar UpdateCellsRequest para crear una tabla dinámica a partir de los datos de origen y anclarla a 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 (Quantity) que indica la cantidad de ventas. Como solo hay un grupo de valores, las 2 opciones de configuración posibles de
valueLayoutson equivalentes. - Dos grupos de filas (Item Category y Model Number) El primer ordena de forma ascendente a partir de la Cantidad total de la Región "Oeste". Por lo tanto, "Motor" (sin ventas en el oeste) aparece antes que "Puerta" (con 15 ventas en el oeste). El grupo Número de modelo ordena en orden descendente del total de ventas en todas las regiones, por lo que "W-24" (15 ventas) aparece por encima de "W-25" (8 ventas). Para ello, se debe configurar el campo
valueBucketen{}. - Un grupo de columnas (Region) que se ordena de forma ascendente a partir de la mayoría de las ventas.
Nuevamente,
valueBucketse establece en{}. “Norte” tiene la menor cantidad de ventas totales, por lo que aparece como la primera columna de Región.
El protocolo de solicitud se muestra a continuación.
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 la siguiente:
Cómo agregar una tabla dinámica con valores calculados
En la siguiente muestra de código de spreadsheets.batchUpdate, 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, anclada 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 (Quantity y Total Price). El primero indica el
número de ventas. El segundo es un valor calculado que se basa en el producto del costo de una pieza y la cantidad total de ventas, mediante esta fórmula:
=Cost*SUM(Quantity). - Tres grupos de filas (Item Category, Model Number y Cost)
- Un grupo de columnas (Región).
- Los grupos de filas y columnas ordenan por nombre (en lugar de Cantidad) en cada grupo, y la tabla se ordena alfabéticamente. Para ello, se debe omitir el campo
valueBucketdePivotGroup. - Para simplificar la apariencia de la tabla, la solicitud oculta los subtotales de todos los grupos de filas y columnas, excepto los principales.
- La solicitud establece
valueLayoutenVERTICALpara mejorar el aspecto de la tabla.valueLayoutsolo es importante si hay 2 o más grupos de valores.
El protocolo de solicitud se muestra a continuación.
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 la siguiente:
Cómo borrar una tabla dinámica
En la siguiente muestra de código de spreadsheets.batchUpdate, 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.
Una UpdateCellsRequest puede quitar una tabla dinámica si incluye "pivotTable" en el parámetro fields y, al mismo tiempo, se omite el campo pivotTable en la celda de anclaje.
El protocolo de solicitud se muestra a continuación.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
"requests": [
{
"updateCells": {
"rows": [
{
"values": [
{}
]
}
],
"start": {
"sheetId": SHEET_ID,
"rowIndex": 49,
"columnIndex": 0
},
"fields": "pivotTable"
}
}
]
}
Cómo editar las columnas y filas de una tabla dinámica
En la siguiente muestra de código de spreadsheets.batchUpdate, se muestra cómo usar UpdateCellsRequest para editar la tabla dinámica creada en Agrega una tabla dinámica.
Los subconjuntos del campo pivotTable en el recurso CellData no se pueden cambiar de forma individual con el parámetro fields. Para realizar cambios, se debe proporcionar todo el campo pivotTable. Esencialmente, editar una tabla dinámica
requiere 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 (Model Number).
- Agrega un grupo de columnas (Vendedor). Las columnas se ordenan en orden descendente por la cantidad total de ventas del panel. "Carmen" (15 ventas de Panel) aparece a la izquierda de "Jessie" (13 ventas de Panel).
- Contrae la columna para cada Región, excepto por “Oeste”, ocultando el grupo Vendedor de esa región. Para ello, se debe configurar
collapsedcomotrueen el elementovalueMetadatacorrespondiente a esa columna del grupo de columnas Región.
El protocolo de solicitud se muestra a continuación.
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 la siguiente:
Cómo leer los datos de la tabla dinámica
En la siguiente muestra de código de spreadsheets.get, se indica cómo obtener datos de tablas dinámicas de una hoja de cálculo. El parámetro de consulta fields especifica que solo se deben mostrar los datos de la tabla dinámica (en lugar de los datos de valores de celdas).
El protocolo de solicitud se muestra a continuación.
GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)
La respuesta consta de un recurso Spreadsheet, que contiene un objeto Sheet con elementos SheetProperties. También hay un array de elementos GridData con información sobre PivotTable.
La información de la tabla dinámica se encuentra dentro del recurso CellData de la hoja para la celda en la que está anclada (es decir, la esquina superior izquierda de la tabla). Si un campo de respuesta tiene 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 las hojas o celdas que no contienen datos de la tabla dinámica. Como referencia, esta solicitud también muestra los ID 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
}
}
],
}