Convierte tus macrodatos en estadísticas con Hojas de cálculo y Presentaciones de Google

1. Del análisis de macrodatos a la presentación de diapositivas

Existen muchas herramientas para que los científicos de datos realicen análisis de macrodatos, pero, al final, los analistas deben justificar los resultados ante la administración. Una gran cantidad de números en papel o en una base de datos difícilmente se puede presentar a las partes interesadas clave. Este codelab intermedio de Google Apps Script usa 2 plataformas para desarrolladores de Google (Google Workspace y consola de Google Cloud) para ayudarte a automatizar ese tramo final.

Las herramientas para desarrolladores de Google Cloud te permiten realizar un análisis de datos detallado. Luego, puedes tomar los resultados, insertarlos en una hoja de cálculo y generar una presentación de diapositivas con los datos. Esto ofrece una forma más adecuada de entregar datos a la administración. En este codelab, se explican la API de BigQuery de la consola de Cloud (como un servicio avanzado de Apps Script) y los servicios integrados de Apps Script para Hojas de cálculo de Google y Presentaciones de Google.

Motivación

La app de ejemplo de este codelab se inspiró en estas otras muestras de código:

La app de ejemplo del servicio de BigQuery de Google Apps Script que es de código abierto en GitHub.
La app de ejemplo que se muestra en el video para desarrolladores Cómo generar diapositivas a partir de datos de hojas de cálculo y que se publicó en esta entrada de blog.
Es la app de ejemplo que se incluye en el codelab de la API de Google Slides.

Si bien la app de ejemplo del codelab de la API de Slides también incluye BigQuery y Slides, difiere de la app de ejemplo de este codelab de varias maneras:

Su app de Node.js en comparación con nuestra app de Apps Script
Usan APIs de REST, mientras que nosotros usamos servicios de Apps Script.
Usan Google Drive, pero no Hojas de cálculo de Google, mientras que esta app usa Hojas de cálculo, pero no Drive.

Para este codelab, queríamos reunir varias tecnologías en una sola app y, al mismo tiempo, mostrar funciones y APIs de todo Google Cloud de una manera que se asemeje a un caso de uso real. El objetivo es inspirarte a usar tu imaginación y considerar el uso de Cloud Console y Google Workspace para resolver problemas complejos para tu organización y tus clientes.

Qué aprenderás

Cómo usar Google Apps Script con múltiples servicios de Google
Cómo usar Google BigQuery para analizar macrodatos
Cómo crear una hoja de cálculo de Google y cómo insertar datos en ella
Cómo crear un gráfico en Hojas de cálculo
Cómo transferir datos y gráficos de Hojas de cálculo a una presentación de Presentaciones de Google

Requisitos

Un navegador web con acceso a Internet
Una Cuenta de Google (es posible que las cuentas de Google Workspace requieran la aprobación del administrador)
Conocimientos básicos de Hojas de cálculo de Google
Capacidad de leer la notación A1 de Hojas de cálculo
Habilidades básicas de JavaScript
Es útil tener conocimientos sobre el desarrollo de Apps Script, pero no es obligatorio.

2. Encuesta

¿Cómo usarás este codelab o instructivo?

Leerlo con fines informativos y, tal vez, pasárselo a colegas técnicos

Leerlo hasta donde pueda y hacer tantos ejercicios como pueda

Completar todo el codelab, pase lo que pase

¿Cómo calificarías tu experiencia con las APIs y las herramientas para desarrolladores de Google Workspace?

Principiante

Intermedio

Avanzado

¿Cómo calificarías tu experiencia con Apps Script específicamente?

Principiante

Intermedio

Avanzado

¿Cómo calificarías tu experiencia con las APIs y las herramientas para desarrolladores de Cloud Console?

Principiante

Intermedio

Avanzado

3. Descripción general

Ahora que sabes de qué se trata este codelab, esto es lo que harás:

Toma una muestra existente de Apps Script y BigQuery, y haz que funcione.
En el ejemplo, aprende a enviar una consulta a BigQuery y obtener los resultados.
Crea una Hoja de cálculo de Google y, luego, inserta los resultados de BigQuery en ella.
Modifica el código para alterar ligeramente los datos que se devuelven y se insertan en la hoja.
Usa el servicio de Hojas de cálculo en Apps Script para crear un gráfico con los datos de BigQuery.
Usa el servicio de Presentaciones para crear una presentación de Presentaciones de Google.
Agrega un título y un subtítulo a la diapositiva de título predeterminada.
Crea una diapositiva con una tabla de datos y exporta las celdas de datos de la hoja de cálculo a ella.
Crea otra diapositiva y, luego, inserta el gráfico de la hoja de cálculo en ella.

Comencemos con información general sobre Apps Script, BigQuery, Hojas de cálculo y Presentaciones.

Google Apps Script y BigQuery

Google Apps Script es una plataforma de desarrollo de Google Workspace que funciona a un nivel superior que las APIs de REST de Google. Es un entorno de desarrollo y hosting de aplicaciones sin servidores que pueden usar desarrolladores con distintos niveles de habilidades. Básicamente, Apps Script es un entorno de ejecución de JavaScript sin servidores para la integración, extensión y automatización de Google Workspace.

Usa JavaScript del lado del servidor, similar a Node.js, pero se enfoca en la integración estrecha con Google Workspace y otros servicios de Google, y no en el hosting de aplicaciones rápido, asíncrono y basado en eventos. También presenta un entorno de desarrollo que puede ser diferente al que está acostumbrado. Con Apps Script, puedes realizar las siguientes acciones:

Desarrollar secuencias de comandos con un editor de código basado en navegador, pero también puedes hacerlo de forma local cuando usas clasp, la herramienta de implementación de línea de comandos para Apps Script
Escribir código en una versión especializada de JavaScript personalizada para acceder a Google Workspace y a otros servicios externos o de Google (con los servicios de URL Fetch o JDBC de Apps Script)
Puedes evitar escribir código de autorización, ya que Apps Script lo hace por ti.
No tienes que alojar tu app, ya que esta reside y se ejecuta en los servidores de Google en la nube.

Apps Script interactúa con otras tecnologías de Google de 2 maneras diferentes:

Como servicio integrado
Como servicio avanzado

Un servicio integrado tiene métodos de alto nivel para interactuar con los datos del usuario, otros sistemas de Google y sistemas externos. Un servicio avanzado es, básicamente, un wrapper delgado en torno a una API de Google Workspace o una API de REST de Google. Los servicios avanzados proporcionan una cobertura completa de la API de REST y, a menudo, pueden hacer más que los servicios integrados, pero requieren un código más complejo (y, a su vez, son más fáciles de usar que la API de REST completa). Los servicios avanzados también deben activarse para un proyecto de secuencia de comandos antes de usarlos.

Siempre que sea posible, los desarrolladores deben usar un servicio integrado, ya que son más fáciles de usar y hacen más que los servicios avanzados. Sin embargo, algunas APIs de Google no tienen servicios integrados, por lo que un servicio avanzado puede ser la única opción. Por ejemplo, Google BigQuery no tiene un servicio integrado, pero sí existe el servicio de BigQuery. El servicio de BigQuery es un servicio de la consola de Cloud que te permite usar la API de Google BigQuery para realizar consultas en grandes volúmenes de datos (por ejemplo, varios terabytes) y, aun así, puede proporcionar resultados en segundos.

Cómo acceder a Hojas de cálculo y Presentaciones desde Apps Script

A diferencia de BigQuery, tanto Hojas de cálculo como Presentaciones tienen servicios integrados. También tienen servicios avanzados para acceder a funciones que solo se encuentran en la API. Antes de pasar al código, consulta la documentación de los servicios integrados de Hojas de cálculo y Presentaciones. Ten en cuenta que también hay documentación para los servicios avanzados de Hojas de cálculo y Presentaciones.

4. Tarea 1: Ejecuta BigQuery y registra los resultados en Hojas de cálculo

Introducción

Realizaremos gran parte de este codelab con esta primera tarea. De hecho, una vez que termines, habrás completado la mitad del codelab. Dividida en varias subsecciones, aprenderás a hacer lo siguiente:

Crea un proyecto de Google Apps Script y otro de Cloud Console.
Activa el acceso al servicio avanzado de BigQuery.
Abre el editor de secuencias de comandos y, luego, ingresa el código fuente de la aplicación.
Realizar el proceso de autorización de la app (OAuth2)
Ejecuta la aplicación que envía una solicitud a BigQuery.
Revisa la nueva hoja de cálculo de Google creada con los resultados de BigQuery.

Configuración

Para crear un proyecto de Apps Script, ve a script.google.com y haz clic en Nuevo proyecto.
Para cambiar el nombre de tu proyecto de Apps Script, haz clic en Proyecto sin título, ingresa un título para tu proyecto y haz clic en Cambiar nombre.

A continuación, deberás crear un proyecto de la consola de Cloud para consultar datos en BigQuery.

Para crear un proyecto de Cloud Console, usa este vínculo de acceso directo para crear un proyecto, asígnale un nombre y haz clic en Crear.

Cuando se complete la creación del proyecto, aparecerá una notificación en la página. Asegúrate de que tu proyecto nuevo esté seleccionado en la lista de proyectos que se encuentra en la parte superior de la página.
Haz clic en Menú y ve a APIs y servicios > Pantalla de consentimiento de OAuth (vínculo directo).
Haz clic en Interna > Crear para compilar una app para los usuarios de Google Workspace de tu organización.
En el campo Nombre de la app, ingresa "Codelab de Big Data".
Ingresa los correos electrónicos de contacto en los campos Asistencia al usuario y Información de contacto del desarrollador.
Haz clic en Guardar y continuar > Guardar y continuar.
Haz clic en Más en la barra de navegación y selecciona Configuración del proyecto (vínculo directo).
Copia el valor que aparece en Número del proyecto. (Más adelante en el codelab, se usará un campo ID del proyecto independiente).

A continuación, conectarás tu proyecto de Apps Script al proyecto de Cloud Console.

Cambia al editor de Apps Script y haz clic en Configuración del proyecto .
En Proyecto de Google Cloud Platform (GCP), haz clic en Cambiar proyecto.
Ingresa el número del proyecto y haz clic en Establecer el proyecto.
A continuación, haz clic en Editor para comenzar a agregar el servicio avanzado de BigQuery.
Junto a Servicios, haz clic en Agregar un servicio .
En el diálogo Agregar un servicio, selecciona API de BigQuery y haz clic en Agregar.

El último paso es activar la API de BigQuery en la consola de Cloud.

Para ello, cambia a la consola de Cloud y haz clic en APIs y servicios > Panel. (Asegúrate de seguir en el mismo proyecto que creaste en el paso 3).
Haz clic en Habilitar APIs y servicios.
Busca "big query", selecciona la API de BigQuery (no la API de BigQuery Data Transfer) y haz clic en Habilitar para activarla.

Ya tienes todo listo para ingresar el código de la aplicación, realizar el proceso de autorización y hacer funcionar la primera iteración de esta aplicación.

Sube la aplicación y ejecútala

En el editor de secuencias de comandos, reemplaza el bloque de código myFunction() predeterminado por el siguiente código:

// File name for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud Console Project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into a Sheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BigQuery job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

  // Get all results from BigQuery.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to Sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
}

Haz clic en Guardar .
Junto a Code.gs, haz clic en Más > Cambiar nombre. Cambia el título de Code.gs a bq-sheets-slides.js.
A continuación, revisemos el código que consulta BigQuery y escribe los resultados en una hoja de cálculo de Google. Puedes verlo cerca de la parte superior de runQuery():

SELECT
    LOWER(word) AS word,
    SUM(word_count) AS count
FROM [bigquery-public-data:samples.shakespeare]
GROUP BY word
ORDER BY count
DESC LIMIT 10

Esta consulta revisa las obras de Shakespeare, que forman parte del conjunto de datos públicos de BigQuery, y muestra las 10 palabras con mayor frecuencia de aparición en todas sus obras, en orden descendente de popularidad. Para tener una idea de lo útil que puede ser BigQuery, basta con imaginar el trabajo que implicaría hacer esta compilación manualmente.

La función también declara una variable PROJECT_ID que requiere un ID de proyecto válido de la consola de Cloud. La instrucción if debajo de la variable sirve para evitar que la aplicación continúe sin el ID del proyecto.

Cambia a tu proyecto de Cloud Console, haz clic en Más en la barra de navegación y selecciona Configuración del proyecto.
Copia el valor que aparece en Project ID.
Vuelve al editor de Apps Script, ubica la variable PROJECT_ID en bq-sheets-slides.js y agrega el valor.
Haz clic en Guardar > Ejecutar.
Haz clic en Revisar permisos para continuar.

Nota: Si usas una cuenta de gmail.com, es posible que aparezca un diálogo que indique que "Esta app no está verificada" cuando uses el script por primera vez. Google usa esta información para advertir a los usuarios que podrían estar usando código de autores desconocidos o que no son de confianza. Si ves este diálogo, puedes continuar, ya que eres el autor de la secuencia de comandos. Sigue las instrucciones en pantalla para continuar autorizando la secuencia de comandos:

En el diálogo de la app no verificada, haz clic en Configuración avanzada.
Haz clic en Ir a tu-nombre-de-proyecto (no seguro).
Haz clic en Permitir.

A lo largo de este codelab, es posible que aparezcan varios mensajes de permisos. Puedes obtener más información sobre este proceso en Autorización para los servicios de Google.

Una vez que se comienza a ejecutar la secuencia de comandos, se abre el registro de ejecución integrado y se registran las acciones de la secuencia de comandos en tiempo real.

Una vez que el registro de ejecución muestre el mensaje "Execution completed", ve a tu unidad de Google Drive (drive.google.com) y busca la hoja de cálculo de Google llamada "Most common words in all of Shakespeare's works" (o el nombre que le asignaste a la variable QUERY_NAME, si la actualizaste):
Abre la hoja de cálculo para ver las 10 palabras más comunes y sus recuentos totales ordenados de forma descendente:

Resumen de la tarea 1

Para repasar, ejecutaste un código que consultó todas las obras de Shakespeare y analizó cada palabra en cada obra. Contó las palabras y las ordenó de forma descendente según su aparición. También usaste el servicio integrado de Apps Script para Hojas de cálculo de Google para mostrar estos datos.

El código que usaste para bq-sheets-slides.js también se puede encontrar en la carpeta step1 del repositorio de GitHub de este codelab en github.com/googlecodelabs/bigquery-sheets-slides. El código se inspiró en este ejemplo original de la página de servicios avanzados de BigQuery que ejecutó una consulta ligeramente diferente para recuperar las palabras más populares con 10 o más caracteres que usó Shakespeare. También puedes ver un ejemplo en su repositorio de GitHub.

Si te interesan otras consultas que puedes compilar con las obras de Shakespeare o con otras tablas de datos públicos, visita Cómo consultar las tablas de muestra de BigQuery y este repo de GitHub.

También puedes ejecutar consultas en la página de BigQuery en Cloud Console antes de ejecutarlas en Apps Script. Para encontrarlo, haz clic en Menú y ve a IU de BigQuery > Espacio de trabajo de SQL (vínculo directo). Por ejemplo, así se ve nuestra consulta en la interfaz gráfica de BigQuery:

BigQueryUI

5. Tarea 2: Crea un gráfico en Hojas de cálculo de Google

El propósito de runQuery() es usar BigQuery y enviar los resultados de sus datos a una Hoja de cálculo de Google. A continuación, debemos crear un gráfico con los datos. Creemos una nueva función llamada createColumnChart() que llame al método newChart() de Hojas de cálculo.

En el editor de Apps Script, agrega la función createColumnChart() a bq-sheets-slides.js después de runQuery(). El código obtiene la hoja de cálculo y solicita un gráfico de columnas con todos los datos. El rango de datos comienza en la celda A2, ya que la primera fila contiene los encabezados de las columnas.

/**
 * Uses spreadsheet data to create a column chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) Sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in Sheet is from cell A2 to B11.
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on Sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the Sheet using above values.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
}

La función createColumnChart() requiere un parámetro de objeto de hoja de cálculo, por lo que debemos actualizar runQuery() para que devuelva un objeto spreadsheet que podamos pasar a createColumnChart(). Al final de runQuery(), devuelve el objeto spreadsheet después de registrar la creación correcta de la hoja:

  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());

  // NEW: Return the spreadsheet object for later use.
  return spreadsheet;
}

Crea una función createBigQueryPresentation() para llamar a runQuery() y createColumnChart(). Lo mejor es separar de forma lógica la funcionalidad de BigQuery y la de creación de gráficos:

/**
 * Runs the query, adds data and a chart to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  createColumnChart(spreadsheet);
}

Previamente realizaste 2 pasos importantes: devolviste el objeto de la hoja de cálculo y creaste la función de entrada. Para que runQuery() sea más utilizable, debemos mover la línea de registro de runQuery() a createBigQueryPresentation(). Ahora, tu método debería verse de la siguiente manera:

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); // MOVED HERE
  createColumnChart(spreadsheet);
}

Con los cambios anteriores (excepto PROJECT_ID), tu bq-sheets-slides.js debería verse de la siguiente manera. Este código también se encuentra en step2 del repositorio de GitHub.

// File name for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud Console Project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into a sheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BigQuery job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

  // Get all results from BigQuery.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  // Return the spreadsheet object for later use.
  return spreadsheet;
}

/**
 * Uses spreadsheet data to create a columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in sheet is from cell A2 to B11.
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on Sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the sheet using above values.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
}

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  createColumnChart(spreadsheet);
}

En el editor de secuencias de comandos, guarda tu proyecto de secuencias de comandos. Luego, selecciona createBigQueryPresentation en la lista de funciones y haz clic en Ejecutar. Cuando finalice, se creará otra hoja de cálculo en tu unidad de Google Drive, pero esta vez se incluirá un gráfico junto a los datos:

Hoja con gráfico

6. Tarea 3: Coloca los datos de resultados en una presentación de diapositivas

La parte final del codelab consiste en crear una presentación de Presentaciones de Google, agregar el título y el subtítulo a la primera diapositiva y, luego, crear diapositivas para las celdas de datos y el gráfico.

En el editor de Apps Script, agrega la función createSlidePresentation() a bq-sheets-slides.js después de createColumnChart(). Todo el trabajo en la presentación de diapositivas se realiza en esta función. Comencemos con la creación de una presentación de diapositivas y, luego, agreguemos un título y un subtítulo a la primera diapositiva predeterminada.

/**
 * Create presentation with spreadsheet data and a chart
 * @param {Spreadsheet} Spreadsheet containing results data
 * @param {EmbeddedChart} Sheets chart to embed on a slide
 * @returns {Presentation} Slide deck with the results
  * @see https://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the presentation.
  var deck = SlidesApp.create(QUERY_NAME);

  // Populate the title slide.
  var [title, subtitle] = deck.getSlides()[0].getPageElements();
  title.asShape().getText().setText(QUERY_NAME);
  subtitle.asShape().getText().setText('using Google Cloud Console and Google Workspace APIs:\n' +
    'Google Apps Script, BigQuery, Sheets, Slides');

El siguiente paso en createSlidePresentation() es importar los datos de las celdas de la hoja de cálculo de Google a la nueva presentación. Agrega este fragmento de código a la función:

  // Data range to copy is from cell A1 to B11
  var START_CELL = 'A1';  // include header row
  var END_CELL = 'B11';
  // Add the table slide and insert an empty table on it with
  // the dimensions of the data range; fails if the sheet is empty.
  var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  var sheetValues = spreadsheet.getSheets()[0].getRange(
      START_CELL + ':' + END_CELL).getValues();
  var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length);

  // Populate the table with spreadsheet data.
  for (var i = 0; i < sheetValues.length; i++) {
    for (var j = 0; j < sheetValues[0].length; j++) {
      table.getCell(i, j).getText().setText(String(sheetValues[i][j]));
    }
  }

El paso final en createSlidePresentation() es agregar una diapositiva más, importar el gráfico desde nuestra hoja de cálculo y mostrar el objeto Presentation. Agrega este fragmento de código a la función:

  // Add a chart slide and insert the chart on it.
  var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  chartSlide.insertSheetsChart(chart);

  // Return the presentation object for later use.
  return deck;
}

Ahora que nuestra función está completa, vuelve a ver su firma. El método createSlidePresentation() requiere parámetros de hoja de cálculo y de objeto de gráfico. Ya ajustamos runQuery() para que devuelva el objeto Spreadsheet, pero debemos hacer un cambio similar en createColumnChart() para que devuelva un objeto de gráfico (EmbeddedChart). Vuelve a createColumnChart() y agrega el siguiente fragmento de código al final de la función:

  // NEW: Return the chart object for later use.
  return chart;
}

Dado que createColumnChart() ahora devuelve un objeto de gráfico, debemos guardar el gráfico en una variable. Luego, pasamos ambas variables, la de la hoja de cálculo y la del gráfico, a createSlidePresentation(). Además, ya que registramos la URL de la hoja de cálculo recién creada, también registremos la URL de la nueva presentación de diapositivas. Actualiza tu createBigQueryPresentation() para que se vea de la siguiente manera:

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet,
 * and adds the data and chart to a new slide presentation.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  var chart = createColumnChart(spreadsheet); // UPDATED
  var deck = createSlidePresentation(spreadsheet, chart); // NEW
  Logger.log('Results slide deck created: %s', deck.getUrl()); // NEW
}

Guarda y vuelve a ejecutar createBigQueryPresentation(). Sin embargo, antes de que se ejecute, tu app necesita un conjunto más de permisos del usuario para ver y administrar las presentaciones de Google Slides. Una vez que revises y permitas estos permisos, se ejecutará como antes.
Ahora, además de la hoja de cálculo que se creó, también deberías ver una nueva presentación de diapositivas con 3 diapositivas (título, tabla de datos, gráfico de datos), como se muestra a continuación:

7. Conclusión

Felicitaciones. Creaste una aplicación que usa ambos lados de Google Cloud. Realiza una solicitud de Google BigQuery que consulta uno de sus conjuntos de datos públicos, crea una hoja de cálculo de Google Sheets para almacenar los resultados, agrega un gráfico basado en los datos y, finalmente, crea una presentación de Google Slides con los resultados de los datos y el gráfico de la hoja de cálculo.

Estos son los pasos que seguiste técnicamente. En términos generales, pasaste de un análisis de macrodatos a un resultado que puedes presentar a las partes interesadas, todo automatizado con código. Esperamos que esta muestra te inspire a personalizarla para tus propios proyectos. Al finalizar este codelab, te brindaremos algunas sugerencias para mejorar aún más esta app de ejemplo.

Con los cambios de la tarea final (excepto PROJECT_ID), tu bq-sheets-slides.js debería verse de la siguiente manera:

/**
 * Copyright 2018 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at apache.org/licenses/LICENSE-2.0.
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

// File name for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud Console Project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into a spreadsheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BigQuery job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

  // Get all results from BigQuery.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  // Return the spreadsheet object for later use.
  return spreadsheet;
}

/**
 * Uses spreadsheet data to create a column chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in sheet is from cell A2 to B11.
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the sheet using above values.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
  
  // Return the chart object for later use.
  return chart;
}

/**
 * Create presentation with spreadsheet data and a chart
 * @param {Spreadsheet} Spreadsheet containing results data
 * @param {EmbeddedChart} Sheets chart to embed on a slide
 * @returns {Presentation} Slide deck with the results
 * @see https://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the presentation.
  var deck = SlidesApp.create(QUERY_NAME);

  // Populate the title slide.
  var [title, subtitle] = deck.getSlides()[0].getPageElements();
  title.asShape().getText().setText(QUERY_NAME);
  subtitle.asShape().getText().setText('using Google Cloud Console and Google Workspace APIs:\n' +
    'Google Apps Script, BigQuery, Sheets, Slides');

  // Data range to copy is from cell A1 to B11
  var START_CELL = 'A1';  // include header row
  var END_CELL = 'B11';
  // Add the table slide and insert an empty table on it with
  // the dimensions of the data range; fails if the sheet is empty.
  var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  var sheetValues = spreadsheet.getSheets()[0].getRange(
      START_CELL + ':' + END_CELL).getValues();
  var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length);

  // Populate the table with spreadsheet data.
  for (var i = 0; i < sheetValues.length; i++) {
    for (var j = 0; j < sheetValues[0].length; j++) {
      table.getCell(i, j).getText().setText(String(sheetValues[i][j]));
    }
  }

  // Add a chart slide and insert the chart on it.
  var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  chartSlide.insertSheetsChart(chart);

  // Return the presentation object for later use.
  return deck;
}

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet,
 * and adds the data and chart to a new slide presentation.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  var chart = createColumnChart(spreadsheet);
  var deck = createSlidePresentation(spreadsheet, chart);
  Logger.log('Results slide deck created: %s', deck.getUrl());
}

Este ejemplo de código también se puede encontrar en la carpeta final del repositorio de GitHub.

8. Recursos adicionales

A continuación, encontrarás más recursos que te ayudarán a analizar en profundidad el material que vimos en este codelab y a explorar otras formas de acceder a las herramientas para desarrolladores de Google de manera programática.

Recursos para esta aplicación

Documentación

Sitio de documentación de Google Apps Script
Apps Script: Servicio de hojas de cálculo
Apps Script: Servicio de Presentaciones
Apps Script: Servicio avanzado de BigQuery

Videos

Novedades y actualizaciones

Blog de Google Cloud Platform
Blog de Google Cloud Data Analytics
Blog de Google Developers
Twitter: Google Developers (@googledevs)
Blog para desarrolladores de Google Workspace
Twitter: Google Workspace Developers (@workspacedevs)

Otros codelabs

Introductorio

[Hojas de cálculo de Google] Conceptos básicos de Apps Script con Hojas de cálculo de Google
[APIs de REST] Usa las APIs de Google Workspace y de Google para acceder a archivos y carpetas en tu unidad de Google Drive

Intermedio

[Apps Script] Herramienta de línea de comandos de CLASP para Apps Script
[Apps Script] Complementos de Google Workspace para Gmail
[Apps Script] Bots personalizados para Hangouts Chat
[APIs de REST] Usa Hojas de cálculo de Google como herramienta de informes de tu aplicación
[APIs de REST] Genera presentaciones de Google Slides con la API de BigQuery

9. El siguiente paso: desafíos de programación

A continuación, se indican diferentes formas en que puedes mejorar o aumentar la muestra que creamos en este codelab. Esta lista no es exhaustiva, pero proporciona algunas ideas inspiradoras sobre cómo puedes dar el siguiente paso.

Aplicación: ¿No quieres limitarte a usar JavaScript o las restricciones que impone Apps Script? Porta esta aplicación a tu lenguaje de programación favorito que use las APIs de REST de Google BigQuery, Hojas de cálculo y Presentaciones.
BigQuery: Experimenta con una consulta diferente para el conjunto de datos de Shakespeare que te interese. Puedes encontrar otra consulta de ejemplo en la app de ejemplo de BigQuery de Apps Script original.
BigQuery: Experimenta con otros conjuntos de datos públicos de BigQuery para encontrar uno que sea más significativo para ti.
BigQuery: Anteriormente, mencionamos otras consultas que puedes crear con las obras de Shakespeare o con otras tablas de datos públicos. Puedes encontrarlos en esta página web y en este repositorio de GitHub.
Hojas de cálculo: Experimenta con otros tipos de gráficos en la Galería de gráficos.
Hojas de cálculo y BigQuery: Usa tu propio conjunto de datos de hojas de cálculo grandes. En 2016, el equipo de BigQuery introdujo una función para permitir que los desarrolladores usen una hoja de cálculo como fuente de datos. Para obtener más información, consulta Google BigQuery se integra con Google Drive.
Diapositivas: Agrega otras diapositivas a la presentación generada, como imágenes o recursos vinculados a tu análisis de macrodatos. Aquí tienes la documentación de referencia para el servicio integrado de Slides.
Google Workspace: Usa otros servicios integrados de Google o Google Workspace desde Apps Script. Por ejemplo, Gmail, Calendario, Documentos, Drive, Maps, Analytics, YouTube, etcétera, así como otros servicios avanzados. Para obtener más información, consulta la descripción general de referencia para los servicios integrados y avanzados.