Les macros/script sur google spreadsheet

Le JavaScript est vraiment LE langage du moment. Non seulement Office 2013, Windows RT l’utilisent mais aussi les documents google (gdoc/spreadsheet). Voilà bien une raison de plus -s’il en fallait- que ce n’est pas une mauvaise idée de commencer le développement par le web -surtout quand on est jeune.

Aujourd’hui fut pour moi un grand moment pour tester la fameuse “courbe du geek” : une répétition en grand nombre de la séquence filter-copy-past.

courbe du geel

Nombre d’opérations réalisées en fonction du temps

Rentrons donc dans le vif du sujet en ce qui concerne la création de script avec google spreadsheet.Tout se fait en javascript et dans le navigateur. N’espérez pas un système d’autocomplétion fiable. Pour ma part sur la dernière version de chrome, avec win7, il n’autocomplétait qu’après que j’ai mis un “;”, ce genre de bug qui énerve.

Pour créer votre script, allez dans “Outils” puis “Créer un script”. Là vous aurez deux choix : soit créer un projet vide, soit un projet pour Spreadsheet. Si vous voulez créer une macro, choisissez le second, quelques lignes seront déjà codées pour vous (la suite de l’article se base là dessus). Si vous voulez créer une fonction personnalisée, choisissez le premier.

Vous arrivez sur une nouvelle page (attention, si vous fermez ou actualisez avec F5 votre feuille de calcul, il arrive que cette page se ferme automatiquement) où deux fonctions sont écrites : readRows et onOpen.Supprimez la première, c’est un exemple pour vous montrer l’API.
editeur de script

Dans la fonction onOpen(), pour une utilisation basique, seul les lignes 29 à 32 vous intéressent : il s’agira d’y enregistrer votre fonction. Pour l’heure, supprimez le contenu de la variable “entries”.
Pour coder un script accessible via le menu, créez une fonction puis renseignez là dans la variable entries. “name” sera le nom visible dans le menu, “functionName” le nom de la fonction JS.

La fonction “onOpen” sera appelée lorsque vous ouvrirez le document auquel le script est lié. La méthode “addMenu” permet d’ajouter des items à l’interface de départ. Un titre pour personne en manque d’inspiration peut être “Admin” (qui est aussi compréhensible des français, que des anglais et autres polonais :-)).

Dans vos fonction, vous avez droit à plusieurs objets globaux dont Browser et SpreadsheetApp. Le premier permet d’interagir avec l’utilisateur (avec une inputBox par exemple), le second de contrôler ce que vous faites.
Je vais vous donner deux ou trois astuces qui vous permettrons de créer des macros facilement.

Obtenir les données d’une sélection

Pour que votre macro agisse, vous pouvez le faire de deux manières :
– en renseignant en dur la plage d’action
– en sélectionnant la plage à la main avant d’activer la macro.

Dans le premier cas, la fonction à aller chercher est SpreadsheetApp.getRange(“plage de cellule”). Vous utilisez les plages comme d’habitude (par exemple A1:B2 est une plage carrée de quatre cases en partant d’en haut à gauche). Vous pouvez aussi définir la plage avec des entiers, dès lors les indices commencent à 1, donc vous auriez SpreadsheetApp.getRange(1,1,2,2).
Une fois que vous avez obtenu votre plage, il faut aller chercher les valeurs grâce à la fonction getValues() qui renverra un tableau JS (indicé à partir de 0!) à deux dimensions, le premier indice correspond à la ligne, le second à la colonne.
[cc lang=”javascript”]var range = SpreadsheetApp.getRange("A1:B10");
var values = range.getValues();
var s =0;
for(var i=1;i<range.length; i++){
s += range[i][0];
}
Browser.msgBox("le premier titre est "+ values[0][0]+ "la somme est de "+ s);
[/cc]
Dans le second cas, la procédure est la même, mais avec SpreadsheetApp.getActiveRange().

Placer des données dans une nouvelle feuille

Déjà, il vous faudra créer la nouvelle feuille grâce à la méthode create.

Tout de suite après cela, vous voudrez sûrement partager la feuille, les fonctions addEditors et addViewers sont là pour ça.
[cc lang=”javascript”]var sheet = SpreadsheetApp.create("Nouvelle synthèse");//on crée
sheet.addEditors(["monAmi@gmail.com","mongooglegroup@googlegroups.com"]);//on partage
sheet.appendLine(["titre1","titre2"]);//on met des entêtes
[/cc]
Enfin, vous allez placer les données. Et là mon conseille, c’est d’y aller ligne par ligne. D’une part parce que l’API est plus utilisable dans ce sens ([cci lang=”javascript”]appendRow[/cci] permet d’ajouter des données, alors que [cci lang=”javascript”]insertColumn[/cci] ne fait que mettre des cellules vides) mais aussi parce que ça vous permettra de mieux séquencer votre script.

 

 

 

 

13 thoughts on “Les macros/script sur google spreadsheet

  1. “mettre une ligne d’une feuille à une autre feuille google sheets”
    Ex : Feuille 1
    je sélectionne la ligne 40
    je clic sur un bouton créé (ex : validez)
    la ça me copie la ligne et ca l’envois sur le feuille 2 en première ligne
    et ça efface la ligne 40 de la feuille 1

    Ps : cela est faisable en VBA sur excel
    mais voila google sheets est en java

    Merci d’avance

    • Bonjour,
      Tu peux tout à fait copier la ligne 40 de la feuille 1 dans la feuille 2 sans problème.
      Il faudrait que je mette à jour ce tuto qui date vraiment beaucoup bien qu’il soit bien référencé. Peut être cette mise à jour passsera-t-elle par https://zestedesavoir.com

      En attendant, gsheet est en javascript, pas en java, et ce que tu demande reste tout à fait possible.
      Si tu as un problème, je te conseille d’aller sur zestedesavoir où pas mal de monde te répondra rapidement, peut-être même moi.

  2. Bonjour,

    Je suis actuellement en stage marketing et je me demandais si tu pouvais m’aider à réaliser un script dont j’ai besoin pour mener à bien une de mes missions.

    Mon problème doit être simple pour toi. Il me faudrait, un script me permettant de récupérer les données de 7 onglets d’un même document Googlesheet organisé de la même manière et de les synthétirer dans un seul est unique onglet de ce même document. De plus il faudrait une mise à jour automatique de cette synthèse si ces feuilles sont mises à jours .

    Merci d’avance, cordialement QP

    • Bonjour,

      oui, il est possible de t’aider. Mais pour ton propre confort et aussi pour améliorer ton ressenti, je te conseille d’aller sur le forum de http://zestedesavoir.com/forums/

      En effet, entre le moment où j’ai écrit cet article et aujourd’hui, les macro google ont pas mal évolué et vis à vis ton besoin, il est fort probable que je réponde à côté si je le fais maintenant car tu donnes très peu d’info sur ce que tu dois faire et ce que tu as déjà fait.

      Bon courage pour ton stage.

  3. Bonsoir,

    je viens vers vous car j’ai un souci (ou pas).
    Faut il etre obligatoirement connecté à son compte gmail, afin d’utiliser les scripts sur google sheet?

    Merci par avance.
    Cordialement.

    • Pour des raisons de sécurité, google demande à ce que vous donniez l’autorisation d’exécuter les scripts. Ce qui exclus la possibilité de les faire tourner lorsqu’on est anonyme/pas connecté.
      Pour poser toutes vos questions, n’hésitez pas à vous rendre sur le site https://zestedesavoir.com, la communauté sera forcément plus réactive que moi. Et le site est plus actif 🙂

  4. Bonjour,
    Novice avec les scripts Google , voilà quelques jours que je tourne en rond sans pouvoir trouver une solution ….
    Explications :
    function showMedic() {
    var html = HtmlService.createHtmlOutputFromFile(‘Dialog1’)
    .setWidth(320)
    .setHeight(250)
    }

    Dialog1.htlm

    h3 { color:black; font: 20px bold verdana, helvetica, sans-serif;text-align:center }
    h2 { color:red; font: 20px bold verdana, helvetica, sans-serif;text-align:center }
    p { font: 16px normal arial, helvetica, sans-serif; }
    #btn { float:right; border-radius: 15px; }

    AJUSTEMENT DE STOCK
    Attention
    Voulez-vous vraiment ajuster le stock ?

    Je cherche à :
    – lancer une fonction a() si le bouton à bien été cliqué… (les “return …” , “google.script….” ne fonctionnent pas …
    – insérer dans la fenêtre html apparente le contenu texte d’une variable appartenant à la fonction showMedic()
    Pouvez-vous me guider ?
    Merci beaucoup.

    • Bonjour,
      je suis en train de revoir cet article car Google a beaucoup changer le modèle de triggering des événements sur spreadshit. En attendant, je vous encourage à poser votre question sur https://zestedesavoir.com où vous aurez plus d’aide.

      Pour ce qui est de réagir à l’appuie d’un bouton, il suffit de faire :
      – éditeur de scrit
      – ressource
      – déclancheurs (trigger en anglais)
      – ajouter un déclancheur
      – “à partir de la feuille”
      – “formulaire en cours d’envoie”

  5. Pingback: Les macro google spreadsheet | Blog de François DAMBRINE

  6. Bonjour je trouve sa chiant google sheet 🙂 , pour ma part je compte faire un bouton avec une flèche sur le haut et une sur le bas pour “simulé” une roulette qui va agir sur un nombre et qui l’augmentera ou décrémentera , ainsi je pourrai faire un index dessus et pouvoir faire des listes déroulantes aussi a partir de cela 😉 .
    Si vous avez des documentation en français ou anglais ce n’est pas de refus , ainsi je peut avoir plus de degré de liberté votre tuto montre comment faire votre fonction en copiant bêtement 😉

Leave a Reply

Your email address will not be published. Required fields are marked *