[Google Sheets] Importer un JSON
Bonjour,
Google Sheets n'a pas de fonction native de type ImportJSON comme pour la "collection"
- ImportHTML : pages HTML
- ImportData : données au format csv (comma = virgule) ou tsv (tabulation)
- ImportFeed : flux rss
- ImportXML : tout fichier structuré, xml, xhtml, ...
Néanmoins, la notation JSON qui signifie JavaScript Object Notation est relativement facile à interpréter par les scripts de Google Sheets. Voici le topo que je vous propose ci-après en 3 chapitres
- Rappel de ce qu'est un JSON
- Comment explorer un JSON
- Comment récupérer de façon sélective les données pertinentes du JSON pour sa propre application
1- Rappel de ce qu'est un JSON
JSON est un format texte de transfert de données entre applications. Il n'est pas réservé à javascript mais utilisé par d'autres supports logiciels. Il peut même par exemple être stocké en cookie de façon à conserver en local les données d'une application.
L'écriture d'un JSON est très souple et très riche, organisée autour d'un couple de données : la clé et sa valeur. Si la clé est textuelle, la donnée peut être variée : texte, nombre, tableau, objet, booléen, mais aussi déclaré null.
Sa structure peut être tout à fait irrégulière et adaptée, à savoir qu' il peut y avoir plusieurs tableaux, des tableaux imbriqués dans d'autres tableaux, des clés absentes pour certains objets comparativement aux objets "voisins". Et comme un JSON est un format texte, et qu'une donnée peut être du texte, il existe des JSON "gigogne" où une valeur du JSON est elle-même un JSON .
2- Comment explorer un JSON
Même s'il existe des fonctions en GScript appelées ImportJSON et qui déploient le JSON en un tableau, de ce qui précède et notamment la structure riche et variée d'un JSON, une telle solution n'est pas à mon sens satisfaisante.
La seule façon de ne pas dénaturer est d'explorer le JSON comme une arborescence. On peut utiliser firefox qui offre d'emblée cette présentation, mais ne permet pas d'exploiter ensuite ces données.
Voici un script relativement court qui permettra cette exploration avec quelques données permettant une première utilisation rapide.
getAllDataJSON(url) :
// https://forum.excel-pratique.com/astuces
// https://www.sheets-pratique.com/
// mike.steelson
let resultat = [];
/**
* Permet d'explorer un json.
* Retourne niveau / id / attribut ou objet / valeur
*
* @param {texte ou cellule} url url du site hébergeant le json ou cellule de la feuille contenant le json
* @customfunction
*/
function getAllDataJSON(url) {
if (url.match(/http(s)?:\/\/?/g)){var data = JSON.parse(UrlFetchApp.fetch(url).getContentText())}
else{var data = JSON.parse(url)}
getAllData(1,eval(data),'data')
return resultat
}
function getAllData(niv,obj,id) {
const regex = new RegExp('[^0-9]+');
for (let p in obj) {
var newid = (regex.test(p)) ? id + '.' + p : id + '[' + p + ']';
if (obj[p]!=null){
if (typeof obj[p] != 'object' && typeof obj[p] != 'function'){
resultat.push([niv, ('■').repeat(niv), trans(newid), p, obj[p]]);
}
if (typeof obj[p] == 'object') {
if (obj[p].length){
resultat.push([niv, ('■').repeat(niv), trans(newid), p + '[0-' +(obj[p].length-1)+ ']', 'tableau']);
}else{
resultat.push([niv, ('■').repeat(niv), trans(newid), p, 'parent']);
}
niv+=1;
getAllData(niv, obj[p], newid );
niv-=1
}
}
}
}
function trans(donnee){
return donnee.replace(/(\[[0-9]+\])/gm,"\[_\]").replace(/data\./gm,"/").replace(/\./gm,"/");
}Exemple dont on peut prendre une copie et changer l'url du JSON.
A noter que le JSON peut être issu d'une url, ou être la valeur d'une cellule de la feuille (car où une donnée serait elle-même en JSON).
https://docs.google.com/spreadsheets/d/1VUvMjdr48hZfwKAicnvZXa4jKVRHywNLWagH6My7ZwA/edit?usp=sharing
ceci permet de prendre ne compte la véritable organisation des données du JSON.
Les clés sont présentées avec une codification de type XPath simplifiée.
Dans cet exemple, j'y ai ajouté une première fonction pour inventorier toutes les clés
=transpose(UNIQUE(query(A3:E;"select C where not C like '%[%]' and not E ='tableau' and not E ='parent' ")))et ensuite, pour certaines d'entre elles, la liste des données
=query($A$2:$E;"select E where C = '"& H$2 &"'")L'emplacement des formules est indiqué sur fond jaune.
3- Comment récupérer de façon sélective les données pertinentes du JSON pour sa propre application
... à suivre ...
3- Comment récupérer de façon sélective les données pertinentes du JSON pour sa propre application
3-1- La méthode query proposée ci-dessus est rapide et intéressante car elle en fait plus appel directement à l'url.
=query($A$2:$E;"select E where C = '"& H$2 &"'")mais elle impose que la structure soit parfaitement régulière (pas de clés "null" ou absente pour un item donné afin de ne pas créer des décalages)
3-2- La méthode qui respecte strictement l'arborescence s'appuie sur le "cheminement" à l'aide des paramètres XPath simplifiés ici
La syntaxe est la suivante
=getDataJSON( url ; "/radical" ; "/xpath1|/xpath2")où :
- url peut être
- une url en http,
- une cellule contenant l'url,
- ou une cellule contenant elle-même le JSON (cel peut exister dans le cas de JSON gigogne)
- /radical peut être
- absent s'il s'agit d'un extrait d'informations depuis la racine,
- ou /nom_du_tableau (sans les marques [_])
- ou un élément commun à tous les xpath
- /xpath,
- une collection de chemins séparés par |
- ou une plage de cellules contenant chacune un xpath
- ces xpath sont obtenus facilement à partir d la fonction getAllDataJSON vue précédemment
Quelques exemples ici https://docs.google.com/spreadsheets/d/17NPQB0tfl_okLGUbRmmxk1T-4KZtd27xdCaS0IB9Q0M/edit?usp=sharing
avec url = https://public.opendatasoft.com/api/records/1.0/search/?dataset=prix_des_carburants_j_7&q=52000
recherche les paramètres d'en-tête du json
=getDataJSON( url ; ; "/parameters/dataset|/parameters/q|/parameters/rows|/parameters/start|/parameters/format")
idem en mettant en radical le point commun des xpath
=getDataJSON( url ; "/parameters" ; "/dataset|/q|/rows|/start|/format")
sortie d'un tableau des valeurs de records, notamment ville, adresses, etc. avec les prix des différents carburants
=getDataJSON( url ; "/records" ; "/fields/city|/fields/address|/fields/brand|/fields/geo_point|/fields/price_gplc|/fields/price_gazole|/fields/price_e10|/fields/price_sp98|/fields/price_e85" )Le code complet disponible ici :https://docs.google.com/spreadsheets/d/17NPQB0tfl_okLGUbRmmxk1T-4KZtd27xdCaS0IB9Q0M/edit?usp=sharing
let resultat = [];
/**
* Permet d'explorer un json.
* Retourne niveau / id / attribut ou objet / valeur
*
* @param {texte ou cellule} url url du site hébergeant le json ou cellule de la feuille contenant le json
* @customfunction
*/
function getAllDataJSON(url) {
if (url.match(/http(s)?:\/\/?/g)){var data = JSON.parse(UrlFetchApp.fetch(url).getContentText())}
else{var data = JSON.parse(url)}
getAllData(1,eval(data),'data')
return resultat
}
function getAllData(niv,obj,id) {
const regex = new RegExp('[^0-9]+');
for (let p in obj) {
var newid = (regex.test(p)) ? id + '.' + p : id + '[' + p + ']';
if (obj[p]!=null){
if (typeof obj[p] != 'object' && typeof obj[p] != 'function'){
resultat.push([niv, ('■').repeat(niv), trans(newid), p, obj[p]]);
}
if (typeof obj[p] == 'object') {
if (obj[p].length){
resultat.push([niv, ('■').repeat(niv), trans(newid), p + '[0-' +(obj[p].length-1)+ ']', 'tableau']);
}else{
resultat.push([niv, ('■').repeat(niv), trans(newid), p, 'parent']);
}
niv+=1;
getAllData(niv, obj[p], newid );
niv-=1
}
}
}
}
function trans(donnee){
return donnee.replace(/(\[[0-9]+\])/gm,"\[_\]").replace(/data\./gm,"/").replace(/\./gm,"/");
}
/**
* Convertit un json en tableau de données.
* Retourne valeurs des champs d'un tableau au format json.
*
* @param {texte ou cellule} url url du site hébergeant le json
* @param {texte ou cellule} tableau tableau à explorer ('blanc' si depuis la racine du json)
* @param {cellules} xpath /niv1/niv2 etc.
* @customfunction
*/
function getDataJSON(url,tableau,xpath){
try{
if (url.match(/http(s)?:\/\/?/g)){var data = JSON.parse(UrlFetchApp.fetch(url).getContentText())}
else{var data = JSON.parse(url)}
var json = eval('data'+tableau.replace(/\//gm,"\."))
if (typeof xpath == 'object'){var liste = xpath.join().split(",")} else {var liste = xpath.split("|")}
if (json.length){json.forEach(function(elem){getData(elem,liste)})} else {getData(json,liste)}
return resultat
}
catch(e) {
return ('Pas de résultat - vérifier l\'url et les paramètres !');
}
}
function getData(elem,liste){
var prov=[]
liste.forEach(function(chemin){
var t=chemin.split('/');
var obj=elem;
for (var i=1;i<t.length;i++){obj=obj.item(t[i])}
if(typeof obj=='object'){prov.push('['+obj+']')}else{prov.push(obj)}
})
resultat.push(prov)
}
Object.prototype.item=function(i){return this[i]};