Solving Quickbooks Online API Limitations with RPA | Microsoft Power Automate

January 4, 2023 6 min read
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 your company’s ID 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=65Note: 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:


{
"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.

Screenshot of an approved bill form in QuickBooks Online, displaying vendor details, terms, bill and due dates, bill number, permit number, payment method as ACH, and a tag input field.

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

A screenshot of the Power Automate interface showcases example categories like Excel Automation, Web Automation, Desktop Automation, and Datetime Handling with a focus on Quickbooks Online. The "Examples" tab is highlighted.Screenshot of a Power Automate interface showing a single flow named "Open Reports" under "My flows." The flow, designed to integrate with Quickbooks Online, was modified a year ago and the status is "Not running.

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.

Screenshot of a flow setup interface, seamlessly integrating QuickBooks Online. Options include flow name, start date and time, and repeat interval. "Every 1 minute" is selected to ensure constant syncing. The "Create" button is highlighted for your convenience.RecurrencePower-Automate

Below is a brief comparison between Power Automate Desktop and Web

AreaPower AutomatePower Automate Desktop
StorageFlows are stored in the cloudDespite running on your desktop, flows are stored in the cloud too.
Flows run ..… in the cloud.. on your desktop
Ease of useYou’ll find yourself caring way more than you should about the contents of JSON text strings. Relatively well-designed and straightforward.Relatively well-designed and straightforward.

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
      create-new-tab

Bill

run-javascript-function


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;
}

A screenshot of a payment processing page on a website, displaying a bill ID number, payment method dropdown, and an option to add tags. The status is marked as "APPROVED" in green. This seamless integration with Quickbooks Online enhances your bookkeeping efficiency.


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
  • Execute-sql-statement

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

sql

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

Chintan Prajapati
Article by

Chintan Prajapati

Chintan Prajapati, a seasoned computer engineer with over 20 years in the software industry, is the Founder and CEO of Satva Solutions. His expertise lies in Accounting & ERP Integrations, RPA, and developing technology solutions around leading ERP and accounting software, focusing on using Responsible AI and ML in fintech solutions. Chintan holds a BE in Computer Engineering and is a Microsoft Certified Professional, Microsoft Certified Technology Specialist, Certified Azure Solution Developer, Certified Intuit Developer, and Xero Developer.Throughout his career, Chintan has significantly impacted the accounting industry by consulting and delivering integrations and automation solutions that have saved thousands of man-hours. He aims to provide readers with insightful, practical advice on leveraging technology for business efficiency.Outside of his professional work, Chintan enjoys trekking and bird-watching. Guided by the philosophy, "Deliver the highest value to clients". Chintan continues to drive innovation and excellence in digital transformation strategies from his base in Ahmedabad, India.