Sheets 
Agenda 
Forms 
Apps Script 
Gmail 
Chat 
Meet 
Contacts 
Chrome 
Sites 
Looker Studio 
Slides 
Docs 
Drive 
AppSheet 
Admin Workspace 
Android 
Chromebook 
Equipements Google 
Google Cloud Platform 
Google Photos 
Maps 
Youtube 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 […]
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 :
- 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.
- 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.
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.
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 connexes
- Plus de l'auteur
Thierry Vanoffe
Relations entre Google Forms et Google Sheets : comment analyser des réponses à une question de type « cases à cocher »
Dans un précédent article, je listais toutes les façons de récupérer les réponses fournies par un formulaire Google Forms dans un fichier Google Sheets. J'y évoquai la complexité de récupérer ...
2 min
Antoine MARTIN 3 ans
Premium
Google Apps Script, Sheets, Contacts, Gmail : envoyer régulièrement un mail à un groupe de contacts
Voici un script permettant d'envoyer un message de Gmail à un groupe de personnes géré dans Contacts. Ce script fait le lien entre trois applications de la suite Workspace : ...
4 min
Antoine MARTIN 4 ans
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.
5
Note du cours
(2)
(0)
(0)
(0)
(0)
« 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 ?
Bonjour Dreadaxe,
Ce script récupère les événements de l’agenda par défaut de ton compte avec CalendarApp.getDefaultCalendar();
si tu souhaites récupérer un autre agenda il faut utiliser CalendarApp.getCalendarById(« IDAGENDA »);
Bonne journée 🙂
MERCI !!
Note intéressante, filtrer semble faire foirer le script.
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.
Meme probleme avec IDAGENDA, je n’arrive pas a faire fonctionner une solution ?
Bonjour Body et Dreadaxe :
La bonne formulation serait :
const IDAGENDA = « n9iknbdch76l16cd2pt3je29po@group.calendar.google.com » ;
const calendar = calendarApp.getCalendarById(IDAGENDA);
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 ?
bonjour.
Vérifiez bien avoir nommé une feuille « TEMPS PASSE » (vérifiez bien l’écriture)
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
Bonjour et merci pour l’info, j’ai corrigé le texte pour enlever le « E » avec accent et éviter d’enduire d’erreur 🙂
excellent
merci pour toutes ces leçons
Merci Edouard 🙂
Bonjour,
dateDebut et dateFin sont mal implémentées.
Bonjour David, qu’est ce qui pose problème dans l’implémentation des DateDébut et DateFin ?
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
Bonjour Erwan, le message indique que ligne 16, getEvents() est appliqué sur un objet « null » c’est à dire qui n’existe pas,
Il s’agit de l’objet « calendar » qui est défini un peu plus haut par
const agenda = CalendarApp.getDefaultCalendar();
Avez-vous bien validé les autorisations pour utiliser le service CalendarApp ?
Avec vous modifié la définition de « calendar » ?
Merci – je vais essayer de revoir ca ce week end.
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, 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
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 ?
agenda n’est pas défini…
donc vérifie si il y a bien avant la fameuse ligne 27 une ligne avec « const agenda=CalendarApp.getCalendarById(…)
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
merci pour votre aide!
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
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 !
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 !
Bonjour GrBCL;
Pouvez-vous me donner un exemple de texte avec les balises HTML?
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 🙂
<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 🙂
Merci, mais je ne vois pas la mise en forme. Je vais faire des tests et revenir vers vous.
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….
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.
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)
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
MErci beaucoup pour ce script qui fonctionne !
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
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