Numeriblog Google Workspace Google Agenda, l’agenda collaboratif le plus puissant Google Agenda et Sheets : synchroniser et mettre à jour des événements d’un agenda dans une feuille de calcul et vice-versa

Google Agenda et Sheets : synchroniser et mettre à jour des événements d’un agenda dans une feuille de calcul et vice-versa

La question de la gestion du temps est récurrente dans le suivi de tous les projets menés en même temps. En tant qu’indépendant, je dois gérer une dizaine de projets simultanément, dont certains sont facturés […]

5(2)
personnes ont consulté cet article

4 minutes

Rédigé par Antoine MARTIN - il y a 3 ans

Ce que vous allez découvrir

  • Script pour récupérer les événements de Google Agenda vers Sheets
  • Script pour mettre à jour l'événement de Google Sheets vers Agenda
  • Conclusion

Google Agenda et Sheets : synchroniser et mettre à jour des événements d’un agenda dans une feuille de calcul et vice-versa

La question de la gestion du temps est récurrente dans le suivi de tous les projets menés en même temps. En tant qu’indépendant, je dois gérer une dizaine de projets simultanément, dont certains sont facturés au temps passé, et les clients me demandent un suivi circonstancié des heures passées. Voyons ensemble la méthode de synchronisation.

Comment suivre le temps passé sur un projet ? Faut-il saisir les événements dans Sheets (je saisis dans une feuille de calcul le numéro du projet, une date et heure de début et de fin… pff, galère et comment être sûr de ne pas oublier des événements) ? Ou bien faut-il saisir naturellement les événements dans mon agenda Google et ensuite les recopier dans une feuille de calcul (je récupère facilement le temps passé réel mais c’est compliqué d’associer l’événement à un projet précisément en suivant une règle de nommage stricte et facile à oublier).

Quel dilemme !

Voici une proposition de compromis exploitant les forces de chaque application :

logo Google Agenda
  • Google Agenda pour saisir, modifier, supprimer les événements dans le calendrier Google, créer des rappels, gérer les invitations et les visio-conférences sur Meet.

apercu Agenda
Logo Google Sheets
  • Google Sheets pour recueillir les événements, calculer le temps passé, associer l’événement à un projet et créer des rapports personnalisés à mes clients.
apercu recueil des evenements

J’ai donc créé deux scripts d’interactions entre l’appli Agenda et Sheets que je vous soumets ici en version simplifiée.

  • Récupérer automatiquement chaque nuit la liste des événements de mes agendas dans Sheets.
  • Associer dans Sheets l’événement à un projet.
  • Modifier l’événement dans l’agenda en y ajoutant en fin de titre l’ID du projet entre crochets.
  • Extraire l’ID du projet du titre de l’événement pour suivre le temps passé par projet dans un TCD.

Script pour récupérer les événements de Google Agenda vers Sheets

  • Ouvrez une feuille de calcul Sheets listant vos projets.
  • Nommez un onglet « TEMPS PASSE ».
  • Créez un script (Outil / Éditeur de script).
  • Supprimez la fonction par défaut et copiez collez le code suivant :
/*****
 *
 * @authour: antoine.martin@amcms.net
 * @owner: numericoach
 * @date: 2021
 *
 * tous droits réservés
 *
 ****/
function listEvents() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetTemps = ss.getSheetByName("TEMPS PASSE");
  const agenda = CalendarApp.getDefaultCalendar();
  const dateAujourdhui = new Date();
  // Date à partir de laquelle vous souhaitez mettre à jour la liste d’événements de votre agenda : ici le 31/12/2021
 const dateDebut = new Date(2020, 12, 31);
  // Date de fin de mise à jour : ici deux mois après la date d'aujourd'hui
  const dateFin = new Date(dateAujourdhui.getFullYear(),dateAujourdhui.getMonth()+2, dateAujourdhui.getDate());
  const events = agenda.getEvents(dateDebut, dateFin);
  let temps = [];
  events.forEach(function (event) {
    const eventId = event.getId();
    const eventTimeDebut = event.getStartTime();
    const eventTimeFin = event.getEndTime();
    const eventTitre = event.getTitle();
    let guestListe = [] ;
  // Si je ne suis pas le créateur de l'évenement, indiquer qui c'est
    if (!event.isOwnedByMe()) {
      guestListe = event.getCreators();
    }
  // Recueillir la liste des invités
    const eventInvitations = event.getGuestList();
    eventInvitations.forEach(function (guest) {
      const guestEmail = guest.getEmail();
      let guestName = guest.getName();
      if (guestName == "") { guestName = guestEmail; }
      guestListe.push(guestName + " (" + guest.getStatus() + ")");
    });
    temps.push([eventId, eventTimeDebut, eventTimeFin, eventTitre, guestListe.join("\n") ]);
  })
  sheetTemps.getRange(2, 1, sheetTemps.getMaxRows() - 1, sheetTemps.getMaxColumns()).clearContent();
  sheetTemps.getRange(2, 1, temps.length, temps[0].length).setValues(temps);
}
  • Lancez une fois le script pour valider les autorisations.
  • Créez un déclencheur pour actualiser les informations au rythme que vous le souhaitez (une fois par jour, entre 3h et 4h du matin par exemple).

La feuille « TEMPS PASSÉ » affiche les événements issus de votre agenda. Paramétrez les colonnes « Début » et « Fin » pour afficher le calendrier avec les dates et les heures.

Script pour mettre à jour l’événement de Google Sheets vers Agenda

Ce script permet de renommer l’événement en y insérant l’ID de mon projet pour qu’il y soit lié de façon pérenne.

Image de référence

Ajoutez une colonne dans la feuille de temps listant par validation de données les projets en cours issue de la feuille « PROJETS ».

  • Copiez collez ce script à la suite du précédent script.

Attention : ce script modifie les titres des événements dans l’agenda principal du compte Google. Numeriblog décline toute responsabilité en cas de dommages subits suite à son utilisation.

function onEditDeclencheur() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetTempsPasse = ss.getSheetByName("TEMPS PASSE");
  const sheetTempsPasseId = sheetTempsPasse.getSheetId();
  const rangeEdited = sheetTempsPasse.getActiveCell();
  const rangeEditedSheetId = rangeEdited.getSheet().getSheetId();
  const rangeEditedCol = rangeEdited.getColumn();
  const rangeEditRow = rangeEdited.getRow();
  // Si dans le sheets TEMPS PASSE
  if (sheetTempsPasseId == rangeEditedSheetId) {
    Logger.log(rangeEditedCol + " " + rangeEditRow)
    // Si c'est la colonne E qui est modifiée
    if (rangeEditedCol == 6) {
      const eventId = sheetTempsPasse.getRange(rangeEditRow, 1).getValue();
      const eventTitle = sheetTempsPasse.getRange(rangeEditRow, 4).getValue();
      const projetId = rangeEdited.getValue().match(/\[(.*)\]/)[1];
      Logger.log(eventTitle + " " + projetId);
      const agenda = CalendarApp.getDefaultCalendar();
      const event = agenda.getEventById(eventId);
      const newTitle = eventTitle + " [" + projetId + "]";
      event.setTitle(newTitle);
      sheetTempsPasse.getRange(rangeEditRow, 4).setValue(newTitle);
      ss.toast("Mise à jour événement " + eventTitle);
      rangeEdited.clearContent();
    }
  }
}
  • Ajoutez un déclencheur « Lors d’une modification ».

À présent, en modifiant le projet dans la colonne E, l’ID du projet est automatiquement inséré dans l’événement.

Vous pouvez calculer la durée de l’événement et afficher le temps global passé par projet dans un TCD.


Conclusion

En mettant en place ces deux scripts, j’ai consolidé la gestion du temps passé sur mes projets, en m’appuyant sur la saisie intuitive des événements dans mon agenda d’une part et la gestion analytique du tableur d’autre part. J’ai pu aussi cadrer la façon de créer des événements en les nommant directement avec l’ID du projet correspondant si je le connais. Cela demande toujours un peu de temps pour associer l’événement au bon projet, mais quelle satisfaction quand les plannings sont à jour. Le temps passé à tenir à jour son agenda est largement valorisé quand je peux fournir à mes clients un planning détaillé des actions menées sur leurs projets. La gestion de l’emploi du temps est améliorée grâce à cette méthode.

Merci pour votre lecture et pour vos retours sur cette procédure dans les commentaires, avez-vous mis en place d’autres outils de suivi de temps de projets ?

Besoin d'un peu plus d'aide sur Sheets ?

Des formateurs sont disponibles toute l'année pour vous accompagner et optimiser votre utilisation de Sheets, que ce soit pour votre entreprise ou pour vos besoins personnels !

Découvrir nos formations Sheets

Articles similaires

  • Articles connexes
  • Plus de l'auteur

Rédacteur

Photo de profil de l'auteur
Antoine MARTIN

Consultant et formateur sur les outils bureautiques, j'ai intégré l'équipe de Numericoach en 2020. J'accompagne les utilisateurs de Google Workspace à trouver des solutions répondant à leurs besoins. Mes domaines de prédilections sont les outils Sheets, Docs, Slides et Google Apps Script.

S’abonner
Notification pour

5

Note du cours

(2)

(0)

(0)

(0)

(0)

37 Commentaires
Le plus ancien
Le plus récent Le plus populaire
Commentaires en ligne
Afficher tous les commentaires
Dreadaxe

« synchroniser et mettre à jour des événements d’un agenda »
Il me semble qu’on ne peut pas choisir un agenda mais que tous les événement dû compte son récupéré quelque soit le calendrier.
Une solution ?

Dreadaxe

MERCI !!
Note intéressante, filtrer semble faire foirer le script.

Dreadaxe

Merdouille ça ne semble pas fonctionner
CalendarApp.getCalendarById(« IDAGENDA »);
« IDAGENDA » = n9iknbdch76l16cd2pt3je29po@group.calendar.google.com
Dommage aussi qu’on ne puisse pas récup l’agenda de contact avec les dates de naissance.

Body

Meme probleme avec IDAGENDA, je n’arrive pas a faire fonctionner une solution ?

Salmaslian

Bonjour,
J’ai ce message d’erreur lorsque j’ai coller votre code
« 12:03:31 Erreur
TypeError: Cannot read property ‘getRange’ of null
listEvents Code.gs:33 »
Pouvez-vous m’aider à ce sujet ?

Antoine MARTIN

bonjour.
Vérifiez bien avoir nommé une feuille « TEMPS PASSE » (vérifiez bien l’écriture)

3 AVEC L4ACCENT

pour information, dans le texte de l’article il est écrit de créer un onglet « TEMPS PASSÉ » avec l’accent
J’ai eu le même problème avant de relire le script avec précision
je vous remercie

EDOUARD D

excellent
merci pour toutes ces leçons

David

Bonjour,
dateDebut et dateFin sont mal implémentées.

erwan

bonjour, j’ai le message :
22:38:16 Erreur
TypeError: Cannot read property ‘getEvents’ of null
listEvents Code.gs:16
pouvez vous m’aider? merci

erwan

Merci – je vais essayer de revoir ca ce week end.

erwan

bonjour, merci du retour, le premier script fonctionne très bien
par contre comment tester le 2eme, a chaque fois l’évènement s’efface ! peut être parce que je n’ai rien mis colonne 1 ! , comment créer un ID EVENEMENT ?
sinon, si je souhaite utiliser un agenda particulier vous écriver : si tu souhaites récupérer un autre agenda il faut utiliser CalendarApp.getCalendarById(« IDAGENDA »)
il faut juste remplacer « IDAGENDA » par l’adresse id de mon agenda ?
Est il possible sur un google sheets de 3 feuilles de mettre 3 agendas différents avec 3 scripts différents?
pour cela je dois :
faire attention au nom de chaque feuille (ici temps passe) ligne 14 et 67
changer l’id de l’agenda comme vu au dessus, ligne 16 et 90
Merci encore
Erwan

erwan

j’ai réussi a faire le changement de calendrier notamment avec votre réponse à body;
Maintenant j’ai :
ReferenceError: agenda is not defined
listEvents Code.gs:27
une idée ?

Antoine MARTIN

Erwan, voici quelques réponses:
« sinon, si je souhaite utiliser un agenda particulier vous écriver : si tu souhaites récupérer un autre agenda il faut utiliser CalendarApp.getCalendarById(« IDAGENDA »)
il faut juste remplacer « IDAGENDA » par l’adresse id de mon agenda ? »
L’Id de l’agenda est récupérable dans les paramètres de l’agenda. https://sites.google.com/site/efficalendar/synchronisation/ou-trouver-l-identifiant-de-l-agenda-google-calendar
« Est il possible sur un google sheets de 3 feuilles de mettre 3 agendas différents avec 3 scripts différents?
pour cela je dois :
faire attention au nom de chaque feuille (ici temps passe) ligne 14 et 67
changer l’id de l’agenda comme vu au dessus, ligne 16 et 90 »
Il faudrait étudier le cas plus précisément, mais oui, l’idée de base est de créer 3 fonctions différentes (avec des noms différents pour chacune !) dans chacune, indiquer le nom d’une feuille et l’id de l’agenda. L’idéal est de passer les infos qui changent dans les arguments d’une seule fonction.
Je t’invite à te rapprocher de Numericoach pour demander une heure d’accompagnement à distance pour avancer dans ton projet 🙂 contact@numericoach.fr

erwan

merci pour votre aide!

GrBcl

Très intéressant tout ca ! est ce possible de récupérer les adresses des RDV ainsi que le descriptif du rendez vous ? j’aimerai en détourner l’usage pour gérer des bons de livraison. merci

Antoine MARTIN

Merci GrBcl (un accident de scrabble ? 🙂 )
Toutes les informations du rendez-vous sont récupérables (sauf le lien meet, il faut passer alors par CalendarApi.
Bonne continuation !

GrBCL

Bonjour ! J’utilise votre script depuis déjà un bon moment pour créer des bons de livraison avec des références que souvent je colle dans la description du google agenda et que je récupère dans GSheet. Ce qui me rend un grand service !
Le problème c’est que dans GSheet, le contenu télécharger insère toutes les balises html etc… ce qui rend difficilement lisible le contenu de la cellule.
Il y a t’il possibilité dans le script de supprimer la mise en forme de la description de google agenda ?? sans avoir a chaque fois a cliquer sur le bouton de suppression de la mise en forme (icone T barré) ou bien de simplement recevoir en format texte brut les donnée dans gsheet?
merci d’avance !

Antoine MARTIN

Bonjour GrBCL;
Pouvez-vous me donner un exemple de texte avec les balises HTML?

GrBCL

0325327***livrer grundig congélateur gfn13740nroute de sainte marthe
voici ce que je reçois dans GSheet si en amont je n’ai pas sélectionné le texte de la description dans google agenda et cliqué sur la suppression de la mise en forme (icone T barré)
Merci pour votre avis 🙂

GrBCL

<html-blob<0323527820<br<grundig congélateur gfn13740n<br/html-blob<
j'ai volontairement inversé les signes des balises car cela ne s'affiché pas dans mon commentaire de 22h21 🙂

Antoine MARTIN

Merci, mais je ne vois pas la mise en forme. Je vais faire des tests et revenir vers vous.

GrBCL

pour contrer le problème j’ai appliqué ce code à la suite de votre script :
var sheet = SpreadsheetApp.getActiveSpreadsheet ();
var textFinder = sheet.createTextFinder (‘<br<');
textFinder.replaceAllWith (' ')
ça règle mon problème, cependant j'aimerai tout de même savoir pourquoi la mise en forme html se télécharge….

Vincent

Bonjour Antoine,
Merci pour toutes ces fonctionnalités très intéressantes.
J’ai deux questions pour vous :
– Est il possible de générer une modification automatique du google Cal via GSheet? Admettons que nous mettons une date de début d’évènement dans le google sheet qui se reporte sur google Cal et que finalement nous le changeons sur GSheet, est ce qu’il existe un moyen que le changement opère également sur Google Cal ?
– Pensez vous qu’il soit possible de rajouter une description dans l’évènement créé de base ? Nous savons aujourd’hui mettre un Titre, une date/heure de début et une date/heure de fin, mais est ce qu’on a le moyen d’ajouter une description?
Merci pour vos brillantes lumières.

Vincent

Pour compléter ma demande, je parle bien d’y ajouter une description à la base, non pas par la suite (chose que fait votre script cité plus haut si je ne m’abuse)

Antoine MARTIN

bonjour Vincent,
La mise à jour de l’événement à partir des modifications apportées dans Sheets est tout à fait possible, d’ailleurs dans le script onEditDeclencheur() le titre est modifié dans Agenda quand une colonne est modifiée dans Sheets. il est possible de demander aussi à modifier les dates.
Pour la description auto,au même titre que vous créez le titre avec setTitle(), vous pouvez créer une description avec setDescription().
N’hésitez aps à nous contacter pour un accompagnement plus personnalisé pour répondre à vos besoins spécifiques 😉 contact@numericach.fr

Isabelle Chevrier
Votre note :
     

MErci beaucoup pour ce script qui fonctionne !

Dernière modification le 1 année il y a par Isabelle Chevrier
Isabelle Chevrier
Votre note :
     

Bonjour, il existe ce plugin que j’ai installé, qui fonctionne il me semble sur la meme idée. mais comment faire pour que l’agenda se mette à jour automatiquement ?
https://workspace.google.com/marketplace/app/sheets2gcal/137564231775?hl=fr

Jennyfer Delage

Bonjour,

Merci pour cette question pertinente.

Afin d’avoir une réponse rapide, je t’invite à poser cette question sur notre nouveau forum : NUM AGORA.
En effet, cet espace gratuit permet de poser des questions et de profiter de centaines de membres pour y répondre.
À tout de suite sur : https://forum.thierryvanoffe.com/ de préférence dans la bonne rubrique !
Ne pas oublier de mettre le lien de l’article en question et un copier-coller de cette question.

À bientôt,

Jennyfer