Google Sheets, uMap, et GeoJSON

Une histoire assez rocambolesque pour générer une carte depuis un tableau de valeurs. On va parler d'immobilier (un peu), de code (modérément), et de bricolage scandaleux (beaucoup).

Introduction

Tout commença avec une recherche d'un hypothétique logement, suite à une recherche sur le ton de plaisanterie à propos de "Château à vendre". Fort rapidement, le site d'immobilier du Figaro se démarque, sûrement grâce a une bonne agrégation d'annonces et une tartine de SEO vu comme le domaine de l'immobilier est concurrentiel.

Bien rapidement, au fil des recherches, je me dis qu'il serait tellement mieux d'avoir les annonces retenues sur une carte, pour les positionner les uns par rapport aux autres, voir l'environnement géographique, et tutti quanti.

uMap

Contributeur à OpenStreetMap, je connais depuis longue date uMap, et plus spécifiquement l'instance hébergée par OpenStreetMap France. Ni une, ni deux, je crée une carte toute neuve, qui va contenir mes recherches de châteaux.

Je commence donc à remplir ma carte, en indiquant le prix dans le titre du popup, l'adresse vers l'annonce dans la description. Plus tard, je décide d'ajouter d'autres caractéristiques qui m'intéressent. Disons par exemple, la superficie du terrain, pour que je puisse organiser des courses de drones dignes de ce nom.

Vient vite le moment où j'ai envie d'afficher les châteaux sur la carte avec une idée du prix et de la superficie en même temps. Je commence par créer des calques par tranche de 500 000 € (par exemple), dont la couleur va approximativement du vert (le moins cher) au rouge (le plus cher). Pour ce qui est de la superficie, il est possible d'écrire un mini-texte dans les marqueurs eux-mêmes. Finalement, je veux changer la forme du marqueur pour distinguer mes coups de cœur !

Et là, tout bascule : il est difficile de penser à toutes les données à ajouter, la saisie est laborieuse, l'échelle de couleurs est tout à fait approximative, on s'y perd rapidement. Il faut que je change mon fusil d'épaule ! Je dois générer la carte en fonction des données brutes, et pas autrement !

Data-Driven Documents

Alors oui, je peux dégainer d3.js pour réinventer la roue et générer une carte depuis des données formatées avec amour. Mais je n'ai pas la motivation de faire ça, vu qu'il existe des outils qui sont très proches de ce que je veux faire :

• Tableau, très connu dans le domaine de la business intelligence, je n'y ai jamais touché mais ça à l'air très puissant, très cher, et très privateur.
• RAWGraphs, qui semble être un concurrent de Tableau, mais en plus libre.
• Apache Superset, qui semble être un autre concurrent de Tableau, mais en plus libre aussi.

Sauf que je n'ai pas de belle carte glissante comme je peux avoir avec uMap : je veux savoir si je peux aller envoyer mon laquais acheter du pain à l'artisan boulanger en moins de 15 minutes de route. Par ailleurs, c'est idéal s'il devient possible de collaborer à la création de la carte.

Et la solution vient de uMap directement :

Je vais pouvoir tout importer depuis un fichier CSV joliment formaté comme il faut. Pour cela, il me faut un tableur.

Google Sheets

Pour autant que j'évite les solutions GAFAMesques, force est de constater que Google Sheets fait bien le job : édition collaborative, et publication des données directement en CSV (menu Fichier → Publier sur le web).

Je commence à remplir mon tableau ainsi :

Avec mon tableau exporté en CSV, je récupère l'adresse de l'export depuis Google Sheets. Dans uMap, je fais dans les options du calque, section "Données distantes", et j'y mets l'adresse du fichier CSV. À cause de la politique de sécurité imposée par Google, il faut demander à uMap de faire la requête en cochant la case "Avec proxy". Autrement, vous verrez dans la console du navigateur (touche F12) une erreur de ce genre :

Une fois l'importation correctement configurer, on obtient le résultat que voici :

Sauf que...

Styler les marqueurs

Je veux modifier les marqueurs selon mon bon vouloir. La documentation anglaise de uMap indique que les colonnes sont bien importées en tant que properties. Soit, je vais essayer de trouver quelles sont les propriétés qui m'intéressent. Vu le peu de détails de la doc, le mieux est donc de demander à uMap directement comment il stocke les données en interne, je vais peut-être trouver comment styler mes marqueurs sur la carte. Je passe donc par la case "Export" pour lui faire tout cracher, et bingo !

{
  "type": "Feature",
  "properties": {
    "_umap_options": {
      "iconClass": "Drop", /* Et de un : la forme du marqueur */
      "color": "Red", /* Et de deux : sa couleur */
      "iconUrl": "70" /* Et de trois : le texte dans le marqueur */
    },
    "name": "3 200 000 €",
    "description": "https://propriet...3411/\n\n70 hectares\n\nEt un golf !"
  },
  "geometry": {
    "type": "Point",
    "coordinates": [
      0.160825,
      43.524017
    ]
  }
}

Et là, c'est le drame : impossible de faire en sorte que uMap lise quoi que ce soit de défini par la propriété _umap_options. J'ai bien tenté de mettre l'objet JSON dans la cellule, de faire des colonnes du genre _umap_options.color et bien d'autres variantes, mais rien à faire : c'est une restriction de l'import CSV, il faut aller générer du GeoJSON.

Du GeoJSON depuis Google Sheets

Changement de programme, il va falloir ruser. Déjà, une bonne partie du travail est déjà faite par les braves gens de Mapbox qui ont pondu un Google Apps script pour permettre de générer un fichier GeoJSON depuis un tableau de données dans Google Sheets. Parfait ! Sauf que...

Déjà, le script ne fonctionne pas, car il utilise des fonctions supprimées par Google. Mais on va pas se laisser abattre : la partie non fonctionnelle correspond à l'interface utilisateur, dont on n’a pas besoin, car on fait du bricolage : il ne veut pas de UiApp ? Très bien, on détermine que notre problème se situe là :

// UI to set up GeoJSON export
function gjDialog() {
  var headersRaw = getHeaders(sheet, activeRange, 1);

  // Create a new UI
  var app = UiApp.createApplication()

On va remplacer tout ce beau monde et court-circuiter tout le formulaire qui est normalement affiché à l'utilisateur :

// UI to set up GeoJSON export
function gjDialog() {
  var e = {parameter: {idBox: "ID", lonBox: "lon", latBox: "lat"}};
  exportGJ(e);
  return;

Très bien, maintenant que le script tourne, il faut écrire le GeoJSON généré à un endroit plus pratique que sous forme de fichier dans Google Drive. Voici le code original :

// Handle submits by updating the settings object, calling the
// export function, updates the UI
function exportGJ(e) {
  settings = {
    id: e.parameter.idBox,
    lon: e.parameter.lonBox,
    lat: e.parameter.latBox
  };

  // Update ui to show status
  updateUi();

  // Create GeoJSON file and pass back it's filepath
  var file = createGJFile();

  // Update ui to deliver file
  displayFile(file);
}

On va le transformer ainsi, pour écrire le résultat dans la cellule A1 de la seconde page du classeur :

// Handle submits by updating the settings object, calling the
// export function, updates the UI
function exportGJ(e) {
  settings = {
    id: e.parameter.idBox,
    lon: e.parameter.lonBox,
    lat: e.parameter.latBox
  };

  // Create GeoJSON file and pass back it's filepath
  var gj = createGJFile();

  // Output in cell A1 of sheet 2
  var range = ss.getSheets()[1].getRange('A1');
  range.setValue(gj);
}

Avant d'aller plus loin, il est temps de tester pour constater que... ça ne fonctionne toujours pas. On change donc l'export de CSV à TSV et magie, c'est bon !

Très bien. Maintenant qu'on est capables d'exporter un GeoJSON de qualité (même si le content-type de la réponse du serveur est absolument pas correct), on va pouvoir trafiquer sérieusement la fonction qui génère le GeoJSON, afin de personnaliser l'apparence du marqueur. Voici donc la victime de notre prochaine altération :

// Create the GeoJSON file and returns its filepath
function createGJFile() {
    return DriveApp.createFile(
        (cleanCamel(ss.getName()) || 'unsaved') + '-' + Date.now() + '.geojson',
        Utilities.jsonStringify({
            type: 'FeatureCollection',
            features: getRowsData(sheet, activeRange, 1)
        })
    );
}

Comme c'est du code jetable et qu'on est dans la catégorie des bricolages scandaleux, je n'ai pas cherché à être particulièrement propre ni robuste. La fonction attend donc que les données commencent tout en haut du tableau, et que les prix sont dans la colonne F. Je l'ai donc changée ainsi :

// Create the GeoJSON file and returns its filepath
function createGJFile() {
  var rowData = getRowsData(sheet, activeRange, 1);

  for(i=0; i< rowData.length; i++) {
    var row = rowData[i].properties;

    // Fetch the price's cell background color
    var priceCell = sheet.getRange("F"+(i+2));

    // Title of the popup
    row.title = "Château " + row.prix + "€, " + row.surface + " hectares à " + row.lieu;

    row._umap_options = {
          "color": priceCell.getBackground(),
          "iconClass": "Drop",
          "iconUrl": row.favori + row.surface
        };
  }

  var json = Utilities.jsonStringify({
    type: 'FeatureCollection',
    features: rowData
  });

  return json;
}

Remarquez que la couleur du marqueur est définie par la couleur d'arrière-plan de la colonne de prix (colonne F). En effet, j'ai défini un formatage conditionnel sur la colonne qui progresse du vert pour le prix le plus bas au rouge pour le prix le plus élevé.

Il est maintenant temps de tester notre œuvre. Enregistrez le script, recharger la page du tableur, puis dans le nouveau menu Geo créé par le script, choisissez Export GeoJSON.

Dernière étape, il faut reconfigurer l'exportation du classeur dans le menu "Fichier -> Publication sur le web" : on ne va demander à publier uniquement la seconde feuille, et en TSV, cette fois-ci. On va maintenant retourner sur uMap pour mettre à jour l'adresse des "Données distantes" et fignoler les marqueurs sur la carte.

Retour dans uMap

Première choses à faire : dans la section "Données distantes" du calque uMap, mettre à jour l'adresse URL, et sélectionner le type de données "geojson" et non pas CSV comme précédemment, ni TSV comme on pourrait le penser. Après avoir enregistré et rafraîchi la page, on obtient la chose suivante :

On voit bien les marqueurs personnalisés avec la couleur extraite directement depuis la feuille de calcul Google Sheets, avec une forme de marqueur autre que celle par défaut, et un titre tel que défini par la ligne "iconUrl": row.favori + row.surface du script d'exportation. Parfait !

On va maintenant améliorer la quantité d'informations quand on clique sur un marqueur. Pour cela, dans les propriétés du calque d'importation de données, on va ouvrir les "Options d'interaction", et personnaliser le "Gabarit de la popup" pour utiliser directement des informations en provenance du tableau :

# {lieu}

{{{image}}}

Magnifique château sur un terrain de {surface} hectares.

Remarques : {extras}

Et le résultat est plutôt très bien :

Il est possible d'ajouter beaucoup plus d'informations dans le tableau pour réutiliser sur la carte. Libre à vous :)

Les améliorations

Une bonne approche de l'informatique, c'est d'être fainéant : l'ordinateur est là pour penser et bosser à notre place. On va donc améliorer deux aspects de l'ensemble.

Amélioration 1 : Google Sheets

Même si tout est fonctionnel, il est passablement embêtant de devoir sélectionner les cellules à exporter. La vie serait nettement mieux avec une sélection automatique des lignes et colonnes adéquates. On peut ratisser large, car les lignes et les colonnes vides ne posent pas de problème.

On va donc retourner modifier le script pour définir la sélection correcte pour l'export. Tout en haut du script, on trouve ça :

// Global variables
var ss = SpreadsheetApp.getActiveSpreadsheet(),
    sheet = ss.getActiveSheet(),
    activeRange = ss.getActiveRange(),
    settings = {};

On va très astucieusement le modifier ainsi :

// Global variables
var ss = SpreadsheetApp.getActiveSpreadsheet(),
    sheet = ss.getSheets()[0],
    activeRange = ss.getSheets()[0].getRange('A1:N255'),
    settings = {};

Et maintenant qu'on a défini une nouvelle zone pour la sélection, il ne reste plus qu'à effectivement sélectionner. On a le début de la fonction gjDialog ainsi :

// UI to set up GeoJSON export
function gjDialog() {
  var e = {parameter: {idBox: "ID", lonBox: "lon", latBox: "lat"}};
  exportGJ(e);
  return;

Il nous reste plus qu'à ajouter une ligne en tête de la fonction :

// UI to set up GeoJSON export
function gjDialog() {
  activeRange.activate();
  var e = {parameter: {idBox: "ID", lonBox: "lon", latBox: "lat"}};
  exportGJ(e);
  return;

Maintenant, lors de la régénération du GeoJSON, la zone à exporter est automatiquement sélectionnée. Ça présente l'avantage bonus de montrer à l'utilisateur ce qui a été effectivement utilisé pour l'export, donc s'il manque des lignes ou colonnes, c'est que le script est à modifier.

Amélioration 2 : Scriptlet

Encore une fois, le but est de faciliter la tâche. Récupérer toutes les informations de l'annonce pour les reporter dans le tableau est fastidieux. On va donc ruser en utilisant une relique du passé : un scriptlet.

Le script récupère les données qui sont entourées dans l'image suivante (donc le prix, l'image de couverture, la taille du terrain, le nombre de pièces, de chambres, surface habitable), mais aussi d'adresse de l'annonce :

Le code suivant est du coup très spécifique du site depuis lequel on veut récupérer les données (et n'est plus exactement celui qui permet de remplir le tableau de châteaux) :

javascript:(function(){

var features = document.querySelectorAll('.list-features > li');

var pieces = '';
var surface = '';
var terrain = '';
var chambres = '';

features.forEach(function(f){
f = f.innerText;
s = f.split(' ');
if(f.endsWith('pièces')) {pieces = f.slice(0, f.length-7);}
if(s.length==2 && s[1] == 'm²') {surface = s[0];}
if(f.startsWith('Terrain')) {terrain = f.slice(11, f.length-3).replace('\xa0','');}
if(f.endsWith('chambres')) {chambres = f.slice(0, f.length-9);}
});

var image = document.querySelector('div.js-img-popup:nth-child(1) > a:nth-child(1) > img:nth-child(1)');
var data = document.getElementById('js-data').dataset;

var row = data.classifiedId+'\t'
+window.location.href+'\t'
+data.detailLocalisation+'\t'
+data.classifiedLongitude+'\t'
+data.classifiedLatitude+'\t'
+data.detailPrice+'\t'
+'\t'
+pieces+'\t'
+chambres+'\t'
+'\t'
+surface+'\t'
+terrain+'\t'
+'\t'
+image.src;

alert(row);

})()

Lors de l'exécution du scriptlet (au clic, depuis la barre de favoris), une boîte de dialogue s'ouvre :

Il ne reste du coup plus qu'à sélectionner tout le texte (triple-clic dessus, ou clic puis Ctrl+A), le copier, et le coller dans le tableau : les tabulations dans le texte sont interprétées par Google Sheets comme le passage à la cellule suivante. Du coup, l'importation de données se fait en un copier-coller !

Conclusion

Pour récapituler, on a un système géographique de gestion d'annonces immobilières qui permet de faire ressortir les éléments qui nous intéressent, le tout relativement facilement. Voici les liens des divers documents :

• Document Google Sheets
• Lien de l'export TSV de la cellule A1 (page 2) qui contient notre GeoJSON
• Le scriptlet Figaro Immo à glisser dans la barre de favoris
• Lien vers la carte uMap correspondante (si la carte est vide, peut-être que vous bloquez les requêtes vers docs.google.com)