How to Get QuickBooks Data into Google Sheets Using Apps Script

QuickBooks is a powerful accounting platform used by businesses of all sizes. But while QuickBooks excels at accounting, Google Sheets is better for custom analysis, dashboards, and data sharing. If you're looking to bring your financial data from QuickBooks into Google Sheets automatically, Google Apps Script is a flexible and code-friendly solution.

This guide walks you through how to connect QuickBooks Online with Google Sheets using QuickBooks API and Google Apps Script, so you can pull in invoices, transactions, customers, and more—right into your spreadsheet.

Why Integrate QuickBooks with Google Sheets?

Here’s what you can do with this integration:

• Automate your monthly or weekly reporting

• Build a real-time financial dashboard in Sheets

• Perform custom data analysis or visualizations

• Export customer, vendor, or sales data easily

• Share financial summaries with teams or stakeholders

Prerequisites

Before you can start writing code, make sure you have the following:

• A QuickBooks Online account (not QuickBooks Desktop)

• A QuickBooks developer account at https://developer.intuit.com

• A QuickBooks app created in your developer dashboard

• Your app’s:

  • Client ID

  • Client Secret

  • Access Token (and optionally, Refresh Token)

  • Realm ID (your company ID)

• A Google Spreadsheet

• Some basic knowledge of JavaScript and Google Apps Script

Step 1: Set Up a QuickBooks App and Get API Credentials

1. Go to https://developer.intuit.com and sign in.

2. Create a new app (select QuickBooks Online + Payments).

3. Navigate to Keys & OAuth.

4. Copy the Client ID and Client Secret.

5. Under Redirect URI, add:

   https://developers.google.com/oauthplayground
   

6. Use the OAuth 2.0 Playground to generate an access token and refresh token.

7. Note down your Realm ID from your QuickBooks company profile or API console.

Step 2: Open Google Apps Script

1. Open your target Google Sheet.

2. Go to Extensions > Apps Script.

3. Delete the default code and paste the following template (we’ll explain each part below).

Step 3: Sample Apps Script Code to Pull Data

This code fetches a list of customers from QuickBooks and writes them into your spreadsheet.

const ACCESS_TOKEN = 'YOUR_ACCESS_TOKEN';
const REALM_ID = 'YOUR_REALM_ID';

function fetchQuickBooksCustomers() {
  const endpoint = `https://quickbooks.api.intuit.com/v3/company/${REALM_ID}/query`;
  const query = 'SELECT * FROM Customer';
  const url = `${endpoint}?query=${encodeURIComponent(query)}&minorversion=65`;

  const options = {
    method: 'get',
    headers: {
      'Authorization': 'Bearer ' + ACCESS_TOKEN,
      'Accept': 'application/json',
      'Content-Type': 'application/text'
    },
    muteHttpExceptions: true
  };

  const response = UrlFetchApp.fetch(url, options);
  const json = JSON.parse(response.getContentText());

  if (!json.QueryResponse || !json.QueryResponse.Customer) {
    Logger.log("No customers found or error: " + JSON.stringify(json));
    return;
  }

  const customers = json.QueryResponse.Customer;
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clear();
  sheet.appendRow(['ID', 'Display Name', 'Primary Email', 'Phone']);

  customers.forEach(customer => {
    sheet.appendRow([
      customer.Id,
      customer.DisplayName,
      customer.PrimaryEmailAddr ? customer.PrimaryEmailAddr.Address : '',
      customer.PrimaryPhone ? customer.PrimaryPhone.FreeFormNumber : ''
    ]);
  });
}

Step 4: Run the Script

1. Click the disk icon to save.

2. Click the run icon ▶️ next to fetchQuickBooksCustomers().

3. Authorize the script when prompted.

4. Your Google Sheet will populate with customer data from QuickBooks!

Other Available Queries

You can replace the query string with any supported QBO SQL-like syntax:

SELECT * FROM Invoice
SELECT * FROM Vendor
SELECT * FROM Purchase
SELECT Id, TotalAmt FROM Invoice WHERE Balance > 0

Simply replace the line:

const query = 'SELECT * FROM Customer';

with another object type you want to retrieve.

Step 5: Refresh Tokens (Optional but Recommended)

QuickBooks access tokens typically expire after 1 hour. You’ll need to refresh them using your refresh token.

Here’s a sample function to refresh:

function refreshAccessToken() {
  const clientId = 'YOUR_CLIENT_ID';
  const clientSecret = 'YOUR_CLIENT_SECRET';
  const refreshToken = 'YOUR_REFRESH_TOKEN';

  const credentials = Utilities.base64Encode(`${clientId}:${clientSecret}`);
  const url = 'https://oauth.platform.intuit.com/oauth2/v1/tokens/bearer';

  const options = {
    method: 'post',
    headers: {
      'Authorization': 'Basic ' + credentials,
      'Content-Type': 'application/x-www-form-urlencoded'
    },
    payload: 'grant_type=refresh_token&refresh_token=' + refreshToken
  };

  const response = UrlFetchApp.fetch(url, options);
  const token = JSON.parse(response.getContentText());
  Logger.log(token);
}

You’ll then update your ACCESS_TOKEN with the new token.

Automating the Script

• To schedule automatic data pulls, go to Apps Script > Triggers.

• Set up a trigger to run fetchQuickBooksCustomers every hour, day, or week.

Best Practices

Conclusion

Integrating QuickBooks with Google Sheets using Apps Script gives you the power to build real-time accounting dashboards, automated financial reports, and live customer databases without needing expensive middleware.

By using QuickBooks' robust API and Google Apps Script, you control the data, automate tasks, and gain insights that live inside your spreadsheets.