Les principaux objets Google Sheets
Créer une macro-commande sous Google Sheets, impose l'utilisation de l'objet SpreadsheetApp, instance de la classe racine SpreadsheetApp (accès à documentation) . Cet objet est créé au lancement de l'application.
Cette classe dispose de nombreuses propriétés et méthodes parmi lesquelles :
getActiveSpreadsheet qui rend un objet correspondant au classeur actif ;
getActiveSheet qui rend un objet correspondant à la feuille active ;
getCurrentCell qui rend un objet correspondant à la cellule active ;
getSelection qui rend un objet correspondant à la sélection actuelle ;
....
setActiveSheet qui rend actif l'objet "feuille de calcul" passé en paramètre ;
setCurrentCell qui sélectionne l'objet "cellule" passé en paramètre.
Exemples : le code ci-dessous instancie les variables :
leClasseur (c'est le classeur en cours d'utilisation) en ligne 1 ;
laFeuille (c'est la feuille de calcul en cours d'utilisation) en ligne 2.
1. var leClasseur = SpreadsheetApp.getActiveSpreadsheet() ;
2. var laFeuille = SpreadsheetApp.getActiveSheet() ;
1: Le classeur actif
Une instance du classeur actif est rendue par la propriété getActiveSpreadsheet de l'objet racine SpreadsheetApp (cf. introduction de ce billet).
Le classeur actif est une instance de la classe spreadsheet (accès à la documentation officielle). Voici quelques unes des très nombreuses propriétés et méthodes de son interface :
addMenu
getActiveCell : rend une instance de la cellule active ;
getActiveRange : rend une instance de la plage active ou de la cellule active ;
getName : rend le nom (intitulé) du classeur ;
getSheetByName : rend une instance de la feuille dont le nom est donné en paramètre ;
getSheetId : rend une instance de la feuille dont le numéro d'ordre est donné en paramètre ;
getSheetName : rend le nom de la feuille dont une instance est donnée en paramètre ;
getSheets : rends la liste des (objets) feuilles du classeur dont une instance est donnée en paramètre ;
insertSheet : insère une nouvelle feuille après la feuille active. Cette feuille porte le nom par défaut : "feuille n°X" où X est le rang de la feuille dans la liste des feuilles du classeur.
Le code ci-dessous instancie les variables :
- leClasseur (c'est le classeur ouvert) en ligne 2 ;
- laFeuille (c'est la feuille de calcul en cours d'utilisation) en ligne 3 ;
Il affiche ensuite en ligne 5, le nombre de feuilles de calcul de ce classeur.
- var nbFeuilles
- var leClasseur = SpreadsheetApp.getActiveSpreadsheet();
- var laFeuille = SpreadsheetApp.getActiveSheet();
- nbFeuilles = leClasseur.getSheets().length ;
- SpreadsheetApp.getUi().alert( leClasseur.getName() + " a " + nbFeuilles + " feuilles");
2 : La feuille active :
Une instance de la feuille active est rendue par la propriété getActiveSheet de l'objet racine SpreadsheetApp (cf introduction de ce billet).
La feuille active est une instance de la classe "sheet" (accès à la documentation officielle) dont voici un extrait des nombreuses propriétés et méthodes de son interface :
activate : la feuille devient la feuille active ;
clear : efface le contenu de la feuille ;
copyTo : copie la feuille (source) dans le classeur dont l'instance est passée en paramètre. La copie est nommé "copie de source" ;
deleteRow (resp. deleteRows) : suppression d'une (resp. de plusieurs) lignes ;
deleteColumn (resp. deleteColumns) : suppression d'une (resp. de plusieurs) colonnes ;
getCurrentCell : rend une instance de la cellule sélectionnée ;
getActiveRange : rend une instance de la plage sélectionnée ;
- getRange : rend une cellule ou une plage de cellules selon le nombre et la nature des paramètres transmis ; exemple :
- getRange("A1")
- getRange("A1:B5")
- getRange(1,1) : rend la cellule "A1"
- getRange(1,1,5) : rend la plage "A1;A5" qui débute en A1 et compte 5 lignes
- getRange(1,1,5,2) : rend la plage "A1:B5" qui débute en A1 et compte 5 lignes et 2 colonnes
- getA1Notation : rend le nom de de la cellule (sous la forme chaîne de caractères) :
- getRange(1,1,5,2).getA1Notation() : rend la chaîne(string) "A1:B5"
- insertColumns, insertColumnsAfter, insertColumnsBefore : insertion de colonnes...
- insertRows, insertRowsAfter ; insertRowsBefore : insertion de lignes...
- setActiveRange, setActiveSelection : détermine la plage (cellule) sélectionnée.
Voir en bas de page une macro qui crée la copie d'une feuille de calcul.
Dans le code ci-dessous, la variable "laFeuille" représente la feuille active d'un classeur (ligne 1) et affiche dans le fichier "log" le texte "A1:B5" (ligne 2).
1. var laFeuille = SpreadsheetApp.getActiveSheet();
2. Logger.log ( "la plage est : " + laFeuille.getRange(1,1,5,2).getA1Notation() )
3 : La cellule et la plage :
La classe "range" définit les propriétés et méthodes permettant l'accès et la gestion d'une plage ou d'une cellule d'une feuille de calcul (accès à la documentation officielle).
Ci-dessous, sont listées celles que j'utilise le plus fréquemment :
activate
clear
copyTo
getFormula, getFormulas, setFormula, setFormulas : obtenir ou écrire une formule de calcul depuis ou dans la cellule (ou les cellules de la plage) ;
getValue, getValues, setValue, setValues : obtenir ou écrire une valeur depuis ou dans la cellule (ou les cellules de la plage)
La portion de code ci-dessous permet l'affichage dans la cellule courante du nombre de valeurs écrites dans les cellules de la plage "A5:A10". La ligne 2 aurait pu s'écrire : var LaFormule = "=COUNTA(A5:A10)" dans une version moins universelle.
1. var laPlage = "A5:A10"
2. var LaFormule = "=COUNTA(" + laPlage + ")"
3. var laCelluleActive = SpreadsheetApp.getCurrentCell();
4. laCelluleActive.setFormula(LaFormule) ;
Contrairement à MS Excel et à LO Calc, il n'y a pas de méthode spécifique aux formules matricielles. Pour utiliser une formule matricielle dans une macro, on utilise la méthode setFormula avec pour paramètre "=ARRAYFORMULA()".
La portion de code ci-dessous place dans la cellule "I5" la formule matricielle "=G5:G10 * F5:F10".
var LaFormuleMatricielle = "=ARRAYFORMULA(G5:G10 * F5:F10)"
var laPlageCible = SpreadsheetApp.getActiveSheet().getRange("I5");
laPlageCible.setFormula(LaFormuleMatricielle);
4 : Quelques exemples :
function creerFeuilleNotes() {
var leClasseur = SpreadsheetApp.getActiveSpreadsheet();
var laFeuille = leClasseur.getSheetByName("source")
const ui = SpreadsheetApp.getUi();
const reponse = ui.prompt('Identifier', 'Nommer la feuille de notes à créer :', ui.ButtonSet.OK_CANCEL);
const nomFeuilleCible = reponse.getResponseText();
const btn = reponse.getSelectedButton();
'
if (nomFeuilleCible != '' && btn == ui.Button.OK) {
laFeuille.copyTo(leClasseur);
leClasseur.getSheetByName("copie de source").setName(nomFeuilleCible);
leClasseur.getSheetByName(nomFeuilleCible).activate();
}
else {
return
}
}
5 : L'exécution :
Pour tester les portions de code ci-dessus, dans une feuille Google Sheet, il faut développer comme le montre l'image ci-dessous l'item "Extensions" du menu principal, puis cliquer sur "Apps Script" pour ouvrir l'environnement de développement de scripts de Google.
Coller ensuite le code d'un des onglets, enregistrer pour vérifier qu'il n'y a pas d'erreur de programmation et lancer l'exécution de la fonction copiée (dont le nom doit être affiché dans la ligne de menu).
Note : pour "copier une feuille", il faut qu'il existe une feuille nommée source dans votre classeur.
Pour exécuter une macro-commande depuis un classeur. il faut l'importer dans le classeur. Pour cela, utilisez l'item "extensions" du menu, puis "Macros" et enfin "importer une macro".
Enfin, choisissez la macro à importer (voir l'image ci-joint) dans la liste affichée.
Une macro importée figure comme item du sous-menu "Macros" dans l'onglet "Extensions" du menu.
Il est aussi possible de créer son propre sous-menu dans la barre de menu ou d'associer cette macro-commande à un bouton de contrôle.
Quelques objets, méthodes et propriétés à connaitre pour créer une macro-commande :
___________________
_____________________