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

1. Desde el análisis de macrodatos hasta la presentación de diapositivas

Hay muchas herramientas para que los científicos de datos realicen análisis de macrodatos, pero, en definitiva, los analistas aún deben justificar los resultados a la administración. Muchos números en papel o en una base de datos son difícil de presentar a los interesados clave. Este codelab intermedio de Google Apps Script usa 2 plataformas para desarrolladores de Google (Google Workspace y la consola de Google Cloud) para ayudarte a automatizar ese período final.

Las herramientas para desarrolladores de Google Cloud te permiten realizar análisis de datos profundos. 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. Este codelab abarca 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 las siguientes muestras de código:

Si bien la app de ejemplo del codelab de la API de Presentaciones también cuenta con BigQuery y Presentaciones, difiere de la app de ejemplo de este codelab en varios aspectos:

  • Su app de Node.js en comparación con nuestra app de Apps Script.
  • Usan APIs de REST, mientras que nosotros usamos los 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.

En 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 usar la consola de Cloud y Google Workspace para resolver problemas desafiantes 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 agregarle datos
  • Cómo crear un gráfico en Hojas de cálculo
  • Cómo transferir datos y gráficos de Hojas de cálculo a un documento de Presentaciones de Google

Requisitos

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

2. Encuesta

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

Léelo con fines informativos y, luego, enviárselo a colegas técnicos. Repórtalo lo más posible y prueba tantos ejercicios como puedas. ¡Vamos, infierno! Voy a completar todo el codelab. .

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

Principiante Intermedio Avanzado

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

Principiante Intermedio Avanzado

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

Principiante Intermedio Avanzado . .

3. Descripción general

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

  1. Toma una muestra existente de Apps Script-BigQuery y ponla en funcionamiento.
  2. A partir de la muestra, aprende a enviar una consulta a BigQuery y obtener los resultados.
  3. Crear una hoja de cálculo de Google y, luego, insertar los resultados de BigQuery allí
  4. Modifica el código para modificar ligeramente los datos que se muestran y se insertan en la hoja de cálculo.
  5. Usar el servicio de Hojas de cálculo en Apps Script para crear un gráfico de los datos de BigQuery
  6. Usar el servicio de Presentaciones para crear un archivo de Presentaciones de Google
  7. Agrega un título y subtítulo a la diapositiva de título predeterminada.
  8. Crear una diapositiva con una tabla de datos y exportar allí las celdas de datos de la hoja de cálculo
  9. Crea otra diapositiva e 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 al que pueden acceder todos los niveles de habilidades de los desarrolladores. En esencia, Apps Script es un entorno de ejecución de JavaScript sin servidores para la automatización, integración y extensió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 y asíncrono basado en eventos. También cuenta con un entorno de desarrollo que puede ser diferente del que estás acostumbrado. Con Apps Script, puedes realizar las siguientes acciones:

  • Desarrolla secuencias de comandos con un editor de código basado en el navegador, pero también puedes desarrollarlas de forma local cuando uses clasp, la herramienta de implementación de línea de comandos para Apps Script.
  • Escribe código en una versión especializada de JavaScript personalizada para acceder a Google Workspace y otros servicios externos o de Google (mediante los servicios de URL Fetch o JDBC de Apps Script).
  • Puede evitar escribir código de autorización como Apps Script lo hace por ti.
  • Ya 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, en esencia, un wrapper delgado en torno a una API de Google Workspace o de 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, al mismo tiempo, son más fáciles de usar que la API de REST completa). Los servicios avanzados también deben estar activados para un proyecto de secuencia de comandos antes de usarlos.

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

Acceder a Hojas de cálculo y Presentaciones de 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. Consulta los documentos de los servicios integrados de Hojas de cálculo y Presentaciones antes de pasar al código. Ten en cuenta que también hay documentos 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

Haremos una gran parte de este codelab con esta primera tarea. De hecho, una vez que termines, estarás casi en la mitad de todo el codelab. Dividido en varias subsecciones, podrás hacer lo siguiente:

  • Crear un proyecto de Google Apps Script y de la consola de Cloud
  • 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.
  • Navegar por el proceso de autorización de aplicaciones (OAuth2)
  • Ejecutar 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

  1. Para crear un proyecto de Apps Script, ve a script.google.com y haz clic en Proyecto nuevo.
  2. Para cambiar el nombre de tu proyecto de Apps Script, haz clic en Proyecto sin título, ingresa un título para el 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.

  1. Si quieres crear un proyecto de la consola de Cloud, usa este vínculo de acceso directo para crear un proyecto, asígnale un nombre y haz clic en Crear.
  1. Cuando finalice 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 ubicada en la parte superior de la página.
  2. Haz clic en Menú f5fbd278915eb7aa.png y ve a APIs & Servicios > Pantalla de consentimiento de OAuth (vínculo directo)
  3. Haz clic en Interno > Create a fin de compilar una app para los usuarios de Google Workspace de tu organización.
  4. En el campo Nombre de la app, ingresa "Big Data Codelab".
  5. Ingresa las direcciones de correo electrónico de contacto en los campos Asistencia al usuario e Información de contacto del desarrollador.
  6. Haz clic en Guardar y continuar > Guardar y continuar
  7. Haz clic en Más 50fa7e30ed2d1b1c.png en la barra de navegación y selecciona Configuración del proyecto (vínculo directo).
  8. 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 la consola de Cloud.

  1. Cambia al editor de Apps Script y haz clic en Configuración del proyecto ajustes-de-configuración.
  2. En Proyecto de Google Cloud Platform (GCP), haz clic en Cambiar proyecto.
  3. Ingresa el número del proyecto y haz clic en Establecer proyecto.
  4. Luego, haz clic en Editor editor de código para comenzar a agregar el servicio avanzado de BigQuery.
  5. Junto a Servicios, haz clic en Agregar un servicio agregar un servicio.
  6. 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.

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

a0e07fa159de9367.png

Ya está 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

  1. 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());
}
  1. Haz clic en Guardar guardar.
  2. Junto a Code.gs, haz clic en Más 50fa7e30ed2d1b1c.png > Cambiar nombre. Cambia el título de Code.gs a bq-sheets-slides.js.
  3. A continuación, revisaremos el código que realiza consultas a 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 que aparecen con mayor frecuencia en todas sus obras, en orden descendente de popularidad. Te das una idea de lo útil que puede ser BigQuery cuando imaginas cuánto trabajo sería hacer esta compilación manualmente.

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

  1. Cambia a tu proyecto de la consola de Cloud, haz clic en Más 50fa7e30ed2d1b1c.png en la barra de navegación y selecciona Configuración del proyecto.
  2. Copia el valor que aparece en ID del proyecto.
  3. Vuelve al editor de Apps Script, ubica la variable PROJECT_ID en bq-sheets-slides.js y agrega el valor.
  4. Haz clic en Guardar guardar > Ejecutar (Run).
  5. Haz clic en Revisar permisos para continuar.
  1. Una vez que la secuencia de comandos comienza a ejecutarse, se abre el registro de ejecución integrado y se registran las acciones de la secuencia de comandos en tiempo real.
  1. Una vez que el registro de ejecución diga “Execution complete”, ve a tu unidad de Google Drive (drive.google.com) y busca la hoja de cálculo de Google con el nombre “Most common words in all of Shakespeare's works”. (o el nombre que asignaste a la variable QUERY_NAME, si la actualizaste):
  2. Abre la hoja de cálculo para ver las 10 palabras más comunes y su recuento total en orden descendente:

afe500ad43f8cdf8.png

Resumen de la tarea 1

Para repasar, ejecutaste un código que consultaba todas las obras de Shakespeare analizando cada palabra de cada obra. Contaba las palabras y las ordenaba en orden descendente de apariencia. 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 ejecutaba una consulta ligeramente diferente que recuperaba las palabras más populares con 10 o más caracteres usados por 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 en 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 con la página de BigQuery en la consola de Cloud antes de ejecutarlas en Apps Script. Para encontrarla, haz clic en Menú f5fbd278915eb7aa.png y ve a IU de BigQuery > Lugar de trabajo de SQL (vínculo directo). Por ejemplo, así es como aparece 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 objetivo de runQuery() es usar BigQuery y enviar los resultados de los datos a una hoja de cálculo de Google. A continuación, debemos hacer un gráfico con los datos. Creemos una nueva función llamada createColumnChart() que llame a Hojas de cálculo newChart().

  1. 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);
}
  1. La función createColumnChart() requiere un parámetro de objeto de hoja de cálculo, por lo que debemos actualizar runQuery() para mostrar un objeto spreadsheet que podamos pasar a createColumnChart(). Al final de runQuery(), muestra el objeto spreadsheet después de registrar la creación correcta de la hoja de cálculo:
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());

  // NEW: Return the spreadsheet object for later use.
  return spreadsheet;
}
  1. Crea una función createBigQueryPresentation() para llamar a runQuery() y createColumnChart(). Se recomienda separar lógicamente la función de creación de gráficos de BigQuery:
/**
 * Runs the query, adds data and a chart to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  createColumnChart(spreadsheet);
}
  1. Anteriormente, realizaste 2 pasos importantes: devolver el objeto de la hoja de cálculo y crear la función de entrada. Para que runQuery() sea más fácil de usar, debemos mover la línea de registro de runQuery() a createBigQueryPresentation(). 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 secuencia de comandos, guarda tu proyecto de secuencia de comandos. Luego, selecciona createBigQueryPresentation de la lista de funciones y haz clic en Ejecutar. Cuando finaliza, se crea otra hoja de cálculo en tu Google Drive, pero esta vez se incluye un gráfico en la hoja 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 implica crear una presentación de Presentaciones de Google, agregar el título y el subtítulo a la diapositiva del título y, luego, crear diapositivas para las celdas de datos y el gráfico.

  1. 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 lleva a cabo en esta función. Empecemos con la creación de una presentación de diapositivas y, luego, agregaremos un título y subtítulo a la diapositiva de título 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');
  1. El siguiente paso en createSlidePresentation() es importar los datos de las celdas de la hoja de cálculo de Google a nuestra nueva presentación de diapositivas. Agrega este fragmento de código a la siguiente 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]));
    }
  }
  1. El último paso 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 siguiente 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;
}
  1. Ahora que la función está completa, vuelve a observar su firma. createSlidePresentation() requiere parámetros de objeto de hoja de cálculo y gráfico. Ya ajustamos runQuery() para mostrar el objeto Spreadsheet, pero necesitamos hacer un cambio similar en createColumnChart() para que muestre un objeto de gráfico (EmbeddedChart). Regresa 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;
}
  1. Dado que createColumnChart() ahora muestra un objeto de gráfico, debemos guardar el gráfico en una variable. Luego, pasamos tanto la hoja de cálculo como las variables del gráfico a createSlidePresentation(). Además, como registramos la URL de la hoja de cálculo recién creada, registraremos 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
}
  1. Guarda y vuelve a ejecutar createBigQueryPresentation(). Sin embargo, antes de que se ejecute, la app necesita un conjunto más de permisos del usuario para ver y administrar archivos de Presentaciones de Google. Una vez que revises y otorgues estos permisos, se ejecutará como antes.
  2. Ahora, además de la hoja de cálculo que se creó, también deberías obtener un nuevo documento de Presentaciones con 3 diapositivas (título, tabla de datos, gráfico de datos), como se muestra a continuación:

f6896f22cc3cd50d.png

59960803e62f7c69.png

5549f0ea81514360.png

7. Conclusión

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

Técnicamente, estos pasos son los que hiciste. En términos generales, pasaste del análisis de macrodatos a un resultado que puedes presentar a las partes interesadas, todo automatizado con código. Esperamos que este ejemplo te inspire a personalizarlo para tus propios proyectos. Al final de este codelab, te daremos 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());
}

Esta muestra 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

Videos

Novedades y actualizaciones

Otros codelabs

Introductorio

Intermedio

9. Próximo paso: Desafíos del código

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

  • Application: ¿No quieres limitarte al uso de JavaScript o por las restricciones impuestas por Apps Script? Transfiere esta aplicación a tu lenguaje de programación favorito que use las APIs de REST para Google BigQuery, Hojas de cálculo y Presentaciones.
  • BigQuery: Experimenta con una consulta diferente para el conjunto de datos de Shakespeare que te interesa. Puedes encontrar otra consulta de muestra en la app de ejemplo de BigQuery de Apps Script original.
  • BigQuery: Experimenta con algunos 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 gran tamaño en hojas de cálculo. En 2016, el equipo de BigQuery presentó una función que permite a los desarrolladores usar un documento de Hojas de cálculo como fuente de datos. Para obtener más información, consulta (Google BigQuery se integra en Google Drive.
  • Presentaciones: Agrega otras diapositivas a la presentación generada, como imágenes o algún otro recurso vinculado a tu análisis de macrodatos. A continuación, encontrarás la documentación de referencia para el servicio integrado de Presentaciones.
  • Google Workspace: Usa otros servicios integrados de Google Workspace o de Google desde Apps Script. Por ejemplo, Gmail, Calendario, Documentos, Drive, Maps, Analytics, YouTube, etc., y otros servicios avanzados. Para obtener más información, ve a la descripción general de la referencia para los servicios integrados y avanzados.