Restar fechas en código Google Script

 En otros artículos, he mostrado la preocupación de no conseguir entender como funcionan las fechas en el código. 

He descubierto que hay dos formas diferentes de gestionarlas. Una es dentro del código y otra en las fórmulas de las hojas de cálculo

Empezamos por el código:

En la siguiente función he probado a restar un número ( un 1 ) a una fecha. Y este es el resultado:

function restar_tiempo(){

// Vamos a intentar ver que pasa si sumamos o restamos una cantidad a una fecha

var herramienta =SpreadsheetApp.getActiveSpreadsheet()

var pestana = herramienta.getSheetByName('agenda')

var fecha = pestana.getRange('f1').getValue()

Logger.log('Fecha: '+fecha) // Resultado: Wed Feb 16 2022 00:00:00 GMT+0100 (CET)

// Empezamos restando 1

var fecha_menos_uno = fecha-1

Logger.log('Fecha: '+fecha_menos_uno) // Lo cambia a número y si hace la resta Resultado: 1644965999999

// Pero en formato fecha como queda?

fecha_menos_uno = new Date(fecha_menos_uno)

Logger.log('Fecha: '+fecha_menos_uno) // Ha restado, parece un milisegundo Resultado: Tue Feb 15 2022 23:59:59 GMT+0100 (CET)

// Vamos a ver la diferencia entre la fecha primera y la resta, pero en número

var numero_fecha = fecha.getTime()

var numero_fecha_menos_uno = fecha_menos_uno.getTime()

Logger.log('Numero_fecha '+numero_fecha) // Resultado: 1644966000000

Logger.log('Numero_fecha_menos_uno '+numero_fecha_menos_uno) // Resultado: 1644965999999

/*******************     CONCLUSIÓN  *********************************

 Cuando restamos a una fecha, estamos restando milisegundos

 Por lo que si queremos restar un día entero deberemos restarle 1000*60*60*24 =8640000 milisegundos

 Es decir 1000 milisegundos por 60 segundos por 60 minutos por 24 horas que tiene cada día

*/

/* Hemos utilizado dos métodos para trabajar con las fechas en el código

     new Date (milisegundos) para conseguir la fecha desde un número

     getTime(fecha) para conseguir lo contrario, es decir, los milisegundos de una fecha

*/

}

Sin embargo en las fórmulas:

Si pones la fórmula =today() te aparece una fecha, en este caso 17/02/2022

Si le restas uno, es decir, =today()-1 te aparece la fecha del día anterior 16/02/2022

CUAL ES EL MISTERIO? PORQUÉ ESA DIFERENCIA?

Parece que en el código los días tienen de base un número con la cantidad de milisegundos que

han pasado desde el 1 de Enero de 1970 a la 1 de la noche hasta la fecha en cuestión

Sin embargo, en las fórmulas los días tienen de base también un número, pero con la cantidad de

días (no de milisegundos) que han pasado desde (no desde 1970) el 1 de Enero de 1900. 

En este último caso, las horas, minutos, segundos y milisegundos se expresan dentro de la parte

decimal del número

Filtrar por fechas en la fórmula query

 Este artículo no va de código. Pero si de un tema recurrente en este blog: las fechas.

No estoy seguro de la razón, pero nunca tengo la sensación de dominar la utilización de fechas tanto en código como en fórmulas. Muchas veces me encuentro que una fórmula o código que funciona perfectamente con otro tipo de datos, deja de hacerlo cuando hay fechas de por medio.

Por lo tanto, este artículo es un recordatorio de la solución a uno de esos problemas, en este caso a filtrar un importrange utilizando Query.

Como introducción diré que la unica forma que he conseguido mezclar importrange con un filtro ha sido a través de query, tal que así:

=query(importrange(url;range);"select * where col2='Martes'") Así consigo importar solamente una parte de los datos de otra hoja de cálculo según una condición.

Peeeero, quería hacerlo con la condición de que los datos serían igual o posteriores a la fecha actual y no había manera.

Al final la fórmula me ha quedado así:

=query(IMPORTRANGE(url;rango);"Select * where Col3 >=date '"&TEXT(TODAY();"yyyy-MM-dd")&"' ")

Como véis hay que utilizar la palabra clave date y además pasar a texto y a formato especial la fecha actual.

Filtrar arrays dentro del código

 Bastantes veces necesito conseguir todos los valores de una columna. Por ejemplo, cuando quiero actualizar las opciones de una pregunta en un formulario con los valores que aparezcan en una hoja de cálculo. 

Este tema está ya tratado en este artículo

Lo vuelvo a traer a colación para presentar  como filtrar un array dentro del código con una sola línea

Imaginaros que la lista de valores puede cambiar de tamaño y, por lo tanto, es lógico, aparecen líneas vacías, unas veces más otras menos, debajo del último valor, tal que así:

Si no quitamos las líneas vacías, el código no va a funcionar, porque le estamos pidiendo que cree opciones con el mismo nombre (en este caso vacío)

Para quitarlas, se pueden utilizar diferentes estrategias.

Una de ellas es utilizar el método filter que tienen los arrays

Quedaría así:

var lista = ss.getRangeByName('lista_valores').getValues()

var lista_filtrada = lista.filter( function (elemento){return elemento !=""})

Simplemente con la línea superior tenemos la lista filtrada sin líneas vacías.

Lo más difícil de esta línea de código es controlar los paréntesis y las llaves :)

Conseguir filtrar correos que no sean de Google

 Para ello debemos aislar la segunda parte de la dirección de correo:

var correo = 'ionrey@hotmail.com'

var posicion = correo.indexOf("@")+parseInt(1)

var empresa = correo.substring(posicion)

 

if (empresa !='gmail.com'){

 

 

}

En el caso del departamento, sería: 

if (empresa !='educacion.navarra.es'){

 

 

}

 

Y los dos a la vez:

if (empresa !='gmail.com'||empresa !='educacion.navarra.es'){

 

 

}

 

 

Sumar en javascript (números y fechas)

 Con lo fácil que es sumar y, sin embargo, parece que los creadores de Javascript se complicaron la vida por un pequeño detalle.

 

En el código para unir texto con texto o texto con variables se utiliza el signo +

¡¡Vaya!! Después de elegir este símbolo para unir nos encontramos que queremos utilizarlo para sumar y ya está pillado. 

Bueno... he aquí las alternativas:

Con números: 

Se utiliza la función parseInt. Veamos dos ejemplos:

var numero = 8

var encadenamiento = numero +3

El resultado de encadenamiento es: 83

var numero = 8

var suma=parseInt(numero) + parseInt(3)

El resultado de suma es 11

Con fechas también hay que utilizar funciones para conseguir sumar, por ejemplo, días a una fecha

Veamos los dos ejemplos:

var fecha = 21/11/2021

var encadenamiento = fecha+1

El resultado de encadenamiento es: 21/11/2021 1

var fecha = 21/11/2021

var suma = fecha.setDate(fecha.getDate()+1)

El resultado de suma es 22/11/2021

Variables al vuelo mediante código

 Como publicaba en este artículo es fácil crear un código que consiga mandar un pdf con la información enviada desde un formulario.

En dicho código existen dos partes ( creación de variables y asignación de contenido a las partes del pdf) que podemos solucionar en unas pocas líneas aunque las preguntas del formulario sean incontables.

Para aclarar de lo que hablo, os explico de antemano cual el proceso:

- El usuario manda un formulario con una serie de respuestas a diferentes preguntas

- El código crea una variable por cada pregunta del formulario

- En una plantilla tenemos dichas preguntas convertidas en variables

- El código introduce en una copia de la plantilla cada respuesta en cada variable, gracias a replaceText

Me ha tocado hacer este proceso con formularios que tenían alrededor de 50 preguntas, por lo que me he encontrado en la tesitura de declarar 50 variables (una por cada pregunta del formulario) y, por lo tanto, crear otras 50 líneas más de código con replaceText.

Me sugerieron intentar hacerlo con bucles, pero no veía como se podía declarar variables de forma indirecta: 

Y aquí aparece eval() que hace que lo que pongamos entre paréntesis lo corra como código dentro del código

Para poder entenderlo os reto a crear un bucle para crear las 50 variables.

Yo intenté algo así:

// Recogo las 50 preguntas desde la hoja de cálculo con un intervalo con nombre

var todas_las_preguntas =  SpreadsheetApp.getActiveSpreadsheet().getRangeByName('variables').getValues()

// Creo el bucle para recorrerlas e intentar crear una variable por pregunta

for (fila in todas_las_preguntas){

var mivariable&fila

}

// Y me encuentro conque no hay forma de nombrar a cada variable de una forma distinta. La declaración var mivariable&fila da error y no hay forma de que no de (que yo sepa)

Y ahí viene a salvarnos eval()

for (fila in todas_las_preguntas){

var nombre = todas_variables[fila][0]

eval("var "+nombre)

}

Y, así conseguimos crear tantas variables como preguntas tiene el formulario

En mi caso queda así:

var numero
for (fila in todas_variables){
  var nombre =todas_variables[fila][0]
numero = parseInt(fila)+parseInt(1)
eval("var "+nombre+"= pestana.getRange(ultima_fila,"+numero+").getValue()")
}

Aprovecho la declaración de la variable para asignarle el valor de la ultima fila de la pestaña de respuestas del formulario y de la columna donde está la pregunta, así consigo la respuesta de cada pregunta

Eso sí, cuando voy a la parte del código de replaceText, vuelvo a utilizar el bucle para recorrer todas las preguntas y necesito utilizar eval() otra vez

for (cada in todas_variables){

var variable = todas_variables[cada][0]

body.replaceText('<<'+todas_variables[cada][0]+'>>', eval(variable))

}

Me da la sensación de que no me he explicado muy bien. Si alguien quiere aclararse más, que se ponga en contacto conmigo y le enseño la aplicación en vivo.

Enviar pdf por correo desde un formulario

 Este es un ejemplo con todas las partes que necesita el código para mandar un pdf a ciertos grupos.

Falta crear los intervalos con nombre necessarios. El más importante es "ultima_fila"

Además utilizamos dentro de las fórmulas: marca_temporal y grupos_correos

También, para conseguir datos, carpeta_url y plantilla_url

Además, por cada campo que queramos incluir en el pdf, utilizamos "jarduera_actividad" y "maila_curso" que toman toda la columna de cada campo. Si necesitamos más datos en el pdf, deberíamos crear los intervalos con nombre necesarios asociados a cada dato.

function enviar_pdf() {

  // variables básicas

  var herramienta = SpreadsheetApp.getActiveSpreadsheet()

  var ultima_fila = herramienta.getRangeByName('ultima_fila').setValue 

('=filter(row(marca_temporal);marca_temporal=max(marca_temporal))').getValue()

  var pestana = herramienta.getRangeByName('ultima_fila').getSheet()

  // variables de carpeta y de plantilla

  var carpeta_id = herramienta.getRangeByName('carpeta_url')

.getValue().match(/[-\w]{25,}/)

  var plantilla_id = herramienta.getRangeByName('plantilla_url')

.getValue().match(/[-\w]{25,}/)

// Variables que se rellenan en la plantilla, de columna y variable

    var columna_jarduera_actividad = herramienta.getRangeByName('jarduera_actividad')

.getColumn()

    var jarduera_actividad = pestana.getRange(ultima_fila, columna_jarduera_actividad)

.getValue()

    

    var columna_maila_curso= herramienta.getRangeByName('maila_curso').getColumn()

    var maila_curso = pestana.getRange(ultima_fila, columna_maila_curso).getValue() 

 

            

 // Conseguimos la carpeta    y creamos la copia basada en la plantilla

  var carpeta = DriveApp.getFolderById(carpeta_id) 

  var nuevo_documento_id = DriveApp.getFileById(plantilla_id).

 makeCopy('nuevo', carpeta).getId()

  var nuevo_documento = DocumentApp.openById(nuevo_documento_id)

  

// Entramos y cambiamos

  var body = nuevo_documento.getBody()

  

  body.replaceText('<<JARDUERA/ACTIVIDAD>>', jarduera_actividad)

 

    

  

// Cerramos

nuevo_documento.saveAndClose()

// Variables para el correo

var arduraduna = herramienta.getRangeByName('arduraduna').getValue()

var ikasketaburua= herramienta.getRangeByName('ikasketaburua').getValue()

var titulo= herramienta.getRangeByName('titulo').getValue()

var mensaje= herramienta.getRangeByName('mezua').getValue()

var correo

// Convertimos en pdf

var pdf =DriveApp.createFile(nuevo_documento.getAs('application/pdf')

.setName(titulo+' -- '+data_fecha+'.pdf')) 

// Pillamos todos los grupos

var array_grupos = maila_curso.split(",")

Logger.log(array_grupos)

// Mandamos a cada correo

for (grupo in array_grupos){

              correo = pestana.getRange('cada_correo')

.setValue('=iferror(vlookup("'+array_grupos[grupo].trim()+'";grupos_correos;2;false))')

.getValue()

             var opciones ={attachments: pdf ,bcc:arduraduna  ,cc:ikasketaburua}

       MailApp.sendEmail(correo, titulo, mensaje, opciones)

                           }

// Borramos el documento

  pdf.setTrashed(true)

//  DriveApp.getFileById(nuevo_documento_id ).setTrashed(true)

}