ImportRange (Problema y solución)

 La fórmula importrange es muy potente, ya que nos permite unir datos de diferentes hojas de cálculo y, con un poco de imaginación, ofrecer datos a diferentes usuarios sin que ellos tengan acceso a todos los datos. 

La sintaxis de la fórmula es =importrange("";""), donde en la primera parte se pone (entre comillas siempre) la url de la hoja de la que queremos importar datos y, donde en la segunda parte, ponemos el trozo de información o rango de datos que queremos. 

Por ejemplo,    =importrange("https://docs.google.com/spreadsheets/d/xxxxxxxxx";"Datos!A2:F")

Pero, y si cambiamos en la hoja de origen el nombre de la pestaña? O, y si introducimos una nueva columna y la columna A pasa a ser la B?

Probadlo y veréis que la fórmula deja de funcionar y nos obliga a cambiarla.

La solución es "intervalo con nombre". Si al rango Datos!A2:F le damos, por ejemplo un nombre tal como "Primeros_datos", podemos crear la fórmula tal que así:

=importrange("https://docs.google.com/spreadsheets/d/xxxxxxxxx";"Primeros_datos")

Ten en cuenta el detalle que se mantienen las comillas

Permitir al usuario crear un activador para una fecha concreta

 En una aplicación el usuario necesita guardar los datos desde varias hojas de cálculo (cada una de un grupo de alumnos) en una hoja aparte para poder gestionar históricos.

La labor que tenemos es crear un activador para que se haga de forma automática.

Existen activadores que se lanzan en una fecha concreta, pero nosotros queremos que sea no una sola vez, sino una fecha concreta cada año.

Nos encontramos que en google script no existe dicha opción ya que lo máximo que podemos automatizar es por meses.

Además, nos interesa que el usuario no tenga que aprender a crear activadores sino que le facilitemos un par de desplegables (para día y mes concretos) desde la hoja de cálculo.

He aquí el código que lo permite

function crear_o_cambiar_activador() {

  // Borramos todos los activadores

  borrar_todos_activadores()

  //creamos el activador dependiendo del día elegido

//(el mes se define dentro de la función - conseguir_datos-)

  // Como solamente existen activadores cada mes,

//hay que crear la comparación entre mes actual y mes elegido

//dentro de la función a activar

  // Hacemos un condicional y si no se cumple salimos de la función con return  

  var educakoa = SpreadsheetApp.getActiveSpreadsheet()

  var dia = educakoa.getRangeByName('dia').getValue()

ScriptApp.newTrigger('conseguir_datos').timeBased().onMonthDay(dia).create()

}

La función auxiliar que necesitamos para borrar los anteriores activadores es esta:

function borrar_todos_activadores() {

  var triggers = ScriptApp.getProjectTriggers()

  for (var i = 0; i < triggers.length; i++) {

    ScriptApp.deleteTrigger(triggers[i]);

  }

}

Este trozo necesitamos incluirlo en la función a activar, al principio del código

  var mes_actual = educakoa.getRangeByName('mes_actual').getValue()

  var mes_elegido = educakoa.getRangeByName('mes_elegido').getValue()

  if (mes_actual !=mes_elegido){return}

El problema se complica si tenemos otros activadores que no queremos borrar. 

Una función intermedia es esta que solamente borra los activadores basados en el tiempo:

function borrar_activadores_de_tiempo()

  {

    var activadores=ScriptApp.getProjectTriggers()

    for (cada in activadores){

          if (activadores[cada].getEventType()=="CLOCK"){

             ScriptApp.deleteTrigger(activadores[cada])

          }

    }

  }

 (en la función -crear_o_cambiar_activadores- sustituimos la fila - borrar_todos_activadores() -  por - borrar_activadores_de_tiempo()-

Como última mejoría, podemos utilizar esta función para borrar (borra activadores de tiempo que llamen a una función concreto, en este caso -conseguir_datos -

function borrar_activadores_de_tiempo_de_una_funcion_concreta()

  {

    var activadores=ScriptApp.getProjectTriggers()

    for (cada in activadores){

          Logger.log(activadores[cada].getHandlerFunction())

          if (activadores[cada].getEventType()=="CLOCK"){

            if (activadores[cada].getHandlerFunction()=='conseguir_datos'){

                       

            ScriptApp.deleteTrigger(activadores[cada])

          }}

    }

  }

 (en la función -crear_o_cambiar_activadores- sustituimos la fila - borrar_todos_activadores() -  por - borrar_activadores_de_tiempo_de_una_funcion_concreta()-

Crear mensajes con posibilidad de confirmación

Metemos el mensaje en una variable y añadimos a msgBox "Browser.Buttons

var response= Browser.msgBox("¿Está usted segur@ que quiere mandar esta petición de información sobre\\n "+alumno+"?\\n\\n ¿De este grupo: "+grupo+" ?"+'\\n\\n',Browser.Buttons.OK_CANCEL); 

Para gestionar la respuesta, utilizamos la variable tal que así:

if (response=='ok'){ // Enviamos la petición} if (response=='cancel'){return} // Paramos el código

Buscar Fórmulas de validación y ChatGPT

 Nuestro agujero negro en fórmulas de Google Sheets, sorprendentemente, es encontrar fórmulas de regex. Por mucho que leemos la función de cada símbolo, a la hora de crear una máscara de validación nos cuesta mucho llegar a armarla bien. 

Una solución para ello muy cómoda parece ser ChatGPT. (Por lo menos la primera prueba que he hecho ha sido muy satisfactoria. Aquí os la presento:

He entrado en ChatGPT y he planteado esta pregunta:

dime un regex para validacion de formulario google para que los usuarios solamente metan números sin letras

Puedes usar la siguiente expresión regular en un formulario de Google para validar que los usuarios solo ingresen números sin letras:

^[0-9]+$

Esta expresión regular asegura que la cadena solo contenga números del 0 al 9 y que no contenga ninguna letra ni ningún otro carácter especial. Aquí tienes una breve explicación de la expresión regula

^: Coincide con el inicio de la cadena.

[0-9]: Coincide con cualquier dígito del 0 al 9.

+: Coincide con uno o más dígitos.

$: Coincide con el final de la cadena.

Entonces, esta expresión regular asegura que la cadena completa esté compuesta únicamente por números.

De todas formas, luego he probado a buscarlo en Google, sin utilizar ninguna AI y también lo he encontrado. La diferencia ha sido las explicaciones adicionales que me ha dado ChatGPT
¿Qué os parece?

(¿Alguien sabe porqué se me desvanecen las últimas letras en este artículo?)

xxxxxxxxxxxxxxxxxxxxxxxxxx

Cambiar el título de un gráfico dinámicamente

 Gracias a Ben Collins podemos utilizar esta función de código para que el título de un gráfico esté unido al contenido de una celda

function changeChartTitle() {

// apunta al gráfico
// Suponemos, en este caso, que solamente hay un gráfico en la pestaña. En caso necesario, ajustaremos el index [0] al gráfico en cuestión

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Hoja 1');

const chart = sheet.getCharts()[0];

// get the chart title from the cell
const newTitle = sheet.getRange('A1').getValue();

// actualiza el título del gráfico
const chartBuilder = chart.modify();
chartBuilder.setOption('title', newTitle);
const updatedChart = chartBuilder.build();
sheet.updateChart(updatedChart);
}

Aparte del código necesitamos crear un activador que se lance cada vez que se edita la celda

AppendRow: empezar a escribir datos en una columna diferente de la A

 Os presento la situación, primero, y luego la comento:

En un curso aprendimos a recorrer todos los archivos y subcarpetas de una carpeta dada. 

Utilizamos appendRow([]) para escribir nombre, tipo y fecha de creación de cada archivo recorrido en una hoja de cálculo, ya que sabemos que es la forma más rápida y sencilla de escribir valores en una pestaña, sin tener que preocuparnos de definir ni fila ni última fila.

Pero, ya que lo habíamos conseguido, se nos ocurrió que estaría bien que lo escrito mostrará la profundidad del archivo, haciendo "sangrías" (empezando a escribir appendRow en una columna diferente a la primera (la columna A) según el árbol de las carpetas.

Tal que así: 

 

 

Para ello necesitamos añadir a appendRow columnas vacías

Por ejemplo, con este appendRow([nombre,tipo,fecha]), el nombre se escribirá en la columna A

y con este diferente    appendRow(['',nombre,tipo, fecha]), el nombre se escribirá en la columa B, gracias a las comillas vacías que hemos escrito antes de nombre.

 

El problema es que no sabemos de antemano si queremos empezar en la columna B (para una subcarpeta de primer nivel) o en la columna C, o en la D....para carpetas más internas.

Debemos, pués, crear el contenido de appendRow de forma dinámica.

 La solución pasa por saber que estamos ante un array y, por lo tanto, debemos utilizar los métodos para los arrays.(Utilizaremos unshift para incluir al principio del array tantas posiciones vacías como necesitemos, utilizando '  ')

Este es el código:

Creamos un contador para saber en que columna empezamos (en el bucle de las subcarpetas), dependiendo del nivel de la subcarpeta

 // Creamos un array con los datos básicos

 var miarray = [nombre,tipo,fecha]

// Imaginamos que queremos escribir en la columna C, y el contador tiene el valor de 3 

var contador = 3

// Organizamos un bucle que de tantas vueltas como el valor del contador menos uno 

  for (var x=1;x<=contador;x++){

 // Y gracias a este método de arrays incluimos tantos espacios vacíos

// al principio del array como vueltas 

    miarray.unshift('')

  }

 // Ahora, ya preparado el array con sus espacios lo escribimos en la pestaña

  pestana_arbol.appendRow(miarray)

Insertar imágenes desde Drive en un correo (por ejemplo)

 Para insertar imágenes en nuestro correo necesitamos utilizar las opción de correo htmlBody

Por ejemplo:

var mensaje = ' Mi mensaje'
var opciones = {htmlBody: mensaje}

MailApp.sendEmail(correo_cargo, 'Nuevo mensaje', mensaje,opciones)




Antes de estas líneas metemos en el mensaje, mediante lenguaje html la imagen que

queremos que aparezca en el correo

var mensaje = ' Mi mensaje'

var imagen = <img src= > // ahí añadimos la url de la imagen.

mensaje = mensaje + imagen


PERO, CON IMÁGENES DRIVE NO FUNCIONA.

Necesitamos crear una url especial, tal que así:

http://drive.google.com/uc?export=view&id=

Nos falta el id de la imagen, que lo sacaremos del enlace público de compartir la imagen.

En dicho enlace público tenemos el id necesario entre /file/d/ y / view?

Una vez conseguido el Id lo metemos en una variable:

var id_conseguido = 'xxxxxxxxxxxxxxxxxxxxxxxxxxx'

Y con este dato y la url anterior creamos la imagen en html

var imagen = '<img src=http://drive.google.com/uc?export=view&id='+idconseguido+'>'

peeeero, aún no va a funcionar, ya que necesitamos desmarcar una opción dentro del menú de

compartir donde hemos conseguido la url pública:

Haz clic en la rueda de

configuración

Quita la marca

a aplicar actualización de seguridad

Una vez hecho esto, la imagen debería aparecer sin problemas en el correo.

Un ejemplo del trozo de código necesario sería este:

var miid = '0B_iUREgZco_VaWpseFlnNFQzZGFrR1VxRXlRWkg4YWVKN2pj' 

// Se consigue del enlace público a compartir entre file/d/ y /view?

// Hay que darle a configuración y quitar la marca de seguridad

var imagen = '<img src=http://drive.google.com/uc?export=view&id='+miid+'>'

mensaje = mensaje + '<br>'+imagen

var opciones = {htmlBody: mensaje}

MailApp.sendEmail(correo_cargo, 'Nuevo mensaje', mensaje,opciones)