Los campos calculados con formulas se utilizan cuando nuestra fuente de datos no cuenta con el campo en el formato o con alguna operacion adicional para su presentacion.
Para hacerlo vamos a la fuente de datos, Editar y agregamos un campo:
Podemos hacer condiciones logicas IF para evaluar, operaciones aritmeticas de multiplicacion y division o hasta entre campos para encontrar relaciones que no vienen calculadas en la planilla o fuente de datos.
Agregamos la formula y un nombre para el campo calculado. Luego clic en Guardar y clic en Listo.
Video con todo el proceso de edicion y agregado de campo calculado con formula:
Las planillas de Excel o Google suelen ser una excelente forma de ingresar y formatear informacion, pero no suelen ser el mejor lugar para presentar la informacion a usuarios que no requieren de calculos y acceso a cada celda.
Data Studio es un caso intermedio entre PPT y Planilla de Calculo.
Data Studio nos permite presentar la informacion como una pagina web pero accediendo a las fuentes de datos y actualizando el informe si las fuentes de datos cambian.
Utilizando una planilla de calculo como fuente de datos, el informe es una forma muy amigable para presentar a otras personas o o mostrar en un lugar permanente sin la complejidad de la planilla completa y con una performance muy superior una vez que la planilla se vuelve compleja:
Si la planilla recibe datos, el informe en Data Studio se actualiza cada unos 15 minutos.
Lo que necesitamos es crear rangos con nombres en la planilla, los cuales luego obtendremos desde apps script para copiar las celdas:
Hecho esto utilizaremos los nombres de los rangos en el codigo de apps script:
// Rango de datos que sera copiado como tabla
var hojaInforme = 'Informe';
var rangosDatos = {
0 : 'RangoNombre1',
1 : 'RangoNombre2',
};
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})
}
La funcion getHtmlTable() es una funcion auxiliar que copia celdas y las transforma en HTML.
Aunque los rangos con nombre sean modificamos o cambien de ubicacion, el script podra ubicarlos y copiar los contenidos, aun si se agregan celdas dentro del rango!
Al momento de preparar informes suele suceder qur vamos cambiando algun criterio por el cual queremos acumular datos, sumar, agrupar.
Una formula que permite hacer sencillo de mantener y actualizar los criterios utilizados es la formula MATCH, que nos permite definir una condicion como si fuera un IF pero para una serie de datos y definiendo los valores en un rango fuera de la formula.
Si cada vez que cambia un criterio es necesario actualizar formulas podemos tener errores de formulas que no se actualizan o quedan inconsistentes.
=SUM(FILTER(BD!G:G,MATCH(BD!C:C,Datos!A2:A5,0)))
Este formula suma todos los elementos que cumplan la condicion que la columna C tiene un valor de los presentados en Datos A2:A5
De esta forma podemos configurar nuestra formula cambiando los datos en la hoja Datos, si agregamos o quitamos valores del rango A2:A5 cambiara el resultado de la formula.
Supongamos que tenemos una lista de propiedades para las cuales queremos sumar el precio:
Queremos sumar la columna G de las propiedaes, pero solo las de determinados tipos definidos en la columna C.
En una hoja auxiliar definidos los tipos que queremos sumar:
Es recomendable usar una hoja auxiliar para mantener el orden claro.
Por ultimo realizamos la formula utilizando MATCH y haciendo referencia al rango A2:A5 para comparar con la columna C de Tipo:
La formula SUM, SUMA el resultado de FILTER. FILTER trae la columna G cuando la columna C tome uno de los valores definidos en Datos!A2:A5
Un detalle importante al utilizar la formula MATCH, es que sin el tercer parametro, 0 en la captura de ejemplo, nos traera resultados que no sean identicos. Es importante siempre incluir este tercer parametro para obtener coincidencias exactas.
Pueden ver una planilla de ejemplo en el siguiente link:
La entrega de informes periódicos es una de las tareas típicas en todos los niveles de organización. La consistencia de los mismos es crucial para evitar que cada informe tengas diferencias que nos hacen dudar del mismo y gastar tiempo en entenderlo en lugar de analizar.
Una buena forma de sistematizar la entrega de informes es utilizar las siguientes herramientas combinadas para automatizar la generación de informes:
Planillas en la nube con una estructura definida de antemano.
Data Studio para leer la planilla como si fuera una base de datos.
El primer paso es definir una estructura general de planilla, definiendo las categorias o areas y el periodo de tiempo, por ejemplo un año dividido en meses o semanas. Hecho esto debemos generar hojas aparte que alimenten a ese cuadro, estas hojas aparte será donde importaremos informacion desde otras fuentes.
El segundo paso es utilizar formulas como IMPORTRANGE (que tiene sus limitaciones, es para algunos cientos de celdas) o bien Apps Script para copiar rangos o leer archivos XLSX. Es importante definir controles de consistencia de datos, para evitar que por ejemplo los XLSX subidos sean distintos cada vez.
Por último una vez que nuestra planilla de Drive es consistente pasamos a Data Studio para leer la misma como si fuera una base de datos, con la ventaja de que podemos ver la información desplegada y modificarla para hacer pruebas.
En Data Studio vamos al menu «Recurso» y accedemos a «Administrar las fuentes de datos…». Clic en «Añadir una fuente de datos» y buscamos la opción «Hojas de cálculo de Google». Buscamos la Hoja de Cálculo e indicamos la hoja en particular que vamos a usar. Podemos agregar distintas hojas como distintas fuentes.
Una vez agregada la fuente de datos debemos verificar que cada campo tiene el Tipo adecuado. Por ejemplo los campos con los cuales queremos graficar valores, sumar, promediar, debemos inficar Tipo: Numero. Para los campos por los cuales vamos a distribuir en los ejes, por grupos o periodos de tiempo debemos indicar Tipo: Texto o alguno de los de Fecha según nuestros datos.
Una buena idea es aplicar cálculos a los campos como números para redondear o pasar a millones o miles si los números son muy grandes, esto lo hacemos agregando campos calculados:
Los campos calculados se indican con un fx, clic en el icono para editarlo.Podemos usar formulas y calculos.
Luego utilizamos estos campos calculados en las gráficas:
Dimension del periodo: las fechas. Dimension: las categorias. Métrica: los numeros a sumar.
Cada vez que actualizamos los datos de la planilla fuente de datos hacemos clic arriba a la derecha para actualizar y listo:
Lo interesante es que el informe se visualiza como una página web simple y no requiere de utilizar formulas avanzadas. Podemos aplicar filtros a las gáficas simplemente agrupando los mismos para indicar cuales filtros aplican a cuales gráficas:
Por último para los campos calculados podemos utilizar la función CASE para realizar un Si lógico, es decir presentar la información que viene de la planilla con un texto distinto de acuerdo a los valores. Por ejemplo si creamos un campo calculado podemos tomar otro campo y presentar distintas opciones para mejorar la lectura:
Así en las graficas y filtros podemos ver textos en lugar de números o códigos.
Una de las funciones que suelen atraer a muchos a utilizar planillas en la nube es IMPORTRANGE que nos permite importar un rango de celdas desde otra planilla. Cuando la fuente remota se actualiza la planilla que usa IMPORTRANGE «ve los cambios» e importa el rango actualizado.
Es análogo a los orígenes de datos de Excel pero solo que en la nube.
Las dos limitaciones que suelen aparecer usando IMPORTRANGE se dan cuando:
Se quieren importar rangos demasiado grandes y se alcanza el limite de la función IMPORTRANGE.
El origen de datos va cambiando muy seguido y nuestra planilla no termina nunca de cambiar…
Al usar Apps script para copiar entre planillas tenemos la capacidad de copiar rangos practicamente ilimitados con mejor performance que usando la formula IMPORTRANGE. Definamos una planilla de origen por su link y las columnas a copiar:
// Link a planilla desde la cual vamos a copiar hacia la planilla actual var linkPlanillaOrigenDatos = ‘https://…’;
// Columnas a copiar desde la hoja de origen, para poder cambiar el orden, indice inicia en 1 var arrColsOrigen = [3, 1, 20, 6];
// Cada hoja de la planilla de origen
for (i in sheets){ var sheetName = sheets[i].getSheetName().substring(0,15);
// Si en origen estamos en la hoja que queremos copiar if (sheetName == hojaBDOrigen) { SpreadsheetApp.getActiveSpreadsheet().toast(‘Copiando datos ‘+hojaBDDestino+’…’, ‘Estado’); var sourcesheet = sourceSS.getSheetByName(sheetName);
// Cada columna a importar for (var iCol in arrColsOrigen) { // Leemos var sourcerange = sourcesheet.getRange(2,arrColsOrigen[iCol],sourcesheet.getLastRow()-1,1); var sourcevalues = sourcerange.getValues(); // Escribimos var destrange = destsheet.getRange(iniRow,colPos,sourcevalues.length,1); destrange.setValues(sourcevalues); colPos++; }
….
Este script lo ejecutamos adjunto a una planilla. Lo primero es obtener el enlace completo de la planilla desde la cual vamos a copiar los datos. Luego indicamos las columnas a copiar. Cuando accedemos a la planilla de origen debemos recorrer las hojas e identificar la hoja que es fuente de informacion. Una vez que estamos en la hoja de origen copiar las columnas en el orden que necesitamos a la planilla actual.
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:
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.
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:
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:
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:
