If you want to pull HubSpot company data directly into Excel—without using a third-party tool—this guide will show you how.
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.
Go to Settings in HubSpot.
Under Integrations, select Private Apps.
Click Create a private app.
Give it a name and description.
Under Scopes, add:
crm.objects.companies.read
crm.schemas.companies.read
Save the app and copy your access token (you’ll need it later).
Open a new or existing Excel workbook.
Go to the Data tab > Get Data > From Other Sources > Blank Query.
Open the Advanced Editor (found in the Home tab).
Paste in the custom Power Query script (available below).
Replace the placeholder token with your HubSpot private app token.
Define which properties you want to retrieve from the API using the properties list in the script.
Click Done > Close & Load.
The script handles pagination, so it will pull more than 100 records if needed.
To pull additional HubSpot properties (like country or region):
Find the internal name of the property in HubSpot (not the label).
Add the internal name to the properties list in the script.
Refresh the Excel query to see the new data appear.
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.
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.
This method eliminates unnecessary tools and gives you direct control over your data. It’s simple, secure, and flexible for various HubSpot reporting needs.
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"