Leer una API externa con Apps Script en una planilla

Una de las tareas más importantes, repetitivas y que ante un error pueden tirar abajo todo un proceso es la carga de datos en la planilla. Por lo general debemos exportar un informe, copiar y pegar en la planilla para iniciar el proceso.

La alternativa es usar una API (Application Programming Interface) que permite a los sistemas hablar en directo entre ellos, por lo general en texto plano con el formato JSON como estándar.

Apps Script nos permite leer una API para obtener los datos directamente desde el software que tiene la base de datos evitando el trabajo manual de copiar y pegar desde planillas, verificar formatos de columnas, puntos y comas… entre otros.

Los tres pasos básicos son:

  1. Leer datos desde la API.
  2. Procesar los datos.
  3. Escribir los datos.

function actualizar() {
  
  // Obtenemos datos de la API
  datos = apiGetBasic('posts');

  // Procesamos los datos obtenidos
  datos = procesarDatos(datos);
  
  // Escribimos en la hoja destino
  escribirRango(datos, hojaDatos);  

}

function apiGetBasic(query) {
  var apiURL = 'https://jsonplaceholder.typicode.com/';
  var USERNAME = '';
  var PASSWORD = '';  
  
  // Si la API precisa autenticacion, podemos usar usuario y clave
  var headers = {
    "Authorization" : "Basic " + Utilities.base64Encode(USERNAME + ':' + PASSWORD)
  };    
  var params = {
    "method":"GET",
    "headers":headers
  };
  
  try {
    var response = UrlFetchApp.fetch(apiURL+query, params); 
    var responseData = response.getContentText();
    var json = JSON.parse(responseData);
    return json;
  }
  catch (e) {
    Logger.log(e);
    return ["Error:", e];
  }
}

Auditoría de permisos de archivos en Google Drive

Con cada vez más archivos compartidos en la nube suele haber accesos demasiado permisivos o directamente fallas de seguridad.

Al compartir documentos en Google Drive podemos cometer el error de exponer información sensible a cualquiera que tenga el link o bien a usuarios que ya no deberian tener mas acceso.

Una vez que se acumulan los archivos puede ser imposible revisar uno a uno los archivos, para eso veamos un script de Apps Script para listar los archivos desde un directorio y todos sus subdirectorios para detectar estos casos:

https://github.com/nrodriguezm/nrm-apps-script-demos/blob/permisos-de-archivos-drive/Code.js

// Planilla activa actual
var timezone = Session.getScriptTimeZone();
var ss = SpreadsheetApp.getActiveSpreadsheet();
  
var hojaDatos = 'Archivos';
var sheet = ss.getSheetByName(hojaDatos);

var rootID = "1AwDzuyFf7gfSJWn1EaNM__FgESj9-Bla";

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 Archivos Drive",
        functionName : "actualizar"
    });
    menuEntries.push(null);

    ss.addMenu("Actualizar", menuEntries);

}


function actualizar() {

  // Lee una carpeta Drive 
  var archivos = listarArchivos( DriveApp.getFolderById(rootID) );

  console.log(archivos);

  // Escribe los archivos leidos en Drive en la planilla
  escribirArchivos(archivos);

}

function listarArchivos(folder) { // Modified

  var filesData = [];

  var folderName = folder.getName();
  var files = folder.getFiles();

  while (files.hasNext()) {
    //var fileName = files.next().getName();
    Logger.log(folderName);

    filesData.push( leerArchivo(files.next()) );
  }

  var subfolders = folder.getFolders();
  while (subfolders.hasNext()) {
    listarArchivos(subfolders.next()); // Modified
  }

  return filesData;
}


function leerArchivo(file) {

  console.log( 'leerArchivo: ' + file.getName() );

  try {

    access     = file.getSharingAccess();
    permission = file.getSharingPermission();
    editors    = file.getEditors();
    viewers    = file.getViewers();
    
    edit = [];
    view = [];
    

    date =  Utilities.formatDate(file.getDateCreated(), timezone, "yyyy-MM-dd HH:mm")

    for (var v=0; v<viewers.length; v++) {
      view.push(viewers[v].getName() + " " + viewers[v].getEmail());
    }

    for (var ed=0; ed<editors.length; ed++) {
      edit.push(editors[ed].getName() + " " + editors[ed].getEmail());
    }

    switch(access) {
      case DriveApp.Access.PRIVATE:
        privacy = "Private";
        break;
      case DriveApp.Access.ANYONE:
        privacy = "Anyone";
        break;
      case DriveApp.Access.ANYONE_WITH_LINK:
        privacy = "Anyone with a link";
        break;
      case DriveApp.Access.DOMAIN:
        privacy = "Anyone inside domain";
        break;
      case DriveApp.Access.DOMAIN_WITH_LINK:
        privacy = "Anyone inside domain who has the link";
        break;
      default:
        privacy = "Unknown";
    }
    
    switch(permission) {
      case DriveApp.Permission.COMMENT:
        permission = "can comment";
        break;
      case DriveApp.Permission.VIEW:
        permission = "can view";
        break;
      case DriveApp.Permission.EDIT:
        permission = "can edit";
        break;
      default:
        permission = "";
    }
  
    edit = edit.join(", ");
    view = view.join(", ");

    user = file.getOwner().getName();
    users_editors = (edit === "" ? "" : edit);
    users_viewers = (view === "" ? "" : view);

  } catch (e) { Logger.log(e.toString()); Logger.log(file.getName()); };

  return [
    file.getId(),
    file.getName(),
    file.getUrl(),
    privacy, 
    user,
    users_editors, 
    users_viewers, 
    date,
    file.getSize(),
    file.getDescription(),
    file.getMimeType(),
  ];
  
}


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

  if (numRows > 0) {
    var numCols = data[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(data);
  }

}

Ingreso de datos por parte del usuario en AppsScript

Al momento de ejecutar un script en nuestra planilla, suele ser útil contar con datos ingresados por el usuario los cuales modifican el comportamiento de nuestro script. Para esto podemos mostrar un cuadro de diálogo que permita al usuario ingresar datos.

Utilizando la clase Ui de la planilla mostraremos un cuadro de diálogo donde el usuario podrá ingresar datos y luego la ejecución de nuestro script cambiará de acuerdo a lo ingresado.

// Planilla activa actual
const 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 : "Enviar mensaje",
        functionName : "enviar"
    });
    menuEntries.push(null);

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

function enviar() {
  var ui = SpreadsheetApp.getUi(); 

  var result = ui.prompt(
     'Cual es tu nombre?',
     'Ingresa los datos:',
      ui.ButtonSet.OK_CANCEL);

  // Procesar la respuesta del usuario
  var button = result.getSelectedButton();
  var nombre = result.getResponseText();

  if (button == ui.Button.OK) {

    console.log('OK');
    notificar(nombre);

  } else if (button == ui.Button.CANCEL) {

    console.log('CANCEL');

  } else if (button == ui.Button.CLOSE) {
    
    
    console.log('CLOSE');

  }
  
}

function notificar(nombre = '') {

  console.log('Estamos en notificar, console log: ' + nombre);

  if (nombre.length > 0)
    ss.toast('Estamos en notificar', 'Tu nombre es: ' + nombre, 3);
  else
    ss.toast('Estamos en notificar', 'Otro dia nos conoceremos :) ', 3);

}

Notificaciones al usuario con cuadros de diálogo

Antes de ejecutar código mediante Apps Script suele ser una buena idea obtener la confirmación por parte del usuario de que quiere realizar la acción. Por ejemplo si vamos a enviar un email con un informe podemos mostrar los destinatarios del correo antes de realizar el envío.

Para esto podemos utilizar cuadros de diálogo con botones de confirmación o cancelación. Para esto utilizaremos la clase Ui de la planilla lo cual nos da acceso a una serie de diálogos estándar. Por ejemplo el diálogo “Alert” nos permite mostrar un mensaje que pide confirmación o cancelación al usuario y obtendremos la respuesta en el código.

function enviar() {
  var ui = SpreadsheetApp.getUi(); 

  var result = ui.alert(
     'Confirmar',
     'Esta seguro de continuar?',
      ui.ButtonSet.YES_NO);

  // Process the user's response.
  if (result == ui.Button.YES) {

    // User clicked "Yes".
    ui.alert('El usuario acepto.');

    notificar();    

  } else {
    // User clicked "No" or X in the title bar.
    ui.alert('El usuario cancelo.');
  }
}

function notificar() {

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

}

Datos combinados de varias fuentes de datos en Data Studio

Por lo general utilizamos una fuente de datos conectada a los gráficos de un informe. Sin embargo en algunas ocasiones podemos precisar graficar datos de dos planillas distintas pero que están unidas por alguna columna en común.

Repasemos los datos de la planilla de casos por semana de cada país. Como podríamos incorporar datos relevantes al informe, como puede ser el promedio de casos de todos los países para cada semana.

En estos casos lo más probable es contar con otra hoja o planilla de datos donde tendremos los promedios semanales. La columna “semana” sería la columna en común entre las dos fuentes de datos. Podemos hacer un cálculo rápido con un listado de las semanas del periodo y luego aplicando un promedio al Filtro de datos de la semana.

Agregamos la fuente de datos de promedios globales por semana.
Recurso > Gestionar las fuentes de datos añadidas

Buscamos la planilla y seleccionamos la hoja de promedios.

Luego debemos unir las dos fuentes de datos por su columna en común, en este caso la columna de semana. Para esto accedemos a datos combinados en:

Recurso > Gestionar datos combinados.

Para crear la combinación de datos agregamos la nueva fuente y seleccionamos la semana como Tecla de unión o Key, esta es la columna que tienen en común los dos informes. Luego en Dimensiones agregamos las columnas de datos relevantes. En este caso sería Promedio.

Adjuntar planilla de Google como Excel XLSX en un correo con Apps Script

En la medida que generamos informes automatizados puede ser buena idea contar con copias diarias de los mismos que lleguen a las partes interesadas. Por ejemplo podemos enviar correos con copias adjuntas de planillas en la nube de forma de contar con una copia o foto del informe en una fecha. También puede ser útil para compartir la información de la planilla pero sin necesidad de otorgar acceso a la misma a terceros.

Utilizaremos la función de exportar el documento a Excel XLSX desde Apps Script, lo cual descarga la planilla como un archivo excel que adjuntamos en un correo electrónico.

Para lograr esto desde Apps Script utilizaremos la funcion UtlfetchApp.

function enviarCorreo() {

    var token = ScriptApp.getOAuthToken(); 
    var nombrexlsx = "Informe adjunto.xlsx";

    var AdjuntoExcel = UrlFetchApp.fetch(
        'https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=' + ssID + '&exportFormat=xlsx',
        { 
          headers : {Authorization : 'Bearer '+ token}
        }
      )
      .getBlob()
      .setName(nombrexlsx);
                                        
    var OpcionesAvanzadas = { name: "Informe Adjunto", htmlBody: emailText , attachments: [AdjuntoExcel]};
    
    // Confirmamos envio con usuario
    var ui = SpreadsheetApp.getUi(); 

    var result = ui.alert(
      'Confirme envio de reporte',
      'Destinatarios: ' + emailTo,
      ui.ButtonSet.YES_NO
    );

    if (result == ui.Button.YES) {
    
      GmailApp.sendEmail(emailTo, emailSubject, emailSubject, OpcionesAvanzadas);      
      SpreadsheetApp.getActiveSpreadsheet().toast("Se envio el correo",'', 10);
    
    }

}

Escribir datos en Firestore mediante functions y requests externos

En este video analizaremos la generación de datos y su almacenamiento en Firestore, mediante la utilización de un proyecto Firebase y Cloud Functions. Firestore es la base de datos no relacional de Firebase, Cloud Functions es un servicio de funciones a demanda serverless, es decir que no requiere un alojamiento dedicado.

El primer paso sera crear un proyecto en Firebase. Hecho esto debemos activar Firestore. Luego al habilitar cloud functions se nos pedirá una cuenta de facturación para utilizar cloud functions. Es posible usar en forma limitada las funciones sin cuenta de facturación pero no podremos llamar APIs externos desde cloud functions si no lo hacemos. 

Luego de pasar el proyecto Firebase del plan Spark básico a un plan de pago por uso, deberemos instalar las librerías locales de node para desarrollar las funciones en nuestra pc local.

Utilizaremos los pasos de la guía básica de referencia de cloud functions disponible en la ayuda de google y añadiremos algunos pasos adicionales para guardar datos en Firestore.

https://firebase.google.com/docs/functions/get-started
https://nodejs.org/

npm install -g firebase-tools

Debemos iniciar sesión en la línea de comandos usando las herramientas de firebase tools recién instaladas. Para hacerlo nos dirigimos a la línea de comandos y ejecutamos el comando firebase login.

Es recomendable iniciar el proyecto de cloud functions en un directorio de código y a su vez en un directorio separado por proyecto. También inicializar un repositorio git para dar seguimiento a los cambios del código.

Podemos copiar el código de ejemplo del link al blog en los comentarios o en el repositorio de referencia:

https://github.com/nrodriguezm/flujo-de-datos-firestore-apps-script-data-studio/commits/master

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, ";");