Excel HubSpot integration Using Power Automate Desktop

This blog post guides you through creating a Power Automate flow that checks for updates in an Excel worksheet and uses the HubSpot API to upload the data for Excel HubSpot integration.

‍

Prerequisites Excel HubSpot integration

• An Excel worksheet containing deal records with fields for "Record ID", "Description", "Order Number", "Created Date", and "Modified Date".
• Access to Power Automate.
• Access to HubSpot with API credentials.
• Basic understanding of JSON and web service invocation.

‍

Setting Up Your Excel Worksheet

First, ensure your Excel worksheet is structured appropriately and stored in a location accessible by Power Automate, such as OneDrive for Business. The worksheet should include a table with columns for:

• Record ID: A unique identifier for each deal, used to match the deal in HubSpot.
• Description: A brief description of the deal.
• Order Number: The order number associated with the deal.
• Created Date: The date the deal was created.
• Modified Date: The date the deal was last modified.

‍

Creating Your Power Automate Flow

1. Initiate the Flow
   • This guide focuses on a manual trigger, but Power Automate allows scheduling the flow for automated Excel HubSpot integration.
2. Read Data from Excel
   • Add an action to read rows from your Excel table. Use "Excel Online (Business): List rows present in a table", specifying the path to your worksheet and the name of the table.
3. Iterate Through the Excel Data
   • Add a "Apply to each" action to process each row from the Excel table. Within this loop, you will format data as necessary and prepare it for updating the corresponding deal in HubSpot.
4. Format Date Fields
   • Use the "Convert time zone" action to format "Created Date" and "Modified Date" into a HubSpot-friendly format (e.g., yyyy-MM-ddTHH:mm:ssZ). Assign these to variables for easy reference.
5. Invoke the HubSpot API to Update Deals
   • Within the loop, add a "HTTP" action to call the HubSpot API's PATCH method for deals. Construct the request URL using the "Record ID" from Excel to identify the specific deal in HubSpot: https://api.hubapi.com/crm/v3/objects/deals/[Record ID].
   • Configure the HTTP action with headers for Accept and Content-Type as application/json, and include your HubSpot API key in the Authorization header.
   • In the request body, format your JSON payload to update the "Description", "Order Number", and date fields for the deal, using the variables you prepared earlier for the date fields.

‍

JSON Payload Example

jsonCopy code

{
 "properties": {
   "description": "%CurrentItem['Description']%",
   "order_number": "%CurrentItem['Order Number']%",
   "created_date": "%CreatedDateVariable%",
   "modified_date": "%ModifiedDateVariable%"
 }
}


‍

The Finished Flow

‍

Conclusion

By automating the update process between Excel and HubSpot, businesses can save time, reduce manual data entry errors, and ensure that their CRM reflects the most current information.

We hope this was useful! If you would like Cloudbliss to help you automate your business process contact us at www.cloudbliss.net or email us at info@cloudbliss.net.

‍

‍