En esta guía, se describe cómo y por qué usar la API de Google Sheets para crear tablas dinámicas en tus hojas de cálculo.
¿Qué es una tabla dinámica?
Las tablas dinámicas proporcionan una forma de resumir los datos de tu hoja de cálculo, ya que agregan, ordenan, cuentan o promedian los datos automáticamente y, al mismo tiempo, muestran los resultados resumidos en una tabla nueva. Una tabla dinámica actúa como una especie de consulta a un conjunto de datos de origen. Estos datos de origen existen en otra ubicación de la hoja de cálculo, y la tabla dinámica presenta una vista procesada de los datos.
Por ejemplo, considera el siguiente conjunto de datos de ventas:
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 | $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 |
Puedes usar una tabla dinámica para crear un informe que muestre cuántos de cada número de modelo se vendieron en cada región:
Para obtener el código fuente que se usa para generar esta tabla dinámica, consulta la sección Ejemplo que aparece a continuación.
Una vez que se coloca una tabla dinámica en una hoja de cálculo, los usuarios pueden cambiar de forma interactiva la estructura y los detalles del resumen con la IU de Hojas de cálculo.
Cómo trabajar con tablas dinámicas
La definición de una tabla dinámica se asocia con una sola celda de una hoja. Aunque su apariencia renderizada es de muchas celdas en altura y ancho, de forma programática se encuentra en una sola coordenada de celda. Esta celda se convierte en la esquina superior izquierda de la tabla dinámica renderizada, con su extensión horizontal y vertical determinada por su definición.
Cómo agregar una tabla dinámica
Para agregar una tabla dinámica, usa el método batchUpdate y proporciona una solicitud updateCells. Usa esta solicitud para proporcionar una definición de PivotTable como el contenido de una celda, como se muestra a continuación:
"updateCells": {
"rows": {
"values": [{
"pivotTable": MyPivotTable
},
"start": {
"sheetId": sheetId,
"rowIndex": 0,
"columnIndex": 0
},
"fields": "pivotTable"
}
Esto coloca la tabla dinámica que describe MyPivotTable
en la hoja especificada, con la esquina superior izquierda en la celda A1
. (La altura y el ancho de la tabla dinámica son dinámicos; solo debes especificar el origen).
El tipo PivotTable te permite especificar lo siguiente:
- Rango de datos de origen
- Uno o más campos cuyos datos formarán las filas de la tabla dinámica
- Uno o más campos cuyos datos formarán las columnas de la tabla dinámica
- Criterios de filtrado y agregación
- Diseño de la tabla dinámica
Cómo modificar y borrar tablas dinámicas
No hay solicitudes explícitas para modificar o borrar una tabla dinámica. En su lugar, usa una solicitud updateCells con contenido de celdas diferente:
- Para modificar una tabla dinámica, crea una definición modificada de PivotTable y actualiza la celda con ella, de manera similar a agregar una tabla dinámica nueva.
- Para borrar una tabla dinámica, actualiza la celda con valores vacíos. Para ver un ejemplo, consulta el ejemplo de Cómo borrar una tabla dinámica.
Casos de uso
Las tablas dinámicas tienen muchos usos diferentes en una amplia variedad de áreas, como el análisis estadístico, las aplicaciones de ERP, la generación de informes financieros y otras. Entre los casos de uso de las tablas dinámicas clásicas, se incluyen los siguientes:
- Ventas totales por región y trimestre
- Salario promedio por cargo y ubicación
- Cantidad de incidentes por producto y hora del día
La cantidad de aplicaciones potenciales de las tablas dinámicas es enorme, y la capacidad de generarlas de manera programática es potente. Puedes generar tablas dinámicas que admitan la exploración interactiva, pero que se adapten a circunstancias específicas, por ejemplo:
- Explora los datos de incidentes del período de 24 horas más reciente
- Consultar o analizar los datos agregados correspondientes a la cuenta seleccionada actualmente
- Examinar los datos de ventas de los territorios que pertenecen al usuario actual
Ejemplo
En este ejemplo, se crea una tabla dinámica a partir de un conjunto de datos para generar el informe “número de modelo por región” que se muestra en la introducción de esta página. Para obtener ejemplos adicionales, consulta la página Ejemplo de tabla dinámica.