Convierta sus macrodatos en estadísticas con Hojas de cálculo y Presentaciones de Google

Existen muchas herramientas disponibles para que los científicos de datos puedan realizar análisis de macrodatos, pero al final del día, ¿aún tienen que justificar esos resultados en la administración? Muchos números en papel o en una base de datos difícilmente se pueden presentar para 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 profundo de los datos para 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. Este codelab abarca 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/Arte artístico

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

Si bien la app de muestra del codelab de Presentaciones también presenta BigQuery y Presentaciones, difiere de esta app de ejemplo de varias maneras:

  • Comparación entre la aplicación de Node.js y nuestra aplicación de Apps Script
  • Usa las API de REST mientras usamos los 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, quisimos reunir la mayor cantidad de las tecnologías en una sola app y, al mismo tiempo, mostrar las funciones y API de todo Google Cloud de una manera que se asemeje mejor a un caso de uso real. El objetivo es inspirarlo a usar su imaginación y considerar aprovechar tanto GCP y G Suuite como la solución de problemas desafiantes para su organización o sus 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 nuevo gráfico en Hojas de cálculo
  • Cómo transferir gráficos y datos de Hojas de cálculo a un archivo de Presentaciones de Google

Requisitos

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

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

Leer para fines informativos, tal vez enviárselo a colegas técnicos Hacerlo lo más posible y probar todos los ejercicios que pueda Poder el infierno o ir al grano, voy a completar todo el codelab

¿Cómo calificarías tu experiencia con las herramientas para desarrolladores de G Suite y las API?

Principiante Intermedio Avanzado

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

Principiante Intermedio Avanzado

¿Cómo calificarías tu experiencia con las herramientas para desarrolladores de GCP y las API?

Principiante Intermedio Avanzado

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

  1. Tome una muestra existente de Apps Script-BigQuery y haga que funcione
  2. A partir de esa muestra, aprende a enviar una consulta a BigQuery y obtener sus resultados.
  3. Crear una hoja de cálculo de Google y propagar los resultados de BigQuery en ella
  4. Modifique un poco el código para alterar ligeramente los datos que se muestran y agregan a la hoja
  5. Use el servicio de Hojas de cálculo en Apps Script para crear un gráfico de los datos de BigQuery
  6. Use el servicio Presentaciones para crear una nueva presentación de diapositivas
  7. Agregar un título y un subtítulo a la diapositiva de título predeterminada que se crea automáticamente para todas las nuevas presentaciones de diapositivas
  8. Crear una diapositiva nueva con una tabla de datos y, luego, importar las celdas de Hojas de cálculo
  9. Agregar otra diapositiva y agregarle 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 API 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, puede realizar lo siguiente:

  • Desarrollar en un editor de código basado en navegador, pero puede elegir hacerlo de manera local si usa 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)
  • Evitar escribir código de autorización porque Apps Script lo realiza por usted
  • Evitar alojar su aplicación, ya que esta reside y se ejecuta en los servidores de Google en la nube

NOTA: En su mayor parte, no se trata del tema de este codelab para enseñarte Apps Script. 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 se debe completar antes de comenzar este.

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

  • servicio integrado y nativo
  • servicio avanzado

Un servicio integrado proporciona métodos de alto nivel que puede usar para acceder a datos de productos de Google o de G Suite, y de otros métodos útiles. 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 porque son más fáciles de usar y realizan más trabajo pesado que los servicios avanzados. Sin embargo, algunas API 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 un servicio avanzado de BigQuery síexiste. (Mejor que ningún servicio, ¿cierto?). Si eres nuevo en BigQuery, es un servicio de GCP que te permite realizar consultas simples (o complejas) en corpus de datos muy grandes, por ejemplo, del orden de múltiples terabytes, pero aún puede proporcionar resultados en segundos.

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

A diferencia de BigQuery, Hojas de cálculo y Presentaciones de Google tienen servicios integrados (y servicios avanzados que solo utilizas para acceder a funciones que solo se encuentran en la API). Consulte los documentos de los servicios integrados de Hojas de cálculo y Presentaciones antes de pasar al código. Por supuesto, también existen documentos para los servicios avanzados. Aquí están para Hojas de cálculo y Presentaciones, respectivamente.

Introducción

En esta primera tarea, analizaremos en gran medida este codelab. De hecho, una vez que hayas terminado aquí, estarás aproximadamente a mitad de camino con todo el codelab. Dividido en varias subsecciones, realizará todas estas acciones:

  • 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

Crear

  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 recién comienzas a desarrollar proyectos, verás una pantalla en blanco junto a un botón para crear tu primer proyecto:


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



c) Si ninguna de las opciones anteriores se ve como la que se muestra a continuación, Si es así, busque el ícono de menú hamburguesa en la esquina superior izquierda y seleccione +Nueva secuencia de comandos.



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 nuevo proyecto de secuencia de comandos 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, lo más importante es que te ubiques en el editor de código de Apps Script, una pantalla que tiene el siguiente aspecto:


  2. Haz clic en File > Save y asígnale 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 Create Project: <Project Name> para abrir el proyecto.
  2. Haz clic en el ícono de menú en la parte superior izquierda y navega hasta API y > Credenciales. Haz clic en la pestaña Pantalla de consentimiento de OAuth (vínculo directo).
  3. En el campo Application name, ingresa Codelab de macrodatos y haz clic en el botón Save en la parte inferior.
  4. Haga clic en el ícono de tres puntos en la parte superior derecha para expandir el menú y seleccione Configuración del proyecto(vínculo directo).
  5. Copie el valor que aparece en Número de proyecto. (Hay un campo separado, Product ID, que usaremos más adelante en el codelab).
  6. En el Editor de App Script, haga clic en Resources > Cloud Platform project.
  7. Ingresa el número de proyecto en el cuadro de texto y haz clic en Establecer proyecto. Cuando se te solicite, haz clic en Confirmar.
  8. Cuando hayas terminado, haz clic en el botón Cerrar para descartar el diálogo.
  9. Ahora que tienes un nuevo proyecto configurado, debes habilitar el servicio avanzado de BigQuery, por lo que debes abrir Recursos - Servicios avanzados de Google y activar el bit para la API de BigQuery.


  10. Nota en la parte inferior: estos servicios también se deben habilitar en el panel de la API de Google Cloud Platform. Haz clic en ese vínculo, que abre otra pestaña del navegador en Play Console o en la consola de desarrolladores.
  11. En la consola de desarrolladores, haga clic en el botón +Habilitar API y servicios que se encuentra en la parte superior, busque "bigquery" seleccione la API de BigQuery (no la API de Transferencia de datos de BigQuery) y haga clic en Habilitar para activarla. Deja esta pestaña del navegador abierta.

    NOTA: Una vez que se haya habilitado 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 ahora, Apps Script se encargará de este paso.


  12. En la pestaña del navegador del editor de código, seguirás en el menú Servicios avanzados de Google, así que haz clic en Aceptar para cerrar el cuadro de diálogo y dejarte en el editor de código. Haga clic en el nombre del proyecto en la parte superior y asígnele el nombre que desee, como "Demostración de BigQuery" o algo similar. Lo que denominamos nuestro "último tramo".

Ya está 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 cuadro y pégalo con 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 le indicamos que consulta a BigQuery y que escribe los resultados en una nueva hoja de cálculo de Google, pero ¿qué es 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 analiza 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 es BigQuery, basta con imaginar lo tedioso que sería hacer esta tarea manualmente.

  1. Ya casi terminamos, pero no 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 ello, regresa a la ventana o pestaña del navegador que tiene la página de la consola del desarrollador. (Te dijimos que la dejes abierta, ¿te acuerdas?)
  2. En la parte superior izquierda del avatar de tu Cuenta de Google, se encuentra el selector de menú desplegable (). Haz clic en él y selecciona Project settings. 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 Developer Console. NOTA: Si el selector de menú se vuelve inestable y no funciona, vuelve a cargar la página.
  3. La sentencia if sirve para evitar que la aplicación avance sin un ID de proyecto implementado. Cuando agregues el tuyo, guarde el archivo y ejecuta tu código. Para ello, ve a la barra de menú y selecciona Run > Run function > runQuery, haz clic en el diálogo Review Permissions, esta app no está verificada y, a continuación, aparece un GIF animado (para otra app) que ilustra los siguientes pasos:
  4. Una vez que solicites los permisos, se mostrará un nuevo diálogo, como se muestra más arriba. Elige la Cuenta de Google correcta que ejecutará la secuencia de comandos, selecciona Avanzada, desplázate hacia abajo y haz clic en “Ir a NOMBRE DEL PROYECTO” (no seguro) para acceder a la pantalla de autorización de la aplicación de OAuth2. (Obtén más información sobre el proceso de verificación para conocer el motivo por el que se encuentra esta pantalla entre tú y el diálogo de autorización de OAuth2).


    NOTA: Una vez que autorices la app, no tendrás que repetir este proceso con cada ejecución. Hasta que no puedas continuar con la Tarea 3 en este instructivo, volverás a ver esta pantalla de diálogo en la que solicitarás permiso de usuario para crear y administrar archivos de Presentaciones de Google.
  5. Una vez que hagas clic en Permitir en la ventana de diálogo de OAuth2, la secuencia de comandos comenzará a ejecutarse... verás un cuadro de color amarillo pastel en la parte superior. Se ejecuta con bastante rapidez, por lo que tal vez no notes que se está ejecutando o que se completó ese proceso.

  6. Ese cuadro de diálogo desaparece una vez que finaliza el trabajo; por lo tanto, si no lo ves, es probable que haya finalizado. Ve a Google Drive (drive.google.com) y busca una nueva Hoja de cálculo de Google llamada "Palabras más comunes en todas las obras de Shakespeare":


  7. Abre la hoja de cálculo. Deberías ver 10 filas de palabras y sus recuentos totales en orden descendente:

Resumen de la tarea 1

Reconoce lo que acaba de suceder... Ejecutaste parte del código que consultó todas las obras de Shakespeare (no una gran cantidad de datos, pero sin dudas más texto que el que puedes escanear con facilidad por tu cuenta para mirar cada palabra en cada juego, administrar un recuento de esas palabras y, luego, ordenarlas en orden descendente). No solo le derivó este trabajo a BigQuery, sino que también pudo usar el servicio integrado en Apps Script para Hojas de cálculo de Google a fin de facilitar su consumo.

El código para bq-sheets-slides.js (el nombre de archivo que elegiste) que pegaste anteriormente (además de PROJECT_ID, que debería tener un ID real del proyecto) también se puede encontrar en la carpeta step1 de este repositorio de GitHub de 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 búsqueda un poco diferente: cuáles son las palabras más populares que usa Shakespeare con 10 caracteres o más. 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. Independientemente de la consulta que use, siempre puede probar la consulta en BigQuery Console antes de ejecutarla en Apps Script. La interfaz de usuario de BigQuery está disponible para desarrolladores en bigquery.cloud.google.com. Por ejemplo, a continuación se muestra cómo se ve nuestra consulta con la IU de BigQuery:

Si bien los pasos anteriores aprovechan el editor de código de Apps Script, también puedes optar por desarrollar de manera local a través de la línea de comandos. Si lo prefieres, crea una secuencia de comandos llamada bq-sheets-slides.js, pega el código anterior en ella y súbela a Google con el comando clasp push. (Si te la perdiste antes, aquí tienes el vínculo a clasp y te indicamos cómo usarla).

El objetivo de runQuery() es hablar 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 Hojas de cálculo para hacerlo.

  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(). Obtiene 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, no los 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. Hoja de cálculo de devolución. Arriba, createColumnChart() necesita el objeto de hoja de cálculo, por lo que debemos actualizar runQuery() para mostrar el objeto spreadsheet a fin de poder pasarlo a createColumnChart(). Después de registrar la creación exitosa de la hoja de cálculo de Google, muestra 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 createBigQueryPresentation() en automóvil Segregar de manera lógica la función de BigQuery y de creación de gráficos 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. Completaste dos pasos importantes: devolver el objeto de la hoja de cálculo y crear una función de conducción. ¿Qué pasa si un colega quisiera reutilizar runQuery() y no quiere registrar la URL? A fin de que runQuery() sea más fácil de leer para el uso general, necesitamos mover esa línea de registro. ¿Cuál es el mejor lugar donde colocarla? Si adivinaste createBigQueryPresentation(), tendrías razón. Después de mover la línea de registro, debería verse de la siguiente manera:
/**
 * 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 (excepto de 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, ve a la parte superior del editor de código y usa createBigQueryPresentation() en lugar de runQuery(). Después de ejecutarla, obtendrá otra hoja de cálculo de Google. Sin embargo, esta vez aparecerá un gráfico en la hoja junto a los datos:

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

  1. Crea una presentación de diapositivas. Todo el trabajo en la presentación de diapositivas se llevará a cabo en createSlidePresentation(), que agregaremos a bq-sheets-slides.js inmediatamente después de createColumnChart(). Comencemos con la creación de una nueva presentación de diapositivas. Luego, agrega un título y un subtítulo a la diapositiva de título predeterminada que obtenemos con todas las presentaciones nuevas.
/**
 * 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. Agrega una tabla de datos. El siguiente paso en createSlidePresentation() es importar los datos de la celda de Hojas de cálculo de Google a nuestra nueva presentación de diapositivas. 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 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 mostrar 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 retorno. Ahora que la función final ya está completa, vuelva a ver su firma. Sí, createSlidePresentation() requiere una hoja de cálculo y un objeto de gráfico. Ya ajustamos runQuery() para mostrar el objeto Spreadsheet, pero ahora debemos realizar un cambio similar a createColumnChart() para mostrar el objeto del gráfico (EmbeddedChart). Para volver, código y agrega una última línea al final de createColumnChart() en tu aplicación:
  // NEW: Return chart object for later use
  return chart;
}
  1. Actualiza createBigQueryPresentation(). Dado que createColumnChart() muestra el gráfico, debemos guardarlo en una variable y, luego, pasar tanto la hoja de cálculo como el gráfico a createSlidePresentation(). Dado que registramos la URL de la hoja de cálculo recién creada, también registramos 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. Vuelve a guardar y ejecuta 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 otorgues este permiso, se ejecutará como antes.
  2. Además del archivo de Hojas de cálculo que creaste, también deberías obtener un nuevo archivo de Presentaciones con 3 diapositivas (título, tabla de datos y gráfico de datos), como se muestra a continuación:

¡Felicitaciones! Ya creaste una aplicación que aprovecha ambos lados de Google Cloud mediante 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 Google que muestra los resultados, así como un gráfico en la hoja de cálculo.

Técnicamente, eso es lo que hizo. En términos generales, pasó de un análisis de macrodatos a algo que puede presentar a las partes interesadas, todo en código, todo automatizado. Esperamos que esta muestra lo inspire a tomar esto y personalizarlo para sus propios proyectos. Al final de este codelab, te brindaremos algunas sugerencias sobre cómo puedes mejorar aún más esta app de muestra.

Con los cambios anteriores de la tarea final (nuevamente, excepto PROJECT_ID), tu bq-sheets-slides.js debería verse de la siguiente manera (y 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());
}

En este codelab, no se tiene ninguna función en el segundo archivo, que es el archivo de manifiesto de Apps Script, appsscript.json. Para acceder a ella, vaya a la pestaña del navegador del editor de código y seleccione Ver > Mostrar archivo de manifiesto en el menú ubicado en 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 de nivel de sistema que usa Apps Script para saber qué entorno de ejecución tiene disponible para su aplicación. Si bien explicar el contenido de un archivo de manifiesto está fuera del alcance de este codelab, puedes tener una idea de lo que hace.

A continuación, encontrarás recursos adicionales que te ayudarán a profundizar en el material que se aborda 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 presentan varios desafíos de código, distintas formas de mejorar o aumentar la muestra que compilamos en este codelab. Esta lista no es exhaustiva, pero debería proporcionar algunas ideas inspiradoras sobre dónde puedes dar el siguiente paso.

  • Aplicación. ¿No quieres estar limitado al usar JavaScript o a las restricciones que impone Apps Script? Transfiere esta aplicación a tu lenguaje de programación favorito que usa las API de REST para Google BigQuery, Hojas de cálculo y Presentaciones.
  • BigQuery. Experimente con una consulta diferente para el conjunto de datos de Shakespeare... tal vez encuentre una consulta que le interese. Puedes encontrar otra consulta de muestra en la app de muestra original de BigQuery de Apps Script.
  • BigQuery. Experimenta con otros conjuntos de datos públicos de BigQuery... y encuentra un conjunto de datos que podría ser más significativo para ti.
  • BigQuery. Anteriormente, mencionamos otras consultas que puede probar con obras de Shakespeare o con otras tablas de datos públicas, y queríamos volver a compartir esta página web y esta.
  • Hojas de cálculo. Experimentar con otros tipos de gráficos
  • Hojas de cálculo y BigQuery Gire las tablas... es posible que tenga un conjunto de datos grande en alguna hoja de cálculo. En 2016, el equipo de BigQuery introdujo la función que permite a los desarrolladores utilizar una hoja de cálculo como fuente de datos (consulte la entrada de blog una y dos para obtener más información).
  • Presentaciones. Agregue otras diapositivas a la presentación generada, como imágenes y otros elementos vinculados a su análisis de macrodatos. Consulta la guía del servicio integrado de Presentaciones para comenzar.
  • G Suite. Buscar uso de otros servicios integrados de Google o de G Suite en Apps Script, es decir, Gmail, Google Drive, Calendario, Documentos, Maps, Analytics, YouTube, etc., y otros servicios avanzados. Consulta la descripción general de referencia de los servicios integrados y avanzados para obtener más información.