Cómo automatizar actualizaciones de correo electrónico en una hoja de Google (Google Scripts)

Tl; dr: Cómo automatizar las actualizaciones de correo electrónico mediante el envío de correo electrónico desde una hoja de Google con una explicación paso a paso del código

Mucha gente usa Google Sheets para colaborar en una gran cantidad de temas. Muchos lo usan para administrar sus KPI. ¿Sabes lo que nadie sabe cómo hacer que sería realmente genial si fuera posible?

¡Envío de una actualización por correo electrónico directamente desde Sheets!

Sí, es cierto que puede hacerlo y personalmente, no tenía idea de que podría hacer eso hasta este fin de semana. Lo usé para hacer una dulce hoja de seguimiento de OKR, PPP y KPI que luego enviaría una actualización de las PPP semanalmente, en la fecha y hora que elegí.

Te recomiendo que lo obtengas para que puedas ver cómo funciona para poner todo esto en perspectiva.

¡Puedes obtenerlo aqui!

Ahora, esta será una clase funcional, así que entremos a las cosas.

Tenga en cuenta que no sé cómo codificar. Tuve que enseñarme a mí mismo cómo hacer esto. Así que si no puedes codificar, créeme, ¡puedes resolverlo como lo hice!

La puesta en marcha

Configuración de hoja

Abre Google Sheets.

Nombra la hoja "Sistema".

Desea agregar algunos puntos de datos ahora.

En la primera fila escriba: Dirección de correo electrónico, Mensaje, Persona, Conteo de correo electrónico y escriba 1 en E.

En la segunda fila agregue los datos

Debe tener un aspecto como este:

Déjame explicarte lo que está pasando aquí.

  • Dirección de correo electrónico: escriba las direcciones de correo electrónico (o vincúlelas a correos electrónicos en otra hoja como lo hice yo)
  • Mensaje: Hice una fórmula súper compleja para formatear un correo electrónico como quiero
  • Persona: Enlace el nombre de la persona que recibe el correo electrónico para que el Mensaje lo dirija a esa persona en el cuerpo del correo electrónico
  • Conteo de correo electrónico: D1 es texto, no hace nada. E2 es donde tengo una función de conteo que me dice cuántas personas deben recibir un correo electrónico. ¡Esto es importante ya que el código que vamos a usar hace referencia!

Por supuesto, puede configurar su hoja de manera diferente y escribir su propio código, pero le voy a enseñar cómo hacer el ejemplo exacto que he hecho.

Configuración de script

Hay una sección oculta (al menos para mí) llamada "Editor de secuencias de comandos". La encontrará en Herramientas en el menú.

Ábrelo y ahí es donde escribes el guión. Solo habrá 3 líneas cuando la abra por primera vez.

Es aquí donde va a copiar y pegar el código. Veamos el guión ahora para comprender lo que hace.

La secuencia de comandos

Aquí hay una captura de pantalla para que veas cómo debería verse:

Y ahora aquí está el código para que pueda pegarlo.

function sendEmails () {// Obtener la hoja donde están los datos, en la hoja 'system' var sheet = SpreadsheetApp.getActiveSpreadsheet (). getSheetByName ("Sistema") var startRow = 2; // Primera fila de datos para procesar ya que hay una fila de encabezado var numRows = sheet.getRange (1,5) .getValue (); // El número de filas para procesar se establece mediante una fórmula que cuenta las filas // Obtenga el rango de celdas A2: B6 donde los correos electrónicos y mensajes son var dataRange = sheet.getRange (startRow, 1, numRows, 2) // Obtener valores para cada fila en el Rango para ingresar al sistema de correo var data = dataRange.getValues ​​(); // Esto procesa los correos electrónicos que desea enviar (i en datos) {var row = data [i]; var emailAddress = fila [0]; // La primera columna es la dirección de correo electrónico var message = row [1]; // La segunda columna es el mensaje var subject = "READ: Weekly PPP update"; // Este es el asunto del correo electrónico // Esto analiza los datos del correo electrónico para enviar MailApp.sendEmail (emailAddress, asunto, mensaje); }}

¿Qué son estas cosas?

¿Ves esos "//"? Estos se utilizan para comentar. Usar // significa para una computadora "ignorar estas cosas, es para humanos insignificantes".

He utilizado este sistema de comentarios para explicarle cada línea de código. Pero vamos a pasar por eso

El código explicado

función sendEmails () {
  • Esto crea un nombre para el script
// Obtenga la hoja donde están los datos, en la hoja 'sistema'
var sheet = SpreadsheetApp.getActiveSpreadsheet (). getSheetByName ("Sistema")
  • Esto define de dónde provienen los datos. Viene de ti Hoja del sistema, ¿verdad? Así que este es el bit interesante: .getSheetByName ("Sistema")
  • Usted ve al final que dice "Sistema" y le dije que nombrara la hoja Sistema. Es por eso. Si ve un script de plantilla en Google, siempre se extrae de la "hoja activa", lo cual está bien si solo tiene una hoja y realiza sus actualizaciones manualmente. Pero tendremos varias hojas y un sistema automatizado. Así que tenemos que decirle a la computadora dónde EXACTAMENTE queremos que haga el trabajo de bruja
  • Entonces, si desea que su hoja se llame de otra manera, simplemente cámbiela a magia, como esta .getSheetByName ("magia")
startRow = 2; // Primera fila de datos para procesar ya que hay una fila de encabezado
  • Mira la hoja escribiste correo electrónico, nombre, etc. en la fila 1. Eso no es útil
  • Entonces le dice al guión que comience en la segunda fila, ya que ahí es donde están las cosas buenas. Si sus correos electrónicos estuvieran en la fila 3, escribiría la fila 3
var numRows = sheet.getRange (1,5) .getValue (); // El número de filas para procesar se establece mediante una fórmula que cuenta las filas
  • Esto es un poco más funky. En la hoja que insertó el conteo de correos electrónicos en E1
  • La forma en que los scripts piensan sobre las referencias de celda es Fila Columna. Entonces E1 es 1, 5. Fila uno, columna 5.
  • Así que mira aquí: sheet.getRange (1,5)
  • Esto dice mira en E1. Si su cuenta de correo electrónico estaba en A1, escribiría sheet.getRange (1,5)
// Obtenga el rango de celdas A2: B6 donde están los correos electrónicos y mensajes
var dataRange = sheet.getRange (startRow, 1, numRows, 2)
  • Entonces, ¿recuerdas el orden Fila, Columna? La forma más larga o escribir esto en esta función es: Fila, Columna, Número de filas, Número de columnas
  • Esta función le dice a los scripts el área exacta donde extraen los datos para analizar y enviar correos electrónicos
  • Solo necesitamos el correo electrónico y el mensaje para el automatizador de correo electrónico. Así que los scripts necesitan que se les diga eso. La forma en que lo hacemos es decirle dónde comenzar y dónde terminar
  • sheet.getRange (Row, Column, Number of Fows, Number of Columns) se convierte en sheet.getRange (startRow, 1, numRows, 2)
  • ¿Por qué hay nombres aquí? ¡Bien definimos startRow y numRows arriba! 1 es la primera columna y 2 significa las dos primeras columnas donde se encuentran el correo electrónico y el mensaje. Sí, podrías hacerlo de manera diferente. Tener una obra de teatro?
// Obtener valores para cada fila en el Rango para ingresar al sistema de correo
datos var = dataRange.getValues ​​();
  • Esto solo le dice al script que robe los datos y los almacene como el nombre "datos"
// Esto procesa los correos electrónicos que desea enviar
para (i en datos) {
var fila = datos [i];
  • Esto inicia el programa (básicamente)
var emailAddress = fila [0]; // La primera columna es la dirección de correo electrónico.
  • Dice que el correo electrónico está en la primera fila (0 es la primera línea en lo que respecta a nuestra matriz)
mensaje var = fila [1]; // La segunda columna es el mensaje
  • Dice que el mensaje está en la segunda columna.
var subject = "READ: Actualización semanal de PPP"; // Este es el asunto del correo electrónico
  • ¡Aquí puede establecer el nombre del asunto del correo electrónico que enviará!
  • Si desea enviar un correo electrónico con el tema "echa un vistazo a este blog sobre cómo automatizar hojas", debe ingresar esto
  • var subject = "mira este blog sobre cómo automatizar Hojas";
// Esto analiza los datos para enviar el correo electrónico
MailApp.sendEmail (dirección de correo electrónico, asunto, mensaje);
}
}
  • Esto establece la estructura en una función de correo electrónico (MailApp) que hace las cosas inteligentes
  • Le hemos dicho que queremos usar la dirección de correo electrónico, el asunto y el mensaje.
  • El material "for (i in data) {" al comienzo del proceso crea un bucle para enviar un correo electrónico a cada persona (la función de recuento indica cuántos bucles se ejecutan, por ejemplo, 2 significaría dos bucles y dos correos electrónicos)

Ese es el código con el que necesitas jugar.

Sé que esto podría ser mucho para entenderlo, pero puedes hacerlo. Si necesita cambiar algún código para hacer otra cosa, consulte la documentación. Sí, parece que trata de entender una palabra latina diciéndole que lea la definición en un diccionario latino, pero es posible

La documentación está aquí: https://developers.google.com/apps-script/reference/mail/

Automatizar el envío de correos electrónicos.

Ahora, pasé años buscando el código de Google para descubrir cómo enviar correos electrónicos en la fecha y hora que quería. Lo hice ... luego me di cuenta de que quería que esto funcionara en cualquier país ... ¡así que la siguiente misión era descubrir cómo ajustar la hoja a la zona horaria de cada persona! Me di cuenta de eso ... ¡y luego encontré una manera realmente tonta y simple de hacerlo! Dios mío!

Olvidemos ese código ... Solo haz esto.

Si desea cambiar el horario o el día, haga esto:

Debes hacer clic en el botón del reloj. La configuración es fácil de entender:

Cambie "Todos los viernes" a "Todos los lunes" si desea enviar el lunes. Puede configurar las cosas diariamente cambiando el temporizador semanal. Cambia el botón de tiempo ... bueno, supongo. Violín, presione guardar. Hecho. FML

Autorizando a Gmail para que realmente funcione

Ahora enviará el correo electrónico desde su cuenta de Google, por lo que debe autorizar a Google para que lo haga por su cuenta. Haces esto de la siguiente manera:

La hoja hace mucho trabajo pesado

Hay muchas cosas elegantes que puedes hacer si puedes codificar bien (no puedo y puedo hacerlo con Excel). Todo el formato, etc. para el mensaje se realiza en Excel. Lo que tuve que hacer para hacer que esta súper dinámica sea realmente complicada y me llevaría una hora explicarlo, así que no voy a Así que averigua cómo quieres utilizar tu nueva superpotencia ahora.

¡Espero que le haya sido útil aprender a automatizar las actualizaciones por correo electrónico! Si fue así, sea social y comparta.