📋 Get QuickBooks Data into Google Sheets with Apps Script

1. Overview

\[ \begin{array}{l} \textbf{You can pull QuickBooks accounting data into Google Sheets --} \\ \text{using Google Apps Script and the QuickBooks Online API.} \\ \textbf{This enables live financial dashboards, reconciliation tools --} \\ \text{and automated accounting reports in your spreadsheet.} \end{array} \]

2. Use Cases

            
                - Fetch invoices, expenses, accounts, or balances from QuickBooks
                - Automate client reporting or internal financial dashboards
                - Schedule daily/weekly syncs to keep Google Sheets updated
                - Integrate accounting data with analytics or forecasting models
            
        

This integration helps reduce manual exports, improves accuracy, and simplifies collaboration on financial data.

3. Steps to Set Up QuickBooks API Access

            
                1. Go to https://developer.intuit.com and create a developer account
                2. Create an app and select “QuickBooks Online” as the product
                3. In Keys & OAuth, get your Client ID and Client Secret
                4. Set Redirect URI to: https://script.google.com/macros/d/YOUR_SCRIPT_ID/usercallback
                5. Use OAuth 2.0 Authorization Code Flow to authenticate
            
        

You’ll use this app to securely connect your Google Apps Script to QuickBooks Online.

4. Google Apps Script: OAuth & Setup

            
                // Add OAuth2 library (project key: 1B...BD2)

                function getQBService() {
                  return OAuth2.createService('quickbooks')
                    .setAuthorizationBaseUrl('https://appcenter.intuit.com/connect/oauth2')
                    .setTokenUrl('https://oauth.platform.intuit.com/oauth2/v1/tokens/bearer')
                    .setClientId('YOUR_CLIENT_ID')
                    .setClientSecret('YOUR_CLIENT_SECRET')
                    .setCallbackFunction('authCallback')
                    .setPropertyStore(PropertiesService.getUserProperties())
                    .setScope('com.intuit.quickbooks.accounting')
                    .setTokenHeaders({ 'Accept': 'application/json' });
                }

                function authCallback(request) {
                  const service = getQBService();
                  const authorized = service.handleCallback(request);
                  return HtmlService.createHtmlOutput(authorized ? "Authorized!" : "Denied");
                }

                function authorize() {
                  const service = getQBService();
                  if (!service.hasAccess()) {
                    Logger.log(service.getAuthorizationUrl());
                  } else {
                    Logger.log("Already authorized.");
                  }
                }
            
        

This script initiates and manages OAuth2 authentication for QuickBooks Online.

5. Fetch Data: Example to Get Invoices

            
                function fetchInvoices() {
                  const service = getQBService();
                  if (!service.hasAccess()) {
                    Logger.log("Run authorize() first.");
                    return;
                  }

                  const realmId = 'YOUR_REALM_ID'; // Found in your QuickBooks app dashboard
                  const url = `https://quickbooks.api.intuit.com/v3/company/${realmId}/query?query=SELECT * FROM Invoice`;

                  const response = UrlFetchApp.fetch(url, {
                    headers: {
                      'Authorization': 'Bearer ' + service.getAccessToken(),
                      'Accept': 'application/json'
                    }
                  });

                  const json = JSON.parse(response.getContentText());
                  const invoices = json.QueryResponse.Invoice;

                  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
                  sheet.clearContents();
                  sheet.appendRow(['DocNumber', 'TxnDate', 'Customer', 'Amount']);

                  invoices.forEach(inv => {
                    sheet.appendRow([
                      inv.DocNumber,
                      inv.TxnDate,
                      inv.CustomerRef.name,
                      inv.TotalAmt
                    ]);
                  });
                }
            
        

This code fetches a list of invoices and displays basic info in your active sheet.

6. Other Query Examples

            
                // SELECT all customers:
                SELECT * FROM Customer

                // SELECT last 10 expenses:
                SELECT * FROM Purchase ORDERBY TxnDate DESC MAXRESULTS 10

                // SELECT balance sheet:
                SELECT * FROM Account WHERE AccountType = 'Asset'
            
        

These can be used with the same base URL by replacing the query parameter.

7. Conclusion

            
                With Google Apps Script and QuickBooks API, you can build custom financial dashboards, 
                automate accounting workflows, and centralize reporting — all in Google Sheets.
            
        

You can further automate this with time-based triggers or UI buttons inside Sheets.