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 !