Actualizar opciones de desplegable en un formulario

Es importante que las opciones que existan en una pregunta desplegable de un formulario sean idénticas a las que utilicemos en la hoja de cálculo que reciba los datos, en caso de querer hacer filtros o estadísticas, o lo que se nos ocurra.

Hasta ahora, para que no hubiese problemas, aconsejabamos copiar la lista de la hoja de cálculo y pegarla en el formulario.

En el caso de una lista de alumnos de ciertos institutos, al haber alrededor de 1000, teníamos un problema añadido, ya que, al actualizar la lista de un curso escolar a otro, debíamos borrar primero los alumnos del anterior curso y, solo después, pegar la nueva lista.

Bueno, gracias al siguiente código, podemos hacerlo todo en un clic: limpiar, copiar y pegar, de forma automática:

function copiar_a_formulario(){
var herramienta = SpreadsheetApp.getActiveSpreadsheet()
// Las tres siguientes variables las utilizamos para definir las dimensiones de la lista a actualizar
// Conseguimos la columna donde está la lista
var column_lista =  herramienta.getRangeByName('tablas_lista').getColumn()
// Conseguimos la pestaña donde está la lista
var tablas = herramienta.getRangeByName('tablas_lista').getSheet()
// Conseguimos la última fila de la lista
var ultima_fila_lista = tablas.getLastRow()
// Gracias a los datos anteriores conseguimos los valores de la lista
var lista = tablas.getRange(1, column_lista, ultima_fila_lista, 1).getValues()
// Entramos en el formulario asociado a la hoja de cálculo
var form_URL =SpreadsheetApp.getActiveSpreadsheet().getFormUrl()
var form = FormApp.openByUrl(form_URL)
// Conseguimos el ID de la pregunta en cuestión
var Id_formacion = '3.64746838E8'  // este ID lo conseguimos con la función inferior
// Actualizamos la lista de opciones                    form.getItemById(Id_formacion).asMultipleChoiceItem().setChoiceValues(lista)                     
}

Esta pequeña función nos sirve para saber el ID de la pregunta que queremos actualizar
function conseguir_id(form){
var herramienta = SpreadsheetApp.getActiveSpreadsheet()
var form_URL =herramienta.getFormUrl()
var form = FormApp.openByUrl(form_URL)
// Conseguimos todas las preguntas
var todos = form.getItems()
// Gracias a este bucle conseguimos los IDs de cada pregunta
for (cada_uno in todos){
Logger.log('El ID de la pregunta '+todos[cada_uno].getTitle()+' es '+todos[cada_uno].getId())
}

}

Conseguir ID de carpeta desde URL

Os presento una pequeña función que extrae la ID de una carpeta haciendo que el usuario introduzca toda la URL.

Antes utilizaba los nombres de carpeta, que parece la opción más sencilla para el usuario, pero tuvimos muchos problemas cuando teníamos carpetas con el mismo nombre. Os recomiendo utilizar siempre el ID tanto de carpetas como de archivos.

Bueno... esta es la función:

function conseguir_ID_carpeta (){
var URL = Browser.inputBox('Pega la URL de la carpeta','Debes estar dentro de la carpeta (Escribe -Drive- para toda la unidad)',Browser.Buttons.OK_CANCEL )
if (URL=="Drive"){var ID=DriveApp.getRootFolder().getId()}else{
var ID =  URL.match(/[-\w]{25,}/)}
return ID

}

Referencia a la última entrada desde un formulario

En el caso que queremos calcular algo mediante código cuando alguien manda un formulario, nuestra primera idea es conseguir los datos de dicha última contestación apelando a la última fila de los datos que se almacenan en la pestaña "Respuestas de formulario", entendiendo que en dicha última fila se van a almacenar los datos de esta última contestación.

Repito a propósito la palabra ULTIMA, ya que hay situaciones que Google no funciona así. Suele pasar, cuando borramos o hacemos alguna otra operación en esta pestaña, que Google empieza a almacenar las contestaciones, NO en la ULTIMA fila, sino en filas intercaladas entre los datos anteriores.

Por lo tanto, ya no podemos fiarnos de utilizar getLastRow() para conseguir el dato más reciente.

Una solución es aprovechar la columna de marca temporal y conseguir la fila del registro que tenga la marca temporal mas reciente, osea la última contestación mandada.

Esto lo podemos conseguir con una fórmula como esta:
=filter(row('Respuestas de formulario'!F:F);'Respuestas de formulario'!A:A=max('Respuestas de formulario'!A:A)) 

La clave está en filtrar la fila (row) que en la columna de la marca temporal (normalmente la A) coincida con la fecha máxima (max) de dicha marca, o lo que es lo mismo, la fecha más reciente.

Referencias a pestañas y/o celdas en código

No es inusual que en el código metamos líneas tal como esta: 

var herramienta = SpreadsheetApp.getActiveSpreadsheet()
var pestana = herramienta.getSheetByName('guardias')
var dato = pestana.getRange('A3').getValue()

 Estas dos líneas funcionarán solamente mientrás no se cambie el nombre de la pestaña y/o mientrás no se meta ninguna fila o columna antes de la fila 3 o de la columna A.

Pero en cuanto un usuario cambie el nombre de la pestaña... el código dejará de funcionar.
Y,  metiendo una fila nueva antes de la 3, el código dará otro valor.

Ante este problema, tenemos la solución:
En vez de utilizar getSheetByName
crearemos un intervalo con nombre llamado "Educa_profesor" en la pestaña que queremos referenciar y en el código utilizaremos esta línea para conseguir la pestaña
herramienta. getRangeByName('Educa_profesor').getSheet()
Con esto logramos que no nos afecte el cambio de nombre de pestaña.

En el caso de pestana.getRange('A3').getValue() podemos utilizar el mismo proceso, es decir, crear un intervalo con nombre que apunte a A3 y en el código:

herramienta. getRangeByName('rangoA3').getValue()


========================================================

========================================================

En mi caso tengo un problema añadido ya que utilizo fórmulas dentro del código tal que así:

  pestana.getRange('I11').setValue('=sort(unique('guardias'!A:A))') 

Si cambiasen el nombre de la pestaña guardias o metiesen una columna nueva antes de A, la fórmula escrita daría error al no encontrar dicho nombre de pestaña.

Para evitarlo la solución es un poco más larga.
Nos apoyaríamos también en un intervalo con nombre que cogiese toda la fila A de la pestaña guardias y escribiríamos esta línea de código:

  var mi_columna = "'"+herramienta.getRangeByName('columnaAA').getSheet().getName()+"'!"+herramienta.getRangeByName('columnaAA').getA1Notation()
Y la línea de escribir la fórmula quedaría así:

  pestana.getRange('I11').setValue('=sort(unique('+mi_columna+'))')

Problemas con fechas (Hoy no es igual a hoy)

Más problemas con fechas.
Queremos comparar una fecha con hoy y así hacer un condicional para que si hoy es la fecha que queremos haga algo. Pues... curiosamente pasa esto:

if (mifecha > hoy){logger.log('funciona')}
if (mifecha < hoy){logger.log('funciona')}

Pero si ponemos
if (mifecha == hoy){logger.log('funciona?')}
nunca entra en el condicional, ya que para ser igual, al llevar "hoy" la hora, el minuto, el segundo y el milisegundo nunca llega a ser igual a mi fecha (por ejemplo, a 22/02/2019)

Un buen truco:

Definimos hoy así:
var Hoy = new Date().setHours(0,0,0,0);

Problemas con fechas (Más soluciones)

Otro problema que me he encontrado con fechas es que queremos utilizar una fecha como argumento de un filter y no funciona.  Saber porqué pasa, a veces, se me hace imposible y, además, después de encontrar la solución, me quedan pocas ganas de seguir investigando. Por lo que solamente os voy a dar la solución.

Probando y probando, he descubierto que tiene que ver con el formato de la celda, que si le quitamos dicho formato funciona. Por lo tanto, desde el código, vamos a quitarle el formato, vamos a conseguir el resultado de la fórmula filter y, después, le volvemos a colocar el formato.

1.  Quitamos formato --->    pestana_asignaciones.getValue('b2:b').setNumberFormat('@')
2.  Escribimos la fórmula ----> .setValue('=filter(asignaciones!e:e;asignaciones!b:b="25/12/2018)') 
3. Volvemos a poner el formato ---->
   pestana_asignaciones.getValue('b2:b').setNumberFormat('d/MM/yyyy')

Al editar una celda

Podemos crear una función que se ponga en marcha cada vez que se cambia algo. Necesitaremos entrar en "Activadores del proyecto activo" y configurar un activador que corra cuando se edita algo.

En la siguiente función vemos como se puede recoger información sobre el cambio

function cambios(e) {
// Información que podemos pillar cuando cambiamos algo en una celda

// 1.- La hoja de cálculo 
        var herramienta = e.source
// 2.- El valor que hemos escrito nuevo  
        var cambio = e.value
// 3.- El valor antiguo  
        var anterior = e.oldValue
// 4.- El correo de quien lo ha cambiado  
        var user = e.user
}

Con la primera (la hoja de cálculo --- e.source) podremos acceder a muchos más datos utilizando esta combinación e.source.getActiveRange()
Por ejemplo:
e.source.getActiveRange().getColumn()
e.source.getActiveRange().getRow()

Líneas demasiado largas en código

Creo que acabo de descubrir como partir líneas largas de código en varias cortas
En otros lenguajes, cuando se acaba una declaración se pone alguna marca para dejarlo claro.
Tanto en Javascript como en PHP se utiliza el punto y coma.

En  Google Script no es necesario acabar las líneas de ninguna manera. Pero si admite el punto y coma. Pero, claro, si no es necesario... ¿Para que escribir de más?

Bueno, pues resulta que puedes jugar con dicho punto y coma para separar en varias filas cortas una declaración.

Un ejemplo:
function larga() {
  var herramienta = SpreadsheetApp.
                    getActiveSpreadsheet().
                    getSheetByName('datos').
                    getRange('F1').
                    setNumberFormat('@');  // Al poner aquí el punto y coma conseguimos que una todas las anteriores líneas
 var herramienta2 =   SpreadsheetApp.
                    getActiveSpreadsheet().
                    getSheetByName('datos').
                    getRange('F1').getValue();  // Aquí va otro punto y coma          
  Logger.log(herramienta2)  //Aquí se nos ha olvidado, pero no importa
 
}

Resumiendo: Si necesitamos partir una línea larga de código, bastará con poner al final, en la última línea resultante, un punto y coma.

Problemas con la marca temporal y otras fechas

El caso es que, mediante código, recojo la marca temporal y quiero mediante las funciones de fecha de javascript utilizar las partes de la fecha para otro propósito.

Juraría que antes funcionaba sin problemas, pero, ahora me da error y me dice que no encuentra getDate(). He descubierto que la marca temporal me la almacena como String y, supongo, que por eso no funciona getDate().

Después de probar algunas cosas, la solución que mejor me funciona para este caso es la siguiente:

var marca_temporal = e.values[0]

var trozos = marca_temporal.split(" ")  // Hago dos trozos, la fecha y las horas,minutos,segundos.
var fecha = trozos[0].split("/") // Cojo el primer trozo y lo vuelvo a dividir

var dia = fecha[0]
var mes = fecha[1]
var aino = fecha[2]

var tiempo = trozos[1].split(":") // Cojo la segunda parte y la vuelvo a dividir
var hora = tiempo[0]
var minutos= tiempo[1]
var segundos= tiempo[2])

Y así tengo todas las partes de la marca temporal cada una en su variable.

En el formulario tengo también un campo que recoge una fecha (sin horas, minutos, segundos)

En este caso me basta con:

var mifecha = e.values[3]

var cachos = mifecha.split("/")
var dia = cachos[0]
var mes = cachos[1]
var aino = cachos[2]

Macro para crear activadores del código

En nuestro trabajo hacemos herramientas con hojas de cálculo que luego distribuimos por centros escolares.
En el proceso de instalar la herramienta en cada centro, como es lógico, creamos una copia de la herramienta. En dicha copia está todo lo necesario para que funcione salvo los activadores del código, que hay que volver a instalarlos.


Hasta ahora, gracias a unos apuntes le mostrábamos a los usuarios como hacerlo de forma manual.
En este artículo, sin embargo, mostramos como hacer este proceso mucho más sencillo para el usuario final.


Necesitamos dos funciones:

function instalar(){
// Apuntamos a la herramienta
var herramienta = SpreadsheetApp.getActiveSpreadsheet()
// Apuntamos a una pestaña especial donde guardamos variables. En este caso guardaremos una variable para que solamente se instalen una vez los activadores
var variables= herramienta.getRangeByName('variables').getSheet()
// Si no está guardada la variable, significa que es la primera vez que el usuario utiliza esta función
if (variables.getRange('G1').getValue() !="fuera"){

// Las siguientes líneas muestra diferentes activadores creados (tiempo, enviar formulario, editar). En la documetación de Google Script encontrarás más información. Después del método newTrigger se escribe el nombre de la función que queremos activar.
ScriptApp.newTrigger('avisar_ausencias').forSpreadsheet(herramienta).onFormSubmit().create()
ScriptApp.newTrigger('mandar_formulario_feedback').forSpreadsheet(herramienta).onFormSubmit().create()
ScriptApp.newTrigger('grabar_asignacion2').forSpreadsheet(herramienta).onEdit().create()
ScriptApp.newTrigger('mandar_correo_con_guardia_hecha').timeBased().everyHours(1).create()
// Una vez creados, mostramos mensaje y damos la posibilidad de que no vuelva a correr esta función gracias a la variable borrar
var borrar = Browser.msgBox('Activadores instalados. \\n\\nDale al botón de aceptar para que no vuelva a salir este mensaje.',Browser.Buttons.OK_CANCEL)
// Si el usuario dice que sí , escribimos la palabra "fuera" y, así, hacemos que no vuelva a entrar en este condicional
if (borrar=='ok'){
variables.getRange('G1').setValue('fuera')

}

}
else { // En la segunda vez que entre, la variable está ya guardada y mostramos este mensaje

Browser.msgBox('Ya tienes instalados todos los activadores.\\n\\n¡No hace falta que vuelvas a hacerlo!')
} 
}

// Para avisar al usuario de que debe correr el código de activadores debemos incluir en la función onOpen (o en cualquiera que corra al abrir) estas líneas. El condicional hace que solamente aparezca la primera vez que se abre la herramienta

var herramienta = SpreadsheetApp.getActiveSpreadsheet()
var variables= herramienta.getRangeByName('variables').getSheet()
if (variables.getRange('G1').getValue() !="fuera"){
  Browser.msgBox('Para que el código funcione, debes activar el código. \\n\\n Vete a -herramientas-Macros \\ny utiliza -Activar código')
  }




Dos notas:

1. Para que el usuario pueda "instalar" los activadores sin entrar en la zona de secuencias de comandos, podemos incluir dicha función entre las macros y así ponerselo fácil.
2. Por suerte, el activador "al abrir" si que se hereda en la copia, por lo que no hace falta activarlo :)