Solving Quickbooks Online API Limitations with RPA | Microsoft Power Automate

We recently encountered a problem where we were unable to retrieve certain fields of Bill (e.g. approval status, payment method, tags) from Quickbooks Online through its API. To solve this problem, we implemented a robotic process automation (RPA) solution using Microsoft Power Automate (formerly known as Flow). We used Power Automation Desktop (PAD) to create a desktop flow that retrieves data from the Quickbooks Online website and stores it in a SQL database. we also schedule the flow to run periodically and update the fields in the database. This solution allowed us to successfully retrieve and store the data we needed from the Quickbooks Online website without using API.

Problem Statement

Let’s dive deep into the problem.

First of all, you need to understand how to get the bill data from Quickbooks Online API

To get bill data using the Quickbooks Online API, you can use the “bill” endpoint and make a GET request to the API. The API will return a list of bills in JSON format.

Here is an example API URL that you can use to get a list of bills:https://quickbooks.api.intuit.com/v3/company/%7BcompanyId%7D/bill
Replace “{companyId}” with the ID of your company in Quickbooks Online.

You can also use query parameters in the API URL to filter the results or specify the fields that you want to retrieve. For example, you can use the “select” query parameter to specify which fields you want to retrieve, and the “where” query parameter to specify conditions for filtering the results.

Here is an example API URL that retrieves a list of bills (maximum 1000 in one request):

https: // quickbooks.api.intuit.com/v3/company/{companyId}/query?query=select * from bill&minorversion=65

Note: You will need to authenticate your API request using OAuth 2.0. You can find more information about authenticating API requests in the Quickbooks Online API documentation.

Below is a typical JSON response of the QuickBooks online bill object.

Sure, here is the same JSON object with modified sample values:

The QuickBooks Online API returns most data, but there are some fields that it does not include, such as Status, Tags, and Payment Method.

One way to access these fields is through the use of Robotic Process Automation (RPA). Clicking on the link provided will give you more information about RPA and how it can be used.

What is RPA ?

Robotic Process Automation (RPA) is a technology that allows businesses to automate repetitive and routine tasks. It involves the use of software robots, or “bots,” that are programmed to perform these tasks in a way that is similar to how a human would do them. RPA can be used to automate tasks across a wide range of industries and applications, including data entry, customer service, and financial processes. It can help businesses increase efficiency and reduce errors, as well as free up employees to focus on more complex and value-added tasks.

For this specific case, we utilised Microsoft Power Automate Desktop to automate the process of signing in to QuickBooks Online (QBO) and retrieving data using JavaScript. This was done through the use of an unattended flow, which is a type of automated workflow that runs without the need for human interaction. The data was then saved in a SQL Server database. This allowed us to streamline the process of accessing and storing data from QBO, improving efficiency and accuracy.

We chose to use Microsoft Power Automate in this case because our client was already using Microsoft Power BI. This made it a convenient choice for integrating with the existing system and streamlining the automation process.

I needed to retrieve and store the values for the following fields from QuickBooks Online (QBO) in a SQL database:

• Approval Status (Approved, Needs Approval, Pending Approval)
• Tags
• Payment Method
• Attachments confirmation (indication of whether there are attachments present or not)

Power Automation Desktop (PAD) offers two types of flows:

• Desktop Flow
• Web Flow

Both Flows have their own features and usages.

In our case, we used both flows. The Desktop Flow was used for the main logic of the automation process, while the Web Flow was used for scheduling the automation process. This allowed us to take advantage of the strengths of each type of flow to achieve the desired automation results.

I used the Desktop Flow in order to extract data from the browser. There were several reasons for this choice:

• The Desktop Flow offers certain features and functions that are not available in the Web Flow, such as the web recorder and the ability to click elements.
• It is easier to debug issues when using the Desktop Flow

We used the Web Flow, specifically a Scheduled Cloud Flow, for scheduling purposes.
This was necessary because the scheduling feature is not available in the Desktop Flow. The Scheduled Cloud Flow can be accessed and configured through the Power Automate website.

Below is brief comparison between Power Automate Desktop and Web

Example Code

1. Database

• To store the value of approval status in sql we need to create a table and add the required columns in it.
• In Our example we have created a table with below fields

2. Get The Bill Id records to get the data

• Now you need the billId to open the bill and get the data for that particular bill so you can get the data from it. For this we have created and set up a Quickbook webhook for Create Update bill so we get the billId.

3. Decide the flow steps for Robots to run

• Then Create the flow steps/Logic which will perform the procedure. We executed this way.
  • Open the link in chrome
    

• Store status into database
• You can create an InsertUpdate Store procedure and use that here
• 

4. Select all records from Bill table, and you see that it has value in status field

Summary:

The article discusses a problem with Quickbooks Online API where certain fields of a bill (e.g. approval status, payment method, tags) are not returned in the API response. The solution proposed is to use robotic process automation (RPA) and Microsoft Power Automate (formerly known as Flow) to retrieve data from the Quickbooks Online website and store it in a SQL database. The solution involves creating a desktop flow in Power Automate Desktop (PAD) and scheduling it to run periodically. This allows the required data to be retrieved and stored from the Quickbooks Online website without the need to use the API. The article also compares Power Automate and Power Automate Desktop in terms of storage, ease of use, and other features. As a Custom QuickBooks Integration Company, we bring a real problem with Quickbooks Online API and solved it with robotic process automation (RPA) and Microsoft Power Automate.

Streamline your financial operations with our custom QuickBooks Integration Service. Connect your Quickbooks Online account in just a few clicks and automate tedious manual processes. Experience seamless integration, real-time data transfer, and improved accuracy. Contact now and start streamlining your finance workflow today