Google Sheets et Google Apps Script : optimisation du temps de traitement
Vous avez re-utilisé ou écrit un script pour manipuler vos classeurs et vous trouvez que son exécution prend trop de temps ? Voici quelques pistes d’amélioration à considérer pour réduire le temps d’exécution. Première piste […]

Ce que vous allez découvrir
- Première piste : la lecture des données
- Deuxième piste : l'écriture des données
- Troisième piste : mise en mémoire des données
- Quatrième piste : exécuter son script en plusieurs étapes
Google Sheets et Google Apps Script : optimisation du temps de traitement
Vous avez re-utilisé ou écrit un script pour manipuler vos classeurs et vous trouvez que son exécution prend trop de temps ? Voici quelques pistes d’amélioration à considérer pour réduire le temps d’exécution.
Première piste : la lecture des données
Quand on débute, on a généralement tendance à vouloir lire uniquement les valeurs qui nous intéressent dans les feuilles de calcul mais on se retrouve rapidement à faire des lectures multiples d’une même feuille et il devient alors plus intéressant de faire une lecture unique de la plage de données complète et d’y piocher les valeurs dont on a besoin.
Imaginons que nous voulions lire la valeur de chaque cellule de la colonne A pour la feuille de calcul suivante :

Au lieu de faire des mutiples getValue() qui prend ici en moyenne plus de deux secondes :
function lectureNonOptimisee() { console.log("Récupération de chaque celulle individuellement puis lecture") const start = new Date(); const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FeuillePerf"); for (let i=1;i<=sheet.getLastRow();i++) { const valeur_premiere_colonne = sheet.getRange("A"+i).getValue(); console.log(valeur_premiere_colonne); } const current = new Date(); const elapsedTime = current.getTime() - start.getTime(); console.log("Il a fallu "+elapsedTime+" ms pour lire les valeurs de la première colonne"); }

Il est plus intéressant de faire un unique getValues() de la colonne souhaitée, qui prend ici moins de 0,2 seconde ! Sachant que la feuille en question ne comprend ici que 26 lignes, cette différence est d’autant plus significative qu’il y a beaucoup de lignes à lire.
function lectureOptimisee() { console.log("Récupération des valeurs de toute la colonne puis lecture individuelle.") const start = new Date(); const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FeuillePerf"); const data = sheet.getRange(1,1,sheet.getLastRow(),1).getValues(); for (let i=0;i<data.length;i++) { const valeur_premiere_colonne = data[i][0]; console.log(valeur_premiere_colonne); } const current = new Date(); const elapsedTime = current.getTime() - start.getTime(); console.log("Il a fallu "+elapsedTime+" ms pour lire les valeurs de la première colonne"); }

Et dans le cas où on veut lire l’intégralité d’une feuille, il est alors préférable d’utiliser getDataRange() qui va récupérer la plage de données la plus large avec au moins une valeur présente dans une cellule.
Pour cette feuille, cela récupérerait la plage A1:B26.
Google permet de récupérer plein d’autres informations pour une plage de données, comme les formules (getFormulas()) ou les validations de données (getDataValidations()) par exemple.
Deuxième piste : l’écriture des données
Tout comme la lecture, l’écriture de données doit être pensée pour minimiser le nombre d’opérations d’écriture.
Imaginons que nous voulons écrire une valeur dans les 26 premières cellules de la colonne B pour la même feuille de calcul.
Au lieu de faire de multiples setValue() qui prennent en moyenne plus de 300 ms…
function ecritureNonOptimisee() { const start = new Date(); const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FeuillePerf"); for (let i=1;i<=26;i++) { sheet.getRange("B"+i).setValue(i); } const current = new Date(); const elapsedTime = current.getTime() - start.getTime(); console.log("Il a fallu "+elapsedTime+" ms pour écrire les valeurs de la deuxième colonne"); }
Il est plus intéressant de créer un tableau avec toutes les données à ajouter et de faire un unique setValues(), qui prend ici moins de 10 ms ! Sachant encore une fois que cette différence est d’autant plus significative qu’il y a de lignes à écrire.
function ecritureOptimisee() { const start = new Date(); const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FeuillePerf"); var rows = []; for (let i=1;i<=26;i++) { row = [i]; rows.push(row); } sheet.getRange(1,2,rows.length,rows[0].length).setValues(rows); const current = new Date(); const elapsedTime = current.getTime() - start.getTime(); console.log("Il a fallu "+elapsedTime+" ms pour écrire les valeurs de la deuxième colonne"); }

Et de même, Google permet d’écrire d’autres informations pour une plage de données, comme les formules (setFormulas()) ou les validations de données (setDataValidations()) par exemple.
Troisième piste : mise en mémoire des données
Il est également possible de sauvegarder en mémoire des données. Cela peut être intéressant pour les données qui prennent du temps à être calculées et qui sont souvent utilisées. Il faut cependant que chaque donnée, stockée au format texte, soit d’une taille inférieure à 9kb (Google quotas).
Pour cela, il faut créer trois fonctions.
Première fonction (gestionMasterData) : celle-ci va évaluer si les données peuvent être récupérées en cache ou pas. Ici la logique est simple, si le cache a été créé ou rafraîchi il y a moins d’une heure, on va lire le cache (appel à la fonction recuperationMasterDataEnCache), sinon on (ré)génère les données et on les sauve dans le cache (appel à la fonction stockageMasterDataEnCache).
Deuxième fonction (stockageMasterDataEnCache) : stockage des données en cache.
Troisième fonction (recuperationMasterDataEnCache) : récupération des données en cache.
function gestionMasterData() { console.log("Manage Master Data"); const documentProperties = PropertiesService.getDocumentProperties(); const recordedTime_MasterData = parseInt(documentProperties.getProperty('recordedTime_MasterData')); console.log("Verifier quand les données ont été enregistrées (moins d'une heure ou pas)"); console.log(new Date(recordedTime_MasterData)); const now = new Date(); var masterData = null; if (recordedTime_MasterData != null && (recordedTime_MasterData + 360000) > now.getTime()) { console.log("Récupération des données sauvegardées en cache"); masterData = recuperationMasterDataEnCache(); } if (masterData == null) { console.log("Régénération des données & mise en cache"); masterData = getSpreadsheetData(masterSpreadsheetId); // fonction non détaillée ici stockageMasterDataEnCache(masterData); } return masterData; } function stockageMasterDataEnCache(masterSpreadsheetData) { var documentProperties = PropertiesService.getDocumentProperties(); documentProperties.setProperty('sheetsHeaders', JSON.stringify(masterSpreadsheetData.sheetsHeaders)); documentProperties.setProperty('sheetsDataFormula', JSON.stringify(masterSpreadsheetData.sheetsDataFormula)); documentProperties.setProperty('sheetsDataValidation', JSON.stringify(masterSpreadsheetData.sheetsDataValidation)); documentProperties.setProperty('sheetsHeadersFontColor', JSON.stringify(masterSpreadsheetData.sheetsHeadersFontColor)); documentProperties.setProperty('sheetsHeadersBackground', JSON.stringify(masterSpreadsheetData.sheetsHeadersBackground)); documentProperties.setProperty('sheetsHeadersTextStyle', JSON.stringify(masterSpreadsheetData.sheetsHeadersTextStyle)); const now = new Date(); documentProperties.setProperty('recordedTime_MasterData', now.getTime().toString()); } function recuperationMasterDataEnCache() { try { const documentProperties = PropertiesService.getDocumentProperties(); var masterSpreadsheetData = {}; masterSpreadsheetData.sheetsHeaders = JSON.parse(documentProperties.getProperty('sheetsHeaders')); masterSpreadsheetData.sheetsDataFormula = JSON.parse(documentProperties.getProperty('sheetsDataFormula')); masterSpreadsheetData.sheetsDataValidation = JSON.parse(documentProperties.getProperty('sheetsDataValidation')); masterSpreadsheetData.sheetsHeadersFontColor = JSON.parse(documentProperties.getProperty('sheetsHeadersFontColor')); masterSpreadsheetData.sheetsHeadersBackground = JSON.parse(documentProperties.getProperty('sheetsHeadersBackground')); masterSpreadsheetData.sheetsHeadersTextStyle = JSON.parse(documentProperties.getProperty('sheetsHeadersTextStyle')); return masterSpreadsheetData; } catch (e) { console.log(e); return null; } }
Quatrième piste : exécuter son script en plusieurs étapes
Si jamais votre script prend malgré tout trop de temps (c’est à dire plus de 6 minutes, qui est actuellement le temps maximum autorisé avant que l’exécution ne s’arrête), il faut alors réfléchir à découper son script en briques élémentaires, mettre en place un chronomètre pour savoir quand on doit s’arrêter (c’est à dire avant d’excéder 6 minutes), sauvegarder son état et programmer un déclencheur qui reprendra l’exécution là où elle s’est arrêtée.
Voyons ici le cas où un script est une répétition d’une seule action qui consiste à ouvrir chaque fichier d’un sous-dossier pour y effectuer une action particulière (par exemple y transférer les lignes ajoutées depuis la dernière synchronisation vers un fichier central). S’il y a beaucoup de fichiers à gérer, il y a un risque réel que le script s’arrête en cours de route (au bout de 6 minutes) et que certains fichiers ne soient alors jamais pris en considération.
Il est alors bienvenu de découper son script en deux fonctions.
Première fonction (initiateTransfertJob) : initialisation du travail de transfert, via le remplissage d’une feuille de route avec tous les fichiers à traiter, puis appel à la deuxième fonction (runTransfertJob) qui s’occupe du transfert de chaque fichier à proprement parler.
function initiateTransfertJob() { const currentFolder = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getParents().next(); var folders = currentFolder.getFoldersByName("Commerciaux inscriptions"); var listOfInscriptions = []; while (folders.hasNext()) { var folder = folders.next(); var files = folder.getFiles(); while (files.hasNext()) { var file = files.next(); listOfInscriptions.push({name: file.getName(), id: file.getId()}); } } var rows = []; var now = new Date(); for (var i=0;i<listOfInscriptions.length;i++) { rows.push([listOfInscriptions[i].name, listOfInscriptions[i].id, now, "", "", "A faire"]); } if (rows.length>0) { const progressSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Transfert Commerciaux->General"); if (progressSheet.getLastRow()>1) progressSheet.getRange(2,1,progressSheet.getLastRow()-1,progressSheet.getLastColumn()).clearContent(); progressSheet.getRange(2,1,rows.length,rows[0].length).setValues(rows); } runTransfertJob(); }


Deuxième fonction (runTransfertJob) : lecture de la feuille de route avec traitement individuel de chaque ligne ainsi que la mise à jour de l’état et la gestion d’un chronomètre pour relancer via un déclencheur cette fonction si jamais on dépasse 5 minutes de traitement. En cas de relance, celle-ci s’effectuera concrètement la minute suivante. Et cet arrêt/redémarrage se fera autant de fois que nécessaire jusqu’à ce que tous les fichiers de la feuille de route soient traités.
function runTransfertJob() { const startTime = new Date(); const progressSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Transfert Commerciaux->General"); const progressSheetValues = progressSheet.getDataRange().getValues(); for (var i=1;i<progressSheetValues.length;i++) { const currentTime = new Date(); if (isTimeUp_(startTime, currentTime)) { ScriptApp.newTrigger("runTransfertJob") .timeBased() .at(new Date(currentTime.getTime())) .create(); return; } const status = progressSheetValues[i][5]; if (status == "A faire") { progressSheet.getRange(i+1,4,1,3).setValues([[currentTime, "", "En cours"]]); const spreadsheetId = progressSheetValues[i][1]; try { // Ici la partie pure de traitement de chaque fichier LibrairieCommercial.transfertInscriptions(spreadsheetId); progressSheet.getRange(i+1,5,1,2).setValues([[new Date(), "Terminé"]]); } catch (e) { const now = new Date(); progressSheet.getRange(i+1,5,1,3).setValues([[now, "Erreur", e]]); } } } } function isTimeUp_(start, current) { return current.getTime() - start.getTime() > 300000; // 5 minutes }

Dans le cas où chaque ligne à traiter prend un temps conséquent, pour gagner du temps, il est également possible de créer de multiples déclencheurs (dans la limite de 20) pour paralléliser l’exécution, mais on doit alors s’assurer que chaque déclencheur traite une ligne différente.
Si jamais vous avez d’autres astuces, n’hésitez-pas à commenter et à les partager avec nous !
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- Tutos connexes
- Plus de l'auteur
Bravo, sujet que je recherchais depuis plusieurs mois.
Cet avis vous a été utile ?
Il ne reste plus qu’à tester.
Cet avis vous a été utile ?
Bonjour,
Tout d’abord merci pour tous vos bons conseils.
Je rencontre un problème de lenteur dans l’exécution d’un script.
Le problème n’apparait plus lorsque je fait une copie de mon tableau (dupliquer sheet).
Par exemple temps d’exécution de votre script lectureOptimisee() sur le 1er tableau : 30916 ms
Après duplication du tableau, temps d’exécution de lectureOptimisee() : 652 ms.
Même nombre de données, même scripts, … (duplication), je ne comprends pas ?
Merci pour l’attention que vous voudrez bien y apporter.
Bonne journée.
Cet avis vous a été utile ?
Bonsoir,
Malheureusement je n’ai jamais été confronté à ce problème. Mais du coup vous avez réussi à le contourner en dupliquant le classeur et en supprimant l’original, ou bien vous avez de nouveau un problème similaire qui resurgit?
Bonne soirée à vous aussi
Cet avis vous a été utile ?
Bonjour,
Merci beaucoup pour ce script !
Je rencontre néanmoins un problème : en effet le dossier DRIVE contient neuf présentations de 22 Mo (elles pourront aller jusque 70Mo) toutes liées au dossier source.
J’ai copié/coller le code proposé ci-dessus comme convenu, cela ne fonctionnait pas.
J’ai ensuite remplacé « const currentPresentation = SlidesApp.openById(presentationId) » (4ème ligne à partir de la deuxième fonction) par l’id du slide à rafraîchir en question.
Cela fonctionne.
Hors, je dois mettre à jour neuf présentations.
J’ai copié/collé la fonction « updateallslides » par le nombre de slides en remplaçant l’id « const currentPresentation = SlidesApp.openById(presentationId) » par l’id de chaque slides.
Or, le temps d’attente est vraiment long, car ce sont des fichers volumineux et l’exécution se fait très lentement.
Comment optimiser le temps de traitement ?
Cordialement,
Carmèle
Cet avis vous a été utile ?