---
title: Traiter l information avec GoogleSheet
tags: nocode, support, googlesheet
robots: noindex, nofollow
author: Julien Noyer
---

# Traiter l'information avec GoogleSheet


[GoogleSheet](https://www.google.com/intl/fr/sheets/about/) est un outil de Google des plus puissant. Il est simple d'accès et offre des possibilités avancées dû au fait qu'il est possible de créer des scripts pour différentes opération. Dans ce support de cours nous allons étudier la mise en place de scripts et l'utilisation de certaines formules de calcules.

> Pour utiliser [GoogleSheet](https://www.google.com/intl/fr/sheets/about/) vous devez disposer d'un compte Google.

<br>

---

<br><br><br>

# Crée un script GoogleSheet

Les scripts sont des outils puissants qui permettent d'exécuter du code directement dans un tableur [GoogleSheet](https://www.google.com/intl/fr/sheets/about/). Pour en ajouter un dans votre tableur vous devez cliquer sur le menu `Outils > Editeur de script` qui ouvre une nouvelle page.

Une fois sur la page d'édition de scripts, il faut créer un projet puis créer un script depuis le menu `Fichier` (le script est à ajouter dans le projet créé). Le script que nous allons créer nous permettra d'exécuter une fonction `ImportJSON` qui à pour but de charger un flux JSON pour l'intégrer dans le tableur.

Le script `ImportJSON` contient le code suivant :

function readRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    Logger.log(row);
  }
};

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Read Data",
    functionName : "readRows"
  }];
  sheet.addMenu("Script Center Menu", entries);
};

function ImportJSON(url, query, options) {
  return ImportJSONAdvanced(url, query, options, includeXPath_, defaultTransform_);
}

function ImportJSONAdvanced(url, query, options, includeFunc, transformFunc) {
  var jsondata = UrlFetchApp.fetch(url);
  var object   = JSON.parse(jsondata.getContentText());
  
  return parseJSONObject_(object, query, options, includeFunc, transformFunc);
}

function URLEncode(value) {
  return encodeURIComponent(value.toString());  
}

function parseJSONObject_(object, query, options, includeFunc, transformFunc) {
  var headers = new Array();
  var data    = new Array();
  
  if (query && !Array.isArray(query) && query.toString().indexOf(",") != -1) {
    query = query.toString().split(",");
  }
  
  if (options) {
    options = options.toString().split(",");
  }
    
  parseData_(headers, data, "", 1, object, query, options, includeFunc);
  parseHeaders_(headers, data);
  transformData_(data, options, transformFunc);
  
  return hasOption_(options, "noHeaders") ? (data.length > 1 ? data.slice(1) : new Array()) : data;
}

function parseData_(headers, data, path, rowIndex, value, query, options, includeFunc) {
  var dataInserted = false;
  
  if (isObject_(value)) {
    for (key in value) {
      if (parseData_(headers, data, path + "/" + key, rowIndex, value[key], query, options, includeFunc)) {
        dataInserted = true; 
      }
    }
  } else if (Array.isArray(value) && isObjectArray_(value)) {
    for (var i = 0; i < value.length; i++) {
      if (parseData_(headers, data, path, rowIndex, value[i], query, options, includeFunc)) {
        dataInserted = true;
        rowIndex++;
      }
    }
  } else if (!includeFunc || includeFunc(query, path, options)) {
    if (Array.isArray(value)) {
      value = value.join(); 
    }
    
    if (!data[rowIndex]) {
      data[rowIndex] = new Array();
    }
    
    if (!headers[path] && headers[path] != 0) {
      headers[path] = Object.keys(headers).length;
    }
    
    data[rowIndex][headers[path]] = value;
    dataInserted = true;
  }
  
  return dataInserted;
}

function parseHeaders_(headers, data) {
  data[0] = new Array();

  for (key in headers) {
    data[0][headers[key]] = key;
  }
}

function transformData_(data, options, transformFunc) {
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      transformFunc(data, i, j, options);
    }
  }
}

function isObject_(test) {
  return Object.prototype.toString.call(test) === '[object Object]';
}

function isObjectArray_(test) {
  for (var i = 0; i < test.length; i++) {
    if (isObject_(test[i])) {
      return true; 
    }
  }  

  return false;
}

function includeXPath_(query, path, options) {
  if (!query) {
    return true; 
  } else if (Array.isArray(query)) {
    for (var i = 0; i < query.length; i++) {
      if (applyXPathRule_(query[i], path, options)) {
        return true; 
      }
    }  
  } else {
    return applyXPathRule_(query, path, options);
  }
  
  return false; 
};

function applyXPathRule_(rule, path, options) {
  return path.indexOf(rule) == 0; 
}

function defaultTransform_(data, row, column, options) {
  if (!data[row][column]) {
    if (row < 2 || hasOption_(options, "noInherit")) {
      data[row][column] = "";
    } else {
      data[row][column] = data[row-1][column];
    }
  } 

  if (!hasOption_(options, "rawHeaders") && row == 0) {
    if (column == 0 && data[row].length > 1) {
      removeCommonPrefixes_(data, row);  
    }
    
    data[row][column] = toTitleCase_(data[row][column].toString().replace(/[\/\_]/g, " "));
  }
  
  if (!hasOption_(options, "noTruncate") && data[row][column]) {
    data[row][column] = data[row][column].toString().substr(0, 256);
  }

  if (hasOption_(options, "debugLocation")) {
    data[row][column] = "[" + row + "," + column + "]" + data[row][column];
  }
}

function removeCommonPrefixes_(data, row) {
  var matchIndex = data[row][0].length;

  for (var i = 1; i < data[row].length; i++) {
    matchIndex = findEqualityEndpoint_(data[row][i-1], data[row][i], matchIndex);

    if (matchIndex == 0) {
      return;
    }
  }
  
  for (var i = 0; i < data[row].length; i++) {
    data[row][i] = data[row][i].substring(matchIndex, data[row][i].length);
  }
}

function findEqualityEndpoint_(string1, string2, stopAt) {
  if (!string1 || !string2) {
    return -1; 
  }
  
  var maxEndpoint = Math.min(stopAt, string1.length, string2.length);
  
  for (var i = 0; i < maxEndpoint; i++) {
    if (string1.charAt(i) != string2.charAt(i)) {
      return i;
    }
  }
  
  return maxEndpoint;
}

function toTitleCase_(text) {
  if (text == null) {
    return null;
  }
  
  return text.replace(/\w\S*/g, function(word) { return word.charAt(0).toUpperCase() + word.substr(1).toLowerCase(); });
}

function hasOption_(options, option) {
  return options && options.indexOf(option) >= 0;
}

> Ce script est au format `.gs` qui est une version de `Javascript` à la sauce Google.

<br>

---

<br><br><br>

# Utiliser le script dans un tableur

Une fois le script `ImportJSON` créé il est utilisable directement dans une cellule du tableur, le flux que nous chargeons pour ce support est celui de l'API JC Decaux qui renvoie la liste des implémentation de stations de vélo de JC Decaux dans le monde. Nous créons donc un tableau nommé `bike-jc-decaux`, nous renommons le premier feuillet `from-api` puis nous nous plaçons sur la première cellule pour y taper le code suivant qui nous permettra d'utiliser le script `ImportJSON` :

=ImportJSON("https://api.jcdecaux.com/vls/v1/stations?apiKey=18fcf3d232b96d61be2cf6e490125e6e0ba2f848")

> Le script `ImportJSON` peut fonctionner sur toutes les routes API.

Il faut attendre un certain temps que la fonction récupère le contenu de l'[API JCDecaux](https://developer.jcdecaux.com) pour que soit remplie le feuillet avec les données de l'API. Le résultat attendu ressemble à l'écran suivant :


> Zoomer sur l'image en [cliquant ici](/img/img-from-api.jpg).

<br>

---

<br><br><br>

# Formater les valeurs des cellules pour réaliser un feuillet d'analyse

Avant d'exécuter des formule de calcule sur un tableur il faut d'abord s'assurer du format des valeurs dans les cellules. Dans le cadre d'un calcule par exemple il faut absolument que la valeur soit définie comme `nombre` sinon nous auront des erreur en retour de nos opérations.

Dans le cadre de notre projet nous allons devoir copier le contenu du feuillet `from-api`(sans la première ligne) pour créer un nouveau feuillet nommé `data` pour formater les données des cellules.

> Ne pas oublier de recréer dans le feuillet `data`l'index du tableau sur la première ligne.

Dans le feuillet `data`nous allons définir les colonnes `Number`, `Bike Stands`, `Available Bike Stands` et `Available Bikes` en les sélectionnant et en allant dans le menu `Format > Nombre > Nombre`. Nous n'allons pas traiter plus de formats différents dans ce support mais il est possible par exemple de définir un format de devise pour calculer des prix.

<br>

---

<br><br><br>

# La fonction SUM()

> **Syntaxe :** SUM(valeur1; [valeur2; ...])

Nous allons à présent créer un feuillet `stat-world` que nous allons utiliser pour calculer les statistiques mondiale. Nous commençons par lui donner la structure suivante :

Bike Stands | Available Bike Stands | Available Bikes | Openned	Closed

Nous allons faire le calcule de la somme totale de chaque implémentation de station JSDecaux, en commençant par le nombre total de stand disponible dans le monde. Pour ce faire nous allons utiliser la fonction [SUM()](https://support.google.com/docs/answer/3093669?hl=fr). Nous nous plaçons dans la cellule `A2` pour y taper le code suivant :

=SUM('data'!C2:C25)

Nous utilisons ensuite cette fonction les autres cellules en faisant glisser le coin inférieur droit de la cellule `A:2` jusqu'à la cellule `B:2`.

> `SUM()` affiche la somme d'une série de nombres et/ou de cellules. [En savoir plus](https://support.google.com/docs/answer/3093198?hl=fr).

<br>

---

<br><br><br>

# La fonction UNIQUE()

> **Syntaxe :** UNIQUE(plage)

Nous allons à présent créer un feuillet `stat-city` que nous allons utiliser pour calculer les statistiques par ville. Nous commençons par lui donner la structure suivante :

Contract Name | Number | Bike Stands | Available Bike Stands | Available Bikes	Openned	Closed

Nous allons à présent lister toutes les villes présente dans le feuillet `data` afin de les ajouter dans le feuillet `stat-city`. Nous nous plaçons sur la cellule `A2` pour y ajouter le code suivant :

=UNIQUE(data!B2:B2606)

> `UNIQUE()` renvoie des lignes uniques en excluant les doublons. [En savoir plus](https://support.google.com/docs/answer/3093198?hl=fr).

<br>

---

<br><br><br>

# La fonction SUMIF()

> **Syntaxe :** SUMIF(plage; critère; [somme_plage])

Dans feuillet `stat-city` à présent nous allons calculer le sommes de `Bike Stands`, nous cellule `C2` pour y ajouter le code suivant :

=SUMIF(data!B2:B2614; A2; data!I2:I2614)

Le code précédent prend en compte toutes les lignes du feuillet `data` qui ont la même valeur dans les colonnes `B2`à `B2614` que la cellule `A:2` du feuillet `stat-city` pour calculer la sommes des valeurs des cellule de `I2` à `12606` du feuillet `data`.

> `UNIQUE()` affiche une somme conditionnelle au sein d'une plage. [En savoir plus](https://support.google.com/docs/answer/3093583?hl=fr).

<br>

---

<br><br><br>

# La fonction COUNTIF()

> **Syntaxe :** COUNTIF(plage, critère)

Dans feuillet `stat-city` à présent nous allons calculer le sommes de stands ouvert, nous nous plaçons sur la cellule `C3` pour y ajouter le code suivant :

=COUNTIF(data!B3:B2614; A3) + COUNTIF(data!L3:L2614; "CLOSE")

Le code précédent prend en compte toutes les lignes du feuillet `data` qui ont la même valeur dans les colonnes `B2`à `B2614` que la cellule `A:2` du feuillet `stat-city` pour calculer la sommes des valeurs égales `CLOSE` des cellule de `L2` à `L2606` du feuillet `data`.

> `COUNTIF()` affiche un calcul conditionnel dans une plage. [En savoir plus](https://support.google.com/docs/answer/3093480?hl=fr).

<br>

---

<br><br><br>

# Ressources


Index des liens vers les sujets traités dans ce document :

- **GoogleSheet** https://www.google.com/intl/fr/sheets/about/
- **Google Aide Editeur Docs** https://support.google.com/docs
- **JCDecaux developer** https://developer.jcdecaux.com