Principes de base d'Apps Script avec Google Sheets #2 : feuilles de calcul, feuilles et plages

1. Introduction

Bienvenue dans la deuxième partie de la playlist d'ateliers de programmation "Principes de base d'Apps Script avec Google Sheets". L'atelier de programmation précédent était axé sur les concepts de l'éditeur de script, des macros et des fonctions personnalisées. Cet atelier de programmation se penche sur le service de feuille de calcul que vous pouvez utiliser pour lire, écrire et manipuler des données dans Google Sheets.

Points abordés

• Comment les feuilles de calcul, les feuilles et les plages sont représentées dans Apps Script.
• Comment accéder à la feuille de calcul active (ouverte), la créer et la renommer avec les classes SpreadsheetApp et Spreadsheet.
• Comment modifier le nom d'une feuille et l'orientation des colonnes/lignes d'une plage à l'aide de la classe Sheet.
• Comment spécifier, activer, déplacer et trier un groupe de cellules ou une plage de données à l'aide de la classe Range.

Avant de commencer

Il s'agit du deuxième atelier de programmation de la playlist "Principes de base d'Apps Script avec Google Sheets". Avant de commencer, assurez-vous d'avoir terminé le premier atelier de programmation : Macros et fonctions personnalisées.

Prérequis

• Comprendre les concepts de base d'Apps Script abordés dans l'atelier de programmation précédent de cette playlist.
• Connaissances de base de l'éditeur Apps Script
• Connaissances de base de Google Sheets
• Possibilité de lire la notation A1 de Sheets
• Connaissances de base de JavaScript et de sa classe String

La section suivante présente les classes principales du service Spreadsheet.

2. Présentation du service Sheets

Quatre classes constituent la base du service Spreadsheet : SpreadsheetApp, Spreadsheet, Sheet et Range. Cette section décrit ces classes et leur utilité.

Classe SpreadsheetApp

Avant de vous plonger dans les feuilles de calcul, les feuilles et les plages, vous devez examiner leur classe parente : SpreadsheetApp. De nombreux scripts commencent par appeler des méthodes SpreadsheetApp, car elles peuvent fournir le point d'accès initial à vos fichiers Google Sheets. Vous pouvez considérer SpreadsheetApp comme la classe principale du service Spreadsheet. La classe SpreadsheetApp n'est pas explorée en détail ici. Toutefois, vous trouverez des exemples et des exercices plus loin dans cet atelier de programmation pour vous aider à comprendre cette classe.

Feuilles de calcul, feuilles et leurs classes

Dans Sheets, une feuille de calcul est un fichier Google Sheets (stocké dans Google Drive) qui contient des données organisées en lignes et en colonnes. Une feuille de calcul est parfois appelée "feuille Google Sheets", de la même manière qu'un document est appelé "document Google Docs".

Vous pouvez utiliser la classe Spreadsheet pour accéder aux données des fichiers Google Sheets et les modifier. Vous pouvez également utiliser cette classe pour d'autres opérations au niveau du fichier, comme l'ajout de collaborateurs.

Une feuille** représente une page individuelle d'une feuille de calcul, parfois appelée "onglet". Chaque feuille de calcul peut contenir une ou plusieurs feuilles. Vous pouvez utiliser la classe Sheet** pour accéder aux données et aux paramètres au niveau de la feuille, et les modifier (par exemple, déplacer des lignes ou des colonnes de données).

Point clé : En plus d'un nom, les feuilles de calcul possèdent également une chaîne d'ID unique. L'ID est présent dans l'URL de la feuille de calcul au format suivant :

https://docs.google.com/spreadsheets/d/spreadsheetID/edit#gid=0

Chaque feuille d'une feuille de calcul possède également un identifiant unique à cette feuille de calcul. Ce numéro d'ID est aléatoire, mais vous pouvez également le voir dans l'URL de la feuille de calcul au format suivant :

https://docs.google.com/spreadsheets/d/spreadsheetID/edit#gid=sheetID

Vous pouvez voir les deux ID dans cet exemple d'URL :

Étant donné que les ID des feuilles de calcul et des feuilles sont uniques (contrairement aux noms des feuilles de calcul), il est préférable d'utiliser les ID pour spécifier les feuilles de calcul et les feuilles lorsque cela est possible. Par exemple, vous pouvez avoir plusieurs feuilles de calcul portant le même nom dans votre Google Drive. En utilisant des méthodes de script telles que SpreadsheetApp.openById(id) pour ouvrir vos feuilles de calcul, vous pouvez éviter de modifier accidentellement le mauvais fichier.

Pour en savoir plus sur les ID dans Google Sheets, consultez la présentation de Google Sheets.

En résumé, la classe Spreadsheet fonctionne sur la collection de feuilles et définit un fichier Google Sheets dans Google Drive. La classe Sheet fonctionne sur des feuilles individuelles d'une feuille de calcul.

Classe Range

La plupart des opérations de manipulation de données (par exemple, la lecture, l'écriture ou la mise en forme des données des cellules) nécessitent que vous définissiez les cellules auxquelles l'opération s'applique. Vous pouvez utiliser la classe Range pour sélectionner des ensembles spécifiques de cellules dans une feuille. Les instances de cette classe représentent une plage, c'est-à-dire un groupe d'une ou plusieurs cellules adjacentes dans une feuille. Vous pouvez spécifier des plages par leur numéro de ligne et de colonne, ou à l'aide de la notation A1.

Terme clé : dans Apps Script, la feuille active est celle qui est actuellement ouverte et visible dans l'interface Google Sheets. De même, la plage active est le groupe d'une ou plusieurs cellules actuellement mises en surbrillance dans la feuille active. La cellule active est la cellule sélectionnée dans la feuille active, sur laquelle le curseur est positionné.

De nombreuses méthodes de classe renvoient l'objet actif de la classe demandée. Par exemple, l'exécution de SpreadsheetApp.getActiveSpreadsheet() renvoie la feuille de calcul active ou actuelle de l'utilisateur. En obtenant la feuille de calcul active, vous pouvez lui appliquer des opérations. De même, des méthodes telles que Sheet.activate() peuvent modifier l'objet actif.

Activer une feuille, une plage ou une cellule revient à cliquer dessus dans l'interface Google Sheets, ce qui déplace le curseur vers cet emplacement.

Le reste de l'atelier de programmation présente des exemples de scripts qui fonctionnent avec ces classes et leurs méthodes.

3. Configurer

Avant de continuer, vous avez besoin d'une feuille de calcul contenant des données. Nous vous en avons fourni un : cliquez sur ce lien pour copier la feuille de données, puis sur Créer une copie.

Une copie de l'exemple de feuille de calcul est placée dans votre dossier Google Drive et nommée "Copie de Feuille de calcul sans titre". Utilisez cette feuille de calcul pour effectuer les exercices de cet atelier de programmation.

Pour rappel, vous pouvez ouvrir l'éditeur de script depuis Google Sheets en cliquant sur Extensions > Apps Script.

Lorsque vous ouvrez un projet Apps Script dans l'éditeur de script pour la première fois, l'éditeur de script crée un projet de script et un fichier de script pour vous.

La section suivante vous explique comment utiliser la classe Spreadsheet pour améliorer cette feuille de calcul.

4. Accéder aux feuilles de calcul et les modifier

Dans cette section, vous allez apprendre à utiliser les classes SpreadsheetApp et Spreadsheet pour accéder aux feuilles de calcul et les modifier. Plus précisément, les exercices vous apprendront à renommer une feuille de calcul et à dupliquer des feuilles dans une feuille de calcul.

Il s'agit d'opérations simples, mais elles font souvent partie d'un workflow plus vaste et plus complexe. Une fois que vous aurez compris comment automatiser ces tâches avec du code de script, il vous sera plus facile d'apprendre à automatiser des opérations plus complexes.

Renommer la feuille de calcul active

Imaginons que vous souhaitiez remplacer le nom par défaut "Copie de la feuille de calcul sans titre" par un titre qui reflète mieux l'objectif de votre feuille de calcul. Vous pouvez le faire avec les classes SpreadsheetApp et Spreadsheet.

1. Dans l'éditeur de script, remplacez le bloc de code myFunction() par défaut par le code suivant :

function renameSpreadsheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  mySS.rename("2017 Avocado Prices in Portland, Seattle");
}

2. Pour enregistrer votre script, cliquez sur Enregistrer .
3. Pour renommer votre projet Apps Script, cliquez sur Projet sans titre, saisissez "Prix des avocats" comme nouveau nom de projet, puis cliquez sur Renommer.
4. Pour exécuter votre script, sélectionnez renameSpreadsheet dans la liste des fonctions, puis cliquez sur Exécuter.
5. Autorisez la macro en suivant les instructions à l'écran. Si le message "Cette application n'est pas validée" s'affiche, cliquez sur Paramètres avancés, puis sur Accéder à Prix des avocats (non sécurisé). Sur l'écran suivant, cliquez sur Autoriser.

Remarque : Si vous utilisez un compte gmail.com, il est possible qu'une boîte de dialogue indiquant que l'application n'est pas validée s'affiche lorsque vous utilisez votre script pour la première fois. Google utilise cette information pour avertir les utilisateurs qui pourraient utiliser du code provenant d'auteurs inconnus ou non fiables. Si cette boîte de dialogue s'affiche, vous pouvez continuer, car vous êtes l'auteur du script. Suivez les instructions à l'écran pour continuer à autoriser le script.

Une fois la fonction exécutée, le nom de fichier de votre feuille de calcul devrait changer :

Examinons le code que vous avez saisi. La méthode getActiveSpreadsheet() renvoie un objet représentant la feuille de calcul active, c'est-à-dire la copie de la feuille de calcul de l'exercice que vous avez créée. Cet objet de feuille de calcul est stocké dans la variable mySS. L'appel rename(newName) sur mySS remplace le nom du fichier de feuille de calcul dans Google Drive par "Prix des avocats à Portland et Seattle en 2017".

Étant donné que la variable mySS est une référence à la feuille de calcul, vous pouvez rendre votre code plus clair et plus efficace en appelant les méthodes Spreadsheet sur mySS au lieu d'appeler getActiveSpreadsheet() à plusieurs reprises.

Dupliquer la feuille active

Votre feuille de calcul actuelle ne comporte qu'une seule feuille. Vous pouvez appeler la méthode Spreadsheet.duplicateActiveSheet() pour créer une copie de la feuille :

1. Ajoutez la nouvelle fonction suivante sous la fonction renameSpreadsheet() déjà présente dans votre projet de script :

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();
}

2. Enregistrez votre projet de script.
3. Pour exécuter votre script, sélectionnez duplicateAndOrganizeActiveSheet dans la liste des fonctions, puis cliquez sur Exécuter.

Revenez dans Sheets. Vous verrez qu'un nouvel onglet de feuille "Copie de Sheet_Original" a été ajouté à votre feuille de calcul.

Dans cette nouvelle fonction, la méthode duplicateActiveSheet() crée, active et renvoie la feuille dupliquée dans votre feuille de calcul. La feuille obtenue est stockée dans duplicateSheet, mais le code ne fait encore rien avec cette variable.

Dans la section suivante, vous utiliserez la classe Sheet pour renommer et mettre en forme la feuille en double.

5. Mettre en forme votre feuille avec la classe Sheet

La classe Sheet fournit des méthodes qui permettent aux scripts de lire et de mettre à jour des feuilles. Dans cette section, vous allez apprendre à modifier le nom d'une feuille et la largeur de ses colonnes à l'aide des méthodes de la classe Sheet.

Remarque : Les classes Spreadsheet** et Sheet partagent plusieurs méthodes courantes spécifiques aux feuilles, telles que Spreadsheet.deleteColumn(columnPosition) et Sheet.deleteColumn(columnPosition).

Lorsque vous appelez Spreadsheet.deleteColumn(columnPosition), la méthode supprime des colonnes de la feuille de calcul actuellement active (ouverte). Vous pouvez obtenir le même résultat en appelant getActiveSheet(), puis Sheet.deleteColumn(columnPosition). La version Spreadsheet vous fait tout simplement gagner du temps.

Modifier le nom de la feuille

Renommer des feuilles est aussi simple que renommer une feuille de calcul dans renameSpreadsheet(). Un seul appel de méthode est nécessaire.

1. Dans Google Sheets, cliquez sur la feuille Sheet_Original pour l'activer.
2. Dans Apps Script, modifiez votre fonction duplicateAndOrganizeActiveSheet() pour qu'elle corresponde à ce qui suit :

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());
}

3. Enregistrez et exécutez la fonction.

Dans Google Sheets, une feuille en double est créée et renommée lorsque vous exécutez la fonction :

Dans le code ajouté, la méthode setName(name) modifie le nom de la feuille duplicateSheet à l'aide de getSheetID() pour obtenir l'ID unique de la feuille. L'opérateur + concatène l'ID de la feuille à la fin de la chaîne "Sheet_".

Modifier les colonnes et les lignes d'une feuille

Vous pouvez également utiliser la classe Sheet pour mettre en forme votre feuille. Par exemple, nous pouvons mettre à jour votre fonction duplicateAndOrganizeActiveSheet() pour redimensionner également les colonnes de la feuille en double et ajouter des lignes figées :

1. Dans Google Sheets, cliquez sur la feuille Sheet_Original pour l'activer.
2. Dans Apps Script, modifiez votre fonction duplicateAndOrganizeActiveSheet() pour qu'elle corresponde à ce qui suit :

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);
}

3. Enregistrez et exécutez la fonction.

Dans Google Sheets, une feuille en double est créée, renommée, activée et mise en forme :

Le code que vous avez ajouté utilise autoResizeColumns(startColumn, numColumns) pour redimensionner les colonnes de la feuille afin de les rendre plus lisibles. La méthode setFrozenRows(rows) fige le nombre de lignes indiqué (deux dans ce cas), ce qui permet de garder les lignes d'en-tête visibles lorsque le lecteur fait défiler la feuille de calcul vers le bas.

Remarque : Lorsque vous travaillez avec des tableaux dans Apps Script, vous suivez la convention selon laquelle la liste commence à l'index 0. Toutefois, lorsque vous travaillez avec des lignes et des colonnes, l'index commence à 1. Cette logique correspond à la mise en forme conventionnelle des feuilles de calcul, où vous commencez à la ligne 1 et à la colonne A dans Sheets.

Gardez cela à l'esprit lorsque vous utilisez des méthodes liées aux colonnes et aux lignes, telles que autoResizeColumns(startColumn, numColumns). Dans l'exercice actuel, vous saisissez 1 (et non 0) comme paramètre pour faire référence à la colonne A de duplicateSheet.

Dans la section suivante, vous découvrirez les plages et la manipulation de base des données.

6. Réorganiser les données avec la classe Range

La classe Range et ses méthodes fournissent la plupart des options de manipulation et de mise en forme des données dans le service Spreadsheet.

Cette section présente la manipulation de données de base avec des plages. Ces exercices se concentrent sur l'utilisation des plages dans Apps Script, tandis que d'autres ateliers de programmation de cette playlist abordent plus en détail la manipulation et la mise en forme des données.

Déplacer des plages

Vous pouvez activer et déplacer des plages de données avec des méthodes de classe et la notation A1, un raccourci permettant d'identifier des ensembles de cellules spécifiques dans les feuilles de calcul. Si vous avez besoin de vous rafraîchir la mémoire, vous pouvez consulter cette description de la notation A1.

Mettons à jour votre méthode duplicateAndOrganizeActiveSheet() pour déplacer également certaines données :

1. Dans Google Sheets, cliquez sur la feuille Sheet_Original pour l'activer.
2. Dans Apps Script, modifiez votre fonction duplicateAndOrganizeActiveSheet() pour qu'elle corresponde à ce qui suit :

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);

  // Move column F to column C.
  var myRange = duplicateSheet.getRange("F2:F");
  myRange.moveTo(duplicateSheet.getRange("C2"));
}

3. Enregistrez et exécutez la fonction.

Lorsque vous exécutez cette fonction, une feuille en double est créée, activée et mise en forme. Le contenu de la colonne F est déplacé vers la colonne C :

Le nouveau code utilise la méthode getRange(a1Notation) pour identifier la plage de données à déplacer. En saisissant la notation A1 "F2:F" comme paramètre de la méthode, vous spécifiez la colonne F (à l'exclusion de F1). Si la plage spécifiée existe, la méthode getRange(a1Notation) renvoie son instance Range. Le code stocke l'instance dans la variable myRange pour faciliter l'utilisation.

Une fois la plage identifiée, la méthode moveTo(target) prend le contenu de myRange (valeurs et mise en forme) et le déplace. La destination (colonne C) est spécifiée à l'aide de la notation A1 "C2". Il s'agit d'une seule cellule, et non d'une colonne. Lorsque vous déplacez des données, vous n'avez pas besoin de faire correspondre les tailles aux plages source et de destination. Apps Script aligne simplement la première cellule de chaque.

Remarque : Lorsque vous écrivez vos propres scripts, veillez à les tester minutieusement pour vous assurer que vos données sont transférées à l'emplacement souhaité. Apps Script écrase les valeurs de données existantes lorsque vous modifiez les valeurs des cellules. Si vous ne faites pas attention, vous risquez d'écraser des données que vous souhaitez conserver. Dans cet exemple, la colonne C était vide, mais si elle contenait des données, la méthode moveTo(target) les aurait supprimées.

Trier des plages

La classe Range vous permet de lire, de mettre à jour et d'organiser des groupes de cellules. Par exemple, vous pouvez trier une plage de données à l'aide de la méthode Range.sort(sortSpecObj) :

1. Dans Google Sheets, cliquez sur la feuille Sheet_Original pour l'activer.
2. Dans Apps Script, modifiez votre fonction duplicateAndOrganizeActiveSheet() pour qu'elle corresponde à ce qui suit :

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);

  // Move column F to column C.
  var myRange = duplicateSheet.getRange("F2:F");
  myRange.moveTo(duplicateSheet.getRange("C2"));

  // Sort all the data using column C (Price information).
  myRange = duplicateSheet.getRange("A3:D55");
  myRange.sort(3);
}

3. Enregistrez et exécutez la fonction.

Désormais, en plus de la mise en forme précédente, la fonction trie toutes les données du tableau en utilisant les informations sur les prix de la colonne C :

Le nouveau code utilise getRange(a1Notation) pour spécifier une nouvelle plage couvrant A3:D55 (l'ensemble du tableau, à l'exception des en-têtes de colonne). Le code appelle ensuite la méthode sort(sortSpecObj) pour trier le tableau. Ici, le paramètre sortSpecObj correspond au numéro de la colonne selon laquelle effectuer le tri. La méthode trie la plage de sorte que les valeurs de la colonne indiquée soient classées de la plus basse à la plus élevée (valeurs croissantes). La méthode sort(sortSpecObj) peut répondre à des exigences de tri plus complexes, mais vous n'en avez pas besoin ici. Vous pouvez consulter toutes les manières d'appeler les plages de tri dans la documentation de référence de la méthode.

Félicitations, vous avez terminé tous les exercices de l'atelier de programmation. La section suivante passe en revue les points clés de cet atelier de programmation et présente le prochain atelier de programmation de cette playlist.

7. Conclusion

Vous avez terminé cet atelier de programmation. Vous pouvez désormais utiliser et définir les classes et les termes essentiels du service Spreadsheet dans Apps Script.

Vous êtes prêt à passer au prochain atelier de programmation.

Avez-vous trouvé cet atelier de programmation utile ?

Oui Non

Points abordés

• Comment les feuilles de calcul, les feuilles et les plages sont représentées dans Apps Script.
• Quelques utilisations de base des classes SpreadsheetApp, Spreadsheet, Sheet et Range.

Concepts clés :

1. Dans Apps Script, tous les tableaux suivent la convention selon laquelle la liste commence à l'index 0. Toutefois, les lignes et les colonnes sont indexées à partir de 1.
2. Dans Apps Script, la feuille, la plage ou la cellule actuellement ouvertes et mises en surbrillance sont considérées comme actives. De nombreuses méthodes Apps Script accèdent à l'élément actif ou le modifient.
3. Les classes Spreadsheet et Sheet partagent plusieurs méthodes spécifiques aux feuilles. Les méthodes dupliquées dans la classe Spreadsheet sont uniquement destinées à faciliter l'utilisation, car elles ciblent généralement la feuille active lorsqu'elles sont appelées.
4. Chaque feuille de calcul et feuille possède un ID unique, que vous trouverez dans l'URL du navigateur. Les méthodes de classe de feuille de calcul et de feuille utilisent des ID. Pour en savoir plus sur les ID et les URL, consultez Présentation de Google Sheets.

Termes clés :

• Active (état) : indique que la feuille de calcul, la feuille, la plage ou la cellule spécifiées sont celles que l'utilisateur de la feuille de calcul consulte ou met en surbrillance actuellement.
• Cellule active : cellule sélectionnée dans la feuille active, sur laquelle le curseur est positionné.
• Plage active : groupe d'une ou plusieurs cellules actuellement mises en surbrillance dans la feuille active.
• Autorisation : processus par lequel l'utilisateur accorde des autorisations à Apps Script pour accéder à ses données dans les services Google concernés ou effectuer d'autres actions en son nom.
• Script lié à un conteneur : tout script lié à un document Google Workspace (par exemple, une feuille de calcul ou un document Google) et créé à partir de celui-ci.
• Plage : groupe d'une ou plusieurs cellules adjacentes dans une feuille. La classe Range** vous permet de lire et de mettre à jour des plages dans une feuille.
• Éditeur de script : éditeur de code intégré au navigateur pour Apps Script.
• Feuille**: page unique d'une feuille de calcul (semblable aux classeurs Microsoft Excel). Terme parfois utilisé pour désigner un fichier Google Sheets dans Google Drive. La classe Sheet** vous permet d'accéder aux feuilles et de les modifier.
• SpreadsheetApp : cette classe sert de classe parente pour le service Spreadsheet et fournit un point de départ pour le code qui lit ou manipule les données Google Sheets.
• Feuille de calcul : fichier Google Sheets résidant dans Google Drive. La classe Spreadsheet** vous permet d'accéder aux feuilles de calcul et de les modifier.
• Service de feuilles de calcul : service Apps Script qui permet aux scripts de créer, d'accéder et de modifier des fichiers Google Sheets.

Étape suivante

Le prochain atelier de programmation de cette playlist explique plus en détail comment lire, écrire et modifier des données dans une feuille de calcul.

Retrouvez le prochain atelier de programmation sur la page Utiliser des données.