flush()

Hemos tenido un problema con el código: iba demasiado rápido.

Tenemos una pestaña donde, gracias a un desplegable, conseguimos un montón de datos sobre una clase concreta. Con el desplegable cambiamos de grupo y, por consiguiente, todos los datos cambian según el grupo.
Queremos hacer un código que manda a cada tutor de cada grupo un pdf con todos los datos de dicha pestaña, siendo diferentes según el grupo.

En sí, creíamos que era fácil hacer el código. Solamente necesitabamos dos pasos:

1. Cambiar desde código el grupo de la lista desplegable
2. Mandar por correo el pdf de los datos que aparecían en pantalla por cada grupo.

Pero nos hemos encontrado con el problema que entre cambiar el grupo y mandar el pdf a la aplicación le costaba unos segundos cambiar todos los datos y el código no esperaba, por lo que el pdf se mandaba con datos a medio cambiar.

La solución:   SpreadsheetApp.flush() , con este método obligamos a actualizar la pantalla.
Por si acaso:  Utilities.sleep(20000), le damos 20 segundos de pausa al código, para que tenga tiempo.

Como poner enlace a un formulario en un menú hecho con código

Gracias al código, podemos crear un menú que se integra con todos los restantes en una hoja de cálculo, por ejemplo:

function onOpen() {
 var ss = SpreadsheetApp.getActive();

  var items = [
        {name: 'INICIO', functionName: 'inicio'},

        null, // Results in a line separator.

    {name: 'Respuestas', functionName: 'ir_a_respuestas'},
   {name: 'Tabla Gestión', functionName: 'ir_a_gestion'},
   {name: 'Control reiteración', functionName: 'ir_a_control'},
         {name: 'Sanciones', functionName: 'sanciones'},
      null, // Results in a line separator.
         {name: 'Por fechas', functionName: 'por_fechas'},
   {name: 'Por curso', functionName: 'por_curso'},
   {name: 'Por tipo', functionName: 'por_tipo'},
       {name: 'Por alumno', functionName: 'por_alumno'},
           {name: 'Histórico', functionName: 'historico'},
           {name: 'Evaluaciones - Grupos', functionName: 'evaluaciones_grupos'},
           {name: 'Conducta - Grupos', functionName: 'conducta_grupos'},
           {name: 'Medida - Grupos', functionName: 'medida_grupos'},
           {name: 'Zona - Grupos', functionName: 'zonas_grupos'},
          null, // Results in a line separator.
               {name: 'Mandar expediente', functionName: 'mandar_expediente'},
               {name: 'Lista de expedientes', functionName: 'expedientes'},
               {name: 'Plantilla de expedientes', functionName: 'mandar_expediente'},
              null, // Results in a line separator.
               {name: 'Plantilla de correo', functionName: 'plantilla'},
               {name: 'METER ALUMNOS', functionName: 'alumnos'},
                {name: 'INSTALACIÓN (Listas para el formulario)', functionName: 'instalacion'}
  ];
  ss.addMenu('Navegación', items);

}

Como la función se llama onOpen(), cada vez que habramos la hoja de cálculo va a correr el código y  creará el menú.

Pero, y este es el problema que nos hemos encontrado, si queremos poner una opción que apunte a la URL de un formulario no nos va a funcionar.

Para solucionarlo, mirando en Internet, mi compañero David Berrogain encontró esta función:

function showDialog() {

  var html = HtmlService.createHtmlOutput('<a target="_blank" href="https://docs.google.com/forms/d/e/1FAIiddementirak9w5wRwOOSQ/viewform?usp=pp_url&entry.155333884587='+SpreadsheetApp.getActiveSpreadsheet().getName()+'">Abrir Formulario</a>')

      .setSandboxMode(HtmlService.SandboxMode.IFRAME)

      .setWidth(200)

      .setHeight(200);

  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.

      .showModalDialog(html, 'Añadir Entrevista con Familia');

}

Teniendo ya la función solamente nos quedaría meter otra fila en la función onOpen() que sería:

                {name: 'fORMULARIO', functionName: 'showDialog()'},

(¡IMPORTANTE! Si esta línea es la última de las opciones hay que borrar la coma del final, de lo contrario mantenerla)

Desconectar de la cuenta desde ciertos sitios

Lo que voy a explicar hoy puede parecer un detalle sin importancia, sobre todo si trabajáis desde vuestro propio ordenador.
Pero, os pongo en situación: Creamos herramientas para gestión de centros escolares, damos permiso para usarlas solamente a miembros de la organización (en nuestro caso @educacion.navarra.es). Estas herramientas se necesitan en el centro escolar, donde el profesor debe entrar desde el ordenador que le pille mas cerca. Las herramientas le piden que se logee con su cuenta de correo. Una vez que ha acabado de utilizar la herramienta, sino pone cuidado, deja su cuenta abierta para el próximo que llegue. Consecuencia, el próximo no necesita logearse, pero trabaja como si fuera el anterior usuario y, además, podría perfectamente entrar en el correo, en el drive, etc.. del anterior...

¿En cuales de las herramientas que utilizamos pasa esto? En el Site nuevo (no hay enlace a cerrar sesión por ningún sitio) y en cualquier formulario (misma situación)

Por lo tanto, una buena idea es conseguir que tanto en el Site como en el mensaje de confirmación de los formularios aparezca algo así como "cerrar sesión".

Pensabamos que iba a ser muy complicado, pero gracias a internet lo hemos conseguimo fácilmente. El enlace en todos los casos es:

https://mail.google.com/mail/logout

Este enlace lo podemos poner en el mensaje de confirmación de los formularios 
y/o crear un menú en el Site nuevo que apunte a dicho enlace
 

Como meter una imagen (una firma) desde un formulario a un documento

Con esta función, un formulario y una plantilla podemos hacer que los usuarios suban su firma (imagen) y se inserte en el lugar de un documento que decidamos. El código lo he metido en la hoja de cálculo unida al formulario y he activado el evento "al enviar el formulario"

(Hay unas cuantas partes interesantes en sí mismas:

• Como conseguir datos del formulario
• Como conseguir el ID de un archivo desde su URL
• Como hacer público un archivo
• Como crear una copia de una plantilla y meterla en una carpeta concreta )

function insertar_imagen(e) {
// Pillamos la imagen subida desde el formulario (Conseguimos por defecto la URL de dicha imagen)
var imagen= e.values[2]
// Utilizamos esta fórmula para extrar de la URL el ID
var id = imagen.match(/[-\w]{25,}/)
// Con el ID abrimos el archivo de imagen subido
var irudia = DriveApp.getFileById(id)
// Le damos permiso para que sea accesible por cualquiera (Parece que sino no funciona el código)
irudia.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.VIEW)

// Conseguimos un ID de una carpeta para meter el nuevo documento
   var folderID='1ROGPRhAgtozzpcQr0jM_ekwBiuFsaw5E' //Este ID debería ir fuera del código
   var folder = DriveApp.getFolderById(folderID)
  // Creamos una copia de la plantilla y abrimos el documento
  var nuevoID= DriveApp.getFileById('1hIjlBCktJdIy8GuDB72XPW-H3_Uw_WhygnbyYLq0sbA').makeCopy('Firmado',folder).getId()
  var doc = DocumentApp.openById(nuevoID)


  // Conseguimos todas las partes del documento, mayormente los párrafos
  var todos = doc.getBody().getNumChildren()
  // Creamos una variable que apunte al cuerpo del documento, no a todo el en su conjunto
  var body = doc.getBody()
// Creamos un array para ir metiendo el texto de cada párrafo, con intención de buscar la marca que hemos elegido ### y sustituirla por la firma
  var el=[]

  // Recorremos todos los elementos del documento
 for (var j=0; j<todos; j++){
 // Metemos por cada vuelta el elemento en una variable
 var elemento = doc.getChild(j)
 // Apuntamos que tipo de documento es en otra variable
 var tipo = elemento.getType()
//Utilizamos condicional para solamente quedarnos con los párrafos
 if (tipo=='PARAGRAPH'){
 // Si es párrafo, conseguimos el texto interno
 el[j]= elemento.getText()
// Buscamos en el texto la marca mediante un condicional
   if (el[j]=='###'){
// Borramos dicho texto cuando lo encontramos
   elemento.removeFromParent()
 // Y lo sustituimos por la imagen de la firma
      body.insertImage(j, irudia)
   }
 }
 }
}

Arrays (Segunda parte)

Hoy he vuelto a tener problemas con arrayformula()
Ya escribí este artículo sobre el tema hace unos días. Podéis consultarlo también

He utilizado una vlookup() para, dado un correo electrónico, me coja de una lista el nombre oficial de dicha persona.
La fórmula me ha quedado así: =vlookup(Q3,responsables!$A:$D,3,false)) >>> Busca el correo que queremos y que está en la celda Q3 entre la lista de responsables y sus datos que tenemos en la pestaña responsable, en la columna A. Y me da el nombre que está en la columna 3 de la lista A:D de responsables, osea en la columna C 

Como tenía que repetir la misma fórmula para todos los correos de la columna Q y, además, para los nuevos que se vayan añadiendo, no me bastaba con arrastrar la columna, sino que debía utilizar la fórmula arrayformula()

Inconsciente de mí he cambiado la fórmula a
=arrayformula(vlookup($Q$3:$Q,responsables!$A:$D,3,false)), queriendo conseguir que pillara cualquier dato de la columna Q.

¡Maldita sea! ha empezado la barra de progreso a moverse y ha creado hasta 50000 filas. Y, a pesar de eliminarlas como cucarachas, la hoja de cálculo ha vuelto a crearla y así hemos estado luchando un buen rato. ¿Quién se ha rendido? :(

He cerrado la hoja de cálculo. Punto!!!

Pero... necesitaba ese array. Por lo que he probado a cambiar un poco la fórmula, metiéndolo un condicional para que solamente se aplicase el vlookup cuando hubiese algo en la columna Q.

Me ha quedado así:
=arrayformula(if($Q$3:$Q="","",vlookup($Q$3:$Q,responsables!$A:$D,3,false)))

y... ¡¡¡¡¡ha funcionado!!!!!


A partir de ahora, meteré siempre en las arraysformulas un freno, un condicional que limite su aplicación, osea que quite los datos vacíos.

Os lo recomiendo.

Problemas con las referencias y función indirect

A veces me ha ocurrido que meto en una fórmula una referencia tal que así:

=iferror(join(char(10);filter(ekintzak!$F$2:$F;ekintzak!$G$2:$G=B4;len(ekintzak!$G$2:$G)>0)))

Es decir con referencias que empiezan en la línea 2, para que no cuente dentro del filtro las cabeceras de columna y...
si borro los datos y empiezo de nuevo a meterlos la fórmula, de forma casí a mala leche, va cambiando el principio de la referencia a la línea 3, luego a la 4, etc... justo con el resultado que nunca se referencia a los datos que existen, sino a los que no existen todavía. En mi caso haría esto:

• Meto un dato en la línea 2 de la pestaña "ekintzak" 
• La fórmula cambia a

 
=iferror(join(char(10);filter(ekintzak!$F$3:$F;ekintzak!$G$3:$G=B4;len(ekintzak!$G$3:$G)>0)))

Y si meto algo en la línea 3, la fórmula coherente y malignamente se convierte en:

=iferror(join(char(10);filter(ekintzak!$F$4:$F;ekintzak!$G$4:$G=B4;len(ekintzak!$G$4:$G)>0)))

El resultado es que nunca hay nada en el filtro que queremos que aparezca.

La solución que he encontrado es utilizar la función indirect. Gracias a ella, consiguo que el número 2 se mantenga siempre estático:



=iferror(join(char(10);filter(indirect("ekintzak!$F$"&2&":$F");indirect("ekintzak!$G$"&2&":$G")=B4;len(indirect("ekintzak!$G$"&2&":$G"))>0)))

Dicha función es muy potente, ya que permite crear referencias con mezcla de partes fijas y dinámicas y, en este caso, de partes fijas que no hacen caso a lo dinámico. Pero tiene dos inconvenientes:

• No es fácil de construir, ya que hay que trozear las referencias, meter entre comillas el texto fijo, unirlo al dinámico con & y volver a abrir y cerrar comillas si hay mas texto fijo detrás.
• Una vez escrita la función es difícil de interpretar

(Mientras escribía el artículo me he dado cuenta que podía haber metido todas las referencias dentro de indirect entre comillas, sin sacar el número y quedaría así:
=iferror(join(char(10);filter(indirect("ekintzak!$F$2:$F");indirect("ekintzak!$G$2:$G")=B4;len(indirect("ekintzak!$G$2:$G"))>0)))

por lo que, simplemente, gracias a indirect, he conseguido que las referencias no cambien de forma dinámica, sean totalmente estables y no les influyan los nuevos registros)

Convertir casillas de verificación ☑️ en selección múltiple 🔘

No hace mucho, Google creó la opción de incluir casillas de verificación en las hojas de cálculo. Os muestro un ejemplo:

En este caso utilizo las casillas para que el usuario decida si un correo se manda en castellano, en euskera o en bilingüe.
Pero, mi problema es que, como son casillas de verificación, no quiero que me marquen mas de una, sino que cuando marquen una las restantes se desmarquen.

Las casillas no tienen por defecto dicho comportamiento, ya que puedes marcar una, varias o todas.

Mediante código podemos conseguir que se comporten como selección múltiple (según el lenguaje de los formularios de drive) o radio buttons ( según html), es decir como:
🔘


Para conseguirlo vamos a utilizar la función onEdit(event) que viene por defecto en Google Script

Esta función corre cada vez que hay un cambio en la hoja de cálculo, por lo que, con una estructura condicional, haremos que cada vez que hagamos clic en una casilla de verificación los valores de todas las demás pasen a "false"

function onEdit(event) {
// Gracias al argumento "event" pillamos la celda que cambia en cada momento
     var celda_cambiada= event.source.getActiveRange().getA1Notation();
//  Enlazamos a la hoja de cálculo activa y la pestaña que nos interesa    
     var aplicacion = SpreadsheetApp.getActiveSpreadsheet()
     var pestana = aplicacion.getRangeByName('correo_plantilla').getSheet()
 
// Por medio de switch tenemos un condicional con más de dos opciones, más claro y eficaz que IF   
     switch(celda_cambiada) { // comparamos la celda cambiada y creamos tres casos
    case 'C15':
        pestana.getRange('c16').setValue(false) // hacemos que la c16 se desmarque
        pestana.getRange('c17').setValue(false) // hacemos que la c17 se desmarque 
       break; // Salimos del código condicional
    case 'C16':
        pestana.getRange('c15').setValue(false)
        pestana.getRange('c17').setValue(false)
        break;
    case 'C17':
        pestana.getRange('c15').setValue(false)
        pestana.getRange('c16').setValue(false)
        break;
    default:
        Browser.msgBox('Mala puntería')
}
}