Google, SaaS, Office – Ecrire des scripts pour la suite de google docs

by Posted on



Dans la plupart des projets désormais, dans le domaine du web et ailleurs, vous êtes amené(e) à collaborer, donc à partager des documents, des informations. C’est la raison d’être de nombreux outils de collaboration. On peut citer les plus connus, Redmine pour gérer des tickets et tracer les demandes d’évolution ou bien de Basecamp, qui est un peu le “couteau suisse” de la gestion de projets. Toutefois, dans de nombreux cas, lorsque le projet est de taille modeste en volume comme en durée (peu d’intervenants, quelques semaines d’itérations…etc.), ces outils se révèlent quelques peu disproportionnés et peuvent finir par créer plus de “bruit” que de clarté.

Parfois, un simple tableur suffit, encore faut-il pouvoir le partager et/ou le rendre disponible pour tous. C’est là où le tableur de Google docs, le Google Spreadsheets rentre dans la danse. Un document ou plusieurs document Google Spreadsheets peuvent amplement suffire à piloter un projet afin de constituer toute type de document nécessaire à la conduite de projet : plan de recette, calendrier, diagramme de Gantt…
Un bémol néanmoins, rappelez-vous que vous êtes sur le Cloud et que c’est un document partagé. Bien que cela sonne comme une évidence, cette double caractéristique commande de rester prudent dans l’utilisation d’un tableur Google Spreadsheets.

  • Il faut rester vigilant sur le volume de données qui va être traité par votre tableur sinon le document sera indigeste donc inutilisable car trop lent.
  • Il faut utiliser avec parcimonie les fonctions additionnelles dont vous souhaiteriez tirer profit (tris croisés, filtres automatiques… etc.) car ce qu’il est possible de faire avec un tableur en local n’est pas forcement reproductible sur un document en ligne.

On ne parle même pas de la sécurité éventuelle de vos documents. En effet, tout ce qui part en ligne peut-être potentiellement, lu, modifié, observé… hacké en quelque sorte. Il est vrai quand on est sur un petit projet, ce n’est pas la préoccupation principale et puis de toutes les façons la NSA * écoute tout le monde alors autant leur donner des billes !

* Source :
Comment la NSA infiltre secrètement les serveurs de Google et Yahoo!

NSA infiltrates links to Yahoo, Google data centers worldwide

A l’aide de différents scripts, on va donc voir comment améliorer la pertinence des informations saisies dans un tableur Google pour partager la meilleure information possible.

La cuisine au GAS ou GAS for SaaS


Pour faire des scripts dans un tableur Google Spreadsheets, il faut se familiariser avec le GAS, sorte de dérivé du JavaScript. C’est le langage de programmation de Google Apps Script.

Google Apps Script is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services.

Le GAS (Google Apps Script) permet donc d’augmenter les “capacités” de votre tableur et peut-être utilisé dans différents situations comme pour :

  • Rédiger des fonctions utilisateur pour Google Spreadsheets.
  • Rédiger des applications de type simple “macro”.
  • Développer des applications basées sur tableur.
  • Intégrer les autres produits et services Google.

Limitations à l'usage des tableurs Google


Attention, selon Google, il existe des limitations mais Google est assez généreux.

“Une feuille de calcul Google ne peut pas dépasser en taille, les 400 000 cellules et chaque feuille est limitée à 256 colonnes.”

On ne va pas ergoter sur la nature du GAS, javascript ou pas javascript. Il est certain que la filiation avec le javascript est évidente. Dans cet article, on se focalise exclusivement en quoi le scripting dans un tableur google produit et peut vous être utile.

Notre cas d’école

On va voir comment si vous pilotez un projet digital multilingue, vous allez pouvoir à l’aide d’un tableur Google constituer une base de traduction. Chacune des phrases sera à traduire du français vers les 4 langues nécessaires à votre projet (Anglais, Espagnol, Allemand, Arabe). Pour chaque phrase, il existe une colonne de saisie pour le traducteur et un modèle de traduction issu automatiquement de l’API google translate.

Ce tableur sera ensuite publié sur le web en format CSV et converti dynamiquement en JSON à l’aide de PHP de sorte que vous puissiez par exemple l’intégrer directement dans des fichiers de traduction de votre site web ou de votre application web.

La totalité des documents présentés dans cet article sont à télécharger dans ce fichier : create_script_for_google_doc.zip

Le tableur google de traduction et comment importer le scrip .gs
oogle, SaaS, Office - Ecrire des scripts pour la suite de google docs

L’éditeur de script
oogle, SaaS, Office - Ecrire des scripts pour la suite de google docs

Le script TranslateSheet.gs pour traduire à l’aide de Google Translate nos phrases en français dans 4 langues (anglais, espagnol, allemand, arabe). Le script est volontairement très simple pour pouvoir être facilement changer sans connaissance particulière de développement.

 
 
 
      function TranslatorSpreadsheet() {
 
      	/* VALUES */
      	var word_2 = 'Voir le mot-clé';
      	var word_3 = 'Afficher la catégorie';
      	var word_4 = 'Mettre à jour le mot-clé';
      	var word_5 = 'Mettre à jour la catégorie';
      	var word_6 = 'Ajouter un nouveau mot-clé';
      	var word_7 = 'Ajouter une nouvelle catégorie';
      	var word_8 = 'Nom du nouveau mot-clé';
      	var word_9 = 'Nom de la nouvelle catégorie';
      	var word_10 = 'Séparez les mots-clés par des virgules';
      	var word_11 = 'Ajouter ou retirer des mots-clés';
      	var word_12 = 'Choisir parmi les mots-clés les plus utilisés';
      	var word_13 = 'Voir la page';
      	var word_14 = 'Chercher dans les articles';
      	var word_15 = 'Chercher dans les pages';
      	var word_16 = 'Aucun article trouvé.';
      	var word_17 = 'Aucune page trouvée.';
      	var word_18 = 'Aucun article trouvé dans la corbeille.';
      	var word_19 = 'Aucune page trouvée dans la corbeille.';
 
 
      	// Get the active Sheet
      	sheet = SpreadsheetApp.getActiveSheet();
 
      	// Get the source of the date in order to translate
      	sheet.getRange('A2').setValue(word_2);
      	sheet.getRange('A3').setValue(word_3);
      	sheet.getRange('A4').setValue(word_4);
      	sheet.getRange('A5').setValue(word_5);
      	sheet.getRange('A6').setValue(word_6);
      	sheet.getRange('A7').setValue(word_7);
      	sheet.getRange('A8').setValue(word_8);
      	sheet.getRange('A9').setValue(word_9);
      	sheet.getRange('A10').setValue(word_10);
      	sheet.getRange('A11').setValue(word_11);
      	sheet.getRange('A12').setValue(word_12);
      	sheet.getRange('A13').setValue(word_13);
      	sheet.getRange('A14').setValue(word_14);
      	sheet.getRange('A15').setValue(word_15);
      	sheet.getRange('A16').setValue(word_16);
      	sheet.getRange('A17').setValue(word_17);
      	sheet.getRange('A18').setValue(word_18);
      	sheet.getRange('A19').setValue(word_19);
 
 
      	// Using the LanguageApp write to cell
        sheet.getRange('B2').setValue(LanguageApp.translate(word_2, 'fr', 'en'));
      	sheet.getRange('D2').setValue(LanguageApp.translate(word_2, 'fr', 'es'));
      	sheet.getRange('F2').setValue(LanguageApp.translate(word_2, 'fr', 'de'));
      	sheet.getRange('H2').setValue(LanguageApp.translate(word_2, 'fr', 'ar'));
 
      	sheet.getRange('B3').setValue(LanguageApp.translate(word_3, 'fr', 'en'));
      	sheet.getRange('D3').setValue(LanguageApp.translate(word_3, 'fr', 'es'));
      	sheet.getRange('F3').setValue(LanguageApp.translate(word_3, 'fr', 'de'));
      	sheet.getRange('H3').setValue(LanguageApp.translate(word_3, 'fr', 'ar'));
 
      	sheet.getRange('B4').setValue(LanguageApp.translate(word_4, 'fr', 'en'));
      	sheet.getRange('D4').setValue(LanguageApp.translate(word_4, 'fr', 'es'));
      	sheet.getRange('F4').setValue(LanguageApp.translate(word_4, 'fr', 'de'));
      	sheet.getRange('H4').setValue(LanguageApp.translate(word_4, 'fr', 'ar'));
 
      	sheet.getRange('B5').setValue(LanguageApp.translate(word_5, 'fr', 'en'));
      	sheet.getRange('D5').setValue(LanguageApp.translate(word_5, 'fr', 'es'));
      	sheet.getRange('F5').setValue(LanguageApp.translate(word_5, 'fr', 'de'));
      	sheet.getRange('H5').setValue(LanguageApp.translate(word_5, 'fr', 'ar'));
 
      	sheet.getRange('B6').setValue(LanguageApp.translate(word_6, 'fr', 'en'));
      	sheet.getRange('D6').setValue(LanguageApp.translate(word_6, 'fr', 'es'));
      	sheet.getRange('F6').setValue(LanguageApp.translate(word_6, 'fr', 'de'));
      	sheet.getRange('H6').setValue(LanguageApp.translate(word_6, 'fr', 'ar'));
 
      	sheet.getRange('B7').setValue(LanguageApp.translate(word_7, 'fr', 'en'));
      	sheet.getRange('D7').setValue(LanguageApp.translate(word_7, 'fr', 'es'));
      	sheet.getRange('F7').setValue(LanguageApp.translate(word_7, 'fr', 'de'));
      	sheet.getRange('H7').setValue(LanguageApp.translate(word_7, 'fr', 'ar'));
 
      	sheet.getRange('B8').setValue(LanguageApp.translate(word_8, 'fr', 'en'));
      	sheet.getRange('D8').setValue(LanguageApp.translate(word_8, 'fr', 'es'));
      	sheet.getRange('F8').setValue(LanguageApp.translate(word_8, 'fr', 'de'));
      	sheet.getRange('H8').setValue(LanguageApp.translate(word_8, 'fr', 'ar'));
 
      	sheet.getRange('B9').setValue(LanguageApp.translate(word_9, 'fr', 'en'));
      	sheet.getRange('D9').setValue(LanguageApp.translate(word_9, 'fr', 'es'));
      	sheet.getRange('F9').setValue(LanguageApp.translate(word_9, 'fr', 'de'));
      	sheet.getRange('H9').setValue(LanguageApp.translate(word_9, 'fr', 'ar'));
 
      	sheet.getRange('B10').setValue(LanguageApp.translate(word_10, 'fr', 'en'));
      	sheet.getRange('D10').setValue(LanguageApp.translate(word_10, 'fr', 'es'));
      	sheet.getRange('F10').setValue(LanguageApp.translate(word_10, 'fr', 'de'));
      	sheet.getRange('H10').setValue(LanguageApp.translate(word_10, 'fr', 'ar'));
 
      	sheet.getRange('B11').setValue(LanguageApp.translate(word_11, 'fr', 'en'));
      	sheet.getRange('D11').setValue(LanguageApp.translate(word_11, 'fr', 'es'));
      	sheet.getRange('F11').setValue(LanguageApp.translate(word_11, 'fr', 'de'));
      	sheet.getRange('H11').setValue(LanguageApp.translate(word_11, 'fr', 'ar'));
 
      	sheet.getRange('B12').setValue(LanguageApp.translate(word_12, 'fr', 'en'));
      	sheet.getRange('D12').setValue(LanguageApp.translate(word_12, 'fr', 'es'));
      	sheet.getRange('F12').setValue(LanguageApp.translate(word_12, 'fr', 'de'));
      	sheet.getRange('H12').setValue(LanguageApp.translate(word_12, 'fr', 'ar'));
 
      	sheet.getRange('B13').setValue(LanguageApp.translate(word_13, 'fr', 'en'));
      	sheet.getRange('D13').setValue(LanguageApp.translate(word_13, 'fr', 'es'));
      	sheet.getRange('F13').setValue(LanguageApp.translate(word_13, 'fr', 'de'));
      	sheet.getRange('H13').setValue(LanguageApp.translate(word_13, 'fr', 'ar'));
 
      	sheet.getRange('B14').setValue(LanguageApp.translate(word_14, 'fr', 'en'));
      	sheet.getRange('D14').setValue(LanguageApp.translate(word_14, 'fr', 'es'));
      	sheet.getRange('F14').setValue(LanguageApp.translate(word_14, 'fr', 'de'));
      	sheet.getRange('H14').setValue(LanguageApp.translate(word_14, 'fr', 'ar'));
 
      	sheet.getRange('B15').setValue(LanguageApp.translate(word_15, 'fr', 'en'));
      	sheet.getRange('D15').setValue(LanguageApp.translate(word_15, 'fr', 'es'));
      	sheet.getRange('F15').setValue(LanguageApp.translate(word_15, 'fr', 'de'));
      	sheet.getRange('H15').setValue(LanguageApp.translate(word_15, 'fr', 'ar'));
 
      	sheet.getRange('B16').setValue(LanguageApp.translate(word_16, 'fr', 'en'));
      	sheet.getRange('D16').setValue(LanguageApp.translate(word_16, 'fr', 'es'));
      	sheet.getRange('F16').setValue(LanguageApp.translate(word_16, 'fr', 'de'));
      	sheet.getRange('H16').setValue(LanguageApp.translate(word_16, 'fr', 'ar'));
 
      	sheet.getRange('B17').setValue(LanguageApp.translate(word_17, 'fr', 'en'));
      	sheet.getRange('D17').setValue(LanguageApp.translate(word_17, 'fr', 'es'));
      	sheet.getRange('F17').setValue(LanguageApp.translate(word_17, 'fr', 'de'));
      	sheet.getRange('H17').setValue(LanguageApp.translate(word_17, 'fr', 'ar'));
 
      	sheet.getRange('B18').setValue(LanguageApp.translate(word_18, 'fr', 'en'));
      	sheet.getRange('D18').setValue(LanguageApp.translate(word_18, 'fr', 'es'));
      	sheet.getRange('F18').setValue(LanguageApp.translate(word_18, 'fr', 'de'));
      	sheet.getRange('H18').setValue(LanguageApp.translate(word_18, 'fr', 'ar'));
 
      	sheet.getRange('B19').setValue(LanguageApp.translate(word_19, 'fr', 'en'));
      	sheet.getRange('D19').setValue(LanguageApp.translate(word_19, 'fr', 'es'));
      	sheet.getRange('F19').setValue(LanguageApp.translate(word_19, 'fr', 'de'));
      	sheet.getRange('H19').setValue(LanguageApp.translate(word_19, 'fr', 'ar'));
 
 
 
 
       }// EOF

Publier sur le web le tableur au format .csv
oogle, SaaS, Office - Ecrire des scripts pour la suite de google docs

La transformation du .csv en .json à l’aide de PHP
oogle, SaaS, Office - Ecrire des scripts pour la suite de google docs

Le script PHP de conversion
Source : http://www.ravelrumba.com/blog/json-google-spreadsheets/

  	  <?php
      header('Content-type: application/json');
 
      // Set your CSV feed
      // $feed = 'https://docs.google.com/spreadsheet/pub?hl=en_US&hl=en_US&key=0Akse3y5kCOR8dEh6cWRYWDVlWmN0TEdfRkZ3dkkzdGc&single=true&gid=0&output=csv';
 
      // Mettre l'adresse de votre feed CSV à la place...
      $feed = 'https://docs.google.com/spreadsheet/pub?key=0An_r4U36nZbxdElZc3RTamdxWG42N2VBVGJ5dE9xVWc&single=true&gid=0&output=csv';
 
      // Arrays we'll use later
      $keys = array();
      $newArray = array();
 
      // Function to convert CSV into associative array
      function csvToArray($file, $delimiter) { 
        if (($handle = fopen($file, 'r')) !== FALSE) { 
          $i = 0; 
          while (($lineArray = fgetcsv($handle, 4000, $delimiter, '"')) !== FALSE) { 
            for ($j = 0; $j < count($lineArray); $j++) { 
              $arr[$i][$j] = $lineArray[$j]; 
            } 
            $i++; 
          } 
          fclose($handle); 
        } 
        return $arr; 
      } 
 
      // Do it
      $data = csvToArray($feed, ',');
 
      // Set number of elements (minus 1 because we shift off the first row)
      $count = count($data) - 1;
 
      //Use first row for names  
      $labels = array_shift($data);  
 
      foreach ($labels as $label) {
        $keys[] = $label;
      }
 
      // Add Ids, just in case we want them later
      $keys[] = 'id';
 
      for ($i = 0; $i < $count; $i++) {
        $data[$i][] = $i;
      }
 
      // Bring it all together
      for ($j = 0; $j < $count; $j++) {
        $d = array_combine($keys, $data[$j]);
        $newArray[$j] = $d;
      }
 
      // Print it out as JSON
      echo json_encode($newArray);
 
      ?>

Notre JSON plus facilement intégrable dans le développement d’un site web, mobile, d’une webapp ou d’un SaaS.
oogle, SaaS, Office - Ecrire des scripts pour la suite de google docs

oogle, SaaS, Office - Ecrire des scripts pour la suite de google docs

oogle, SaaS, Office - Ecrire des scripts pour la suite de google docs

Toutes les modifications apportées au tableur se répercutent sur le flux JSON. Le flux est donc remis à jour constamment si vous cochez dans le mode de publication “Republier automatiquement après chaque modification”
oogle, SaaS, Office - Ecrire des scripts pour la suite de google docs

Conclusion : Sans vouloir être trop enthousiaste, ce POC (Proof Of Concept) offre des perspectives en matière de collaboration jusqu’à présent inimaginables. C’est sans doute pour cela que l’on nous bassine avec la révolution du Cloud computing et du SaaS mais c’est un fait ! Si de telles services sont désormais disponibles, cela change irrémédiablement nos conditions de travail et de partage de l’information. En clair, ces outils sont bien le pendant technologique de notre monde globalisé, désintermédié, digitalisé et dans une vision moins rose ultra inféodé à des géants tel que Google mais ne faut-il pas aller dans le sens de l’Histoire ?

Quelques scripts en complément

Il existe toute une palette de scripts à votre disposition sur Google, en voici quelques-uns notamment un script pour annoter dans 2 colonnes, la date et la personne qui a pour la dernière fois modifier la ligne d’un tableur. Pratique.

Un script utile pour voir l’heure et le nom de la dernière personne qui modifie un tableur

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

    // update a cell with last modified time.  
    // Edit this script to your taste, e.g. a different cell, different date formatting (see javascript date)
    // You could also use d.toString() to have whatever default date formatting your browser/system provides.    function onEdit() {
      var d = new Date();
      var h = d.getHours();
      var min = d.getMinutes();
      var sec = d.getSeconds();
      var h_str = h;
      var min_str = min;
      var sec_str = sec;
      var day = d.getDate();
      var day_str = day;
      var month = d.getMonth()+1;
      var month_str = month;
      var year = d.getFullYear();
 
      // format time nicely
      if (h < 10) 
        h_str = '0' + h;
      if (min < 10) 
        min_str = '0' + min;
      if (sec < 10) 
        sec_str = '0' + sec;
      if (day < 10) 
        day_str = '0' + day;
      if (month < 10) 
        month_str = '0' + month;
 
      var date_str = year + '/'+ month_str + '/' + day_str;
 
      // create the formatted time string
      var time_str = h_str + ':' + min_str + ':' + sec_str;
 
      var user = Session.getEffectiveUser().getEmail();
 
      // create the message (change this to whatever wording you prefer)
      // note also that rather than all the above, you could just use d.toString() 
      // I didn't because I didn't want it printing the timezone.
      var s = date_str + ' ' + time_str;
 
      // change the range from "A1" to whatever cell you wish to update
 
      var activeRow = SpreadsheetApp.getActiveSheet().getActiveRange().getRowIndex();
 
      // This were you fix the column for instance in our example it is Column L and Column M
      if (activeRow > 1) {
        SpreadsheetApp.getActiveSheet().getRange("L"+activeRow).setValue(s);
        SpreadsheetApp.getActiveSheet().getRange("M"+activeRow).setValue(user);
      }
    }

Une script pour le word de google docs

 
   function createAndSendDocument() {
     // Create a new document with the title 'Hello World'
     var doc = DocumentApp.create('Hello World');
 
     // Add a paragraph to the document
     doc.appendParagraph('This document was created by my first Google Apps Script.');
 
     // Save and close the document
     doc.saveAndClose();
 
     // Get the URL of the document
     var url = doc.getUrl();
 
     // Get the email address of the active user - that's you
     var emailAddress = Session.getActiveUser().getEmail();
 
     // Send yourself an email with a link to the document
     GmailApp.sendEmail(emailAddress,
                        'Hello from my first Google Apps Script!',
                        'Here is a link to a document created by my ' +
                        'first Google Apps Script: ' + url);
   }

Source : https://developers.google.com/apps-script/your_first_script

Un autre script….

// Declare a variable called 'user_name' and assign it to the user name of the the Google account user.
  function displayUsername() {
      // var user_name = Session.getActiveUser().getUsername();
       var user_name ="Bruno";
      Browser.msgBox("Hello, " + user_name);
  }

En savoir plus

Published by Bruno Flaven

Bruno Flaven has been a Project Manager in a wide variety of Internet business applications both in Mobile and in Desktop for 20 years now. You can find more information about his professional life on his personal website (www.flaven.fr) or his linkedin profile: https://fr.linkedin.com/in/brunoflaven. He is currently working In France Media Monde (FMM) mostly on mobile applications (iOS and Android). He is currently P.O for a Backoffice project made with Symfony. He also made few trainings to facilitate the handling of the tools that he helps to make.

Related Posts