How to Integrate Retool with QuickBooks

Before configuring Retool, you need an Intuit Developer app to obtain OAuth 2.0 credentials. Go to developer.intuit.com and sign in with your Intuit account. Navigate to 'My Apps' and click 'Create an app'. Select 'QuickBooks Online and Payments' as the platform. Give the app a name (e.g., 'Retool Integration') and click Create. In the app settings: 1. Go to the 'Development' section → 'Keys & OAuth'. Copy the 'Client ID' and 'Client Secret' — you will need these in Retool. 2. Scroll to 'Redirect URIs' and add Retool's OAuth callback URL. For Retool Cloud, this is `https://oauth.retool.com/oauth/oauthcallback`. For self-hosted Retool, it is `https://your-retool-domain.com/oauth/oauthcallback`. 3. Under 'Scopes', ensure 'com.intuit.quickbooks.accounting' is selected (required for all accounting data access). For production (live QuickBooks company data), you will need to go through Intuit's app listing process or use a production app. For development and testing, the sandbox app with sandbox credentials works against QuickBooks' test company data. To obtain your initial refresh token, use the Intuit OAuth 2.0 Playground (developer.intuit.com/app/developer/qbo/docs/develop/authentication-and-authorization/oauth-2.0-playground) or the Postman collection Intuit provides. Complete the OAuth authorization flow, then copy the resulting access_token, refresh_token, and x_refresh_token_expires_in values. Capture your QuickBooks company's realm ID: log into QuickBooks Online at qbo.intuit.com and look at the URL — the numeric value after '/app/' in the URL is your realm ID (e.g., 1234567890123456789).

1// QuickBooks OAuth 2.0 token endpoint and scopes reference
2// Token URL (for refresh token exchange):
3// https://oauth.platform.intuit.com/oauth2/v1/tokens/bearer
4//
5// Authorization URL:
6// https://appcenter.intuit.com/connect/oauth2
7//
8// Required scope for accounting data:
9// com.intuit.quickbooks.accounting
10//
11// Base API URL:
12// https://quickbooks.api.intuit.com
13//
14// Sandbox Base API URL:
15// https://sandbox-quickbooks.api.intuit.com

Navigate to the Resources tab in Retool and click Add Resource. Select 'REST API' as the resource type. In the resource configuration form: - 'Resource name': Enter 'QuickBooks Online' or 'QuickBooks Production'. - 'Base URL': Enter `https://quickbooks.api.intuit.com` (or `https://sandbox-quickbooks.api.intuit.com` for sandbox). - 'Headers': Add `Accept: application/json` and `Content-Type: application/json` as default headers. For authentication, scroll to the 'Authentication' section and select 'Custom Auth' from the dropdown. Custom Auth allows you to configure a token refresh flow that Retool runs automatically when an API call returns a 401. In the Custom Auth 'Login' section, configure the initial token exchange: - Step 1 (HTTP Request): POST to `https://oauth.platform.intuit.com/oauth2/v1/tokens/bearer` with body `grant_type=refresh_token&refresh_token=YOUR_INITIAL_REFRESH_TOKEN` and header `Authorization: Basic BASE64(CLIENT_ID:CLIENT_SECRET)`. - Step 2 (Set Variable): Set `access_token = {{ step1.response.access_token }}` and `refresh_token = {{ step1.response.refresh_token }}`. In the Custom Auth 'Headers' section, add: `Authorization: Bearer {{ auth.access_token }}`. In the 'Refresh Token' section (triggered on 401 responses), configure the same token refresh steps as the Login section above — this is what enables automatic token rotation when the 1-hour access token expires. Store the Client ID and Client Secret as Retool Configuration Variables (Settings → Configuration Variables) marked as secret, and reference them as `{{ environment.variables.QB_CLIENT_ID }}` in the auth configuration. Click Save Changes.

1// Base64 encode CLIENT_ID:CLIENT_SECRET for Basic auth header
2// In Custom Auth HTTP request step, set Authorization header to:
3// Basic {{ btoa(environment.variables.QB_CLIENT_ID + ':' + environment.variables.QB_CLIENT_SECRET) }}
4//
5// Token request body (form-encoded):
6// grant_type=refresh_token&refresh_token={{ auth.refresh_token || environment.variables.QB_INITIAL_REFRESH_TOKEN }}

QuickBooks Online's REST API v3 uses two query patterns: direct entity endpoints for individual records, and a SQL-like Query endpoint (IDS Query Language) for filtering and listing entities. Create your first query: click + New query in the Code panel, select the QuickBooks Resource, set Method to GET, and set the Path to: `/v3/company/{{ environment.variables.QB_REALM_ID }}/query?query=SELECT * FROM Invoice WHERE Balance > 0 ORDER BY DueDate ASC MAXRESULTS 100&minorversion=65` Store the realm ID as a Configuration Variable 'QB_REALM_ID' so it can be referenced consistently across all QuickBooks queries. Name this query 'getOpenInvoices'. The response returns an array of Invoice objects under `response.QueryResponse.Invoice`. Common QuickBooks IDS Query examples: - Open invoices: `SELECT * FROM Invoice WHERE Balance > 0 ORDER BY DueDate ASC MAXRESULTS 100` - Customers: `SELECT * FROM Customer WHERE Active = true MAXRESULTS 100` - Vendor bills unpaid: `SELECT * FROM Bill WHERE Balance > 0 ORDER BY DueDate ASC MAXRESULTS 100` - Chart of accounts: `SELECT * FROM Account WHERE AccountType = 'Accounts Receivable' MAXRESULTS 100` - Payments received: `SELECT * FROM Payment WHERE TxnDate >= '{{ dateFilter.startDate }}' MAXRESULTS 100` Add a JavaScript transformer to the getOpenInvoices query to flatten the nested response structure for display in a Table.

1// Transformer: flatten QuickBooks Invoice response for Table display
2// Raw response path: data.QueryResponse.Invoice
3const invoices = data?.QueryResponse?.Invoice || [];
4
5return invoices.map(inv => ({
6  id: inv.Id,
7  doc_number: inv.DocNumber || 'N/A',
8  customer: inv.CustomerRef?.name || 'Unknown',
9  customer_id: inv.CustomerRef?.value,
10  txn_date: inv.TxnDate,
11  due_date: inv.DueDate,
12  total: parseFloat(inv.TotalAmt || 0).toFixed(2),
13  balance: parseFloat(inv.Balance || 0).toFixed(2),
14  days_overdue: inv.DueDate
15    ? Math.max(0, Math.floor((Date.now() - new Date(inv.DueDate)) / 86400000))
16    : 0,
17  status: parseFloat(inv.Balance) <= 0 ? 'Paid' : new Date(inv.DueDate) < new Date() ? 'Overdue' : 'Open',
18  currency: inv.CurrencyRef?.value || 'USD',
19  link: inv.InvoiceLink || ''
20}));

With queries returning data, build the dashboard UI in Retool's canvas view. Drag a Table component onto the canvas and name it 'invoiceTable'. Set its Data to `{{ getOpenInvoices.data }}`. Configure the following columns: - doc_number: rename to 'Invoice #' - customer: rename to 'Customer' - txn_date: rename to 'Invoice Date' - due_date: rename to 'Due Date' - total: rename to 'Total', format as currency - balance: rename to 'Balance', format as currency - days_overdue: rename to 'Days Overdue' - status: rename to 'Status', add tag coloring: green for 'Paid', orange for 'Open', red for 'Overdue' Add filter controls above the table: - A Text Input 'customerFilter' for customer name search - A Date Range Picker 'dateFilter' for transaction date filtering - A Select component 'statusFilter' with options: All, Open, Overdue, Paid Update the getOpenInvoices query to use these filters in the IDS query: ``` SELECT * FROM Invoice WHERE CustomerRef.name LIKE '%{{ customerFilter.value }}%' AND Balance > 0 MAXRESULTS 100 ``` Add a summary stats bar at the top with Text components showing: - Total open balance: `${{ getOpenInvoices.data.reduce((sum, inv) => sum + parseFloat(inv.balance), 0).toFixed(2) }}` - Number of overdue invoices: `{{ getOpenInvoices.data.filter(inv => inv.status === 'Overdue').length }}` - Oldest overdue: `{{ Math.max(...getOpenInvoices.data.map(inv => inv.days_overdue)) }} days` Add a detail panel on the right side: a Container that appears when an invoice row is selected, showing line item details from a second query 'getInvoiceDetail' bound to `{{ invoiceTable.selectedRow.id }}`.

1// Query: get single invoice detail with line items
2// Path: /v3/company/{{ environment.variables.QB_REALM_ID }}/invoice/{{ invoiceTable.selectedRow.id }}?minorversion=65
3// Method: GET
4
5// Transformer for line items:
6const invoice = data?.Invoice;
7if (!invoice) return [];
8
9const lines = invoice.Line || [];
10return lines
11  .filter(line => line.DetailType === 'SalesItemLineDetail')
12  .map(line => ({
13    description: line.Description || line.SalesItemLineDetail?.ItemRef?.name || 'N/A',
14    quantity: line.SalesItemLineDetail?.Qty || 1,
15    unit_price: parseFloat(line.SalesItemLineDetail?.UnitPrice || 0).toFixed(2),
16    amount: parseFloat(line.Amount || 0).toFixed(2)
17  }));

Beyond read operations, Retool can write to QuickBooks via POST and PUT requests. Common write operations include creating new invoices, updating payment status, and voiding transactions. Create a 'Record Payment' query: Method POST, Path `/v3/company/{{ environment.variables.QB_REALM_ID }}/payment?minorversion=65`, Body (JSON): ```json { "TotalAmt": {{ parseFloat(paymentAmountInput.value) }}, "CustomerRef": { "value": "{{ invoiceTable.selectedRow.customer_id }}" }, "Line": [{ "Amount": {{ parseFloat(paymentAmountInput.value) }}, "LinkedTxn": [{ "TxnId": "{{ invoiceTable.selectedRow.id }}", "TxnType": "Invoice" }] }] } ``` Add a payment form below the invoice table: amount input, payment date picker, payment method Select, and a 'Record Payment' button wired to this query with a confirmation modal. For cross-system data joining, combine QuickBooks customer data with your CRM: create a second query ('getCRMCustomers') against your PostgreSQL or Salesforce resource. In a JavaScript transformer, join the datasets on email address: ```javascript const qbCustomers = getQBCustomers.data?.QueryResponse?.Customer || []; const crmCustomers = getCRMCustomers.data || []; const crmByEmail = {}; crmCustomers.forEach(c => { crmByEmail[c.email?.toLowerCase()] = c; }); return qbCustomers.map(qb => ({ ...qb, crm_account: crmByEmail[qb.PrimaryEmailAddr?.Address?.toLowerCase()] || null, in_crm: !!crmByEmail[qb.PrimaryEmailAddr?.Address?.toLowerCase()] })); ``` For complex QuickBooks integrations involving journal entries, multi-currency transactions, custom field management, and advanced reporting across multiple QuickBooks companies, RapidDev's team can help design and build the full Retool solution.

1// JavaScript transformer: join QuickBooks customers with CRM data
2// Assumes getQBCustomers and getCRMCustomers queries have run
3const qbCustomers = getQBCustomers.data?.QueryResponse?.Customer || [];
4const crmData = getCRMCustomers.data || [];
5
6// Build lookup map from CRM by email
7const crmByEmail = {};
8crmData.forEach(c => {
9  if (c.email) crmByEmail[c.email.toLowerCase()] = c;
10});
11
12return qbCustomers.map(qb => {
13  const email = qb.PrimaryEmailAddr?.Address?.toLowerCase() || '';
14  const crm = crmByEmail[email] || null;
15  return {
16    qb_id: qb.Id,
17    name: qb.DisplayName || qb.FullyQualifiedName,
18    email: email,
19    phone: qb.PrimaryPhone?.FreeFormNumber || '',
20    balance: parseFloat(qb.Balance || 0).toFixed(2),
21    // CRM-enriched fields
22    in_crm: !!crm,
23    crm_owner: crm?.owner_name || 'Not in CRM',
24    crm_stage: crm?.pipeline_stage || 'N/A',
25    crm_arr: crm?.annual_revenue ? `$${parseFloat(crm.annual_revenue).toLocaleString()}` : 'N/A'
26  };
27});