Hojas conectadas

Hojas conectadas te permite analizar petabytes de datos directamente en Hojas de cálculo de Google. Puedes conectar tus hojas de cálculo con un almacén de datos de BigQuery y realizar el análisis mediante herramientas conocidas de Hojas de cálculo, como las tablas dinámicas, los gráficos y las fórmulas.

En esta guía, se usa el conjunto de datos públicos Shakespeare para mostrar cómo usar las Hojas conectadas. El conjunto de datos contiene la siguiente información:

Campo Tipo Descripción
palabra STRING Una sola palabra única (donde el espacio en blanco es el delimitador) extraída de un corpus.
word_count INTEGER Es la cantidad de veces que esta palabra aparece en este corpus.
corpus STRING La obra de la que se extrajo esta palabra.
corpus_date INTEGER Es el año en el que se publicó este corpus.

Si tu aplicación solicita datos de Hojas conectadas, debe proporcionar un token de OAuth 2.0 que otorgue el alcance bigquery.readonly, además de los otros permisos necesarios para una solicitud normal a la API de Hojas de cálculo de Google. Para obtener más información, consulta Elige los alcances de la API de Hojas de cálculo de Google.

Administra una fuente de datos

Una fuente de datos especifica una ubicación externa donde se encuentran datos. Luego, la fuente de datos se conecta a la hoja de cálculo.

Agregar una fuente de datos de BigQuery

Para agregar una fuente de datos, proporciona un AddDataSourceRequest con el método spreadsheets.batchUpdate. El cuerpo de la solicitud debe especificar un campo dataSource del tipo DataSource.

"addDataSource":{
   "dataSource":{
      "spec":{
         "bigQuery":{
            "projectId":"PROJECT_ID",
            "tableSpec":{
               "tableProjectId":"bigquery-public-data",
               "datasetId":"samples",
               "tableId":"shakespeare"
            }
         }
      }
   }
}

Reemplaza PROJECT_ID por un ID del proyecto de Google Cloud válido.

Después de crear una fuente de datos, se crea una hoja DATA_SOURCE asociada para proporcionar una vista previa de hasta 500 filas. La vista previa no está disponible de inmediato. Se activa una ejecución de forma asíncrona para importar los datos de BigQuery.

AddDataSourceResponse contiene los siguientes campos:

  • dataSource: Es el objeto DataSource creado. El dataSourceId es un ID único con alcance de hoja de cálculo. Se propaga y se hace referencia a él para crear cada objeto DataSource desde la fuente de datos.

  • dataExecutionStatus: Es el estado de una ejecución que importa datos de BigQuery a la hoja de vista previa. Para obtener más información, consulta la sección Estado de ejecución de los datos.

Actualiza o borra una fuente de datos

Usa el método spreadsheets.batchUpdate y proporciona una solicitud UpdateDataSourceRequest o DeleteDataSourceRequest según corresponda.

Administrar objetos de fuente de datos

Una vez que se agrega una fuente de datos a la hoja de cálculo, se puede crear un objeto de fuente de datos a partir de ella. Un objeto de fuente de datos es una herramienta normal de Hojas de cálculo, como tablas dinámicas, gráficos y fórmulas, que está integrada en Hojas conectadas para potenciar el análisis de datos.

Hay cuatro tipos de objetos:

  • DataSource tabla
  • DataSource pivotTable
  • Gráfico de DataSource
  • Fórmula DataSource

Agrega una tabla de fuente de datos

El objeto de la tabla, que se conoce como "extracción" en el editor de Hojas de cálculo, importa a Hojas de cálculo un volcado estático de los datos de la fuente de datos. De manera similar a una tabla dinámica, la tabla se especifica y se ancla a la celda superior izquierda.

En la siguiente muestra de código, se indica cómo usar el método spreadsheets.batchUpdate y un UpdateCellsRequest para crear una tabla de fuente de datos de hasta 1,000 filas de dos columnas (word y word_count).

"updateCells":{
   "rows":{
      "values":[
         {
            "dataSourceTable":{
               "dataSourceId":"DATA_SOURCE_ID",
               "columns":[
                  {
                     "name":"word"
                  },
                  {
                     "name":"word_count"
                  }
               ],
               "rowLimit":{
                  "value":1000
               },
               "columnSelectionType":"SELECTED"
            }
         }
      ]
   },
   "fields":"dataSourceTable"
}

Reemplaza DATA_SOURCE_ID por un ID único con alcance de hoja de cálculo que identifique la fuente de datos.

Después de crear una tabla de fuente de datos, los datos no están disponibles de inmediato. En el editor de Hojas de cálculo, se muestra como una vista previa. Debes actualizar la tabla de fuente de datos para recuperar los datos de BigQuery. Puedes especificar un objeto RefreshDataSourceRequest dentro del mismo batchUpdate. Ten en cuenta que todos los objetos de fuente de datos funcionan de manera similar. Para obtener más información, consulta Cómo actualizar un objeto de fuente de datos.

Una vez que se completa la actualización y se recuperan los datos de BigQuery, la tabla de fuente de datos se propaga como se muestra a continuación:

Tabla de fuentes de datos que muestra los datos del conjunto de datos públicos Shakespeare.

Cómo agregar una pivotTable de fuente de datos

A diferencia de una tabla dinámica convencional, una tabla dinámica de fuente de datos está respaldada por una fuente de datos y hace referencia a los datos por nombre de columna. En la siguiente muestra de código, se indica cómo usar el método spreadsheets.batchUpdate y un UpdateCellsRequest para crear una tabla dinámica que muestre el recuento total de palabras por corpus.

"updateCells":{
   "rows":{
      "values":[
         {
            "pivotTable":{
               "dataSourceId":"DATA_SOURCE_ID",
               "rows":{
                  "dataSourceColumnReference":{
                     "name":"corpus"
                  },
                  "sortOrder":"ASCENDING"
               },
               "values":{
                  "summarizeFunction":"SUM",
                  "dataSourceColumnReference":{
                     "name":"word_count"
                  }
               }
            }
         }
      ]
   },
   "fields":"pivotTable"
    }

Reemplaza DATA_SOURCE_ID por un ID único con alcance de hoja de cálculo que identifique la fuente de datos.

Una vez que se recuperan los datos de BigQuery, la tabla dinámica de la fuente de datos se propaga como se muestra a continuación:

Tabla dinámica de la fuente de datos que muestra los datos del conjunto de datos públicos de Shakespeare.

Agrega un gráfico de fuente de datos

En la siguiente muestra de código, se indica cómo usar el método spreadsheets.batchUpdate y un AddChartRequest para crear un gráfico de fuente de datos con un chartType de COLUMN, que muestra el recuento total de palabras por corpus.

"addChart":{
   "chart":{
      "spec":{
         "title":"Corpus by word count",
         "basicChart":{
            "chartType":"COLUMN",
            "domains":[
               {
                  "domain":{
                     "columnReference":{
                        "name":"corpus"
                     }
                  }
               }
            ],
            "series":[
               {
                  "series":{
                     "columnReference":{
                        "name":"word_count"
                     },
                     "aggregateType":"SUM"
                  }
               }
            ]
         }
      },
      "dataSourceChartProperties":{
         "dataSourceId":"DATA_SOURCE_ID"
      }
   }
}

Reemplaza DATA_SOURCE_ID por un ID único con alcance de hoja de cálculo que identifique la fuente de datos.

Después de recuperar los datos de BigQuery, el gráfico de fuente de datos se renderiza de la siguiente manera:

Gráfico de fuente de datos que muestra datos del conjunto de datos público de Shakespeare.

Agregar una fórmula de fuente de datos

En la siguiente muestra de código, se indica cómo usar el método spreadsheets.batchUpdate y UpdateCellsRequest para crear una fórmula de fuente de datos a fin de calcular el recuento promedio de palabras.

"updateCells":{
   "rows":[
      {
         "values":[
            {
               "userEnteredValue":{
                  "formulaValue":"=AVERAGE(shakespeare!word_count)"
               }
            }
         ]
      }
   ],
   "fields":"userEnteredValue"
}

Una vez que se recuperan los datos de BigQuery, la fórmula de la fuente de datos se propaga como se muestra a continuación:

Fórmula de la fuente de datos que muestra los datos del conjunto de datos públicos Shakespeare.

Cómo actualizar un objeto de fuente de datos

Puedes actualizar un objeto de fuente de datos para recuperar los datos más recientes de BigQuery en función de las especificaciones de la fuente de datos y la configuración de objetos actuales. Puedes usar el método spreadsheets.batchUpdate para llamar a RefreshDataSourceRequest. Luego, especifica una o más referencias de objetos para actualizar con el objeto DataSourceObjectReferences.

Ten en cuenta que puedes crear y actualizar objetos de fuente de datos dentro de una sola solicitud batchUpdate.

Estado de ejecución de los datos

Cuando creas fuentes de datos o actualizas objetos de fuente de datos, se crea una ejecución en segundo plano para recuperar los datos de BigQuery y mostrar una respuesta que contiene DataExecutionStatus. Si la ejecución se inicia correctamente, el DataExecutionState suele estar en el estado RUNNING.

Debido a que el proceso es asíncrono, tu aplicación debe implementar un modelo de sondeo para recuperar periódicamente el estado de los objetos de la fuente de datos. Usa el método spreadsheets.get hasta que el estado muestre SUCCEEDED o FAILED. En la mayoría de los casos, la ejecución se completa rápidamente, pero depende de la complejidad de la fuente de datos. Por lo general, la ejecución no supera los 10 minutos.