Google Sheets : comment utiliser des services Web dans vos feuilles de calculs

Laisser une évaluation

3 177
personnes ont consulté cet article

5 minutes

Rédigé par Gilles HERRGOTT - il y a 3 ans et modifié le 26/07/2023 à 16:09

Ce que vous allez découvrir

Etape 1 - Installer la fonction "ImportJSON" sur votre feuille de calcul
Etape 2 - Un exemple : quelles recettes de cuisine à faire avec un ingrédient précis ?

Google Sheets : comment utiliser des services Web dans vos feuilles de calculs

Vous connaissez la fonction Google Sheets GoogleFinance qui vous permet très simplement de suivre les cours ou les bénéfices par action (action Google, Facebook, Amazon…) ? Cette fonction, en précisant quelle action consulter, sur quelle période, extraire les informations boursières de Google Finance et les écrit dans votre feuille de calcul Google Sheets, qui, dès son ouverture, met les données fraîches de Google Finance à votre disposition.

Et si je vous disais qu’il y a d’innombrables services de ce type accessibles sur Internet au travers de ce que l’on nomme des « services Web » ? Ces services sont utilisés pour communiquer entre les applications, par exemple, pour obtenir l’état d’avancement de la préparation d’une commande chez votre commerçant en ligne préféré, ou pour obtenir le trafic routier sur votre GPS.

Certains de ces services sont accessibles librement et peuvent être intégrés dans votre feuille de calcul Google Sheets avec un script que nous allons voir ensemble.

À quoi cela peut-il bien vous servir ? Consulter le catalogue de musique de Spotify, obtenir la météo du jour, lister les horaires d’un vol depuis un aéroport, obtenir les informations d’une société, rechercher dans la base de livres de Google, lister les tweets d’une personne… et beaucoup d’autres choses.

Ces services Web, ou API REST sont très souvent déjà disponibles sur vos applications préférées mais aussi disponibles via des sites qui les recensent, comme par exemple Any API ou Public APIS sur GitHub.

Prêt pour un essai ensemble ? Allons-y.

Etape 1 – Installer la fonction « ImportJSON » sur votre feuille de calcul

Nous allons utiliser un script nommé “ImportJSON” qui va nous permettre d’utiliser une fonction pour appeler ces services Web.

Créer un nouveau Google Sheets
Ouvrir l’éditeur de script en allant dans le menu Outils / Editeur de scripts (mais je suis sûr que vous connaissez quasiment tous la manipulation à présent).

Rendez-vous ensuite chez M. Brad JASPER, sur son GitHub qui a développé tout ce qu’il nous faut pour appeler des services Web. Le script qu’il a développé ajoute une fonction ImportJSON() à Google Sheets. Nous allons voir comment l’intégrer à notre feuille de calcul puis comment l’utiliser avec un exemple.
Copier le contenu du fichier ImportJSON.gs. En ayant cliqué sur le lien du fichier, vous obtiendrez tout le code des fonctions que Brad a créé pour nous. Copier tout le contenu de la page via un Contrôle + A (ou CMD + A sur Mac) puis un Contrôle + C (ou CMD + C sur Mac).

code de IMPORTJASON de Brad Jasper — Code Apps Script de ImportJSON

Retourner dans l’éditeur de code Google Sheets et coller le code précédemment copié à la place du code existant (Contrôle + V ou CMD + V sur Mac).

Copie du code d’ImportJSON dans l’éditeur d script

Il ne nous reste plus qu’à cliquer sur le bouton de sauvegarde de l’éditeur de script pour enregistrer les modifications. Nous avons fait quasiment le plus dur. Vous pouvez fermer la fenêtre de code, nous n’en aurons plus besoin.
De retour dans Google Sheets, dans une cellule, saisir “=ImportJSON“. De nouvelles fonctions ont fait leur apparition. Ce sont des fonctions personnalisées créées par le code précédemment copié.

Les fonctions personnalisées de ImportJSON

Etape 2 – Un exemple : quelles recettes de cuisine à faire avec un ingrédient précis ?

Nous allons à présent créer une feuille de calcul pour chercher des idées de repas en fonction d’un ingrédient que nous avons à disposition dans notre placard. Pour cela, nous allons utiliser les services Web mis à disposition par TheMealDB, une base de données de recettes de cuisine.

Le service de TheMealDB s’utilise de la manière suivante : https://www.themealdb.com/api/json/v1/1/search.php?s=tomato, en saisissant cette URL dans notre navigateur Chrome (ou autre), nous obtenons la réponse suivante :

https://www.themealdb.com/api/json/v1/1/ qui est l’adresse de base du service
search.php qui est la fonction que nous souhaitons utiliser, ici, une recherche par ingrédient
?s=tomato, ?s= qui permet de préciser l’ingrédient recherché, et tomato, l’ingrédient (c’est un site Anglais, il faut donc utiliser le nom des ingrédients en Anglais, mais Google Traduction est votre ami).
La réponse est un texte au format JSON, qui est le format utilisé par nos applications pour communiquer entre elles. Dans notre cas, le texte est structuré de la manière suivante :
- meals : pour la liste des recettes
- Pour chaque recette :
  - idMeal : un identifiant de la recette dans la base TheMealDb
  - strMeal : le nom de la recette
  - strCategory : la catégorie de la recette
  - strArea : le pays de la recette
  - strInstructions : pour les instructions de la recette
  - strMealThumb : une miniature de la photo de la recette
  - strTags : les tags associés à la recette
  - strYoutube : la vidéo associée sur Youtube
  - strIngredient1 à n : la liste des ingrédients

En utilisant un outil pour restructurer la réponse JSON, nous pouvons mieux analyser la réponse :

{
  "meals": [
    {
      "idMeal": "52841",
      "strMeal": "Creamy Tomato Soup",
      "strDrinkAlternate": null,
      "strCategory": "Starter",
      "strArea": "British",
      "strInstructions": "Put the oil, onions, celery, carrots, potatoes and bay leaves in a big casserole dish, or two saucepans. Fry gently until the onions are softened - about 10-15 mins. Fill the kettle and boil it.\r\nStir in the tomato purée, sugar, vinegar, chopped tomatoes and passata, then crumble in the stock cubes. Add 1 litre boiling water and bring to a simmer. Cover and simmer for 15 mins until the potato is tender, then remove the bay leaves. Purée with a stick blender (or ladle into a blender in batches) until very smooth. Season to taste and add a pinch more sugar if it needs it. The soup can now be cooled and chilled for up to 2 days, or frozen for up to 3 months.\r\nTo serve, reheat the soup, stirring in the milk - try not to let it boil. Serve in small bowls with cheesy sausage rolls.",
      "strMealThumb": "https://www.themealdb.com/images/media/meals/stpuws1511191310.jpg",
      "strTags": "Baking",
      "strYoutube": "https://www.youtube.com/watch?v=lBhwjjUiFk4",
      "strIngredient1": "Olive Oil",
      "strIngredient2": "Onions",
      "strIngredient3": "Celery",
      "strIngredient4": "Carrots",
      "strIngredient5": "Potatoes",
      "strIngredient6": "Bay Leaf",
      "strIngredient7": "Tomato Puree",
      "strIngredient8": "Sugar",
      "strIngredient9": "White Vinegar",
      "strIngredient10": "Chopped Tomatoes",
      "strIngredient11": "Passata",
      "strIngredient12": "Vegetable Stock Cube",
      "strIngredient13": "Whole Milk",
    }]
}

Le travail de Brad a été d’interpréter cette réponse pour la transformer en un tableau Google Sheets. Toute la complexité de l’appel au service Web, l’obtention de la réponse et l’interprétation de cette réponse sont cachées par le script que nous avons copié.

Mais revenons à notre Google Sheets :

Dans la cellule A10 de votre feuille de calcul, saisissez la formule suivante :

=ImportJSon("https://www.themealdb.com/api/json/v1/1/search.php?s=tomato";"/")

Attendez quelques secondes, et un tableau de données va s’afficher dans votre feuille de calcul :

À présent, laissons-nous l’opportunité de saisir un ingrédient de manière plus ergonomique, et en français s’il vous plaît. Dans la cellule A7, saisissez tout simplement tomate.
Nous allons faire appel à la fonction GoogleTranslate de Google Sheets pour traduire notre ingrédient et appeler ensuite notre service Web sur TheMealDB et rechercher des recettes. Modifier la formule en A10 de la manière suivante :

=ImportJSon("https://www.themealdb.com/api/json/v1/1/search.php?s=" &amp; GOOGLETRANSLATE(A7;"FR";"EN");"/")

Ajout du champ de saisie de l’ingrédient et utilisation de la fonction GoogleTranslate dans l’appel au service Web

Si vous saisissez à présent “canard” dans la cellule A7, vous obtiendrez directement la liste des recettes à base de canard :

Résultat des recettes pour des plats à base de canard

Naturellement, nous pouvons employer une fonction Query pour diminuer le nombre de colonnes en retour de l’appel à notre service Web. Saisir en A10 la nouvelle formule suivante :

=Query(ImportJSon(« https://www.themealdb.com/api/json/v1/1/search.php?s= » & GOOGLETRANSLATE(A7; »FR »; »EN »); »/ »); « select Col2, Col4, Col5, Col6, Col9, Col10, Col11, Col12, Col13, Col14 »)

Amélioration de la formule avec une fonction Query

Ceci fonctionne avec tous les services Web qui ne nécessitent pas d’autorisation. Alors, comment faire pour ceux qui en demandent une ? Certains services demandent une clé d’API; il faut dans ce cas créer un compte sur le service concerné, et depuis les paramètres du compte, demander à obtenir une clé d’API. Cette clé sera à utiliser dans les paramètres d’appel au service web, dans l’URL.

En espérant avoir ouvert de nouvelles perspectives d’utilisation des services web qui regorgent d’informations sur Internet, je vous propose de nous faire part de vos expériences dans les commentaires !

Besoin d'un peu plus d'aide sur Sheets ?

Des formateurs sont disponibles toute l'année pour vous accompagner et optimiser votre utilisation de Sheets, que ce soit pour votre entreprise ou pour vos besoins personnels !

Découvrir nos formations Sheets