Cómo organizar un intercambio de regalos (amigo secreto) con Google Sheets

por | Nov 23, 2022 | Blog, Google Spreadsheets

Amigo secreto

El famoso “amigo secreto” es una divertida dinámica que podemos organizar entre un grupo de personas para planear un intercambio de regalos. Es muy común que se organice esta actividad para coincidir con tiempos festivos como por ejemplo, la Navidad. La peculiaridad de este intercambio es que nadie debe saber quién le va a dar su regalo, pero todos deben saber a quién le deben dar un regalo, concluyendo en que al final todos terminan con regalo. Hoy, vamos a aprender a organizar el amigo secreto o intercambio de regalos usando solamente Google Sheets, asignando a los participantes e incluso enviando automáticamente los correos.

Registrando a los participantes

Bueno, lo primero que vamos a hacer, es usar un sencillo formulario de Google para recopilar los datos que requerimos. Vamos a preguntar 3 cosas muy sencillas:

  • El nombre
  • El correo
  • Una sugerencia de regalo, o cualquier tipo de ayuda para quien lo escogerá. Por ejemplo, gustos particulares.

Este formulario lo enviaremos a todos los que piensen participar, recordando activar la opción de Crear hoja de cálculo en la pestaña de Respuestas

Este formulario no es obligatorio, porque podemos escribir la información nosotros mismos en la hoja, pero ayuda mucho a quitar trabajo manual innecesario. Ah, y no olvides llenarlo tú también si quieres participar 😀. No te preocupes, cuando terminemos nuestro ejercicio agregaremos una forma sencilla de asegurar que no te arruinarás la sorpresa de quién te da regalo a ti.

Preparando la hoja del amigo secreto

OK, ahora lo que haremos es preparar la hoja donde estará toda la información necesaria para organizar el intercambio del amigo secreto adecuadamente. En la hoja en donde recibimos los datos de las personas interesadas en participar tendremos las columnas que ya habíamos dicho: Nombre, Correo y Sugerencias de regalo, además de la marca temporal del formulario que se agrega automáticamente. Vamos a renombrar esta hoja a Registros.

Primera hoja: Registros

Ahora vamos a hacer una hoja nueva, cuyo nombre será Lista secreta . No te saltes este paso de renombrar las hojas, porque es importante para pasos siguientes donde usamos los nombres exactos de estas hojas, así como el orden de las columnas. En esta hoja de Lista secreta vamos a agregar las columnas necesarias para organizar a todos los participantes del intercambio de regalos, empatados cada uno con su amigo secreto correspondiente y la sugerencia de regalo. Las columnas son muy parecidas a la hoja de Registros: tenemos Nombre y Correo de cada uno. Luego, a quién debe dar regalo esa persona, es decir, su amigo secreto, junto con la sugerencia dada por el mismo. Finalmente, una columna llamada Notificado donde registraremos justamente si ya hemos notificado con esta información a la persona indicada.

Segunda hoja: Lista secreta

Generando la lista secreta del intercambio

Ahora te preguntarás: ¿Y bueno, cómo es que la hoja de Lista secreta se llenará de datos? ¿Qué formula se debe usar para empatar a cada persona con su amigo secreto del intercambio? La respuesta rápida es que no funcionará usar solamente fórmulas. Para respondernos esta pregunta, tenemos que analizar y separar el problema en partes más pequeñas.

Necesitamos

  • Generar una lista aleatoria de los participantes
  • Asignar a cada participante un amigo secreto
  • Asegurarnos de que todos regalan por lo menos una vez, y por lo tanto
  • Asegurarnos de que nadie se regala a sí mismo

Este problema parece complicado, pero realmente tiene una solución bastante sencilla: si después de revolver aleatoriamente la lista de participantes simplemente hacemos una ‘cadena’ entre ellos y además conectamos al primer participante de la lista aleatoria con el último, aseguramos todos los puntos anteriores. El amigo secreto matemáticamente es en esencia una permutación perteneciente a la categoría de los desarreglos.

Bueno, ya sabemos qué hacer, pero ahora ¿cómo hacerlo? Necesitamos que la asignación aleatoria se calcule solo una vez, es decir, simular que cada quién saque un ‘papelito’ con un nombre. Las fórmula de ALEATORIO no nos servirá de mucho porque se recalcula cada vez que cambia algo en la hoja sin que lo podamos controlar, entonces más bien requerimos algo que escriba datos fijos en la hoja solo cuando que se lo indiquemos. Para esto, nos sirve mejor hacer un poco de código de Apps Script. No te preocupes, será sencillo y lo explicaremos todo.

Código para generar una lista de amigo secreto (intercambio de Navidad)

Entonces, vamos a convertir todo este proceso en código. Primero que nada usamos el menú Extensiones – Apps Script para crear nuestro proyecto ligado a la hoja de cálculo y poder ingresar código.

Una vez ahí, usamos este código para generar un menú con el que podremos ejecutar las dos funciones principales de nuestro proyecto: generar la lista secreta y enviar las notificaciones por correo.

function onOpen() {
 SpreadsheetApp.getUi()
  .createMenu("Intercambio")
    .addItem("Generar lista secreta", "generar_lista_secreta")
    .addItem("Enviar correos", "enviar_correos")
    .addToUi();
}
Lenguaje del código: JavaScript (javascript)

Luego, vamos a pegar este código debajo, que es la función principal para generar la lista secreta:

function generar_lista_secreta() {
  var sheet_registros = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Registros");
  var sheet_lista_secreta = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Lista secreta");
  var ultima_fila = sheet_registros.getLastRow();
  // El rango con la información necesaria: 
  // de B2 (columna Nombre) hasta la última fila con datos,
  // abarcando 3 columnas
  var info_range = sheet_registros.getRange(2,2,ultima_fila-1,3);

  // Mapeamos los valores a un arreglo de objetos con sus propiedades correspondientes
  var info_participantes = info_range.getValues().map(
    ([nombre, correo, sugerencia]) => ({nombre, correo, sugerencia})
  );

  // Ordenamos la lista aleatoriamente
  var lista_aleatoria = info_participantes
    .map(value => ({value, sort: Math.random()}))
    .sort((a,b) => a.sort - b.sort)
    .map(({value}) => value);

  // Generamos la lista secreta
  var lista_secreta = lista_aleatoria.map((info_persona, posicion) => {
    return {
      persona: info_persona,
      da_regalo_a: lista_aleatoria[posicion + 1] || lista_aleatoria[0]
    }
  });

  // Limpiamos el rango donde aparecerá la nueva lista secreta
  limpiar_lista_secreta();

  // Escribimos la lista secreta en la hoja correspondiente
  for(i=0; i < lista_secreta.length; i++) {
    sheet_lista_secreta.getRange(i+2,1).setValue(lista_secreta[i].persona.nombre);
    sheet_lista_secreta.getRange(i+2,2).setValue(lista_secreta[i].persona.correo);
    sheet_lista_secreta.getRange(i+2,3).setValue(lista_secreta[i].da_regalo_a.nombre);
    sheet_lista_secreta.getRange(i+2,4).setValue(lista_secreta[i].da_regalo_a.sugerencia);
  }
}

function limpiar_lista_secreta() {
  var rango_a_limpiar = SpreadsheetApp
                        .getActiveSpreadsheet()
                        .getSheetByName("Lista secreta")
                        .getRange("A2:E");
  // .clearContent() limpia solo los valores sin borrar el formato
  rango_a_limpiar.clearContent();
}
Lenguaje del código: JavaScript (javascript)

Vamos a explicarlo un poco. Primero, separamos las dos hojas que tenemos, cada una en su variable: Registros y Lista secreta. De la hoja de Registros necesitamos saber siempre cuál es la última fila con datos y con esa información guardamos el rango desde la segunda fila y segunda columna (osea, B2, porque no incluimos los encabezados) hasta esa última fila con datos y abarcando 3 columnas, para incluir Nombre, Correo y Sugerencia.

Después mapeamos esa información a un arreglo de objetos con las propiedades correspondientes a la información original. Esto solamente es convertir una estructura de tabla a una estructura de objetos estilo JSON para poderlos manipular fácilmente con las funciones de JavaScript.

Luego generamos una lista nueva en la que este mismo arreglo de datos tendrá un orden aleatorio. Para eso es la función Math.random() combinada con la función sort. Fíjate también que nos estamos ayudando mucho del método de map, con el que podemos aplicarle una acción a cada elemento del arreglo fácilmente.

Lo siguiente es generar la lista secreta, en la que crearemos esta cadena de amigos secretos. Usamos de nuevo map, y a cada persona le asignamos el siguiente elemento del arreglo de la lista aleatoria como “da regalo a”, osea su amigo secreto. Fíjate aquí cómo estamos resolviendo el caso especial de asignar el último elemento del arreglo con el primero: si el siguiente elemento que estemos considerando no existe, es decir, posicion + 1 no regresa nada, le asignamos justamente el elemento de posición 0, osea el primero.

Después, llamamos a una función propia que limpiará el rango en el que trabajaremos. Esto es necesario para que no mezclemos información de ejecuciones anteriores, por si se modifica el número de participantes y quieres volver a empezar.

Finalmente, escribimos en la hoja de Lista secreta toda la información del arreglo lista_secreta en las columnas que les corresponden: el nombre y el correo de quien se debe notificar, y el nombre y la sugerencia del amigo secreto a quien debe darle un regalo.

Entonces guardamos nuestro código y ejecutamos el menú personalizado por primera vez para darle permisos a nuestro proyecto para modificar la hoja. Ejecutamos de nuevo nuestro menú, y podremos ver el resultado: se han asignado correctamente todos los amigos secretos del intercambio de regalos, junto con las sugerencias correspondientes.

Participar en el amigo secreto siendo el organizador

Obviamente esta información se supone que es secreta (por eso le pusimos de nombre Lista secreta), y hace rato te prometí que tú también podrías participar aunque fueras quien organiza, y te cumpliré la promesa 😉. La solución es muy sencilla: solamente tienes que cambiar el formato a todas las columnas, excepto la de Notificado, para que tengan el mismo color de fondo que el color de la letra, por ejemplo, con fondo negro. La información seguirá ahí si seleccionas la celda, y si eres demasiado curiosa o curioso, te puedes arruinar la sorpresa, pero si no te aguantas a mirar, ya nadie te puede ayudar. 😅 🙊

Código para enviar notificaciones de amigo secreto por correo

Lo último que tenemos que hacer es enviarle un correo a cada persona con la información que le corresponda sobre su amigo secreto y la sugerencia. Recordarás que esto lo cubrimos anteriormente en otra guía, lo único que cambia un poco es el código, que actualicé para usar JavaScript más moderno. La función es la siguiente, que agregamos debajo de lo que ya tenemos:

function enviar_correos() {
  var sheet_lista_secreta = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Lista secreta");
  var sheet_lista_secreta_range = sheet_lista_secreta.getRange("A2:E");

  // Mapeamos los valores a un arreglo de objetos con sus propiedades
  var lista_secreta = sheet_lista_secreta_range.getValues().map(
    ([nombre, correo, da_regalo_a, sugerencia, notificado]) => 
    ({nombre, correo, da_regalo_a, sugerencia, notificado})
  )
  // filtramos nombres vacíos
  .filter(d => (d.nombre == "" ? false : true));


  // Para cada elemento de la lista secreta enviamos un correo
  lista_secreta.forEach((intercambio,index) => {
    if(intercambio.notificado != "Notificado" && intercambio.correo != "") {
      var mensaje = `¡Hola ${intercambio.nombre}! 
      Tu amigx secretx para el intercambio es ${intercambio.da_regalo_a}
      La sugerencia para su regalo es...
      ${intercambio.sugerencia}
      `
      var asunto = "Abre este correo para conocer a tu amigx secretx!";
      // Enviamos el correo
      MailApp.sendEmail(intercambio.correo, asunto, mensaje);
      // Borra este log cuando termines tus pruebas
      Logger.log("Enviando correo: " + JSON.stringify(intercambio) + " " + mensaje);
      // Escribimos en la hoja la confirmación de la notificación
      sheet_lista_secreta.getRange(2 + index,5).setValue("Notificado");
      // Recomendado por Google para obligar a la hoja a actualizarse
      SpreadsheetApp.flush();
    }
  })
}
Lenguaje del código: JavaScript (javascript)

Brevemente lo explicamos. Primero obtenemos el rango con la lista secreta, que va de A2 a E, osea, ahora sí incluimos la columna de Notificado. Luego mapeamos los valores de ese rango a un arreglo de objetos llamado lista_secreta, con propiedades que corresponden a las columnas, filtrando las celdas que estén vacías. Después, vamos uno por uno de los elementos del arreglo en un ciclo para realizar varias acciones. Revisamos si esa persona no ha sido notificada todavía y si el campo de su correo no está vacío. Si esto se cumple, construimos el mensaje con la información correspondiente. Después usamos la clase MailApp con el método sendEmail con la información del correo, el asunto y el mensaje para enviar el correo. Y finalmente, en la fila correspondiente y la columna 5 (osea, la de Notificado) escribimos en la hoja la confirmación de que la persona ya fue notificada.

Si ejecutamos la función desde el menú, veremos que se pone a trabajar nuestra hoja, enviando los correos y llenando la columna de Notificado. ¿Está genial no? 😱😀

Te comparto el código completo:

function onOpen() {
 SpreadsheetApp.getUi()
  .createMenu("Intercambio")
    .addItem("Generar lista secreta", "generar_lista_secreta")
    .addItem("Enviar correos", "enviar_correos")
    .addToUi();
}

function generar_lista_secreta() {
  var sheet_registros = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Registros");
  var sheet_lista_secreta = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Lista secreta");
  var ultima_fila = sheet_registros.getLastRow();
  // El rango con la información necesaria: 
  // de B2 (columna Nombre) hasta la última fila con datos,
  // abarcando 3 columnas
  var info_range = sheet_registros.getRange(2,2,ultima_fila-1,3);

  // Mapeamos los valores a un arreglo de objetos con sus propiedades correspondientes
  var info_participantes = info_range.getValues().map(
    ([nombre, correo, sugerencia]) => ({nombre, correo, sugerencia})
  );

  // Ordenamos la lista aleatoriamente
  var lista_aleatoria = info_participantes
    .map(value => ({value, sort: Math.random()}))
    .sort((a,b) => a.sort - b.sort)
    .map(({value}) => value);

  // Generamos la lista secreta
  var lista_secreta = lista_aleatoria.map((info_persona, posicion) => {
    return {
      persona: info_persona,
      da_regalo_a: lista_aleatoria[posicion + 1] || lista_aleatoria[0]
    }
  });

  // Limpiamos el rango donde aparecerá la nueva lista secreta
  limpiar_lista_secreta();

  // Escribimos la lista secreta en la hoja correspondiente
  for(i=0; i < lista_secreta.length; i++) {
    sheet_lista_secreta.getRange(i+2,1).setValue(lista_secreta[i].persona.nombre);
    sheet_lista_secreta.getRange(i+2,2).setValue(lista_secreta[i].persona.correo);
    sheet_lista_secreta.getRange(i+2,3).setValue(lista_secreta[i].da_regalo_a.nombre);
    sheet_lista_secreta.getRange(i+2,4).setValue(lista_secreta[i].da_regalo_a.sugerencia);
  }
}

function limpiar_lista_secreta() {
  var rango_a_limpiar = SpreadsheetApp
                        .getActiveSpreadsheet()
                        .getSheetByName("Lista secreta")
                        .getRange("A2:E");
  // .clearContent() limpia solo los valores sin borrar el formato
  rango_a_limpiar.clearContent();
}

function enviar_correos() {
  var sheet_lista_secreta = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Lista secreta");
  var sheet_lista_secreta_range = sheet_lista_secreta.getRange("A2:E");

  // Mapeamos los valores a un arreglo de objetos con sus propiedades
  var lista_secreta = sheet_lista_secreta_range.getValues().map(
    ([nombre, correo, da_regalo_a, sugerencia, notificado]) => 
    ({nombre, correo, da_regalo_a, sugerencia, notificado})
  )
  // filtramos nombres vacíos
  .filter(d => (d.nombre == "" ? false : true));


  // Para cada elemento de la lista secreta enviamos un correo
  lista_secreta.forEach((intercambio,index) => {
    if(intercambio.notificado != "Notificado" && intercambio.correo != "") {
      var mensaje = `¡Hola ${intercambio.nombre}! 
      Tu amigx secretx para el intercambio es ${intercambio.da_regalo_a}
      La sugerencia para su regalo es...
      ${intercambio.sugerencia}
      `
      var asunto = "Abre este correo para conocer a tu amigx secretx!";
      // Enviamos el correo
      MailApp.sendEmail(intercambio.correo, asunto, mensaje);
      // Borra este log cuando termines tus pruebas
      Logger.log("Enviando correo: " + JSON.stringify(intercambio) + " " + mensaje);
      // Escribimos en la hoja la confirmación de la notificación
      sheet_lista_secreta.getRange(2 + index,5).setValue("Notificado");
      // Recomendado por Google para obligar a la hoja a actualizarse
      SpreadsheetApp.flush();
    }
  })
}
Lenguaje del código: JavaScript (javascript)

¡Y listo! Ahora podrás organizar el amigo secreto o el intercambio de Navidad entre tus amigos o con tus compañeros de oficina fácilmente. Aunque hay páginas que ya hacen esto por ti, a mí me gusta más hacerlo así porque no tengo que obligar a todos a dar su información personal a un sitio desconocido. Además, seguro aprendiste mucho en el proceso, y ese es el mejor regalo que te puedo dar 😉🎁

Plantilla terminada para descargar

Plantilla para Organizar un intercambio de regalos (amigo secreto) con Google Sheets

Guía en video

Si tienes dudas puedes ver esta misma guía explicada en video

¡Espero que te haya sido útil esta guía! No olvides seguirnos en YouTube para más tips, trucos y guías de productividad.

Si te interesa aprender más sobre Google Sheets, checa nuestro Curso: Aprende Sheets en el que podrás elevar tu aprendizaje de hojas de cálculo al nivel experto.

Aprende Sheets: Curso práctico de Hojas de cálculo de Google
Aprende Sheets: Curso práctico de Hojas de cálculo de Google

Deja un comentario