Como seleccionar la fecha actual al abrir un spreadsheet con Google Apps Script

En muchas ocasiones nos puede ser útil que se haga una tarea automáticamente al abrir nuestro spreadsheet en referencia al día actual usando Google Apps Script: Marcar ciertas celdas, aplicar formatos, setear textos, etc...

En este ejemplo vamos a montar un sistema que cada día nos seleccione una columna con la fecha actual tal y como podemos ver en este imagen:

Autoselección de columnas



function onOpen()
{
   MarkToday();  
}

function MarkToday()
{
  var today = new Date();
  var today_day   = today.getDate();
  var today_month = today.getMonth();
  var today_year  = today.getFullYear();

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  var range = sheet.getRange(1,1,1,sheet.getLastColumn() );
  var caption = range.getValues()[0];

  //Buscamos el indice de columna que nos concuerda con el dia actual
  var index = 0;
  for(var i = 0 ; i < caption.length ; ++i)
  {       
      var d = new Date(caption[i]);

      //Compensamos la diferencia horaria entre el servidor de Google y nuestra situación.
      //De no compensar esta diferencia nos puede marcar dias diferentes de los reales.
      d.setTime( d.getTime() + 12*60*60*1000 );

      if(today_day == d.getDate() && today_month == d.getMonth() && today_year == d.getFullYear())
      {
        index = i+1;
        break;
      }
  }

  //Eliminamos marcas que pudieramos tener
  range.setBackground("white");

  //Marcamos el dia actual
  sheet.getRange(1,index).setBackground("yellow");
}

Como podéis ver el código no es complicado y se puede adaptar a lo que necesitéis. Se os ocurre alguna otra funcionalidad que se podría aplicar?

No vaciléis en hacerme llegar vuestras dudas.

Nos vemos


Como cambiar permisos de un fichero con Google Apps Script


En muchas ocasiones nos puede resultar útil poder modificar los permisos de un fichero de Google ( spreadsheets sobre todo ), así como copias para ciertos usuarios , o durante la preparación para distribuir información a un grupo de personas. Con Google Apps Script podemos automatizar de una forma muy sencilla la asignación de permisos a un fichero.

Primero de todo, hemos de tener claro que toda la gestión se hace a través de DriveApp, es decir, si queremos dar permisos a un spreadsheet o un doc, por ejemplo, primero deberemos obtener su id.

Ya veréis que es muy sencillo: 

function SetPermisos() 
{
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var id = spreadsheet.getId();
    DriveApp.getFileById(id).setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
}

El tipo de acceso puede tener diferentes opciones:

tipos de acceso google apps script
ANYONE, ANYONE_WITH_LINK,DOMAIN,DOMAIN_WITH_LINK, PRIVATE

De la misma manera podemos asignar diferentes tipos de permisos:
Tipos de permisos Google Apps Script
COMMENT, EDIT, FILE_ORGANIZER,NONE,ORGANIZER,OWNER,VIEW


Como veis es muy sencillo cambiar permisos de un fichero de Drive con Google Apps Script.

No vaciléis en hacerme llegar vuestras dudas

Nos vemos!




Como leer datos de un webhook con Google Apps Script


Una aplicación bastante interesante de Google Apps Script es recibir callbacks de un WebHook. Un WebHook básciametne es un servicio web que atacará a la url que nosotros le digamos ( generalmente haciendo un POST ) cuando pase un cierto evento.

Es decir, un webhook nos informará de eventos que pasen en algún servicio externo: pagos recibidos, mensajes pendientes, errores en servicios, etc....

Ahora bien, una cosa que no está demasiado bien documentada y que siempre trae algún quebradero de cabeza es como obtener la información del callback. La solución es fácil, salvo que nos indiquen lo contrario el webhook llamará a nuestra url con un método POST cuyo payload será un JSON con la información que necesitamos. Cada servicio implementará su protocolo y su forma de enviar la información dentro del JSON, pero lo básico es lo que os acabo de explicar.

Así pues el código para procesar esa llamada es extremadamente fácil:

function doPost(e)
{
    var jsonString = e.postData.getDataAsString();
    var data = JSON.parse(jsonString);
    // AQUI VA TU GESTION
}

Con esté código ya tenéis la estructura básica para saber como procesar los JSON que nos vengan de un webhook. Si algo no lo tenéis claro os recomiendo que reviséis mi post sobre como trabajar con JSON en Google Apps Script

No vaciléis en hacerme llegar cualquier duda que tengáis.

Nos vemos



Como detectar que el usuario ha añadido una nueva línea con Google Apps Script


En muchas ocasiones nos puede resultar muy útil usar Google Apps Script para saber cuando alguien inserta una nueva fila. Ya sea Zapier generando una nueva fila, otro script que tengamos o que el mismo usuario inserten una nueva fila, con el código que os enseñaré podréis detectar este hecho y hacer lo que necesitéis (cambiar formatos de celdas, setear timestamps, crear logs, etc...)

function CrearTriggerOnChange()
{
    var sheet = SpreadsheetApp.getActive();
    ScriptApp.newTrigger("onChange")
      .forSpreadsheet(sheet)
      .onChange()
      .create();
}

function onChange(e)
{
   if(e.changeType=="INSERT_ROW")
   {
      SpreadsheetApp.getUi().alert("Se ha insertado una fila nueva");
   }
}

Usamos el evento onChange (parecido, pero diferente al evento onEdit) para detectar cuando alguien inserta una nueva fila. Este evento tiene muchos más usos, pero por ahora, con este ya podemos hacer.

En el ejemplo concreto que os muestro lo que ocurre es que cada vez que alguien cree una nueva fila aparecerá un mensaje informando que se ha creado una nueva fila, algo de este estilo:

Como detectar una nueva fila con Google Apps Script

En otros artículos os explicaré más usos del onChange.

Solo un último detalle. Al contrario del evento onEdit, el evento de onChange es necesario instalarlo. Es por ello que os he dejado la función de CrearTriggerOnChange. La tendréis que ejecutar una vez para que se instale el trigger ( solo una vez :) ). Si lo necesitáis tengo otro artículo que explica muy claramente como funcionan los triggers

No vaciléis en hacerme llegar vuestras dudas

Nos vemos





Como convertir índices de columna a letras con Google Apps Script


Un problema que podemos tener a veces cuando programamos en Google Apps Script es que trabajamos con índices de columna pero necesitamos las columnas en notación con letras (por ejemplo cuando queremos construir fórmulas de forma dinámica) y esta conversión no es fácil.

El código que os dejo a continuación se encarga de hacer esa conversión.  Si lo miráis con detenimiento veréis como funciona. Básicamente convierte directamente cada índice con la letra del abecedario que le toca (1-> A, 2-> B, etc...). En el supuesto que el índice sea superior a la letra Z se convertirá lo que se pueda y se generarán las próximas letras de forma cíclica :

function ConvertirIndiceALetra(column, row) 
{
  var temp, letra = '';
  var first_index = String('A').charCodeAt(0);
  var last_index  = String('Z').charCodeAt(0);
  var num_letras  = last_index - first_index;

  while (column > 0) 
  {
    temp   = (column - 1) % num_letras;
    letra  = String.fromCharCode(temp + first_index) + letra;
    column = (column - temp - 1) / num_letras;
  }
  
  return letra + row;
}

Aquí tenéis un ejemplo de como queda:
Tabla de ejemplos de conversión a letras


Espero que os sirva, no vaciléis en hacerme llegar cualquier duda.

Como hacer un timestamp cuando alguien modifica una celda con Google Apps Script


Una aplicación muy sencilla pero útil de Google Apps Script en el ámbito empresarial es montar un sistema que nos permita tener un timestamp de cuando alguien modifica una celda.

Utilizaremos el evento onEdit y nos limitaremos a apuntar la fecha actual en la primera columna del spreadsheet cuando se haga alguna modificación en alguna de las filas del documento.

El código para hacer esto es muy sencillo:

function onEdit(e)
{
  var range = e.range;
  var sheet = range.getSheet();
  var row   = range.getRow();
  var col   = range.getColumn();
  
  if(col > 1 && row > 1)
  {
    sheet.getRange(row,1).setValue(new Date());
  }
}

Si os fijáis en el código hemos hecho, la primera fila ( las cabeceras) y la primera columna ( donde tenemos los timestamps) no aplican para el cálculo de tiempo. Podéis poner las limitaciones que queráis.

Así es como se vería el resultado:

timestamp con Google Apps Script


Una cosa que no se puede hacer en este código ( seguro que muchos lo querríais :) ) es apuntar quien ha hecho la modificación. El motivo es por que el onEdit trabaja sin permisos ( nadie tiene que dar permisos al script para funcionar ) así que no podemos obtener el usuario actual por que no le hemos pedido que nos otorgue esa capacidad.

Espero que os sirva!

Nos vemos


Como enviar correo cuando alguien rellena un formulario con Google Apps Script


Una cosa que a veces puede ir muy bien es que cada vez que alguien responda a un formulario se pueda enviar un correo a "alguien" con cierta información de la respuesta. Ese alguien puede ser un administrador, un gestor, o el mismo que ha enviado la respuesta. La información que enviemos  puede ser lo que queramos: un resumen de las respuestas, un PDF, el acceso a una carpeta de Drive,etc...

Con Google Apps Script podemos enviar un correo cuando alguien hace un submit en un GoogleForm y lo mejor de todo, es que hacerlo es muy sencillo!

Aquí tenéis el código:

function InstallTrigger()
{
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var triggers = ScriptApp.getUserTriggers(spreadsheet);
    
    for(var t = 0 ; t < triggers.length ; ++t )
    {
      ScriptApp.deleteTrigger(triggers[t]);
    }
    
    ScriptApp.newTrigger("SendMessageWhenSubmit")
             .forSpreadsheet(spreadsheet.getId())
             .onFormSubmit()
             .create();
}  

function SendMessageWhenSubmit(e)
{
    var values = e.values;
           
    for(var i = 0 ; i < values.length ; ++i)
    {
       if(values[i] == "yes" || values[i] == "Yes" || values[i] == "YES")
       {
          SendMessage();
          break;
       }
    }
}

function SendMessage()
{
  var html = "<html>";
  html += "<body>";
  html += "<h1>Report</h1>";
  html += "<p>Test</p>";
  html += "</body>";
  html += "</html>";
  var title = "Titulo de ejemplo";
  MailApp.sendEmail("bablablabl@gmail.com", title, '', {
           name: title,
           subject: "Report",
           htmlBody: html,
          // attachments: [pdfFile.getAs(MimeType.PDF)] //Aqui pondriamos los adjuntos si los necesitaramos
         });
}

El trigger que nos informará de cuando alguien ha rellenado un formulario es un trigger instalable . Si ejecutais els InstallTriggers todo el trabajo lo tendreis hecho.

La función SendMessageWhenSubmit será llamada cuando alguien responda al formulario con el parámetro e que contendrá la información de la respuesta. En este ejemplo concreto enviaremos un correo cuando  alguien responda a alguna pregunta con un "yes", pero la condición la podríamos hacer como quisiéramos.

Como podéis observar el código es muy sencillo y es extremadamente fácil de adaptar a lo que necesitéis.

No vaciléis en hacerme llegar vuestras dudas.

Nos vemos!


Diferencias entre getValues y getDisplayValues en Google Apps Script


 Generalmente cuando queremos leer un hoja de cálculo con Google Apps Script usamos la función getValues del range. Esto en casi todos lo casos funciona como esperamos , pero hay ciertos casos en los que nos puede dar problemas, sobretodo cuando hablamos de datos formateados ( por ejemplo fechas o monedas)

El caso de las fechas en Google Sheets es paradigmático. Podemos ver un ejemplo en la siguiente imagen, todas las celdas tienen el mismo valor pero su representación es claramente diferente en cada celda por que depende del formato:

Diferencias de formato con celdas con los mismo valores

Lo gracioso de estas celdas es que si obtienes los valores con getValues obtendremos siempre el mismo valor. Si queremos obtener el valor tal y como se ve en la celda tendremos que usar la función getDisplayValues.

Para hacer la prueba podemos usar este código:

function TestDisplayValues() 
{
    var sheet          = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var range          = sheet.getDataRange();
    var values         = range.getValues();
    var display_values = range.getDisplayValues();
    
    sheet.getRange(1, 2, values.length, values[0].length).setValues(values);
    sheet.getRange(1, 3, display_values.length, display_values[0].length).setValues(display_values);
}

Básicamente este código  obtiene un rango y copia en otras columnas lo que obtenemos con el getValues y con el getDisplayValues.

Este es el resultado:

Diferencias entre getValue y getDisplayValues

Como podéis ver en la columna B hemos puesto los valores que vienen del getValues,  mientras que lo que hay en la columna C hay los valores que vienen del getDisplayValues.

Es un tema bastante más complejo de lo que puede parecer en un inicio, no vaciléis en hacerme llegar vuestras dudas.

Nos vemos




Como filtrar filas usando filter con Google Apps Script


 Una operación muy común cuando programamos con Google Apps Script es filtrar filas. Obtenemos todas las filas de una hoja y nos quedamos solo con aquellas que nos interesan: las que tiene un cierto valor en una columna en concreto, las que tienen vacía cierta celda, etc...

Implementar un código con Google Apps Script que filtre filas es bastante fácil. De hecho sería algo como esto:

function FilterRows(values) 
{
  var out = new Array();
  
  for(var i = 0 ; i < values.length ; ++i)
  {
     var row = values[i];
     
     if( row[0] == "A" )
     {
       out.push(row);
     }
  }
  
  return out;
}

Fàcil no? Recibe un array (como por ejemplo el que podríamos obtener de un range.getValues() ) y revisa fila por fila la condición que nos interese (en nuestro caso, solo nos quedaremos con las filas cuya primera celda sea una "A"). Lo que devolverá la función será un array con solamente las filas que nos interesan.

El código es eficiente, funciona bien y no da problemas, pero... son bastantes líneas de código que en ciertos proyectos pueden llegar a molestar.

Solución? Utilizar la función filter que nos da JavaScript y que podemos usar sin problemas dentro de Google Apps Script. 

El código quedaría tal que así:

function FilterRow(values)
{
   return values.filter(row => row[0] == 'A');
}

Antes que recordad que values una matriz de datos que representa la información de un sheet, es decir, es un array de arrays de datos. Cada elemento de values es una fila de información.

.filter es una funcionalidad de los arrays de Java(GoogleApps)Script que devuelve solo los elementos del array que cumplan la condición que se le da

En est caso row es la variable de iteración, es decir, la variable que usará el filter para comprobar si se acepta o no ese elemento.

El formato de la condición de filter siempre será el mismo: var => condicion


Que es mejor?

A nivel de rendimiento , es más o menos lo mismo, he hecho bastantes pruebas y no sabría decir cual sería una mejor opción ( al menos en Google Apps Script). Ambas opciones son bastante comparables.

Ahora bien, a nivel de calidad de código, usar filter nos permite reducir muchísimas líneas de código, de hecho, eliminar funciones directamente por que nos permitiría tener el código más compacto sin tener que ir creando funciones.

Hacedlo como queráis, pero yo personalmente os aconsejaría que usarais el filter. A la que lo hayáis hecho un par de veces lo veréis mucho más natural y las ventajas que da en el mantenimiento del código son evidentes.

Espero que os haya servido de ayuda. 

Nos vemos!





Como eliminar un carácter de un spreadsheet con Google Apps Script


En ocasiones puede ser útil tener un script que se encargue de eliminar ciertos caracteres de nuestro spreadsheet con Google Apps Script. Generalmente cuando importamos datos de un CSV , o de una API podemos obtener los datos en un formato que no nos va bien para el resto del proyecto.

Todo lo que tendremos que hacer es obtener todos los datos del spreadsheet, ver para cada celda si existe el carácter en concreto que queremos eliminar y reemplazarlo por un carácter nulo. Cuando hayamos acabado de reemplazarlo solo tendremos que volver a poner toda la info en el spreadsheet. 

Aunque pueda parecer complicado ,es muy muy fácil, ya lo veréis.

En este ejemplo concreto lo haremos eliminando el carácter  "»" que puede darnos problemas con fórmulas por ejemplo.

function onOpen()
{
    SpreadsheetApp.getUi().createMenu("[SCRIPT]").addItem("Refactor", "Refactor").addToUi();
}

function Refactor()
{
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var range = sheet.getDataRange();
    var values = range.getValues();

    for(var i = 0 ; i < values.length ; ++i)
    {
        for(var j = 0 ; j < values[i].length ; ++j)
        {
            var str = values[i][j];

            if ( typeof(str) === 'string')
            {
                while( str.indexOf("»") != -1)
                {
                    str = str.replace("»","");
                }
                values[i][j] = str;
            }
        }
    }

    range.setValues(values);
}

Para probar su funcionamiento, copiad este código en el editor de código del spreadsheet donde querais implementar la funcionalidad, guardad y refrescad el spreadsheet.

Al reiniciar veréis que os aparece una opción en el menú superior [SCRIPT]> Refactor , que cuando le clickeis se encargará de hacer su magia.

Espero que os haya servido.

No vaciléis en hacerme llegar vuestras dudas.

Nos vemos!

Tal vez te interese