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

1. Introduction

Bienvenue dans la deuxième partie de la playlist de l'atelier de programmation sur les principes de base d'Apps Script avec Google Sheets. Le précédent atelier de programmation était consacré aux concepts de l'éditeur de scripts, des macros et des fonctions personnalisées. Cet atelier de programmation aborde le service Spreadsheet qui vous permet de lire, écrire et manipuler des données dans Google Sheets.

Points abordés

• Mode de représentation des feuilles de calcul, des feuilles et des plages dans Apps Script.
• Accéder à la feuille de calcul active (ouverte), la créer et la renommer à l'aide des classes SpreadsheetApp et Spreadsheet.
• Comment modifier le nom d'une feuille et l'orientation d'une colonne ou d'une ligne 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 sur la playlist Fundamentals Fundamentals of Apps Script with Google Sheets. Avant de commencer, veillez à suivre le premier atelier de programmation: Macros et fonctions personnalisées.

Ce dont vous avez besoin

• Une compréhension des thèmes de base d'Apps Script explorés dans l'atelier de programmation précédent de cette playlist.
• Connaissances de base de l'éditeur Apps Script
• Des connaissances de base sur Google Sheets
• Possibilité de lire la notation A1 de Sheets
• Bonne connaissance de JavaScript et de sa classe String

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

2. Présentation du service Spreadsheet

Quatre classes couvrent la base du service Spreadsheet, à savoir SpreadsheetApp, Spreadsheet, Sheet et Range. Cette section décrit ces classes et ce à quoi elles servent.

Classe SpreadsheetApp

Avant d'étudier les feuilles de calcul, les feuilles et les plages, vous devez consulter leur cours parent: SpreadsheetApp. De nombreux scripts commencent par appeler les méthodes SpreadsheetApp, car ils peuvent servir de 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, dans la suite de cet atelier de programmation, vous trouverez des exemples et des exercices pour vous aider à comprendre cette leçon.

Feuilles de calcul, feuilles et leurs classes

Un terme Sheets désigne une feuille de calcul Google Sheets (stockée dans Google Drive) qui contient des données organisées par lignes et colonnes. De même qu'une feuille de calcul est parfois appelée "Google Sheets'".

La classe Spreadsheet vous permet d'accéder aux données des fichiers Google Sheets et de les modifier. Vous pouvez également utiliser cette classe pour d'autres opérations au niveau du fichier, par exemple pour ajouter des collaborateurs.

Une feuille** représente la page spécifique 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, les lignes ou les colonnes de données déplacées.

Important : Outre la dénomination, les feuilles de calcul présentent également une chaîne d'ID propre à cette feuille. L'ID se trouve dans l'URL de la feuille de calcul au format suivant:

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

Un ID est également attribué à chaque feuille d'une feuille de calcul. Cet identifiant est aléatoire, mais il peut également figurer dans l'URL de la feuille de calcul au format suivant:

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

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

Les ID des feuilles de calcul et des feuilles étant uniques (et non leurs noms), il est préférable d'utiliser les ID afin de spécifier des feuilles de calcul. Par exemple, vous pouvez créer plusieurs feuilles de calcul portant le même nom dans votre compte 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 par erreur 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 s'appuie sur la collection de feuilles et définit un fichier Google Sheets dans Google Drive. La classe Sheet s'applique à des feuilles individuelles dans une feuille de calcul.

Classe Range

La plupart des opérations de manipulation de données (lecture, écriture ou mise en forme des données de cellule, par exemple) nécessitent de définir 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 de plusieurs cellules adjacentes dans une feuille. Vous pouvez spécifier des plages par numéro de ligne et de colonne, ou en utilisant 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 la même manière, la plage active correspond au groupe d'une ou de plusieurs cellules actuellement mises en évidence dans la feuille active. La cellule active est la seule cellule en surbrillance dans la feuille active, avec le curseur.

De nombreuses méthodes classent 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. La feuille de calcul active vous permet d'y appliquer des opérations. De même, des méthodes telles que la méthode Sheet.activate() peuvent modifier l'objet actif.

L'activation d'une feuille, d'une plage ou d'une cellule équivaut à cliquer sur cette feuille, cette plage ou cette cellule dans l'interface Google Sheets, ce qui permet de déplacer le curseur vers cet emplacement.

Le reste de l'atelier de programmation propose des exemples de scripts qui utilisent ces classes et leurs méthodes.

3. Configurer

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

Une copie de l'exemple de feuille de calcul est à votre disposition dans votre dossier Google Drive. Elle est nommée "Copie de feuille de calcul sans titre". Utilisez-la pour réaliser les exercices de cet atelier de programmation.

Pour rappel, vous pouvez ouvrir l'éditeur de scripts à partir de 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, il crée à la fois un projet et un fichier de script.

La section suivante vous explique comment améliorer la feuille de calcul à l'aide de la classe Spreadsheet.

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 à des feuilles de calcul et les modifier. Plus précisément, les exercices vous expliquent comment renommer une feuille de calcul et comment dupliquer des feuilles dans une feuille de calcul.

Bien qu'il s'agisse d'opérations simples, elles font souvent partie d'un workflow plus vaste et plus complexe. Une fois que vous savez comment automatiser ces tâches avec du code de script, il vous sera plus facile d'apprendre à automatiser des opérations plus sophistiquées.

Renommer la feuille de calcul active

Supposons que vous souhaitiez remplacer le nom par défaut (copie de la feuille de calcul sans titre) par un titre plus représentatif de la fonction de la feuille de calcul. Pour ce faire, utilisez 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 Project sans nom, saisissez "prix Avocado" comme nouveau nom du projet, puis cliquez sur Renommer.
4. Pour exécuter votre script, sélectionnez renameSpreadsheet dans la liste des fonctions, puis cliquez sur Run (Exécuter).
5. Autorisez la macro en suivant les instructions à l'écran. Si vous recevez un message concernant le statut "Cette application n'est pas validée", cliquez sur Paramètres avancés, puis sur Accéder aux prix d'Avocado (non sécurisé). Sur l'écran suivant, cliquez sur Allow (Autoriser).

Remarque : Si vous utilisez un compte gmail.com, une boîte de dialogue d'application non validée peut s'afficher lorsque vous utilisez votre script pour la première fois. Nous l'utilisons pour avertir les utilisateurs susceptibles d'utiliser du code provenant d'auteurs inconnus ou non approuvés. 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 du fichier de la feuille de calcul devrait changer:

Regardons 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 que vous avez créée. Cet objet de feuille de calcul est stocké dans la variable mySS. L'appel de rename(newName) au mySS remplace le nom du fichier de la feuille de calcul dans Google Drive par "Advocates 2017" à Portland, Seattle."

La variable mySS étant une référence à la feuille de calcul, vous pouvez rendre votre code plus propre et plus efficace en appelant des méthodes Spreadsheet sur mySS au lieu d'appeler getActiveSpreadsheet() à chaque fois.

Dupliquer la feuille active

Votre feuille de calcul actuelle ne contient 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à dans votre projet de script:

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

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

Revenez à Sheets pour afficher le nouvel onglet "Feuille "Original" de la feuille de calcul.

Dans cette nouvelle fonction, la méthode duplicateActiveSheet() crée, active et renvoie la feuille en double dans votre feuille de calcul. La feuille de calcul obtenue est stockée dans duplicateSheet, mais le code n'effectue aucune action avec cette variable pour le moment.

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

5. Mettre en forme votre feuille à l'aide de la classe Sheet

La classe Sheet fournit des méthodes permettant aux scripts de lire et de mettre à jour des feuilles. Dans cette section, vous pouvez apprendre à modifier le nom d'une feuille et la largeur de ses colonnes à l'aide de méthodes issues 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 les colonnes de la feuille actuellement active (ouverte) dans la feuille de calcul. Pour obtenir le même résultat, appelez getActiveSheet(), puis Sheet.deleteColumn(columnPosition). La version Spreadsheet vous fait gagner du temps.

Modifier le nom de la feuille

Pour renommer des feuilles, il suffit de renommer la 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 du duplicateSheet à l'aide de getSheetID() pour obtenir le numéro d'identification de la feuille. L'opérateur + concatène l'ID de 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.

Une feuille de calcul Google Sheets est créée, renommée, activée et formatée:

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

Remarque : Lorsque vous utilisez des tableaux dans Apps Script, vous respectez la convention d'origine de la liste à l'index 0. Toutefois, lorsque vous travaillez avec des lignes et des colonnes, l'index commence à 1. Cette logique est alignée sur la mise en forme traditionnelle des feuilles de calcul. Elle débute à la ligne 1 et à la colonne A de Sheets.

Tenez compte de cela lorsque vous utilisez des méthodes liées aux colonnes et aux lignes telles que autoResizeColumns(startColumn, numColumns). Dans notre exercice actuel, vous avez saisi 1 (et non 0) comme paramètre pour faire référence à la colonne A du 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 base des données avec des plages. Ces exercices se concentrent sur l'utilisation de plages dans Apps Script, tandis que les autres ateliers de programmation de cette playlist approfondissent 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 à l'aide de méthodes de classe et de la notation A1, dans le but d'identifier des ensembles spécifiques de cellules dans une feuille de calcul. Si vous devez vous reconnaître, consultez cette description de la notation A1.

Mettez à 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é dans 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&quot" en tant que paramètre de méthode, vous spécifiez la colonne F (sauf 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 son utilisation.

Une fois la plage identifiée, la méthode moveTo(target) utilise le contenu de myRange (à la fois les valeurs et la mise en forme) et les déplace. La destination (colonne C) est spécifiée à l'aide de la notation A1, "C2". Il s'agit d'une cellule unique, plutôt que d'une colonne. Lorsque vous déplacez des données, il n'est pas nécessaire que les tailles correspondent aux plages cible et de destination. Apps Script aligne simplement la première cellule de chacune.

Remarque : Lorsque vous créez vos propres scripts, veillez à les tester soigneusement pour vous assurer que vos données sont traitées comme vous le souhaitez. Apps Script écrase les valeurs de données existantes lors de la modification des valeurs de cellule. Si vous ne faites pas attention, vous pouvez remplacer les données que vous souhaitez conserver. Dans cet exemple, la colonne C était vide, mais si des données s'affichaient, la méthode moveTo(target) l'aurait supprimée.

Trier les 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.

Après la mise en forme précédente, la fonction trie toutes les données de la table en fonction des informations tarifaires dans la colonne C:

Le nouveau code utilise getRange(a1Notation) pour spécifier une nouvelle plage couvrant la plage A3:D55 (tout le 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 colonne à utiliser pour le tri. La méthode trie la plage pour que les valeurs de colonne indiquées passent de la valeur la plus faible à la valeur la plus élevée (valeurs croissantes). La méthode sort(sortSpecObj) permet d'effectuer des tris plus complexes, mais vous n'en avez pas besoin ici. Pour connaître les différentes façons d'appeler des plages de tri, consultez la documentation de référence sur les méthodes.

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évisualise le prochain atelier de programmation dans cette playlist.

7. Conclusion

Vous avez atteint la fin de cet atelier de programmation. Vous pouvez désormais utiliser les classes et conditions essentielles du service Spreadsheet dans Apps Script.

Vous êtes prêt à passer à l'atelier de programmation suivant.

Cet atelier de programmation vous a-t-il été utile ?

Oui Non

Points abordés

• Mode de représentation des feuilles de calcul, des feuilles et des plages dans Apps Script.
• Voici quelques exemples d'utilisation de base des classes SpreadsheetApp, Spreadsheet, Sheet et Range.

Concepts clés:

1. Dans Apps Script, tous les tableaux suivent la convention établie à partir de laquelle la liste commence à l'index 0. Toutefois, les lignes et les colonnes sont indexées à partir de 1.
2. Dans Apps Script, la cellule actuellement ouverte, telle que la plage en surbrillance, est considérée comme active. De nombreuses méthodes Apps Script accèdent au composant actif ou le modifient.
3. Les classes Spreadsheet et Sheet partagent plusieurs méthodes spécifiques à une feuille. Les méthodes en double dans la classe Spreadsheet sont fournies à des fins de confort uniquement, car elles ciblent généralement la feuille active lorsqu'elles sont appelées.
4. Chaque feuille de calcul ou chaque feuille possède un identifiant unique, que vous pouvez trouver dans l'URL de votre navigateur. Les méthodes de classe "Feuille de calcul" et "Feuille" utilisent des ID. Pour en savoir plus sur les ID et les URL, consultez Présentation de Google Sheets.

Termes clés:

• Actif (état) : indique la feuille de calcul, la feuille, la plage ou la cellule spécifiées par l'utilisateur de la feuille de calcul actuellement affichée ou mise en surbrillance.
• Cellule active:cellule unique en surbrillance dans la feuille active avec le curseur.
• Plage active: groupe d'une ou plusieurs cellules actuellement mises en évidence dans la feuille active.
• Autorisation : processus permettant à l'utilisateur d'autoriser Apps Script à accéder aux données utilisateur dans les services Google concernés ou à effectuer des actions pour le compte de l'utilisateur.
• Script lié à un conteneur : tout script lié à un document Google Workspace, tel qu'une feuille de calcul Google Sheets ou un document Google Docs, et créé à partir de ce document.
• Plage** : groupe d'une ou de plusieurs cellules adjacentes dans une feuille. La classe Range** vous permet de lire et de mettre à jour les plages au sein d'une feuille.
• Éditeur de scripts : éditeur de code intégré au navigateur pour Apps Script.
• Feuille**: page unique d'une feuille de calcul (semblable aux classeurs Microsoft Excel). Sert parfois à faire référence à un fichier Google Sheets dans Google Drive. La classe Sheet** vous permet d'accéder à des feuilles et de les modifier.
• SpreadsheetApp : cette classe est la classe parente du service Spreadsheet. Elle fournit un point de départ pour la lecture et la manipulation des données Google Sheets.
• Feuille de calcul**: fichier Google Sheets stocké dans Google Drive. La classe Spreadsheet** vous permet d'accéder aux feuilles de calcul et de les modifier.
• Service Spreadsheet: service Apps Script qui permet aux scripts de créer des fichiers Google Sheets, d'y accéder et de les modifier.

Étapes suivantes

Dans l'atelier de programmation suivant de cette playlist, nous allons approfondir la lecture, l'écriture et la modification des données dans une feuille de calcul.

Trouvez le prochain atelier de programmation sur Utiliser des données.