Propiedades para guardar datos en apps script

Hasta el momento hemos ejecutado scripts en la nube en forma aislada, cada script corre una vez pero no tiene forma de dejar un registro o leer registros de la ejecución anterior más allá de usar una hoja de la planilla adjunta. 

Supongamos usando el ejemplo anterior de envío de informe con pdf adjunto, que queremos evitar enviarlo múltiples veces en el mismo día. Si nuestro programador de tareas corre cada algunas horas, tenemos que poder detectar que el envío ya se hizo para el día de hoy.

Una solución elegante a este problema es utilizar el servicio de propiedades, PropertiesService, el cual nos permite guardar valores que pueden ser recuperados por una siguiente ejecución del script.

Para acceder al servicio de propiedades tenemos tres opciones o niveles:

  1. Script Properties: Propiedades a nivel de script: disponible para todo el script y todo usuario que ejecute el script.
  2. User Properties: Propiedades a nivel de usuario: disponible solo para el usuario que guarda los datos.
  3. Document Properties: Propiedades a nivel de documento: disponible solo para el documento asociado al script (por ejemplo la planilla).

Nota: Es importante tener en cuenta que este servicio guarda los valores como clave => valor de texto (string), por lo que al guardar un número por ejemplo, deberemos convertirlo nuevamente a tipo número cuando lo consultemos de las propiedades.

En nuestro caso nos interesan los dos primeros modos, las propiedades a nivel de script que serán visibles por todo usuario que utilice el script y las de nivel de usuario para que cada usuario pueda guardar datos propios. 

Utilicemos las propiedades a nivel de script (Script Properties). Volviendo a nuestro ejemplo de enviar el informe diario con un PDF adjunto. Queremos que el activador corra cada hora pero solo envie si han pasado cierta cantidad de horas desde el ultimo envío.

Para esto agregamos una función verificarEnviado en la cual guardaremos una marca de tiempo en milisegundos con el último envío de email y en cada ejecución siguiente compararemos esa marca de tiempo para saber si debemos volver a enviar o esperar.

Con un objeto Date, obtenemos los milisegundos actuales con getTime. Usaremos esta marca hoy_milis para comparar con la marca de la ejecución anterior. 

En la primera ejecución, no tenemos una marca anterior guardada por lo que debemos verificar si ultimoEnvio obtenido de las propiedades del script es nulo. Si es asi procedemos a hacer el envío y guardamos la marca de tiempo por primera vez.

En las siguientes ejecuciones obtendremos la marca de tiempo anterior y procedemos a comparar los milisegundos, en caso de que la última marca más las horas de espera sea mayor a la actual, esperamos y no haremos el envío de email.

function verificarEnviado() {

  // Cuantas horas queremos esperar entre envios? 
  // Las marcas trabajan en milisegundos
  // Modifcar el 1 para cambiar la cantidad de horas
  const horas_espera = 1 * 60 * 60 * 1000;

  const hoy = new Date();
  const hoy_milis = hoy.getTime();
  Logger.log('hoy_milis: ' + hoy_milis);

  // Buscamos la marca en milisegundos del ultimo envio
  const scriptProperties = PropertiesService.getScriptProperties();
  // Para obtener un numero (int) nuevamente convertimos de string a int
  const ultimoEnvio = scriptProperties.getProperty('MAIL_SENT_ON') * 1;
  Logger.log('MAIL_SENT_ON: ' + ultimoEnvio);


  // Si no hay marca de envio o si la marca es menor a la marca actual en cantidad: horas_espera
  if (ultimoEnvio === null || (ultimoEnvio + horas_espera) < hoy_milis ) {

    // Seteamos nueva marca en propiedades del script para consultar en proxima ejecucion
    scriptProperties.setProperty('MAIL_SENT_ON', hoy_milis);
    Logger.log('seteamos nuevo MAIL_SENT_ON: ' + hoy_milis);

    return true;

  } else {

    if (ultimoEnvio !== null) {

        const suma = ultimoEnvio + horas_espera;
        Logger.log('suma: ' + suma + ' > ' + hoy_milis);

    }

    return false;
  }

}

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/