How to Consolidate Multiple QuickBooks Companies in One Google Sheet

Consolidating multiple QuickBooks Online companies into a single Google Sheet is not a built-in QuickBooks feature. You have to build this yourself, but once it is set up correctly, you get a live, consolidated view of every entity without touching QuickBooks again.

This guide covers every method, ranked from most manual to fully automated, plus the specific patterns (portal columns, chart-of-accounts mapping, inter-company eliminations) that make consolidated reporting actually useful.

Why Multi-Entity QuickBooks Consolidation Is Hard

QuickBooks Online treats each company file as a fully isolated environment. There is no native consolidation report, no cross-company dashboard, and no way to query data from multiple files in a single session. Each entity has its own login, its own chart of accounts, and its own API credentials.

This creates a compounding problem for accounting firms and multi-entity businesses:

• Multiple logins. You must authenticate separately for each company file, which means separate OAuth tokens, separate API connections, and separate refresh cycles.
• Inconsistent charts of accounts. One entity might call it "Consulting Revenue" while another uses "Professional Services Income." These are the same account in economic terms but will never merge cleanly without explicit mapping.
• Inter-company transactions. If Entity A loans money to Entity B, that balance appears as an asset on one side and a liability on the other. A naive consolidation double-counts it. Correct consolidation eliminates it.
• Volume. Firms with 10, 20, or 50 clients face this problem at a scale where any manual step compounds into hours of work every month.

None of this is unsolvable. But every solution requires you to make deliberate choices about how you handle the three problems above.

Method 1: Manual Export and Concatenate

How it works: Log into each QuickBooks company, export a report as CSV, import the CSV into Google Sheets, add a column identifying the entity, and then use a consolidation tab with QUERY or pivot table logic to aggregate across entities.

Steps:

1. In each QBO company, go to Reports > Profit and Loss (or whichever report you need) and set the date range.
2. Export as CSV.
3. In Google Sheets, paste each CSV into its own tab. Name the tabs by entity (e.g., raw_entity_a, raw_entity_b).
4. Add a column in each raw tab called Entity and hard-code the entity name.
5. In a master tab, use =QUERY({raw_entity_a!A:Z; raw_entity_b!A:Z}, "SELECT * WHERE Col1 <> 'Account'", 1) to stack the data.
6. Build a pivot table on the master tab to aggregate by account and entity.

Time cost: 15–45 minutes per consolidation cycle, depending on entity count. For a firm with 20 clients running monthly closes, this is 60–180 hours per year on a single task.

When to use it: One-time consolidation for a due diligence project. Not suitable for ongoing reporting.

Method 2: G-Accon Multi-Entity Feature

G-Accon is a Google Sheets add-on that connects directly to QuickBooks Online via the QBO API. Its multi-entity feature lets you authenticate against multiple company files and pull reports from each within the same Google Sheet.

How it works:

1. Install G-Accon from the Google Workspace Marketplace.
2. Authenticate each QBO company file individually under Settings > Manage Connections.
3. In a sheet tab, use the G-Accon sidebar to configure a report pull, selecting the target company and report type.
4. Repeat for each entity, directing results to separate tabs.
5. Build a consolidation layer on top using QUERY or ARRAYFORMULA.

Strengths:

• Handles authentication complexity for you.
• Supports scheduled refreshes (daily or on-demand).
• Covers most standard QBO reports.

Limitations:

• The add-on UI becomes cumbersome at 10+ entities. You are configuring each pull individually.
• No built-in consolidation logic. You still build the aggregation layer manually.
• Pricing scales with the number of connected companies.

Method 3: Coefficient

Coefficient is a no-code data connector that works inside Google Sheets and supports QuickBooks Online among dozens of other sources.

How it works: Similar to G-Accon: authenticate each QBO file, configure pulls for each entity, schedule refreshes. Coefficient's UI is generally cleaner for managing many connections and it has a stronger LOOKUP-style data model that makes cross-tab joins slightly easier.

Strengths:

• Polished interface for managing multiple connections.
• Snapshot history (keeps prior pulls so you can compare period-over-period).
• Better for teams where multiple people need to refresh or reconfigure pulls.

Limitations:

• No native inter-company elimination logic.
• Still requires you to build the consolidation layer in Sheets.
• Higher price point than G-Accon.

Method 4: brooked.io

brooked.io is a data connector for Google Sheets focused on live database and API connections. For QuickBooks consolidation, it operates differently from G-Accon and Coefficient: rather than pulling pre-built QBO reports, it exposes the underlying QuickBooks data as queryable tables, which you query directly from Google Sheets using SQL-style syntax.

How it works:

1. Connect each QBO company file to brooked.io under your account's connections panel.
2. In Google Sheets, install the brooked.io add-on.
3. Write a query that joins data across company files: SELECT entity, account, SUM(amount) FROM transactions WHERE entity IN ('entity_a', 'entity_b') GROUP BY entity, account.
4. Schedule the query to refresh on a set interval.

Strengths:

• Single query can span multiple entities, no manual tab-stacking needed.
• SQL interface gives you full control over aggregation, filtering, and joining.
• Fixed egress IPs for IT/security compliance (relevant if your firm has firewall rules).
• Refresh scheduling with change alerts.

Limitations:

• Requires comfort with SQL-style queries.
• Initial connection setup takes longer than report-based tools.

The Portal Column Pattern

Regardless of which method you use, consolidated data needs a reliable way to distinguish which row belongs to which entity. The portal column pattern is the standard approach.

Definition: Add a column called Entity (or Portal, Company, File) to every raw data tab. Populate it with a consistent, human-readable identifier for each company file.

Why it matters for formulas: When you stack data from multiple tabs using QUERY or ARRAYFORMULA, this column becomes the primary dimension for slicing. Without it, you cannot tell whether a $50,000 revenue figure belongs to Entity A or Entity B.

Why it matters for pivot tables: The Entity column becomes a row or column dimension in your pivot, letting viewers drill into individual companies or aggregate across all.

Naming convention recommendation: Use short, stable identifiers: abbreviations or codes rather than full legal names. Legal names change (acquisitions, rebranding). Codes do not. Example: ENT-A, ENT-B, ENT-C.

Chart of Accounts Mapping

When different entities use different account names for economically equivalent accounts, raw consolidation produces misleading results. The solution is an explicit mapping table.

Structure of a mapping table:

How to apply it in Sheets: Use VLOOKUP or INDEX/MATCH on the raw account name to return the consolidated account name before aggregating. This should happen in an intermediate transformation tab, not in the raw data tab.

Maintenance: The mapping table needs to be updated whenever a new account is added to any entity's chart of accounts. Build an alert or a formula that flags unmapped accounts (any row where the VLOOKUP returns #N/A).

Inter-Company Eliminations

Inter-company eliminations are the accounting step that prevents double-counting when one entity within a consolidated group transacts with another.

Common scenarios:

• Intercompany loans: Entity A lends $100,000 to Entity B. Entity A has a $100,000 asset (receivable). Entity B has a $100,000 liability (payable). In consolidated financials, both net to zero.
• Intercompany sales: Entity A sells services to Entity B for $50,000. From a consolidated perspective, this is an internal transfer, not revenue.
• Management fees: A holding company charges management fees to subsidiaries. The fee is revenue at the holding level and an expense at the subsidiary level: both cancel in consolidation.

How to handle eliminations in Google Sheets:

1. Maintain a separate Eliminations tab with entries for each intercompany transaction: entity pair, account, amount, and direction (debit/credit).
2. In your consolidation query or pivot, subtract elimination amounts from the relevant accounts.
3. Reconcile eliminations every close period. The receivable at Entity A must equal the payable at Entity B.

This is the most error-prone part of manual consolidation. If you are managing more than two or three intercompany relationships, consider whether a purpose-built consolidation tool is more appropriate than a Sheets-based approach.

Method Comparison Table

Troubleshooting

Duplicate rows after stacking entity tabs Cause: The date range or report settings differ between entities, pulling the same transaction twice. Solution: Standardize date range inputs in a single cell reference and pass that reference to every pull configuration.

Account names not matching after mapping Cause: Trailing spaces or inconsistent capitalization in raw QBO data. Solution: Wrap raw account names in TRIM(UPPER()) before the VLOOKUP.

OAuth tokens expiring for one entity but not others Cause: Each QBO company file has its own OAuth session. If one file goes unused, its token expires independently. Solution: Set up all entities to refresh on the same schedule, even if some are not actively queried.

Eliminations not zeroing out Cause: One entity recorded the transaction in a different period than the other. Solution: Match transactions by reference number, not by period, and flag any reference number that appears in only one entity.

Pivot table not updating after refresh Cause: Google Sheets pivot tables do not auto-update when source data changes. Solution: Add a refresh trigger via Apps Script, or manually right-click the pivot and select Refresh.

Bottom Line

Consolidating multiple QuickBooks companies in Google Sheets is achievable, but the quality of the result depends on three decisions you make upfront: how you identify entities (portal column), how you normalize accounts (mapping table), and how you handle intercompany transactions (eliminations). Get those three right and the tool you use to pull the data matters less.

For firms with fewer than five entities and a monthly close cycle, G-Accon or a careful manual process is workable. For larger entity counts or tighter reporting timelines, brooked.io's SQL-based multi-entity queries reduce the per-cycle work to near zero once the initial setup is done.

Get Started with brooked.io

brooked.io connects to all your QuickBooks Online companies and lets you query across them in a single Google Sheet. No tab-stacking, no manual exports.

Connect your QuickBooks companies to brooked.io and run your first consolidated report in under 30 minutes.

Related Articles

• Stop Being a Human API: How to Automate Data Entry Into Google Sheets From Any Tool
• Why Your IT Team Won't Whitelist Google Apps Script IPs, and How to Connect Your Database Without Opening the Firewall

Troubleshooting quick reference

Frequently asked questions