Using Housecall Pro API w/ Power Automate

Will Cauthen

Housecall Pro is a popular field service management software. They have webhooks and an API available for Max (subscription tier) customers. They integrate with several different project management tools, CRMs, and workflow apps.

Everyone’s needs are different when it comes to solutions, connecting data from different apps, and reporting. Housecall Pro has an integration with Zapier, a popular no-code automation tool that has connections to over 5000 different apps. Currently, actions are limited in Zapier for HouseCall Pro.

How can we do more custom actions and automations with our HouseCall Pro data? Enter Microsoft Power Automate. Power Automate, formally Flow, is a low-code platform for automating processes and workflows. The possibilities are endless when you’re building something yourself. In this article, we will be reviewing the basic steps involved for the following use case.

Use Case: To enhance the customer experience, we want to send a personalized thank you email to customers after an estimate has been scheduled. Office staff will send a personalized email with a video from the owner. We need to be able to query a list of customer emails for appointments that are created or scheduled today.

This guide is going to cover the basics for generating this list in Excel format. The additional customization options are endless. You could schedule this to run at a certain time, execute on demand (webhook), or even automate the email sending through Outlook or Mailchimp (using a pretty email template.

Let’s get into the basics!

Connecting to the Housecall Pro API

In Power Automate, create a new flow with a manual trigger. The trigger can always be updated later to another trigger such as a scheduled time.

Search for the HTTP Trigger and add it. We are going to request data from Housecall Pro through HTTP and receive our data in JSON format.

Now we need to understand how to structure our query. Housecall Pro’s public API documentation is located here: https://docs.housecallpro.com/docs/housecall-public-api/e430ba3d520a0-get-estimates

Start off by updating the method, URI, and headers. For our query, we want to show up to 100 records and we only want to see estimates that have a scheduled start today of today or later. It’s important to note that we are going to later filter all the returned estimates to show us estimates scheduled today versus estimates scheduled for today.

Note: The scheduled_start_min expression is: startOfDay(utcNow())

The default page_size is 10. If you have over 100 future estimates scheduled, you can bump the page_size higher, however 500 is the limit in my testing.

Isolate the Data

With the returned JSON through our HTTP step, we need to pull out the body, disregarding the headers and status code information. To do this, Initialize an object variable and set the value to Body, from the HTTP step.

Now we need to create an array variable from the selected object. Doing so will allow us to filter the array for the estimates that were booked specifically today. Below is an excerpt from a sample record from the returned HTTP results.

Tip: When working with JSON, it's easy to read it and structure it in a formatting tool like: https://codebeautify.org/jsonviewer

Initialize another variable, however set the type to: Array. Set the Value to:

variables('HTTPObject').estimates

Now we need to declare a variable for today’s date. This will be used later for filtering the results. Initialize another string variable.

Value: utcNow('yyyy-MM-dd')

Map the Data

Add a Select Array action. Filter the varCustomers array from the previous action. When mapping the array values, most of it is straight forward. Most of the expressions can be set to item().columnname. For our example, compare the Select action below with the highlighted portions from the sample JSON.

It’s important to note that you only need to map data that is going to add value for your report or workflow.

ID: item().id
Customer Full Name: concat(item().customer.first_name,' ',item().customer.last_name)
Email: item().customer.email
Tags: join(item().customer.tags, ' / ')
*** For tags, we need to add join to the expression for scenarios where there are multiple tags. Tags are stored as an array so our process must merge these, otherwise later steps will fail.
Created: item().created_at
Address: item().address.street

Filter the Data

With how the Housecall Pro API is structured, we cannot query based on when the estimate was created…. however we can query their API based on when the estimate is scheduled for. For all of the returned records, we can then filter estimates that have a creation date of today (teal text below).

Add a filter array action. Set the From to the Output from the Select action. Now, we will filter the records in the array that have a created data that is equal to today. The data values need to be in the same format so we need to wrap and format item().Created the same way we declared varToday (previous action).

formatDateTime(item().Created, 'yyyy-MM-dd') is equal to variables('varToday')

Create a CSV table

To create a CSV table, we simply add a Create CSV table action and set the From to the output of our Filter action.

body('Filter_Array_By_Creation_Date')

After the CSV is created, we can then email the CSV file. Add a Send an email action.

Fill in necessary information and then add the CSV table output to the Attachments Name.

Attachments Content - body('Create_CSV_table')

That’s it! There’s hundreds of different real world scenarios and flows you could continue to build out from here. Let me know in the comments if you would be interested in Part 2! In a real world scenario, you would likely want to further filter down the estimates for unique customers and even filter by time range of last estimate for whether it was appropriate for them to receive the additional customer engagement email from your staff/CRM.

Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments