How to Pull HubSpot Data Directly Into Excel Without a Third-Party Integration

If you want to pull HubSpot company data directly into Excel—without using a third-party tool—this guide will show you how.

Why Do This?

One of my clients needed to regularly compare HubSpot company records with another data source. Instead of manually exporting and formatting data every month, we automated the process using Excel and a HubSpot API script—no third-party integrations required.

Step 1: Create a Private App in HubSpot

1. Go to Settings in HubSpot.

2. Under Integrations, select Private Apps.

3. Click Create a private app.

4. Give it a name and description.

5. Under Scopes, add:

   • crm.objects.companies.read

   • crm.schemas.companies.read

6. Save the app and copy your access token (you’ll need it later).

Step 2: Set Up Excel to Pull HubSpot Data

1. Open a new or existing Excel workbook.

2. Go to the Data tab > Get Data > From Other Sources > Blank Query.

3. Open the Advanced Editor (found in the Home tab).

4. Paste in the custom Power Query script (available below).

5. Replace the placeholder token with your HubSpot private app token.

6. Define which properties you want to retrieve from the API using the properties list in the script.

7. Click Done > Close & Load.

The script handles pagination, so it will pull more than 100 records if needed.

Step 3: Customize the Data

To pull additional HubSpot properties (like country or region):

1. Find the internal name of the property in HubSpot (not the label).

2. Add the internal name to the properties list in the script.

3. Refresh the Excel query to see the new data appear.

Refresh and Update

After setup, updating your HubSpot data in Excel is as easy as clicking Refresh in the workbook. Any new records or updates in HubSpot will be reflected in Excel.

Use Cases

This method works for more than just company data. You can adapt it to pull contacts, deals, or other objects by modifying the endpoint and properties in the script.

Final Thoughts

This method eliminates unnecessary tools and gives you direct control over your data. It’s simple, secure, and flexible for various HubSpot reporting needs.

Power Query Script

let
    // ===============================
    // 1) Replace with your Token
    // ===============================
    AccessToken = "{your_access_token}",

    // ------------------------------------------------------------------------------
    // 2) Base URL and pagination settings
    // ------------------------------------------------------------------------------
    baseUrl = "https://api.hubapi.com/crm/v3/objects/companies",
    pageSize = 100, // maximum is 100

    // ------------------------------------------------------------------------------
    // 3) Define the list of properties we want returned
    //    (These are appended as multiple &properties=XYZ in the query string)
    // ------------------------------------------------------------------------------
    PropertiesList = {
        "name",
        "address",
        "address2",
        "city",
        "state",
        "zip",
        "phone",
        "fax",
        "email",
        "country",
        "hs_country_code",
        "createdate"
    },

    // ------------------------------------------------------------------------------
    // 4) Generate the query segment for the properties
    //
    //    This will produce something like:
    //    "properties=owner_number___ogsys&properties=name&properties=active&..."
    // ------------------------------------------------------------------------------
    PropertiesQueryString = Text.Combine(
        List.Transform(PropertiesList, each "properties=" & _),
        "&"
    ),

    // ------------------------------------------------------------------------------
    // 5) Recursive function to get pages
    // ------------------------------------------------------------------------------
    GetPage = (after as text) =>
        let
            // If no 'after' (i.e. first page), don't include it in the query.
            url = 
                if after = "" then
                    baseUrl 
                    & "?" 
                    & PropertiesQueryString 
                    & "&limit=" 
                    & Number.ToText(pageSize)
                else
                    baseUrl 
                    & "?" 
                    & PropertiesQueryString 
                    & "&limit=" 
                    & Number.ToText(pageSize) 
                    & "&after=" 
                    & after,

            // Make the API call
            Source = Json.Document(
                Web.Contents(
                    url,
                    [
                        Headers = [
                            Authorization = "Bearer " & AccessToken,
                            #"Content-Type" = "application/json"
                        ]
                    ]
                )
            ),

            // 'results' is the array of company objects
            results = if Record.HasFields(Source, "results") 
                      then Source[results] 
                      else {},

            // 'paging' is the record that may contain 'next' info
            paging = if Record.HasFields(Source, "paging") 
                     then Source[paging]
                     else null,

            // If 'paging.next.after' exists, store that. Otherwise it's null.
            nextAfter = 
                if paging <> null 
                   and Record.HasFields(paging, "next") 
                   and Record.HasFields(paging[next], "after") 
                then paging[next][after] 
                else null,

            // Recursively combine results with next pages if nextAfter is present
            combined = 
                if nextAfter <> null then
                    List.Combine({results, @GetPage(Text.From(nextAfter))})
                else
                    results
        in
            combined,

    // ------------------------------------------------------------------------------
    // 6) Retrieve all data by calling GetPage() starting with an empty 'after'
    // ------------------------------------------------------------------------------
    data = GetPage(""),

    // ------------------------------------------------------------------------------
    // 7) Convert the list of records into a table
    // ------------------------------------------------------------------------------
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    // ------------------------------------------------------------------------------
    // 8) Expand the top-level record columns (id, properties, etc.)
    // ------------------------------------------------------------------------------
    #"Expanded Record" = Table.ExpandRecordColumn(
        #"Converted to Table", 
        "Column1", 
        {"id", "properties"}, 
        {"id", "properties"}
    ),

    // ------------------------------------------------------------------------------
    // 9) Expand the requested properties into columns
    // ------------------------------------------------------------------------------
    #"Expanded properties" = Table.ExpandRecordColumn(
        #"Expanded Record",
        "properties",
        PropertiesList,
        PropertiesList
    )
    in
    #"Expanded properties"