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

Existen muchas herramientas que les permiten a los científicos de datos realizar análisis de macrodatos, pero, al final del día, ¿no tienes que justificar esos resultados ante la administración? Los números en papel o en una base de datos difícilmente se pueden presentar a las partes interesadas clave. Este codelab intermedio de Google Apps Script aprovecha un par de plataformas para desarrolladores de Google, G Suite y Google Cloud Platform (GCP), para ayudarte a completar ese proceso.

Las herramientas para desarrolladores de Google Cloud te permiten realizar un análisis de datos detallado y, luego, tomar esos resultados, colocarlos en una hoja de cálculo y generar una presentación de diapositivas con esos datos, lo que proporciona una etapa más adecuada para que los resultados se entreguen a la administración. En este codelab, se explican la API de BigQuery de GCP (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 y conocimiento anterior

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

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 en varios aspectos:

  • Comparación entre la app de Node.js y nuestra app de Apps Script
  • Usa APIs de REST mientras nosotros usamos servicios de Apps Script
  • Usa 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 la mayor cantidad posible de tecnologías en una sola app y, al mismo tiempo, mostrar las funciones y las APIs de todo Google Cloud de una manera que se asemeje más a un caso de uso real. El objetivo es inspirarte a usar tu imaginación y considerar aprovechar GCP y G Suite para resolver problemas desafiantes para tu organización o tus clientes.

Qué aprenderás

  • Cómo usar Google Apps Script con múltiples servicios de Google (GCP y G Suite)
  • Cómo usar Google BigQuery para realizar un análisis de macrodatos
  • Cómo crear una hoja de cálculo de Google y propagar datos en ella
  • Cómo crear un gráfico nuevo en Hojas de cálculo
  • Cómo transferir gráficos y datos de Hojas de cálculo a una presentación de Presentaciones de Google

Requisitos

  • Acceso a Internet y un navegador web
  • Una Cuenta de Google (las cuentas de G Suite pueden requerir la aprobación del administrador)
  • Habilidades básicas de JavaScript
  • Puede ser útil tener conocimientos sobre el desarrollo de Apps Script, pero no es obligatorio.

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

Leerlo con fines informativos y, posiblemente, 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 herramientas y las APIs para desarrolladores de G Suite?

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 herramientas y las APIs para desarrolladores de GCP?

Principiante Intermedio Avanzado

Ahora que sabes de qué se trata este codelab, ¿qué harás exactamente?

  1. Toma una muestra existente de Apps Script y BigQuery y haz que funcione
  2. A partir de esa muestra, aprende a enviar una consulta a BigQuery y obtener sus resultados.
  3. Crea una hoja de cálculo de Google y propágala con los resultados de BigQuery
  4. Modifica un poco el código para alterar ligeramente los datos que se devuelven y se agregan a la hoja.
  5. Usa el servicio de Hojas de cálculo en Apps Script para crear un gráfico con los datos de BigQuery
  6. Usa el servicio de Presentaciones para crear una nueva presentación de diapositivas
  7. Agrega un título y un subtítulo a la primera diapositiva predeterminada que se crea automáticamente para todas las presentaciones nuevas.
  8. Crea una nueva diapositiva con una tabla de datos y, luego, importa las celdas de datos de la hoja de cálculo
  9. Agrega otra diapositiva nueva y agrégale el gráfico de la hoja de cálculo

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 G Suite 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. A modo de resumen, "Apps Script es un entorno de ejecución de JavaScript sin servidores para la integración, extensión y automatización de G Suite".

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

  • Desarrollar en un editor de código basado en navegador, pero puedes elegir hacerlo localmente si 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 G Suite y a otros servicios externos o de Google (mediante los servicios de URLfetch o Jdbc de Apps Script)
  • Puedes evitar escribir código de autorización, ya que Apps Script lo hace por ti
  • Evitar alojar tu aplicación, ya que esta reside y se ejecuta en los servidores de Google en la nube

NOTA: En su mayor parte, la enseñanza de Apps Script no es parte del alcance de este codelab. Hay muchísimos recursos en línea que lo ayudarán con esto. La documentación oficial también incluye una descripción general con guías de inicio rápido, instructivos y videos. Por último, no olvides el codelab introductorio de Apps Script, que debes completar antes de comenzar este.

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

  • servicio integrado o nativo
  • servicio avanzado

Un servicio integrado proporciona métodos de alto nivel que puedes usar para acceder a datos de productos de Google o de G Suite, y también otros convenientes métodos de utilidad. Un servicio avanzado es simplemente un wrapper delgado en torno a una API de REST de Google o de G Suite. 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). Los servicios avanzados también deben estar habilitados para un proyecto de secuencia de comandos antes de usarlos.

Cuando sea posible, los desarrolladores deben preferir un servicio integrado, ya que son más fáciles de usar y ejecutan más trabajo pesado 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. Google BigQuery es un ejemplo de esto: no hay un servicio integrado disponible, pero sí existe un servicio avanzado de BigQuery. (mejor que ningún servicio, ¿cierto?). Si no tienes experiencia en BigQuery, se trata de un servicio de GCP que te permite realizar consultas simples (o complejas) en grandes volúmenes de datos, por ejemplo, de varios terabytes, y aun así puede proporcionar resultados en segundos.

Accede a Hojas de cálculo y Presentaciones de Google desde Apps Script

A diferencia de BigQuery, tanto Hojas de cálculo como Presentaciones de Google tienen servicios integrados (así como servicios avanzados, que solo usarías para acceder a funciones que solo se encuentran en la API). Consulta la documentación de los servicios integrados de Hojas de cálculo y Presentaciones antes de pasar al código. Por supuesto, también hay documentación para los servicios avanzados; aquí se encuentran para Hojas de cálculo y Presentaciones, respectivamente.

Introducción

Cubrirás gran parte del contenido de este codelab cuando realices esta primera tarea. De hecho, cuando termines aquí, habrás completado la mitad del codelab. Dividido en varias subsecciones, harás todo lo siguiente:

  • Iniciar un nuevo proyecto de Google Apps Script
  • Habilitar el acceso al servicio avanzado de BigQuery
  • Ir al editor de desarrollo y, luego, ingresar el código fuente de la aplicación
  • Realizar el proceso de autorización de la aplicación (OAuth2)
  • Ejecutar la aplicación que envía una solicitud a BigQuery
  • Ver un documento completamente nuevo de Hojas de cálculo de Google con los resultados de BigQuery

Configuración

  1. a) Crea un nuevo proyecto de Apps Script en script.google.com . Existen diferentes líneas de productos de G Suite, y la forma de crear un proyecto nuevo puede variar según la versión que uses. Si solo usas tu cuenta de Gmail y no tienes experiencia en el desarrollo de proyectos, verás una pantalla en blanco junto con un botón para crear tu primer proyecto:


b) De lo contrario, es posible que veas todos tus proyectos y un botón grande +Nuevo en la esquina superior izquierda. Haz clic en él.



c) Si no se da ninguna de las opciones anteriores, es posible que tu pantalla se vea como la siguiente. Si es así, busca el ícono de menú de hamburguesa en la esquina superior izquierda y selecciona + Nuevo script.



d) Para quienes prefieren la línea de comandos. Tu herramienta es clasp, específicamente, ejecutarás el comando clasp create.

e) La última forma de crear un proyecto de secuencia de comandos nuevo es simplemente ir al vínculo de acceso directo: https://script.google.com/create.

  1. Independientemente de la técnica que hayas usado para iniciar un proyecto nuevo, el resultado final es que deberías acceder al editor de código de Apps Script, una pantalla como esta:


  2. Haz clic en File > Save y asigna un nombre a tu proyecto.
  3. A continuación, deberás crear un proyecto de Google Cloud Console para ejecutar consultas de BigQuery.
  1. Crea un proyecto nuevo, asígnale un nombre, selecciona una cuenta de facturación y haz clic en CREAR.
  1. Cuando se complete la creación del proyecto, aparecerá una notificación en la esquina superior derecha de la página. Haz clic en la entrada Crear proyecto: <Nombre del proyecto> para abrir el proyecto.
  2. Haz clic en el ícono de menú en la esquina superior izquierda y navega a APIs y servicios > Credenciales. Haz clic en la pestaña Pantalla de consentimiento de OAuth (vínculo directo).
  3. En el campo Nombre de la aplicación, ingresa "Big Data Codelab" y haz clic en el botón Guardar que se encuentra en la parte inferior.
  4. Haz clic en el ícono de tres puntosen la esquina superior derecha para expandir el menú y selecciona Configuración del proyecto (vínculo directo).
  5. Copia el valor que aparece en Número del proyecto. (Hay un campo independiente ID de producto que usaremos más adelante en el codelab).
  6. En el editor de App Script, haz clic en Resources > Cloud Platform project.
  7. Ingresa el número del proyecto en el cuadro de texto y haz clic en Set Project. Cuando se te solicite, haz clic en Confirmar.
  8. Cuando termines, haz clic en el botón Cerrar para descartar el cuadro de diálogo.
  9. Ahora que configuraste un proyecto nuevo, debes habilitar el servicio avanzado de BigQuery. Para ello, despliega Resources -> Advanced Google Services y activa el bit (on) para la API de BigQuery.


  10. En la parte inferior, hay una nota que dice: "Estos servicios también deben habilitarse en el "Panel de la API de Google Cloud Platform", así que haz clic en ese vínculo que abre otra pestaña del navegador en la consola para desarrolladores o "devconsole" para abreviar.
  11. En la consola para desarrolladores, haz clic en el botón +Habilitar APIs y servicios en la parte superior, busca "bigquery", selecciona la API de BigQuery (no la API de BigQuery Data Transfer) y haz clic en Habilitar para activarla. Deja abierta esta pestaña del navegador.

    NOTA: Después de habilitar la API, es posible que veas una nota en esta página que diga algo como "Para usar esta API, debes crear credenciales…", pero no te preocupes por eso por ahora. Apps Script se encargará de este paso por ti.


  12. De vuelta en la pestaña del navegador del editor de código, aún estás en el menú de Servicios avanzados de Google, así que haz clic en Aceptar para cerrar el diálogo y volver al editor de código. Haz clic en el nombre del proyecto en la parte superior y asígnale el nombre que quieras, como "Demostración de BigQuery" o similar. Nosotros lo llamamos "final mile".

Ya estás listo para ingresar el código de la aplicación, realizar el proceso de autorización y hacer funcionar la primera versión de esta aplicación.

Sube la aplicación y ejecútala

  1. Copia el código del siguiente recuadro y pégalo por encima de todo lo que aparece en el editor de código:
// Filename for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud API project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into Sheet. You must enable
 * the BigQuery advanced service before you can run this code.
 * @see http://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see http://github.com/googleworkspace/apps-script-samples/blob/main/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see http://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 BQ 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 new 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());
}


Ahora guarda el archivo que acabas de crear, pero cámbiale el nombre de Code.gs a bq-sheets-slides.js. ¿Qué hace este código? Ya te dijimos que consulta BigQuery y escribe los resultados en una nueva Hoja de cálculo de Google, pero ¿qué representa esta consulta? 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. Para tener una idea de lo útil que es BigQuery, basta con imaginar lo tedioso que sería hacer esta tarea manualmente.

  1. Ya casi estamos listos para probarlo. Como puedes ver cerca de la parte superior de este fragmento de código, se requiere un ID de proyecto válido, por lo que debemos agregar el tuyo al código de la aplicación. Para obtenerlo, regresa a la ventana o pestaña del navegador que tiene la página de la consola para desarrolladores. (Te dijimos que la dejaras abierta, ¿recuerdas?)
  2. En la parte superior, a la izquierda de tu avatar de la Cuenta de Google, se encuentra el selector del menú desplegable (). Haz clic en él y selecciona Configuración del proyecto. Verás el nombre, el ID y el número del proyecto. Copia el ID del proyecto y establece la variable PROJECT_ID en la parte superior de bq-sheets-slides.js con el valor que obtuviste de la consola para desarrolladores. NOTA: Si el selector de menú está fijo y no funciona, vuelve a cargar la página.
  3. La instrucción if sirve para evitar que la aplicación avance sin un ID de proyecto implementado. Una vez que hayas agregado la tuya, guarda el archivo y ejecuta tu código. Para ello, sube hasta la barra de menú y selecciona Ejecutar > Ejecutar la función > runQuery. Haz clic en el diálogo Revisar permisos , Esta app no está verificada. A continuación, se muestra un GIF animado (para otra app) que ilustra los próximos pasos:
  4. Una vez que solicites revisar los permisos, aparecerá un diálogo nuevo, como se muestra arriba. Elige la cuenta de Google correcta que ejecutará la secuencia de comandos, selecciona Avanzado, desplázate hacia abajo y, luego, haz clic en "Ir a <NOMBRE DE TU PROYECTO> (inseguro)" para llegar a la pantalla de autorización de la aplicación OAuth2. (Obtén más información sobre el proceso de verificación para saber por qué esta pantalla se interpone entre tú y el diálogo de autorización de OAuth2 que se muestra a continuación).


    NOTA: Una vez que autorices la app, no necesitarás repetir este proceso para cada ejecución. Cuando llegues a la tarea 3 más adelante en este instructivo, verás la pantalla de diálogo nuevamente. Ahí se te pedirá permiso de usuario para crear y administrar los documentos de Presentaciones de Google.
  5. Una vez que hagas clic en Permitir en la ventana de diálogo de OAuth2, comenzará a ejecutarse la secuencia de comandos. Verás un diálogo de color amarillo pastel en la parte superior. Se ejecuta con bastante rapidez, por lo que es posible que no notes que se está ejecutando o que se completó la ejecución.

  6. Ese diálogo desaparece una vez que finaliza el proceso. Si no lo ves, es probable que haya terminado. Ve a tu unidad de Google Drive (drive.google.com) y busca una nueva hoja de cálculo de Google con el nombre "Most common words in all of Shakespeare's works" o el nombre que le hayas asignado a la variable QUERY_NAME:


  7. Abre la hoja de cálculo. Deberías ver 10 filas de palabras y su conteo total en orden descendente:

Resumen de la tarea 1

Reconoce lo que acaba de suceder… ejecutaste código que consultó todas las obras de Shakespeare (no es una cantidad ENORME de datos, pero definitivamente es más texto del que puedes revisar por tu cuenta para examinar todas las palabras en todas las obras, llevar un conteo de esas palabras y clasificarlas en orden descendente. No solo le derivaste este trabajo a BigQuery, sino que también pudiste usar el servicio integrado en Apps Script para Hojas de cálculo de Google con el fin de organizar los datos y facilitar su utilización.

El código de bq-sheets-slides.js (el nombre de archivo que elegimos) que pegaste anteriormente (además de PROJECT_ID, que debería tener un ID de proyecto real) 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 el ejemplo original de la página de servicios avanzados de BigQuery, que ejecutaba una consulta ligeramente diferente: ¿cuáles son las palabras más populares que usa Shakespeare con 10 o más caracteres? También puedes ver esa muestra en su repositorio de GitHub.

Si te interesan otras consultas que puedes probar con las obras de Shakespeare o con otras tablas de datos públicos, consulta esta página web y esta otra. Independientemente de la consulta que uses, siempre puedes probarla en la consola de BigQuery antes de ejecutarla en Apps Script. La interfaz de usuario de BigQuery está disponible para los desarrolladores en bigquery.cloud.google.com. Por ejemplo, así se ve nuestra consulta con la IU de BigQuery:

Si bien los pasos anteriores aprovecharon el editor de código de Apps Script, también puedes optar por desarrollar localmente a través de la línea de comandos. Si lo prefieres, crea un script llamado bq-sheets-slides.js, pega el código anterior en él y, luego, súbelo a Google con el comando clasp push. (Si no lo viste antes, aquí tienes de nuevo el vínculo a clasp y cómo usarlo).

El propósito de runQuery() es comunicarse con BigQuery y enviar sus resultados a una hoja de cálculo. Ahora debemos crear un gráfico con los datos. Creemos una nueva función llamada createColumnChart() que llame al método newChart() de la hoja de cálculo para hacer esto.

  1. Crear gráfico Agrega el cuerpo de createColumnChart() que se muestra a continuación a bq-sheets-slides.js justo después de runQuery(). Recibe la hoja de cálculo con los datos y solicita un gráfico de columnas con todos los datos. El rango de datos comienza en la celda A2 porque la primera fila contiene los encabezados de las columnas, y no datos.
/**
 * Uses spreadsheet data to create columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} visualizing the results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first and only) 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 params.
  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. Return spreadsheet. En el ejemplo anterior, createColumnChart() necesita el objeto de la hoja de cálculo, por lo que debemos actualizar runQuery() para que muestre el objeto spreadsheet y podamos pasarlo a createColumnChart(). Luego de crear correctamente la hoja de cálculo de Google y de registrarlo, devuelve el objeto spreadsheet al final de runQuery(), justo después de la línea de registro:
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());

  // NEW: Return the spreadsheet object for later use.
  return spreadsheet;
}
  1. Función de createBigQueryPresentation() conducción Segregar de manera lógica la funcionalidad para crear gráficos de BigQuery es una gran idea. Ahora, creemos una función createBigQueryPresentation() para controlar la app, llamando a runQuery() y createColumnChart(). El código que agregues debería verse de la siguiente manera:
/**
 * Runs a BigQuery query, adds data and a chart in a Sheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  createColumnChart(spreadsheet);
}
  1. Haz que el código sea más reutilizable. Previamente realizaste 2 pasos importantes: devolviste el objeto de la hoja de cálculo y creaste una función de accionamiento. ¿Qué sucede si un colega quisiera reutilizar runQuery() y no quiere que se registre la URL? Para que runQuery() sea más accesible para un uso general, debemos mover esa línea de registro. ¿Cuál es el mejor lugar donde colocarla? Si pensaste en createBigQueryPresentation(), estás en lo correcto. Después de mover la línea de registro, el código debería verse así:
/**
 * Runs a BigQuery query, adds data and a chart in a Sheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); // MOVED HERE
  createColumnChart(spreadsheet);
}

Con estos cambios anteriores (nuevamente, excepto por PROJECT_ID), tu bq-sheets-slides.js debería verse de la siguiente manera (y también se encuentra en la carpeta step2 del repositorio de GitHub):

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

/**
 * Runs a BigQuery query; puts results into Sheet. You must enable
 * the BigQuery advanced service before you can run this code.
 * @see http://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see http://github.com/googleworkspace/apps-script-samples/blob/main/advanced/bigquery.gs
 *
 * @returns {Sheet} Returns a sheet with results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/sheet
 */
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 BQ 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 new 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 columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} visualizing the results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first and only) 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 params.
  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 in a Sheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  createColumnChart(spreadsheet);
}

Guarda el archivo y, luego, diríjase a la parte superior del editor de código y cambia a ejecutar createBigQueryPresentation() en lugar de runQuery(). Después de ejecutarlo, obtendrás otra hoja de cálculo de Google, pero esta vez aparecerá un gráfico junto a los datos en la hoja:

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

  1. Crear presentación de diapositivas Todo el trabajo en la presentación se realizará en createSlidePresentation(), que agregaremos a bq-sheets-slides.js justo después de createColumnChart(). Comencemos con la creación de una nueva presentación de diapositivas y, luego, agreguemos un título y un subtítulo a la primera diapositiva predeterminada que se genera en toda presentación nueva.
/**
 * Create presentation with spreadsheet data & chart
 * @param {Spreadsheet} Spreadsheet with results data
 * @param {EmbeddedChart} Sheets chart to embed on slide
 * @returns {Presentation} Slide deck with results
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the new 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('via GCP and G Suite APIs:\n' +
    'Google Apps Script, BigQuery, Sheets, Slides');
  1. Agregar tabla de datos 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. Este es el fragmento de código, así que agrégalo 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 of
  // the dimensions of the data range; fails if Sheet 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. Importar gráfico. El paso final en createSlidePresentation() es crear una diapositiva más, importar el gráfico desde nuestra hoja de cálculo y devolver el objeto Presentation. Agrega este fragmento final 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;
}
  1. Gráfico de devoluciones. Ahora que nuestra función final está completa, vuelve a ver su firma. Sí, createSlidePresentation() requiere tanto una hoja de cálculo como un objeto de gráfico. Ya ajustamos runQuery() para que devuelva el objeto Spreadsheet, pero ahora necesitamos hacer un cambio similar en createColumnChart() para que devuelva el objeto de gráfico (EmbeddedChart). Vuelve a tu aplicación para agregar una última línea al final de createColumnChart() para hacerlo:
  // NEW: Return chart object for later use
  return chart;
}
  1. Actualizar createBigQueryPresentation(). Como createColumnChart() devuelve el gráfico, debemos guardarlo en una variable y, luego, pasar tanto la hoja de cálculo como el gráfico a createSlidePresentation(). 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 in a Sheet,
 * 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, reconoce que tu app ahora necesita un conjunto más de permisos de tu usuario para ver y administrar tus presentaciones de Presentaciones de Google. Una vez que permitas este permiso, se ejecutará como antes.
  2. Ahora, además de la hoja de cálculo que se creó, también deberías obtener una nueva presentación de Presentaciones con 3 diapositivas (título, tabla de datos, gráfico de datos), como se muestra a continuación:

¡Felicitaciones! Ahora creaste una aplicación que aprovecha ambos lados de Google Cloud realizando una solicitud de Google BigQuery que consulta uno de sus conjuntos de datos públicos, crea una nueva Hoja de cálculo de Google para almacenar los resultados, agrega un gráfico basado en esos datos recién recuperados y, finalmente, crea una presentación de Diapositivas de Google que incluye los resultados y el gráfico en la hoja de cálculo.

Técnicamente, eso es lo que hiciste. En términos generales, pasaste de un análisis de macrodatos a algo que puedes presentar a las partes interesadas, todo en código, todo automatizado. Esperamos que este ejemplo te inspire a tomarlo y personalizarlo para tus propios proyectos. Al finalizar este codelab, te brindaremos algunas sugerencias para que puedas mejorar aún más esta app de ejemplo.

Con los cambios anteriores de la tarea final (nuevamente, excepto por PROJECT_ID), tu bq-sheets-slides.js debería verse de la siguiente manera (también se encuentra en la carpeta final del repositorio de GitHub):

bq-sheets-slides.js

/**
 * 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.
 */

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

/**
 * Runs a BigQuery query; puts results into Sheet. You must enable
 * the BigQuery advanced service before you can run this code.
 * @see http://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see http://github.com/googleworkspace/apps-script-samples/blob/main/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see http://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 BQ 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 new 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 columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} visualizing the results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first and only) 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 params.
  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 & chart
 * @param {Spreadsheet} Spreadsheet with results data
 * @param {EmbeddedChart} Sheets chart to embed on slide
 * @returns {Presentation} Returns a slide deck with results
 * @see http://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the new 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('via GCP and G Suite 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 of
  // the dimensions of the data range; fails if Sheet 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 in a Sheet,
 * 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());
}

El "segundo archivo" de este codelab, que es el archivo de manifiesto de Apps Script, appsscript.json, no desempeña ningún papel en este codelab. Para acceder a él, ve a la pestaña del navegador del editor de código y selecciona Ver > Mostrar archivo de manifiesto en el menú de la parte superior. El contenido debería ser similar a este:

appsscript.json

{
  "timeZone": "America/Los_Angeles",
  "dependencies": {
    "enabledAdvancedServices": [{
      "userSymbol": "BigQuery",
      "serviceId": "bigquery",
      "version": "v2"
    }]
  },
  "exceptionLogging": "STACKDRIVER"
}

El archivo de manifiesto es un archivo de configuración a nivel del sistema que Apps Script usa para saber qué entorno de ejecución debe estar disponible para tu aplicación. El contenido de un archivo de manifiesto está fuera del alcance de este codelab, pero puedes hacerte una idea de lo que hace.

A continuación, encontrarás recursos adicionales 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. Nuestro objetivo es mantener este codelab sincronizado con el repositorio.

Recursos para esta aplicación

Documentación

Otros codelabs

Introductorio

Intermedio

Apps de referencia

A continuación, se incluyen varios "desafíos de código", diferentes formas en las que puedes mejorar o aumentar la muestra que compilamos en este codelab. Esta lista no es exhaustiva, pero debería brindarte algunas ideas inspiradoras sobre el próximo paso que puedes dar.

  • Aplicación. ¿No quieres limitarte a usar JavaScript o las restricciones impuestas por 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… quizás encuentres una consulta que te interese. Puedes encontrar otra consulta de ejemplo en la app de ejemplo de BigQuery de Apps Script original.
  • BigQuery. Experimenta con algunos de los otros conjuntos de datos públicos de BigQuery… tal vez encuentres uno que sea más significativo para ti.
  • BigQuery. Anteriormente, mencionamos otras consultas que puedes probar con las obras de Shakespeare o con otras tablas de datos públicos. Queremos volver a compartir esta página web y esta otra.
  • Hojas de cálculo Experimenta con otros tipos de gráficos.
  • Hojas de cálculo y BigQuery Cambia las cosas… quizás tengas un conjunto de datos grande en una hoja de cálculo en algún lugar. En 2016, el equipo de BigQuery introdujo la función que permite a los desarrolladores usar una hoja de cálculo como fuente de datos (consulta las entradas de blog uno y dos para obtener más información).
  • Presentaciones. Agrega otras diapositivas a la presentación generada, como imágenes o recursos relacionados con tu análisis de Big Data. Aquí tienes una guía del servicio integrado de Presentaciones para comenzar.
  • G Suite Encuentra usos para otros servicios integrados de G Suite o Google desde Apps Script, es decir, Gmail, Google Drive, Calendario, Documentos, Maps, Analytics, YouTube, etc., así como otros servicios avanzados Consulta la descripción general de referencia para obtener más información sobre los servicios integrados y avanzados.