Leer datos de Firestore a una planilla de Google

Al ser Firestore una base de datos no relacional puede ser un poco engorroso navegar los datos dentro de la consola de Firebase.

Una buena forma de visualizar, filtrar y validar los datos que ingresan a Firestore es con una simple planilla donde podemos aplicar fórmulas en un formato conocido.

Para lograr esto en una planilla de calculo de Google, vamos a utilizar la siguiente libreria: FirestoreGoogleAppsScript
https://github.com/grahamearley/FirestoreGoogleAppsScript

Codigo de referencia del video, verificar que contamos con una hoja llamada «Registros» donde se escribiran los datos:


const hojaRegistros = 'Registros';

function onOpen() {
    // La función onOpen se ejecuta automáticamente cada vez que se carga un Libro de cálculo
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [];
 
    menuEntries.push({
        name : "Leer Datos",
        functionName : "update"
    });
  
    menuEntries.push(null);
  
    ss.addMenu("Actualizar Datos", menuEntries);
}

function update() {  
  getFireStore();

}

function writeInSpreadSheet(data, current_sheet) {
  var numRows = data.length;
  if (numRows > 0) {
    var numCols = data[0].length;
	
	  var Avals = current_sheet.getRange("B1:B").getValues();
	  var last_row = Avals.filter(String).length;
	  last_row++;
    current_sheet.getRange(last_row, 1, numRows, numCols).setValues(data);
  }
}


function getFireStore() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName(hojaRegistros);
 const allDocuments = firestore.getDocuments("registros");

 var data = [];

 // for each column and row in the document selected
 for(var i = 0; i < allDocuments.length; i++){

  var document_key = allDocuments[i].name.split("/").pop();
  var nombre = allDocuments[i].fields["nombre"].stringValue;
  //var agregado = new Date(allDocuments[i].fields["agregado"].timestampValue).toISOString();

  data.push([
    document_key,
    nombre,
    //agregado,
  ]);

 }

 if (data.length > 0) {  
  // write to ss    
  writeInSpreadSheet(data, sheet);
 }

}

Por ultimo debemos agregar un archivo adicional con el contenido de la cuenta de servicio, para luego inicializar la libreria en una variable firebase:

var config = {

  "project_id": "ejemplo-escribir-leer-drive",
  "private_key_id": "0451f9......891",
  "private_key": "-----BEGIN PRIVATE KEY-----\n....",
  "client_email": "flujos-de-da......ount.com",
  "client_id": "102555....",

 };

 var firestore = FirestoreApp.getFirestore(config.client_email, config.private_key, config.project_id);

Transformar un rango de celdas a tabla HTML

Cuando queremos enviar un rango de celdas por email suele ser practico convertir las celdas a una tabla HTML para mejor visualizacion.

El siguiente codigo Apps Script tiene una funcion para realizar la conversion:


/**
 * Return a string containing an HTML table representation
 * of the given range, preserving style settings.
 */
function getHtmlTable(range){
  var ss = range.getSheet().getParent();
  var sheet = range.getSheet();
  startRow = range.getRow();
  startCol = range.getColumn();
  lastRow = range.getLastRow();
  lastCol = range.getLastColumn();

  // Read table contents
  var data = range.getValues().filter(noVacio);

  // Get css style attributes from range
  var fontColors = range.getFontColors();
  var backgrounds = range.getBackgrounds();
  var fontFamilies = range.getFontFamilies();
  var fontSizes = range.getFontSizes();
  var fontLines = range.getFontLines();
  var fontWeights = range.getFontWeights();
  var horizontalAlignments = range.getHorizontalAlignments();
  var verticalAlignments = range.getVerticalAlignments();

  // Get column widths in pixels
  var colWidths = [];
  for (var col=startCol; col<=lastCol; col++) { 
    colWidths.push(sheet.getColumnWidth(col));
  }
  // Get Row heights in pixels
  var rowHeights = [];
  for (var row=startRow; row<=lastRow; row++) { 
    rowHeights.push(sheet.getRowHeight(row));
  }

  // Future consideration...
  var numberFormats = range.getNumberFormats();

  // Build HTML Table, with inline styling for each cell
  var tableFormat = 'style="border:1px solid black;border-collapse:collapse;text-align:center" border = 1 cellpadding = 5';
  var html = ['<table '+tableFormat+'>'];
  // Column widths appear outside of table rows
  for (col=0;col<colWidths.length;col++) {
    html.push('<col width="'+colWidths[col]+'">')
  }
  // Populate rows
  for (row=0;row<data.length;row++) {
    html.push('<tr height="'+rowHeights[row]+'">');
    for (col=0;col<data[row].length;col++) {
      // Get formatted data
      var cellText = data[row][col];
      if (cellText instanceof Date) {
        cellText = Utilities.formatDate(
                     cellText,
                     'Montevideo', 
                     'dd/MM');
      }
      var style = 'style="'
                + 'color: ' + fontColors[row][col]+'; '
                + 'font-family: Arial, sans-serif; '
                + 'font-size: 14; '
                + 'font-weight: ' + fontWeights[row][col]+'; '
                + 'background-color: ' + backgrounds[row][col]+'; '
                + 'text-align: ' + horizontalAlignments[row][col]+'; '
                + 'vertical-align: ' + verticalAlignments[row][col]+'; '
                +'"';
      html.push('<td ' + style + '>'
                +cellText
                +'</td>');
    }
    html.push('</tr>');
  }
  html.push('</table>');

  return html.join('');
}

function noVacio(arr) {
  return arr[0].toString().toLowerCase() != "";
};

Leer archivos en directorio Drive con Apps Script

Una opción para integrar sistemas externos y leer los datos en planillas es mediante la suba de archivos a Google Drive. Si exportamos datos en CSV por ejemplo, se suben a una carpeta en Drive y luego podemos leer los archivos, podremos integrar distintos sistemas sin hacer cambios en los mismos. Similar a la lectura de mails pero con archivos.

Veamos un ejemplo de cómo leer una carpeta en Drive, listar los archivos y obtener datos de los mismos.

function leerDrive() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var data = [];
  
  var folder = DriveApp.getFolderById(folderID);//not placing actual id for privacy
  //var contents = folder.getFiles();
  var contents = folder.getFilesByType(MimeType.CSV);

  var fileID, file; 

  // Cada hoja de insumo, empresa mes, acumulamos data
  while(contents.hasNext()) {   

    file = contents.next();    
    fileID = file.getId();
    sheetName = file.getName();
    
    var temp = [
      fileID,
      sheetName,
    ];

    data.push(temp);
    
    var csvValues = Utilities.parseCsv(file.getBlob().getDataAsString(), ";");
    console.log(csvValues);

    Logger.log(data);

  }

  return data;
  
}

Actualizacion ante error de lectura de archivos subidos por Windows:

En algunas ocasiones se reporta un error de lectura de los contenidos de los archivos CSV subidos a Drive y que Apps Script no puede leer. Para corregir este error podemos eliminar caracteresa especiales antes del comando parseCsv:

// Pre procesamos el string del archivo manteniendo solo caracteres que nos interesan
// Eliminamos caracteres raros unicode
var csv = file.getBlob().getDataAsString('ISO-8859-1');
csv = csv.replace(/[^a-zA-Z0-9\-\.\,\;\nÃÀÁÄÂÈÉËÊÌÍÏÎÒÓÖÔÙÚÜÛãàáäâèéëêìíïîòóöôùúüûÑñ ]/g, "");

var values = Utilities.parseCsv(csv, ";");

Copiar rangos de celdas entre planillas de Google

Una de las fórmulas más famosas de las planillas de Google es IMPORTRANGE, la cual permite conectar rangos de celdas entre planillas sin esfuerzo. Pero esta fórmula tiene dos limitaciones:

La planilla que importa datos no puede evitar actualizarse cuando la planilla de origen es actualizada.
La fórmula IMPORTRANGE tiene un límite de celdas y no es muy rápida en su ejecución.

Para estos casos podemos utilizar un Apps Script que copie hojas de una planilla a otra a demanda, de esta forma el usuario tiene control de la actualización de datos.

// Hoja de los Drive fuente donde se consolida el informe
var linkPlanillaOrigenDatos = "https://docs.google.com/spreadsheets/d/1if4jdub72bZnr4TyiTuSKorFj35ifBvonSkNsU0CDro/edit#gid=0";
var hojaFuente = "BD";
var hojaDestino = "BD";

function onOpen() {
    // La función onOpen se ejecuta automáticamente cada vez que se carga un Libro de cálculo
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [];
 
    menuEntries.push({
        name : "Copiar Datos",
        functionName : "copiarDatos"
    });
    menuEntries.push(null);

    ss.addMenu("Actualizar", menuEntries);
}

function copiarDatos() {  
  
  // Mensaje al usuario
  SpreadsheetApp.getActiveSpreadsheet().toast('Copiando datos '+hojaDestino+'...', 'Estado');

  // Limpieza en hoja destino
  var destSS = SpreadsheetApp.getActiveSpreadsheet();
  var destSheet = destSS.getSheetByName(hojaDestino); // la planilla actual, hoja destino de los datos
  var range = destSheet.getRange("A2:U");
  range.clear();
  
  // Buscamos hoja preprocesada
  var sourceSS = SpreadsheetApp.openByUrl(linkPlanillaOrigenDatos);
  var sourceSheet = sourceSS.getSheetByName(hojaFuente); // todas las hojas de fuente, vamos a buscar la que ya esta preprocesada
  
  Logger.log("Entramos en la hoja: "+hojaFuente);

  // Datos de origen  
  var sourcerange = sourceSheet.getRange(2,1,sourceSheet.getLastRow()-1,20);
  var sourcevalues = sourcerange.getValues();

  // Datos, saltamos columna de titulo
  var iniRow = 2;
  var destRange = destSheet.getRange(iniRow,1,sourcevalues.length,sourcevalues[0].length);
  destRange.setValues(sourcevalues); 
  
  // Podemos agregar formulas adicionales a los datos importados
  var destRange = destSheet.getRange(iniRow,22,sourcevalues.length,1);
  destRange.setValue("=M2*Datos!$B$1"); 
  
  // Mensaje al usuario
  SpreadsheetApp.getActiveSpreadsheet().toast('Copiado finalizado de '+hojaDestino+' finalizado. Se actualizarán formulas.', 'Estado', 2);

}

Leer emails de Gmail y guardarlos en una planilla de Google

Hagamos una planilla que lea Gmail y acumule los correos que nos interesan sin repetirlos.

Uno de los típicos problemas a la hora de automatizar es como obtener los datos desde otro sistema. Para estos casos el email suele ser un aliado ya que la mayoría de los sistemas permiten enviar correos con información. Así podemos usar el email o Gmail en este caso como bandeja de entrada de la planilla de datos.

Haremos que cada correo que llega con determinadas palabras o remitentes sea introducido en la planilla con lo cual podemos iniciar un flujo de trabajo.

Para que nuestra planilla pueda acumular los correos debemos ejecutar la lectura en forma repetitiva, por lo que tenemos que pensar en un loop que se repita y guarde solo correos nuevos.

Buscar emails que contengan una palabra
Recorrer los hilos y cada email dentro del mismo
Guardar datos en planilla

Buscar emails que contengan una palabra
Recorrer los hilos y cada email dentro del mismo
Guardar datos en planilla (solo los nuevos!)
….
Esto en definitiva es un algoritmo.

// Planilla activa actual
var ss = SpreadsheetApp.getActiveSpreadsheet();
  
var hojaDatos = 'Emails';
var sheet = ss.getSheetByName(hojaDatos);


// Preparamos menu para que el usuario pueda actualizar desde la planilla sin entrar al editor de codigo
function onOpen() {

    // La función onOpen se ejecuta automáticamente cada vez que se carga un Libro de cálculo
    var menuEntries = [];
 
    menuEntries.push({
        name : "Leer Gmail",
        functionName : "leerGmail"
    });
    menuEntries.push(null);

    ss.addMenu("Actualizar", menuEntries);
}


function leerGmail() {

  // Lee de Gmail con un parametro de busqueda
  var emails = leerMails("G Suite");

  // Retorna solo emails nuevos que no estan presentes en la planilla comparando ID
  var emailsNuevos = reducirEmails(emails);

  // Escribe los emails nuevos a la planilla
  escribirEmails(emailsNuevos);

}


function leerMails(searchTerm = 'G Suite') {
  
  var threads = GmailApp.search(searchTerm,0,20);

  var mailsData = [];
    
  // Conversaciones o hilos (threads) que contienen muchos emails
  for (var i = 0; i < threads.length; i++) {
    Logger.log("Each Thread: "+threads[i].getFirstMessageSubject());
    
    var thread = threads[i];
    var messages = GmailApp.getMessagesForThread(thread);

    // Mensajes dentro de conversaciones
    for (var j = 0 ; j < messages.length; j++) {

      mailsData.push(messages[j]);      
      
    }
  }
   
  return mailsData;
}


// Los emails ya presentes en la planilla los descartamos
function reducirEmails(messages) {

  var emailsLimpios = [];
  var arrIdsMails = [];
  
  // IDs ya existentes  
  var arrIdsCells = [];
  
  // Columna A donde tenemos los IDs de los emails
  var dataRange = sheet.getRange(2,1,sheet.getMaxRows()-1,sheet.getMaxColumns());
  
  // Obtenemos los valores de la columna A, o sea un arreglo de IDs
  var dataCurrent = dataRange.getValues();
  
  // Guardamos un arreglo son los IDs que usaremos para comparar con IDs de emails nuevos
  for (var i = 0; i < dataCurrent.length; ++i) {
      
    var row = dataCurrent[i];
    
    // celdas actuales
    var valId = row[0];

    // si encontramos
    arrIdsCells.push(valId);

  }

  // Recorremos cada mensaje
  for (var j = 0 ; j < messages.length; j++) {

    var messageId = messages[j].getId();
    var messageDate = messages[j].getDate();
    var messageTitle = messages[j].getSubject();
    var messageBody = messages[j].getBody();       
    
    // Si no es de los ya existentes
    if (arrIdsCells.indexOf(messageId) == -1 && messageDate != undefined && messageDate != "") {
    
      Logger.log("Insertamos email: " + messageId);
      
      // Guardamos datos de cada email nuevo
      emailsLimpios.push([
        messageId,
        messageDate,
        messageTitle,           
      ]);
        
      // Agregarmos 
      arrIdsMails.push(messageId);
      
    } else {
      Logger.log("Ya existe: " + messageId);
    }

  }

  return emailsLimpios;

}


// Recorrer e insertar en planilla
function escribirEmails(mailsData) {
    
  // calculate the number of rows and columns needed
  var numRows = mailsData.length;

  if (numRows > 0) {
    var numCols = mailsData[0].length;
                      
    // Escribir en filas nuevas antes de la fila 2 (para mantener formato)
    sheet.insertRowsBefore(2, numRows);
    sheet.getRange(2, 1, numRows, numCols).setValues(mailsData);
  }

}

  

Apps Script para enviar rangos de celdas por Email como tablas

Dentro de los automatismos de las planillas el lograr que los informes se completen solos es el primer paso, el segundo es lograr que los informes se envían solos. Para esto crearemos un script que envíe rangos de celdas por email a pedido del usuario. 

Los rangos de celdas pueden ser definidos con coordenadas o bien con nombres de forma que si se agregan filas o columnas la referencia al rango sigue funcionando.

var emailRecipient = '[email protected]';
var emailTitle = 'Rango de celdas como tabla';

// Rango de datos que sera copiado como tabla
var hojaInforme = 'Informe';
var rangosDatos = {
  0 : 'RangoNombre1',
  1 : 'RangoNombre2',
};

// Preparamos menu para que el usuario pueda actualizar desde la planilla sin entrar al editor de codigo
function onOpen() {
    // La función onOpen se ejecuta automáticamente cada vez que se carga un Libro de cálculo
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [];
 
    menuEntries.push({
        name : "Enviar Email",
        functionName : "enviarEmail"
    });
    menuEntries.push(null);

    ss.addMenu("Actualizar", menuEntries);
}


function enviarEmail() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActive().getSheetByName(hojaInforme);
  
  var name = SpreadsheetApp.getActiveSpreadsheet().getName();
  var subject = emailTitle + ' ';
  subject +=  new Date().toISOString().split('T')[0];
  
  var body = '<html><body><div style="text-align:center;display: inline-block;font-family: arial,sans,sans-serif">'
 
  var iLoop = 1;
  for (var key in rangosDatos) {  // OK in V8
    var value = rangosDatos[key];
    Logger.log("value = %s", value);
    
    body += '<H2>Titulo ' + iLoop + '</H2>';
  
    var rango1 = sheet.getRange(value);
    body += getHtmlTable(rango1);
   
    iLoop++;
  }

  body += '</div></body></html>';

  GmailApp.sendEmail(emailRecipient, subject, "Requires HTML", {htmlBody:body})
}

Para ver el codigo de conversion de rango de celdas a tabla HTML, la funcion getHtmlTable, pueden hacerlo en la siguiente entrada:

Apps Script para crear un menu que ejecute codigo en una hoja de calculo de Google

Veamos un ejemplo de menú para ejecutar apps script desde la planilla. El menú en la planilla nos permite ejecutar el código Apps Script desde la planilla sin tener que acceder al editor, de forma que otros usuarios pueden usar el código directamente desde la hoja de cálculo.

// Planilla activa actual
var ss = SpreadsheetApp.getActiveSpreadsheet();

// Preparamos menu para que el usuario pueda actualizar desde la planilla sin entrar al editor de codigo
function onOpen() {

    // La función onOpen se ejecuta automáticamente cada vez que se carga un Libro de cálculo
    var menuEntries = [];
 
    menuEntries.push({
        name : "Notificar",
        functionName : "notificar"
    });
    menuEntries.push(null);

    ss.addMenu("Actualizar", menuEntries);
}

function notificar() {

  console.log('Estamos en notificar, console log.');
  ss.toast('Estamos en notificar', 'Apps Script', 3);
}

Métricas calculadas y títulos de gráficos en DataStudio

Las métricas calculadas en los gráficos nos permite desplegar campos con operaciones realizadas, por ejemplo máximos, mínimos, conteos, promedios. Esto se realiza sin generar un campo calculado, se realiza directamente en el gráfico para darle formato al mismo.

Veamos un ejemplo donde queremos realizar un conteo de elementos distintos dentro de un grupo, por ejemplo cantidad de países en cada continente. 

Dimension vs Métrica

Debemos tener en cuenta que los campos que agrupan los datos van en Dimensión, mientras que los campos que realizan operaciones se indican en Métrica. Los campos en Dimensión no tienen opción de realizar operaciones, mientras que los de Métrica si.

Conteo y Conteo Diferenciado

Si queremos contar los países en cada continente debemos agregar un campo en Métrica, luego agregar la operación de Conteo. Si utilizamos Conteo simple obtendremos el total de filas de nuestra fuente de datos que tienen nombres de países, por lo tanto debemos usar recuento diferenciado que nos trae la cantidad de países distintos agrupados por cada continente. Si un mismo país se repite en el mismo continente no queremos contarlo dos veces.

Titulos de campos en graficos

Al editar los campos en Métrica podemos asignar un nombre a los mismos si el nombre del campo no nos satisface. Clic en el lápiz, editar y veremos que el nombre cambia en la tabla.

Filtros en graficos de DataStudio

Los filtros en gráficos nos permiten limitar los datos de la fuente sin tener que modificar la misma.

Podemos usar cualquier tipo de columna de la fuente de datos para filtrar, las columnas provienen de la fuente y columnas calculadas con fórmulas. De esta forma podemos hacer gráficos sin tener que generar distintas fuentes de datos.

Vemos un ejemplo con nuestra base de datos COVID 19.

Campos calculados con formulas avanzadas en DataStudio

Modificar fuentes de datos agregando campos calculados con ejemplos de fórmulas que usen IF, CASE y otras funciones para combinar columnas creando nuevas.

Al realizar el agregado de columnas en Data Studio no afectamos nuestra fuente de datos lo que es bueno para evitar tener que cambiar la estructura de la misma, podemos no tener acceso a cambios o podría afectar a otras personas que usen la misma fuente de datos.