Les principaux objets Google Sheets

Créer une macro-commande sous Google Sheets, impose l'utilisation de SpreadsheetApp (accès à  documentation) instance de la classe racine SpreadsheetApp. Cet objet est créé au lancement de l'application.

La classe SpreadsheetApp (accès à la documentation) 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.

  var leClasseur = SpreadsheetApp.getActiveSpreadsheet();
  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 une 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 ou de la cellule active ;
  • getName : rend l'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 : insert une nouvelle feuille après la feuille active. Cette feuille porte le nom par défaut : feuille n°

   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 l 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 ; deleteRows : suppression d'une ou plusieurs lignes ;
  • deleteColumn ; deleteColumns : suppression d'une ou plusieurs colonnes ;
  • getCurrentCell : rend une instance de la cellule sélectionnée ;
  • getActiveRange ; rend une instance de la plage sélectionnée ;
  • getA1Notation : rend le nom de de la cellule
    • voir ci-dessous getRange
  • getRange rend une cellule ou une plage 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
      • 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.

  var laFeuille = SpreadsheetApp.getActiveSheet();
  Logger.log ( "la plage est : " + laFeuille.getRange(1,1,5,2).getA1Notation() )

 

3 : La cellule, 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"

    var laPlage = "A5:A10"
    var LaFormule = "=COUNTA(" + laPlage + ")"
    var laCelluleActive = SpreadsheetApp.getCurrentCell();
    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().
 

    var LaFormuleMatricielle = "=ARRAYFORMULA(G5:G10 * F5:F10)"
    var laPlageCible = SpreadsheetApp.getActiveSheet().getRange("I5");
    laPlageCible.setFormula(LaFormuleMatricielle) ;

 

4 : Quelques exemples :

Cliquez sur le bouton pour copier le code
dans le presse papier
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 G Sheet, 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 doit 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.

Cliquer pour agrandir l'image


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-contre) dans la liste affichée.

Une macro importée figure comme item du sous-menu "Macros" dans l'onglet "Extensions" du menu.

Cliquer pour agrandir l'image

___________________

_____________________