La API de Hojas de cálculo de Google te permite crear y actualizar tablas dinámicas dentro de hojas de cálculo. Los ejemplos de esta página ilustran cómo puedes realizar algunas tablas dinámicas comunes las operaciones con la API de Hojas de cálculo.
Estos ejemplos se presentan en forma de solicitudes HTTP como lenguaje neutrales. Para aprender a implementar una actualización por lotes en diferentes idiomas con las bibliotecas cliente de la API de Google, consulta Actualización hojas de cálculo.
En estos ejemplos, los marcadores de posición SPREADSHEET_ID y SHEET_ID
se indica dónde se deben proporcionar esos ID. Puedes encontrar la hoja de cálculo
ID en la URL de la hoja de cálculo. Puedes obtener
el ID de la hoja mediante el
spreadsheets.get. El
los rangos se especifican con la notación A1. Los
el rango de ejemplo es Hoja1!A1:D5.
Además, el marcador de posición SOURCE_SHEET_ID indica que tu hoja
con los datos de origen. En estos ejemplos, esta es la tabla que aparece en Tabla dinámica
datos de origen de la tabla.
Datos de origen de la tabla dinámica
Para estos ejemplos, supón que la hoja de cálculo utilizada tiene la siguiente fuente “ventas” datos en su primera hoja ("Sheet1"). Las cadenas de la primera fila etiquetas para las columnas individuales. Para ver ejemplos de cómo leer desde otros 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 | 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 | USD 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 | $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
Lo siguiente
spreadsheets.batchUpdate
de muestra de código muestra cómo usar el
UpdateCellsRequest
para crear una tabla dinámica a partir de los datos de origen, anclarla a la celda A50 del
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. Desde
solo hay un grupo de valores, los 2 posibles
valueLayoutson equivalentes. - Dos grupos de filas (Item Category y Model Number) El primer ordena
valor ascendente de la Quantity total desde "Oeste" Región. Por lo tanto,
"Motor" (sin ventas en el oeste) aparecen arriba de "Door" (con 15 ventas en el oeste). El
El grupo de Número de modelo ordena en orden descendente a las ventas totales de todas.
regiones, es decir, "W-24", (15 ventas) aparece sobre "W-25" (8 ventas) Ya se completó este paso
estableciendo
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, y por lo que aparece como la primera columna Region.
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
Lo siguiente
spreadsheets.batchUpdate
de muestra de código muestra cómo usar el
UpdateCellsRequest
para crear una tabla dinámica con un grupo de valores calculados a partir de los datos de origen,
anclarlo a 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 la
la cantidad de ventas. El segundo es un valor calculado que se basa en el producto de un
y el número total de ventas usando 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 se ordenan por nombre (en lugar de por Cantidad) en cada uno.
y ordenar la tabla alfabéticamente. Para ello, se debe omitir el
valueBucketde laPivotGroup - Para simplificar la apariencia de la tabla, la solicitud oculta los subtotales para todos los grupos de filas y columnas, excepto para los principales.
- La solicitud establece
valueLayout. aVERTICALpara mejorar la apariencia de la tabla.valueLayoutes solo 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
Lo siguiente
spreadsheets.batchUpdate
de muestra de código muestra cómo usar el
UpdateCellsRequest
para eliminar una tabla dinámica (si hay alguna) anclada en la celda A50 de la hoja
especificadas por SHEET_ID.
Un elemento 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 el ancla
de la celda.
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
Lo siguiente
spreadsheets.batchUpdate
de muestra de código muestra cómo usar el
UpdateCellsRequest
para editar la tabla dinámica creada en Cómo agregar una tabla dinámica.
Subconjuntos del
pivotTable
en la
CellData recurso
No se puede cambiar de forma individual con el parámetro fields. Para realizar cambios,
se debe proporcionar pivotTable completo. Básicamente, editar una tabla dinámica
requiere reemplazarlo por uno nuevo.
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 del Panel) parece a la izquierda de "Jessie" (13 ventas del panel).
- Contrae la columna para cada Region, excepto por "Oeste", ocultando
Salesperson de esa región. Para ello, se debe configurar
collapsedcomotrueen lavalueMetadatapara esa columna en el 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
Lo siguiente
Muestra de código spreadsheets.get
muestra cómo obtener los datos de una tabla dinámica de una hoja de cálculo. La consulta fields
parámetro especifica que solo se deben mostrar los datos de la tabla dinámica (como
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
Spreadsheet
recurso, que contiene un
Un objeto Sheet con
SheetProperties
o de terceros. También hay una serie de
GridData
elementos que contienen información sobre el
PivotTable
La información de la tabla dinámica se incluye en el
CellData recurso
para la celda en la que está anclada la tabla (es decir, en el extremo superior izquierdo
). Si un campo de respuesta tiene el valor predeterminado, se omite de la
respuesta.
En este ejemplo, la primera hoja (SOURCE_SHEET_ID) tiene la tabla sin procesar
los datos de origen, mientras que la segunda hoja (SHEET_ID) tiene la tabla dinámica,
se basa en B3. Las llaves vacías indican que las hojas o celdas no tienen
contienen datos de tablas dinámicas. A modo de referencia, esta solicitud también devuelve la hoja
de sus IDs.
{
"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
}
}
],
}