Intégration de vos données Hubspot dans Power BI (Guide technique)

Introduction

Dans la continuité de notre précédent article sur les enjeux de l’intégration de HubSpot à Power BI, nous vous proposons ici un guide pratique pour passer à la mise en œuvre. Si vous avez compris l’intérêt stratégique de cette connexion, il est temps de découvrir comment la réaliser, étape par étape.

Contexte et Objectif

L’objectif de ce script est de :

• Interroger l’API HubSpot pour récupérer des informations sur des emails (ici, les prénoms et noms d’expéditeurs).
• Gérer la pagination : L’API renvoie par défaut un nombre limité de résultats par requête, il est donc nécessaire de récupérer toutes les pages disponibles.
• Transformer les données JSON en table exploitable dans Power Query pour une analyse ultérieure.

Construction de la Fonction de Récupération

Pour commencer, depuis Power BI allez dans Accueil > transformer les données. Cette étape va vous ouvrir une nouvelle page.
Depuis cette nouvelle page, cliquez sur « Nouvelle Source » puis sur « Requête vide ». Dans la liste des requêtes, clic droit sur votre requête puis « Editeur avancé ».

Définition de la Fonction GetEmailsPage

GetEmailsPage = (after as nullable text) as record =>
    let
        baseUrl = "https://api.hubspot.com/crm/v3/objects/emails?properties=hs_email_from_firstname,hs_email_from_lastname",
        queryParameters = if after = null then [limit = "100"] else [limit = "100", after = after],
        authHeader = [Authorization = "Bearer <votre clé API>"],
        Source = Json.Document(
                    Web.Contents(baseUrl, 
                        [
                            Query = queryParameters,
                            Headers = authHeader
                        ]
                    )
                 )
    in
        Source,

Explication :

• Paramètre after :
  Ce paramètre optionnel représente le token de pagination. Lors du premier appel, il est null, ce qui indique que l’on souhaite récupérer la première page.
• Définition de baseUrl :
  L’URL de base de l’API est spécifiée, incluant des paramètres pour indiquer quelles propriétés d’email nous intéressent (ici le prénom et le nom de l’expéditeur).
• Paramètres de la requête (queryParameters) :
  On fixe une limite à 100 résultats par page. Si le token after est fourni, il est ajouté aux paramètres pour récupérer la page suivante.
• Authentification :
  Le header Authorization contient le token d’accès à remplacer par votre propre clé API HubSpot.
  Pour récupérer votre clé API, rendez vous dans les paramètres d’Hubspot dans la section Intégration, puis Applications privée. Ensuite créez une application et gardez précieusement la clé API fournie.
  Ensuite accordez les droits nécessaires à votre application, dans notre le cas, cochez le droit : crm.objects.contacts.read
• Appel de l’API :
  La fonction Web.Contents envoie la requête à l’API. La réponse est ensuite convertie en format JSON avec Json.Document.

Cette fonction renvoie un record (un type de structure de données en M) contenant les données de la page récupérée.

Gestion de la Pagination par une Fonction Récursive

Définition de la Fonction GetAllPages

GetAllPages = (after as nullable text, acc as list) as list =>
    let
        currentPage = GetEmailsPage(after),
        newAcc = acc & {currentPage},
        hasNext = Record.HasFields(currentPage, "paging") and 
                  (currentPage[paging] <> null) and 
                  Record.HasFields(currentPage[paging], "next"),
        nextAfter = if hasNext then currentPage[paging][next][after] else null
    in
        if hasNext then 
            @GetAllPages(nextAfter, newAcc)
        else 
            newAcc,

Explication :

• Paramètres de la fonction :
  • after : Le token permettant de récupérer la page suivante.
  • acc : L’accumulateur, c’est-à-dire une liste qui va stocker toutes les pages récupérées.
• Récupération de la page actuelle : La fonction appelle GetEmailsPage(after) pour obtenir la page courante.
• Mise à jour de l’accumulateur (newAcc) :
  La page récupérée est ajoutée à la liste accumulée.
• Gestion de la pagination :
  • La variable hasNext vérifie si le record de la page contient un champ "paging" avec une sous-structure "next".
  • Si c’est le cas, nextAfter extrait le token pour la page suivante.
• Appel récursif : Si hasNext est vrai, la fonction s’appelle elle-même en passant le nouveau token et l’accumulateur mis à jour. Sinon, elle retourne l’accumulateur complet.

Ce mécanisme récursif permet de récupérer dynamiquement toutes les pages de résultats sans connaître à l’avance leur nombre.

Extraction et Transformation des Données

Après avoir récupéré toutes les pages, plusieurs étapes permettent d’extraire les emails et de les transformer en une table.

Récupération de Toutes les Pages

AllPages = GetAllPages(null, {}),

Début de la récursion : On lance la fonction GetAllPages avec after = null (première page) et un accumulateur vide {}.

Extraction des Emails

AllEmails = List.Combine(
    List.Transform(AllPages, each if Record.HasFields(_, "results") then _[results] else {})
),

• Transformation de chaque page : Pour chaque page récupérée, on vérifie si elle contient le champ "results", qui stocke la liste d’emails.
• Combinaison des listes : List.Combine fusionne toutes les listes d’emails issues des différentes pages en une seule liste.

Conversion de la Liste en Table

EmailsTable = Table.FromList(AllEmails, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Column1 développé" = Table.ExpandRecordColumn(EmailsTable, "Column1", {"id", "properties", "createdAt", "updatedAt", "archived"}, {"Column1.id", "Column1.properties", "Column1.createdAt", "Column1.updatedAt", "Column1.archived"})

• Création d’une table : La liste d’emails est convertie en table avec Table.FromList. Chaque élément de la liste devient une ligne dans la table.
• Expansion des enregistrements : Table.ExpandRecordColumn décompose la colonne d’enregistrements en plusieurs colonnes distinctes (par exemple, l’ID, les propriétés, les dates de création/mise à jour, et le statut d’archivage). Cette étape rend les données facilement utilisables pour l’analyse.

Assemblage Final du Code

L’ensemble du script est encapsulé dans une expression let ... in ... qui définit successivement toutes les étapes et renvoie finalement la table développée :

let
    GetEmailsPage = (after as nullable text) as record =>
        let
            baseUrl = "https://api.hubspot.com/crm/v3/objects/emails?properties=hs_email_from_firstname,hs_email_from_lastname",
            // Paramètres de requête avec une limite (ici 100, à adapter si besoin)
            queryParameters = if after = null then [limit = "100"] else [limit = "100", after = after],
            // Remplacez <your_access_token> par votre token d'accès HubSpot
            authHeader = [Authorization = "Bearer <Votre clé API>"],
            Source = Json.Document(
                        Web.Contents(baseUrl, 
                            [
                                Query = queryParameters,
                                Headers = authHeader
                            ]
                        )
                     )
        in
            Source,

    // Fonction récursive pour accumuler toutes les pages
    GetAllPages = (after as nullable text, acc as list) as list =>
        let
            currentPage = GetEmailsPage(after),
            newAcc = acc & {currentPage},
            // Vérifier si la page actuelle contient un champ de pagination "paging" avec une valeur "next"
            hasNext = Record.HasFields(currentPage, "paging") and 
                      (currentPage[paging] <> null) and 
                      Record.HasFields(currentPage[paging], "next"),
            nextAfter = if hasNext then currentPage[paging][next][after] else null
        in
            if hasNext then 
                @GetAllPages(nextAfter, newAcc)
            else 
                newAcc,

    // Récupérer toutes les pages à partir de la première (after = null) en démarrant avec une liste vide
    AllPages = GetAllPages(null, {}),

    // Extraction des emails présents dans chaque page (le champ "results" contient la liste d'emails)
    AllEmails = List.Combine(
        List.Transform(AllPages, each if Record.HasFields(_, "results") then _[results] else {})
    ),

    // Conversion de la liste d'emails en table
    EmailsTable = Table.FromList(AllEmails, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Column1 développé" = Table.ExpandRecordColumn(EmailsTable, "Column1", {"id", "properties", "createdAt", "updatedAt", "archived"}, {"Column1.id", "Column1.properties", "Column1.createdAt", "Column1.updatedAt", "Column1.archived"})
in
    #"Column1 développé"

Conclusion

En suivant ce tutoriel, vous avez découvert comment construire un script Power Query en M pour interroger l’API HubSpot, gérer la pagination de manière récursive, extraire les résultats et les transformer en une table. Ce tutoriel vous offre les bases pour :

• Comprendre l’appel d’une API avec authentification via un header.
• Mettre en place une fonction récursive pour accumuler les pages de résultats.
• Effectuer des transformations de données pour obtenir un format exploitable dans votre environnement DATA.

Vous pouvez ainsi adapter ce modèle pour d’autres API ou enrichir l’extraction en ajoutant des vérifications, des gestionnaires d’erreurs ou en modifiant les paramètres de la requête selon vos besoins. Ce type d’intégration s’avère essentiel pour automatiser et optimiser la collecte de données dans vos projets d’analyse.