Comment automatiser les mises à jour par e-mail dans une feuille Google (Scripts Google)

Tl; dr: comment automatiser les mises à jour par e-mail en envoyant un e-mail à partir d'une feuille Google, en expliquant pas à pas le code

De nombreuses personnes utilisent Google Sheets pour collaborer sur un grand nombre de sujets. Beaucoup l'utilisent pour gérer leurs KPI. Savez-vous que personne ne sait comment faire ce serait vraiment cool si c'était possible?

Envoi d'un email de mise à jour directement à partir de Sheets!

Oui, c’est vrai, et personnellement, je ne savais pas que je pouvais le faire avant ce week-end. Je m'en suis servi pour créer une feuille de suivi attrayante OKR, PPP et KPI, qui enverrait ensuite une mise à jour des PPP hebdomadairement, à l'heure et à la date sélectionnées.

Je vous recommande de l'obtenir afin que vous puissiez voir comment cela fonctionne pour mettre tout cela en perspective.

Vous pouvez l'avoir ici!

Maintenant, cela va être une classe fonctionnelle, alors passons à autre chose.

Remarque, je ne sais pas comment coder. Je devais apprendre moi-même comment faire cela. Donc, si vous ne pouvez pas coder, croyez-moi, vous pourrez le comprendre comme je l’ai fait!

La mise en place

Configuration de la feuille

Ouvrez Google Sheets.

Nommez la feuille "Système".

Vous voulez ajouter quelques points de données maintenant.

Dans la première ligne, écrivez: Adresse e-mail, Message, Personne, Nombre d'e-mails et tapez 1 dans E.

Dans la deuxième ligne, ajoutez les données

Ça devrait ressembler à ça:

Laissez-moi vous expliquer ce qui se passe ici.

  • Adresse e-mail: vous tapez les adresses e-mail (ou les associez à des e-mails dans une autre feuille, comme je l'ai fait)
  • Message: J'ai créé une formule très complexe pour formater un email à ma guise
  • Personne: je lie le nom de la personne qui reçoit l'e-mail afin que le message l'adresse à cette personne dans le corps de l'e-mail.
  • Nombre de courriels: D1 est un texte, il ne fait rien. E2 est l'endroit où j'ai une fonction de compte qui me dit combien de personnes sont censées recevoir un email. Ceci est important car le code que nous allons utiliser le fait référence!

Bien sûr, vous pouvez configurer votre feuille différemment et écrire votre propre code, mais je vais vous apprendre à reproduire l’exemple que j’ai fait.

Configuration du script

Il existe une section cachée (pour moi du moins) appelée "Éditeur de script". Vous la trouverez sous Outils dans le menu.

Ouvrez-le et c’est là que vous écrivez le script. Il n'y aura que 3 lignes lorsque vous l'ouvrirez pour la première fois.

C’est ici que vous allez copier et coller le code. Regardons maintenant le script pour comprendre ce qu’il fait.

Le scénario

Voici une capture d'écran pour que vous voyiez à quoi elle devrait ressembler:

Et maintenant, voici le code pour que vous puissiez le coller.

function sendEmails () {// Récupère la feuille où se trouvent les données, dans la feuille 'system' var sheet = SpreadsheetApp.getActiveSpreadsheet (). getSheetByName ("System") var startRow = 2; // Première ligne de données à traiter puisqu'il existe une ligne d'en-tête var numRows = sheet.getRange (1,5) .getValue (); // Le nombre de lignes à traiter est défini par une formule qui compte les lignes // Récupère la plage de cellules A2: B6 où les courriels et les messages sont var dataRange = sheet.getRange (startRow, 1, numRows, 2) // Récupère des valeurs pour chaque ligne de la plage à saisir dans le système de mailing var data = dataRange.getValues ​​(); // Ceci traite les emails que vous voulez envoyer pour (i dans les données) {var row = data [i]; var emailAddress = row [0]; // La première colonne est l'adresse email var message = row [1]; // La deuxième colonne est le message var subject = "READ: Mise à jour hebdomadaire de PPP"; // Ceci est l'objet de l'email // Ceci analyse les données de l'email à envoyer à MailApp.sendEmail (emailAddress, subject, message); }}

Quelles sont ces choses?

Voir ces “//”? Ceux-ci sont utilisés pour commenter. Utiliser // signifie pour un ordinateur "ignorer ce genre de choses, c’est pour les chétifs humains".

J’ai utilisé ce système de commentaires pour vous expliquer chaque ligne de code. Mais passons à travers

Le code expliqué

fonction sendEmails () {
  • Cela crée un nom pour le script
// Récupère la feuille où se trouvent les données, dans la feuille 'système'
var sheet = SpreadsheetApp.getActiveSpreadsheet (). getSheetByName ("System")
  • Ceci définit la provenance des données. Cela vient de votre fiche système, non? Voici donc le bit intéressant: .getSheetByName («System»)
  • Vous voyez à la fin, il est écrit «Système» et je vous ai dit de nommer la feuille Système. C'est pourquoi. Si vous voyez un modèle de script dans Google, il se basera toujours sur la "feuille active", ce qui est bien si vous n’avez qu’une feuille et effectuez les mises à jour manuellement. Mais nous aurons plusieurs feuilles et un système automatisé. Nous devons donc dire à l'ordinateur où EXACTEMENT nous voulons qu'il fasse le métier de sorcière
  • Donc, si vous voulez que votre feuille soit appelée autre chose, changez-la simplement en magie, comme ceci .getSheetByName («magie»)
startRow = 2; // Première ligne de données à traiter puisqu'il existe une ligne d'en-tête
  • Regarde la feuille. vous avez écrit un e-mail, un nom, etc. à la rangée 1. Cela n’est pas utile
  • Donc, vous dites au script de commencer à la deuxième rangée, car c’est là que se trouvent les goodies. Si vos courriels étaient à la ligne 3, vous écririez à la ligne 3
var numRows = sheet.getRange (1,5) .getValue (); // Le nombre de lignes à traiter est défini par une formule qui compte les lignes.
  • C'est un peu plus funky. Dans la feuille, vous avez inséré le nombre de courriels dans E1
  • La façon dont les scripts envisagent les références de cellule est Row Column. Donc, E1 vaut 1, 5. Ligne un, colonne 5.
  • Alors regardez ici: sheet.getRange (1,5)
  • Cela dit regardez dans E1. Si votre compte de courrier électronique était en A1, vous écririez sheet.getRange (1,5)
// Récupère la plage de cellules A2: B6 où se trouvent les emails et les messages
var dataRange = sheet.getRange (startRow, 1, numRows, 2)
  • Alors rappelez-vous l'ordre Row, Column? Le chemin le plus long ou l’écriture dans cette fonction est: Ligne, Colonne, Nombre de lignes, Nombre de colonnes
  • Cette fonction indique aux scripts la zone exacte où ils extraient les données pour analyser et envoyer des emails.
  • Nous avons seulement besoin de l'e-mail et du message pour l'automate de messagerie. Donc, les scripts doivent être informés de cela. La façon dont nous le faisons est de lui dire par où commencer et par où finir
  • sheet.getRange (ligne, colonne, nombre de lignes, nombre de colonnes) devient sheet.getRange (startRow, 1, numRows, 2)
  • Pourquoi y a-t-il des noms ici? Eh bien, nous avons défini startRow et numRows ci-dessus! 1 est la première colonne et 2, les deux premières colonnes où sont situés les courriers électroniques et les messages. Oui, vous pourriez le faire différemment. Avoir un jeu?
// Récupère les valeurs de chaque ligne de la plage à saisir dans le système de mailing
var data = dataRange.getValues ​​();
  • Cela indique simplement au script de voler les données et de les stocker sous le nom «data».
// Ceci traite les emails que vous voulez envoyer
pour (i dans les données) {
var row = data [i];
  • Cela commence le programme (essentiellement)
var emailAddress = row [0]; // La première colonne est l'adresse email
  • Dit que l'email est dans la première ligne (0 est la première ligne en ce qui concerne notre tableau)
var message = rangée [1]; // la deuxième colonne est le message
  • Dit que le message est dans la deuxième colonne
var subject = "READ: Mise à jour hebdomadaire de PPP"; // C'est le sujet de l'email
  • Ici, vous pouvez définir le nom de l'objet de l'e-mail que vous allez envoyer!
  • Si vous souhaitez envoyer un courrier électronique avec le sujet «Consultez ce blog sur la façon d’automatiser les feuilles», vous devez saisir ceci.
  • var subject = “consultez ce blog pour savoir comment automatiser les feuilles”;
// Ceci analyse les données de l'email à envoyer
MailApp.sendEmail (emailAddress, subject, message);
}
}
  • Ceci définit la structure dans une fonction de courrier électronique (MailApp) qui fait le travail intelligent
  • Nous lui avons dit que nous voulions utiliser l'adresse e-mail, le sujet et le message
  • Le contenu «for (i in data) {» au début du processus crée une boucle pour envoyer un courrier électronique à chaque personne (la fonction de décompte indique le nombre de boucles exécutées, par exemple 2 signifierait deux boucles et deux courriels).

C’est le code avec lequel vous devez jouer.

Je sais que c'est peut-être un peu difficile à comprendre, mais vous pouvez le faire. Si vous devez modifier le code pour faire autre chose, consultez la documentation. Oui, c’est comme essayer de comprendre un mot latin en se faisant dire de lire la définition dans un dictionnaire latin, mais c’est possible

La documentation est ici: https://developers.google.com/apps-script/reference/mail/

Automatisation de l'envoi des emails

Maintenant, j'ai passé du temps à chercher du code sur Google pour savoir comment envoyer des courriels à la date et à l'heure que je voulais. C'est ce que j'ai fait… puis j'ai réalisé que je voulais que cela fonctionne dans n'importe quel pays… alors la mission suivante consistait à trouver un moyen d'ajuster la feuille au fuseau horaire de chaque personne! Je l'ai compris… et puis j'ai trouvé un moyen vraiment stupide et simple de le faire! Gosh darn!

Oublions ce code… Fais-le.

Si vous souhaitez modifier le calendrier ou le jour, procédez comme suit:

Vous devez cliquer sur le bouton de l'horloge. Les paramètres sont faciles à comprendre:

Remplacez ‘Chaque vendredi’ par ‘Chaque lundi’ si vous souhaitez envoyer le lundi. Vous pouvez régler les choses quotidiennement en modifiant le minuteur hebdomadaire. Vous changez le bouton de temps… bien vous devinez. Violon, appuyez sur Enregistrer. Terminé. FML.

Autoriser Gmail donc comme des œuvres

Il enverra maintenant l'email de votre compte Google. Vous devez donc autoriser Google à le faire pour votre compte. Vous faites ceci comme suit:

La feuille fait beaucoup de travail lourd

Il y a beaucoup de choses fantaisistes que vous pouvez faire si vous savez bien coder (je ne peux pas et je ne peux le faire avec Excel). Toute la mise en forme, etc. pour le message est faite dans Excel. Ce que je devais faire pour rendre cette super dynamique est très compliqué et prendrait une heure à expliquer. Je ne vais donc pas:

J'espère que l'apprentissage de l'automatisation des mises à jour par courriel vous a été utile! Si c'était le cas, soyez social et partagez.