Extraire les données d’une feuille de calcul Google Sheets avec PHP

Introduction à l’API Google Sheets et à la librairie PHP google/apiclient pour extraire les données d’une feuille de calcul
Table des matières

Contexte

Cet article est une introduction à l’utilisation de l’API Sheets. Nous allons utiliser la librairie PHP google/apiclient pour extraire les données présentes dans une feuille de calcul Google Sheets.

Pré-requis

Avant d’aller plus loin, vérifiez que vous devez disposer :

  • D’un compte Google ;
  • D’un environnement de développement avec Composer ;

Dans le cadre de ce tutoriel, nous n’allons pas aborder l’authentification via OAuth et nous allons utiliser une clé de compte de service.

Présentation de l’API Google Sheets

Les outils Google disposent d’une API permettant d’effectuer diverses opérations. L’API Google Sheets permet de créer des applications connectés à un document et d’automatiser un certain nombre de tâches :

  • Lecture des données ;
  • Écriture de données ;
  • Créer et gérer des feuilles de calcul ;
  • Gérer le format des cellules ;
  • etc ;

Le document Google Sheets

J’ai créé un document grâce à ChatGPT. J’ai demandé à ce dernier deux listes de personnalités célèbres :

  • Une liste de quinze femmes victimes de l’effet Matilda et pour chacune : ses nom et prénom, ses années de naissance et de décès, le domaine de compétence, une biographie en une phrase, le bénéficiaire de leur travaux, le prix éventuel non obtenu.
  • Une liste dix philosophes célèbres avec pour chaque personnalité : ses nom et prénom, ses années de naissance et de décès, le concept phare qu’on leur attribue et une biographie en une phrase ;

Vous devrez copier le fichier et l’ajouter à votre Google Drive : https://docs.google.com/spreadsheets/d/13vos2BO91vxaxkahtY3zb9TM-Qssxhae8qmBwRj6xGo/

Une fois le fichier copié et créé, il faut que vous notiez l’ID du document. C’est la chaine de caractères après « https://docs.google.com/spreadsheets/d/ » et avant « /edit« .

Configuration de l’API Google Sheets

Création du projet Google Cloud

Rendez-vous à cette adresse : https://console.cloud.google.com et créez un nouveau projet. Je l’ai appelé Projet Google Sheets et PHP.

Activation de l’API Google Sheets

Depuis la page d’accueil de votre projet, allez dans la rubrique API et services puis dans la bibliothèque d’API. Si vous ne trouvez pas la page d’accueil, cliquez sur le logo Google Cloud.

Dans le champs de recherche, saisissez Google Sheets et sélectionnez l’API désirée et activez-la.

Gestion des accès à l’API

Une fois l’API Google Sheets associée à votre projet, vous pouvez accéder à un tableau de bord spécifique à la gestion du service.

Création d’un compte de service

Dans la tableau de bord de l’API, cliquez sur Créer des identifiants.

Sélectionnez votre API et choisissez de partager les données de l’application. Cliquez sur suivant.

Dans le formulaire Créer un compte de service :

  1. Saisissez le nom que vous souhaiter donner au compte de service ;
  2. Vous pouvez personnaliser l’ID du compte de service ;
  3. La description est facultative.
  4. Sautez les étapes facultatives, cliquez sur OK pour valider la création du compte de service.

La création d’un compte de service génère une adresse e-mail qui va reprendre l’ID du compte de service. Notez l’adresse e-mail générée.

Allez ensuite sur votre la feuille de calcul Google Sheet et partagez-la avec l’adresse e-mail générée.

Cette étape étape de délégation de privilège est essentielle.

Création d’une clé d’authentification

Au niveau du tableau de bord de l’API, dans la rubrique Compte de service, cliquez sur le compte que vous venez de créer.

Dans l’écran suivant, cliquez sur l’onglet Clés. Cliquez sur le bouton Ajouter une clé et sélectionnez Créer une clé.

Une popin apparait et vous demande quelle type de clé vous voulez créer. Sélectionner JSON, et cliquez sur Créer.

La création de la clé entraine le téléchargement d’un fichier JSON d’authentification qu’il conviendra de sauvegarder. Nous l’utiliserons plus tard lors des requêtes à l’API.

Nous en avons fini avec la configuration du projet Google Cloud. Je vous rassure, c’était la partie la moins intéressante.

Extraction des données de la feuille de calcul Google Sheets

Installation de la librairie PHP google/apiclient

Placez-vous dans le répertoire de travail et tapez la commande suivante :

composer require google/apiclient

Composer va installer la dernière version de la librairie. Vous pouvez également spécifier une version spécifique.

composer require google/apiclient:^2.0

Connexion à l’API

Dans le répertoire où vous avez exécuté la commande composer pour installer la librairie google/apiclient ; créez un fichier PHP dans lequel vous allez saisir le code suivant :

require "vendor/autoload.php";
  
$client = new \Google_Client();
$client->setApplicationName('Google Sheets API');
$client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
$client->setAccessType('offline');
$path = PATH.'/credentials.json'; // PATH = chemin physique vers le fichier contenant la clé du compte de service
$client->setAuthConfig($path);

Extraction et mise en forme des données de la feuille de calcul

Nous allons extraire l’ensemble des données de l’onglet « Philosophe »

// Utilisation de la connexion
$service = new \Google_Service_Sheets($client);

// Désignation de la feuille de calcul
$spreadsheetId = '13vos2BO91vxaxkahtY3zb9TM-Qssxhae8qmBwRj6xGo'; // ID de la feuille de calcul que j'ai partagé ; vous devrez saisir ici l'ID de votre Google Sheets
$spreadsheet = $service->spreadsheets->get($spreadsheetId);

// Détermination de la portée de la requête, ici l'onglet "Philosophes"
$range = 'Philosophes';

// Positionnement sur l'onglet "Matilda"
$response = $service->spreadsheets_values->get($spreadsheetId, $range);

// Récuparation de toutes les lignes présentes dans l'onglet "Matilda"
$rows = $response->getValues();

// Extraction de l'entête
$headers = array_shift($rows);

// Création du tableau associatif
$array = [];
foreach ($rows as $row) {
    $array[] = array_combine(array_intersect_key($headers, $row), array_intersect_key($row, $headers));
}

Le contenu du tableau associatif $array présentera les données ainsi :

Array
(
    [0] => Array
        (
            [Nom] => du Châtelet
            [Prénom] => Émilie
            [Naissance] => 1706
            [Mort] => 1749
            [Domaine de compétence] => Physique et mathématiques
            [Biographie] => Émilie du Châtelet était une physicienne et mathématicienne française dont les contributions à la science ont été minimisées en faveur de Voltaire.
            [Bénéficiaire] => Voltaire
        )

    [1] => Array
        (
            [Nom] => Anning
            [Prénom] => Mary
            [Naissance] => 1799
            [Mort] => 1847
            [Domaine de compétence] => Paléontologie
            [Biographie] =>  Mary Anning était une paléontologue anglaise qui a découvert d'importants fossiles marins, bien que ses contributions aient été largement ignorées de son vivant.
            [Bénéficiaire] => Des géologues masculins de son époque
        )

    [2] => Array
        (
            [Nom] => Franklin
            [Prénom] => Rosalind
            [Naissance] => 1920
            [Mort] => 1958
            [Domaine de compétence] => Chimie et cristallographie
            [Biographie] => Rosalind Franklin a contribué à la compréhension de la structure de l'ADN, bien que sa contribution ait été largement méconnue.
            [Bénéficiaire] => James Watson et Francis Crick
            [Prix non obtenu] => Le prix Nobel de physiologie ou médecine pour la découverte de la structure de l'ADN.
        )

    [3] => Array
        (
            [Nom] => d'Alexandrie
            [Prénom] => Hypatia
            [Naissance] => vers 360
            [Mort] => 415
            [Domaine de compétence] => Mathématiques et philosophie
            [Biographie] => Hypatia était une mathématicienne et philosophe grecque dont le travail a été minimisé par ses contemporains masculins.
        )

    [4] => Array
        (
            [Nom] => Lovelace
            [Prénom] => Ada
            [Naissance] => 1815
            [Mort] => 1852
            [Domaine de compétence] => Programmation informatique
            [Biographie] => Ada Lovelace est considérée comme la première programmeuse, ayant écrit le premier algorithme destiné à être exécuté par une machine.
            [Bénéficiaire] => Charles Babbage
        )

    [5] => Array
        (
            [Nom] => Noether
            [Prénom] => Émilie
            [Naissance] => 1882
            [Mort] => 1935
            [Domaine de compétence] => Mathématiques et physique
            [Biographie] => Émilie Noether était une mathématicienne allemande dont les contributions à l'algèbre abstraite sont essentielles.
            [Bénéficiaire] => Albert Einstein et d'autres collègues masculins
        )

    [6] => Array
        (
            [Nom] => Meitner
            [Prénom] => Lise
            [Naissance] => 1878
            [Mort] => 1968
            [Domaine de compétence] => Physique nucléaire
            [Biographie] => Lise Meitner a été une pionnière de la physique nucléaire et de la découverte de la fission nucléaire.
            [Bénéficiaire] => Otto Hahn
            [Prix non obtenu] => Le prix Nobel de chimie pour la découverte de la fission nucléaire.
        )

    [7] => Array
        (
            [Nom] => McClintock
            [Prénom] => Barbara
            [Naissance] => 1902
            [Mort] => 1992
            [Domaine de compétence] => Génétique
            [Biographie] => Barbara McClintock a été une généticienne pionnière et a découvert les éléments transposables dans le maïs.
            [Bénéficiaire] => 
            [Prix non obtenu] => Le prix Nobel de physiologie ou médecine pour ses travaux sur les éléments transposables.
        )

    [8] => Array
        (
            [Nom] => Fossey
            [Prénom] => Dian
            [Naissance] => 1932
            [Mort] => 1985
            [Domaine de compétence] => Primatologie
            [Biographie] => Dian Fossey est célèbre pour son étude des gorilles des montagnes au Rwanda, contribuant à la conservation de cette espèce en danger.
            [Bénéficiaire] => Dans une certaine mesure, à George Schaller et Louis Leakey
        )

    [9] => Array
        (
            [Nom] => Payne-Gaposchkin
            [Prénom] => Cecilia
            [Naissance] => 1900
            [Mort] => 1979
            [Domaine de compétence] => Astronomie
            [Biographie] => Cecilia Payne-Gaposchkin a été une astronome britannico-américaine dont les recherches sur la composition des étoiles ont été attribuées à Henry Norris Russell.
            [Bénéficiaire] => Henry Norris Russell
        )

    [10] => Array
        (
            [Nom] => Wu
            [Prénom] => Chien-Shiung
            [Naissance] => 1912
            [Mort] => 1997
            [Domaine de compétence] => Physique nucléaire
            [Biographie] => Chien-Shiung Wu a joué un rôle clé dans l'expérience qui a confirmé la violation de la parité, mais ses collègues masculins ont été récompensés par le prix Nobel.
            [Bénéficiaire] => Tsung-Dao Lee et Chen-Ning Yang
        )

    [11] => Array
        (
            [Nom] => Stevens
            [Prénom] => Nettie
            [Naissance] => 1861
            [Mort] => 1912
            [Domaine de compétence] => Génétique
            [Biographie] => Nettie Stevens a découvert les chromosomes sexuels, mais ses travaux ont été attribués à Edmund Beecher Wilson.
            [Bénéficiaire] => Edmund Beecher Wilson
        )

    [12] => Array
        (
            [Nom] => Bell Burnell
            [Prénom] => Jocelyn
            [Naissance] => 1943
            [Mort] => 
            [Domaine de compétence] => Astronomie
            [Biographie] => Jocelyn Bell Burnell a découvert les pulsars, mais son directeur de thèse a reçu le prix Nobel pour cette découverte.
            [Bénéficiaire] => Son directeur de thèse, Antony Hewish
            [Prix non obtenu] => Le prix Nobel de physique pour la découverte des pulsars.
        )

    [13] => Array
        (
            [Nom] => Marić
            [Prénom] => Mileva
            [Naissance] => 1875
            [Mort] => 1948
            [Domaine de compétence] => Physique
            [Biographie] => Mileva Marić, l'épouse d'Albert Einstein, a contribué à ses premiers travaux, mais sa contribution a été minimisée.
            [Bénéficiaire] => Albert Einstein
        )

    [14] => Array
        (
            [Nom] => Hodgkin
            [Prénom] => Dorothy Crowfoot
            [Naissance] => 1910
            [Mort] => 1994
            [Domaine de compétence] => Cristallographie
            [Biographie] => Dorothy Crowfoot Hodgkin a été pionnière en cristallographie, mais son mentor a été reconnu pour son travail.
            [Bénéficiaire] => Son mentor, John Desmond Bernal
            [Prix non obtenu] =>  Le prix Nobel de chimie pour la détermination de structures cristallines.
        )

)

La même opération avec l’onglet « Philosophes » nous donnera le résultat suivant :

Array
(
    [0] => Array
        (
            [Nom] => Socrate
            [Prénom] => 
            [Naissance] => 470 av. J.-C.
            [Mort] => 399 av. J.-C.
            [Concept phare] => Philosophie socratique
            [Biographie] => Brisa les conventions sociales en remettant en question les croyances et les valeurs de son époque, et a marqué l'histoire de la philosophie grâce à sa méthode dialectique.
        )

    [1] => Array
        (
            [Nom] => Platon
            [Prénom] => 
            [Naissance] => 427 av. J.-C.
            [Mort] => 347 av. J.-C.
            [Concept phare] => Platonisme
            [Biographie] => Disciple de Socrate, Platon a fondé l'Académie, et ses écrits, dont "La République," ont influencé la philosophie occidentale pendant des siècles.
        )

    [2] => Array
        (
            [Nom] => Aristote
            [Prénom] => 
            [Naissance] => 384 av. J.-C.
            [Mort] => 322 av. J.-C.
            [Concept phare] => Aristotélisme
            [Biographie] => Élève de Platon, Aristote a contribué à de nombreux domaines de la philosophie, de la biologie à la métaphysique, en passant par l'éthique.
        )

    [3] => Array
        (
            [Nom] => Descartes
            [Prénom] => René
            [Naissance] => 1596
            [Mort] => 1650
            [Concept phare] => Rationalisme
            [Biographie] => Connus pour sa déclaration "Je pense, donc je suis," Descartes a jeté les bases du rationalisme moderne.
        )

    [4] => Array
        (
            [Nom] => Kant
            [Prénom] => Emmanuel
            [Naissance] => 1724
            [Mort] => 1804
            [Concept phare] => Idéalisme transcendantal
            [Biographie] => Kant a révolutionné la philosophie en développant sa critique de la raison pure et en explorant les limites de la connaissance humaine.
        )

    [5] => Array
        (
            [Nom] => Rousseau
            [Prénom] => Jean-Jacques
            [Naissance] => 1712
            [Mort] => 1778
            [Concept phare] => Contrat social
            [Biographie] => Rousseau a influencé la pensée politique avec son ouvrage "Du contrat social" et a contribué au romantisme littéraire.
        )

    [6] => Array
        (
            [Nom] => Nietzsche
            [Prénom] => Friedrich
            [Naissance] => 1844
            [Mort] => 1900
            [Concept phare] => Nihilisme
            [Biographie] => Nietzsche a critiqué la morale traditionnelle et la religion, prônant un individualisme radical et une "volonté de puissance."
        )

    [7] => Array
        (
            [Nom] => Marx
            [Prénom] => Karl
            [Naissance] => 1818
            [Mort] => 1883
            [Concept phare] => Marxisme
            [Biographie] => Marx est l'auteur du "Manifeste du Parti communiste" et a formulé la théorie du matérialisme historique.
        )

    [8] => Array
        (
            [Nom] => Beauvoir
            [Prénom] => Simone de
            [Naissance] => 1908
            [Mort] => 1986
            [Concept phare] => Existentialisme
            [Biographie] => Philosophe existentialiste et féministe, elle a écrit "Le Deuxième Sexe" et a influencé la pensée féministe moderne.
        )

    [9] => Array
        (
            [Nom] => Camus
            [Prénom] => Albert
            [Naissance] => 1913
            [Mort] => 1960
            [Concept phare] => Absurde
            [Biographie] => Connu pour son roman "L'Étranger," Camus a exploré les thèmes de l'absurdité de la vie et de la révolte.

        )

)

Dans un prochain article, nous verrons comment encapsuler ces différents éléments dans une extension WordPress, gérer le cache et l’automatisation, faire du mapping pour alimenter des custom posts type et des métadonnées. Il sera alors possible d’exploiter les données d’une feuille de calcul Google Sheets via des shortcodes ou des blocs Gutenberg.

Dans notre exemple, nous n’avons pas pris le temps de parler plus en détail de l’utilisation du fichier credentials.json. Il est recommandé de ne pas le rendre accessible en http.

Je suis Thanh Nguyen, artisan du Web depuis 1998 et le doublé de Zidane en finale de coupe du monde.

5 réponses

  1. Bonjour, j’ai enfin trouvé une réponse à cette question qui ne nécessite pas de compte google cloud donc je la partage partout pour éviter à d’autres les mêmes recherches

    J’ai utilisé ChatGPT pour chercher une méthode pour récupérer le contenu d’une ou plusieurs cellules depuis google Sheet vers un site internet et j’ai validé que cela fonctionnait correctement.

    Cette opération fonctionne pour tout le monde, avec n’importe quel compte google et n’importe quel fichier HTML. Il n’y a pas de Backend, pas de serveur, pas de frais. La seule contrainte est de mettre un fichier excel en « public » donc potentiellement des problèmes de sécurité donc cette partie doit être amélioré pour un déploiement plus sérieux mais c’est mieux que tout ce que j’ai trouvé jusque la :

    1 : Créer le google sheet, le remplir et le partager un « public »

    2 : Récupérer l’ID du google sheet (une suite de chifres et de lettres dans le lien de partage, regarder sur google pour comprendre quoi garder dans ce lien)

    3 : Toujours dans google sheet, cliquez sur Extentions => Apps Script

    4 : Restez dans le fichier Code.gs (fichier par défaut) et remplacez le code par défaut par le code suivant (code généré par ChatGPT) :

    // Remplacez ‘ID_DU_DOCUMENT’ par l’ID réel de votre document Google Sheets
    var spreadsheetId = ‘IDduGoogleSHEET’;

    function doGet() {
    // Ouvrir le document Google Sheets
    var sheet = SpreadsheetApp.openById(spreadsheetId).getActiveSheet();
    // Récupérer les valeurs des cellules spécifiées
    var cellA1 = sheet.getRange(1, 1).getValue(); // A1
    var cellB3 = sheet.getRange(3, 2).getValue(); // B3
    var cellC7 = sheet.getRange(7, 3).getValue(); // C7
    // Retourner les données au format JSON
    var response = {
    A1: cellA1,
    B3: cellB3,
    C7: cellC7
    };

    // Configurer la réponse HTTP
    return ContentService.createTextOutput(JSON.stringify(response))
    .setMimeType(ContentService.MimeType.JSON);
    }
    5 : Enregistrez (icone de disquette), débuggez, et déployez votre script avec les paramètres suivants :
    Déployer => Nouveau Déploiement
    Dans l’engrenage (partie gauche de la fenêtre qui s’est ouverte) => Application web
    Dans la partie droite => Qui a accès => Tout le monde (cette parti peux surement être améliorer)
    6 : Créer votre site web en adaptant le code suivant (code généré par ChatGPT) :

    Exemple HTML

    Contenu des cellules spécifiées depuis Google Sheets

    // Charger le contenu depuis l’URL du script déployé
    fetch(‘URL_DU_SCRIPT_DÉPLOYÉ’)
    .then(response => response.json())
    .then(data => {
    document.getElementById(‘content’).innerHTML = `
    Contenu de la cellule A1 : ${data.A1}
    Contenu de la cellule B3 : ${data.B3}
    Contenu de la cellule C7 : ${data.C7}
    `;
    })
    .catch(error => console.error(‘Erreur :’, error));

    Et voila, reste à faire la mise en page mais ce code fonctionne pour moi !

  2. Bonjour
    La configuration de mon entreprise ne me permet pas de creer un nouveau projet via Google cloud console. Existe-il un moyen de passer par Google Apps script pour le faire svp ?

      1. Pas encore. La finalité de mon experience est de connecter Sheet à Periscope data du coup, je vais essayer la méthode de Jean et vous revenir. J’ai pas mal d’étape à suivre pour ça. Si vous avez une astuces pour me permettre d’aller plus vite…

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ces articles pourraient aussi vous intéresser

Rechercher sur le site