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];
  }
}

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

Restringir ejecución de apps script por usuario o dominio

A medida que se desarrollan herramientas y automatizaciones en la organización es posible que se maneje información sensible que solo deba ser ejecutado por ciertos usuarios o areas de la empresa.

En estos casos podemos limitar la ejecución de apps script mediante el archivo manifest.json definiendo la configuración y acceso del script.

Según la documentación del manifest debemos agregar en el parametro webapp la propiedad access restringiendo por ejemplo al dominio, asi si corremos los scripts con un usuario @empresa podemos evitar que usuarios externos a la organización utilicen los mismos.

En el editor de script.google.com, abrimos el menu Ver para mostrar el archivo de manifiesto:

Abrimos el archivo appscript.json y debemos agregar dentro de webapp lo siguiente:

«webapp»: {
«access»: «DOMAIN»,
«executeAs»: «USER_DEPLOYING»
},

Quedando de la siguiente forma:

El parametro executeAs nos permite definir a nombre de que usuario se ejecuta el script, puede ser a nombre del desarrollador que hace el deploy o bien a nombre del usuario que pide la ejecución.

Leer mails de Gmail e insertar en una planilla de Drive

El correo suele ser la forma típica de comunicación para eventos de facturación, impuestos, relaciones oficiales y otras que suelen perderse en bandejas de entrada son cientos de correos sin leer.

Una forma práctica de procesar cadenas de mail que no se deben perder es procesar todos los mensajes de una casilla y generar una planilla donde dar seguimiento a todos los correos sin excepción.

Vamos a utilizar la API de Gmail para leer los mensajes en App Scripts via GmailApp:
https://developers.google.com/apps-script/reference/gmail/gmail-message

El primer paso es realizar una busqueda para obtener las conversaciones pertinentes, luego recorrer los hilos y dentro de los hilos los mensajes:

var searchTerm = «Google Cloud»;
var threads = GmailApp.search(searchTerm,0,20);
//Logger.log(«Search term: «+searchTerm);

var mailsData = [];
var arrIdsMails = [];

// Conversaciones
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++) {
//Logger.log(«Each Message: «+threads[i].getFirstMessageSubject());

var messageId = messages[j].getId();
var messageDate = messages[j].getDate();
var messageTitle = messages[j].getSubject();

// Se puede obtener el mensaje completo
var messageBody = messages[j].getBody();
….

Es importante guardar el ID de cada mensaje para luego al escribir las celdas comparar si el ID ya está insertado. Si no controlamos esto vamos a volver a insertar los mismos mensajes cada vez.
Para procesar el cuerpo del mensaje podemos buscar textos o bien expresiones regulares. El cuerpo del mensaje puede ser excesivamente grande especialmente si se trata de un HTML con estilos.
El código completo se puede ver en el repositorio en Github:

Añadir un indicador de avance de ejecución en App Scripts

Al ejecutar funciones en App Scripts es posible que la ejecución tome cierto tiempo, para lo cual es útil mostrar al usuario algún mensaje de avance para evitar que quiera trabajar sobre datos incompletos.

Para esto podemos usar los toast de app scripts que indican al usuario el estado del script:

// Mensaje de esto para el usuario
SpreadsheetApp.getActiveSpreadsheet().toast(‘Finalizamos!’, ‘Estado’, 2);

Para ver la demo completa ver la siguiente planilla:

Demo1 nrm.uy – Leer API
https://docs.google.com/spreadsheets/d/1ap2IQeQ73049NE6lj_oRDBFQ-Wjz0GDiZoXPkFf-a4I/

Código en Github del proyecto:
https://github.com/nrodriguezm/app-scripts-demo/blob/master/demo1-api-menu-toast/Code.js

Crear un menu personalizado en planilla de Google Drive

Continuando con el ejemplo de como consumir una API externa usando app scripts en una planilla de Google Drive, vamos a agregar un menú personalizado que nos permita ejecutar el script desde la planilla para que el usuario no tenga que acceder al código del proyecto.

Para eso vamos a utilizar la función onOpen de las planillas que se dispara cuando el usuario abre la misma, luego vamos a insertar una entrada de menú:

// 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 : «Actualizar Datos»,
functionName : «getDatosAPI»
});
menuEntries.push(null);

ss.addMenu(«Actualizar», menuEntries);
}

Con este código logramos un menú como el siguiente:

Cuando el usuario hace clic en el menú personalizado de «Actualizar Datos», se dispara la función de App Scripts «getDatosAPI».

Para ver la demo completa ver la siguiente planilla:

Demo1 nrm.uy – Leer API
https://docs.google.com/spreadsheets/d/1ap2IQeQ73049NE6lj_oRDBFQ-Wjz0GDiZoXPkFf-a4I/

Código en Github del proyecto:
https://github.com/nrodriguezm/app-scripts-demo/blob/master/demo1-api-menu-toast/Code.js

 

Leyendo datos desde una API en planillas de Google

Cuando trabajamos con planillas en la nube tenemos la posibilidad de leer datos desde nuestro sistema de gestión para tener la planilla sincronizada a la realidad de la operación.

Por ejemplo podemos traer la facturación, compras o costos de RRHH de nuestra empresa analizando los números en tiempo real, día a día sin esperar reportes manuales que pueden y suelen tener errores (olvidos, cambios en el formato, etc.).

Para lograr esto podemos utilizar App Scripts en las planillas de Google para leer una API. En el menú superior: Herramientas > Editor de secuencia de comandos

Vamos a definir una función auxiliar para llamadas a APIs externas que responden con JSON:

// Llamada a API externa
function getApiData(url,query) {

try {
var response = UrlFetchApp.fetch(url);
var responseData = response.getContentText();
var json = JSON.parse(responseData);
return json;
}
catch (e) {
Logger.log(e);
return [«Error:», e];
}
}

El código quedará mas o menos así:

Con esto tenemos una función auxiliar para llamadas a APIs externas que responden con formato JSON.

Para ver la demo completa ver la siguiente planilla:

Demo1 nrm.uy – Leer API
https://docs.google.com/spreadsheets/d/1ap2IQeQ73049NE6lj_oRDBFQ-Wjz0GDiZoXPkFf-a4I/

Código en Github del proyecto:
https://github.com/nrodriguezm/app-scripts-demo/blob/master/demo1-api-menu-toast/Code.js

 

 

 

Comercio, apertura global y productividad

El siguiente podcast es una interesante discusión acerca de qué impactos tiene sobre las sociedades que se abren al comercio.

Se discute el ejemplo de la apertura China a finales de los 90 con la explosión comercial desde y hacia los Estados Unidos, el aumento nñinimo de la productividad de la fuerza laboral norteamericana y los incentivos que genera en la innovación.

Relacionado al tema de por qué la antigua Roma no desarrollo una revolución industrial, cuando sus indicadores económicos en general eran comparables a los siglos 16 y 17 de Europa, se plantea que la entrada de China al mercado mundial generó una enorme presión por migrar la producción hacia una fuerza laboral barata e ilimitada en lugar de continuar el desarrollo de la productividad.

Llegando a finales de la segunda década del siglo XXI, la sociedad China ha elevado sus estándares de vida a niveles comparables a Occidente por lo que el incentivo de la fuerza laboral gratuita e ilimitada dejan de ser válidos y se debería volver a trabajar sobre la productividad.

A su vez se plantea que el modelo de comercio aperturista de los 80 y 90 provocó un error en las consecuencias supuestas por la apertura comercial de los años 2000, ya que la entrada de un pais pobre como China y de una escala mucho mayor al principal mercado (USA) no tenia precedentes.

a16z Podcast: Adjusting to Trade… and Innovation

 

 

¿Por qué no hubo una revolucion industrial en la antigua Roma?

Una de las grandes interrogantes en la historia económica es que tan cerca de tener una revolución industrial estuvieron los romanos.

Se sabe que entre los años 100 AC y 400 BC la antigua Roma contaba con las bases para dar los primeros pasos de una revolución industrial, con el motor a vapor entre los escritos de varios inventores romanos, al menos en modelos a pequeña escala en el siglo I.

Es cierto que otras tecnologías necesarias para poder producir motores industriales no parecen haber estado disponibles en la época por lo que la aplicación industrial de los mismos es cuestionable.

De toda formas, ¿pudo haber sido la disponibilidad de mano de obra a costo cero e ilimitada mediante la expansión del imperio y la esclavitud la razón por la que medios de producción industriales no fueron explorados? Hay que tener en cuenta que una cantidad importante de indicadores económicos y de estándares de vida durante el Imperio Romano no se volverían a ver hasta mil años después de su caída.

Hoy que vivimos en una época donde es normal pensar que vamos a tener mejor tecnología que nuestros padres no concebimos sociedades que declinen durante siglos. Esto también es una de los puntos para entender el rol de la iglesia luego de la caída del orden romano.

Estos son algunos de los puntos analizados por el siguiente artículo de Mark Komaya:

https://fee.org/articles/why-rome-declined-and-modern-europe-grew/

 

Productividad y el impacto humano

En Uruguay estamos en epocas de presupuesto y negociación salarial, por lo que la conflictividad está por las nubes, la más alta desde 1995.

Conflictividad laboral Uruguay 2015

Este problema esta presente en todo el mundo, basta ver la comparativa entre puertos del primer mundo como Rotterdam vs Oakland (San Francisco) donde el primero es un 80% mas productivo que el segundo. En Oakland y el resto de puertos de la costa oeste por el año 1970 con la introducción del contenedor hubo grandes conflictos y la nueva generación de automatismos promete generar nuevos conflictividad con el aumento de prductividad.

Resulta interesante ver como las situaciones de desplazamiento de la fuerza laboral no es un problema actual, ya pasamos por enormes migraciones de la fuerza de trabajo, está por verse si la introducción de las tecnologías de la información a la industria tiene una velocidad de impacto diferente o es una transición mas de las ya vividas.