Solving Quickbooks Online API Limitations with RPA | Microsoft Power Automate

Below is a typical JSON response of the QuickBooks online bill object. Sure, here is the same JSON object with modified sample values:

{
"Bill": {
	"SyncToken": "5",
	"domain": "QBO",
	"APAccountRef": {
		"name": "Accounts Payable",
		"value": "456"
	},
	"VendorRef": {
		"name": "Acme Corporation",
		"value": "789"
	},
	"TxnDate": "2022-01-01",
	"TotalAmt": 250.00,
	"CurrencyRef": {
		"name": "United States Dollar",
		"value": "USD"
	},
	"LinkedTxn": [],
	"SalesTermRef": {
		"value": "7"
	},
	"DueDate": "2022-01-31",
	"sparse": false,
	"Line": [
		{
			"Id": "1",
			"Description": "Consulting Services",
			"Amount": 250.00,
			"DetailType": "AccountBasedExpenseLineDetail",
			"AccountBasedExpenseLineDetail": {
			"TaxCodeRef": {
				"value": "NON"
			},
			"AccountRef": {
				"value": "987",
				"name": "Consulting Expenses"
			},
			"BillableStatus": "Billable",
			"CustomerRef": {
				"value": "654",
				"name": "John Smith"
			}
			}
		}
	],
	"Balance": 0,
	"Id": "123",
	"MetaData": {
		"CreateTime": "2022-01-01T09:00:00-08:00",
		"LastUpdatedTime": "2022-01-01T09:00:00-08:00"
	}
},
"time": "2022-01-01T09:00:00.000-08:00"
}

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 Robotic Process Automation (RPA). Clicking on the link provided will give you more information about RPA and its uses.

What is RPA?

Robotic Process Automation (RPA) is a technology that allows businesses to automate repetitive and routine tasks. It involves using software robots, or “bots,” that are programmed to perform these tasks in a way 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, reduce errors, and free up employees to focus on more complex and value-added tasks.

For this specific case, we utilized Microsoft Power Automate Desktop to automate 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.

Further Reading: Is Accounting Automation Required?

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 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 a 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 the below fields

CREATE TABLE bills (
BillId INT PRIMARY KEY,
Status VARCHAR(255),
Tags VARCHAR(255),
PaymentMethod VARCHAR(255)
);

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
    

function getApprovalStatus() {
var statusId = document.getElementById("approvalStatusHeader");
if (statusId === '' || statusId === ' ' || statusId === "" || statusId === 'undefined') {
	return 'NA';
}
var dStatus = statusId.querySelectorAll("div.TextBadge__Wrapper-oh51im-0");
if (dStatus === null || dStatus.length <= 0) {
	return 'NA';
}
var status = dStatus[0].innerText;
if (status === '' || status === ' ' || status === "" || status === 'undefined') {
	return 'NA';
}
return status;
}

function ExecuteScript() {
let tags = document.getElementsByClassName("jcXNwi")[0].childNodes;
var text = "";
if (tags != null && tags.length > 0 && tags != 'undefined' && tags != ' ' && tags != " ") {
	for (let i = 0; i < tags.length; i++) {
		if (tags[i].innerText != "" && tags[i].innerText != '' && tags[i].innerText != null) {
			text = text + tags[i].innerText.replace(/n/g, '') + "|";
		}
	}
} else {
	text = 'NA';
}
if (text == '' || text == ' ' || text == "" || text === 'undefined' || text === "|") {
	text = 'NA';
}
return text;
}

function ExecuteScript() {
var text = 'NA';
var textLabel = document.querySelectorAll('.custom-form-field .ha-text-field .ha-label');

if (textLabel != null && textLabel != 'undefined') {
	if (textLabel[0].textContent.toLowerCase().indexOf("payment") > -1) {
		var content = document.querySelectorAll(".custom-form-field .ha-text-field input");
		text = content[0].defaultValue;
	}
}

if (text == '' || text == ' ' || text == "" || text == 'undefined') {
	text = 'NA';
}
return text;
}

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

4. Select all records from the 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 an 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