All Collections
Google Sheets & Excel Formulas
How to import large amounts of QuickBooks transactions to Google Sheets with Live Functions
How to import large amounts of QuickBooks transactions to Google Sheets with Live Functions

Learn how to import tens of thousands of transactions from QuickBooks into Google Sheets or Excel with LiveFlow

Lasse Kalkar avatar
Written by Lasse Kalkar
Updated over a week ago

Here's how to import tens of thousands of transactions from QuickBooks into G-Sheets or Excel with LiveFlow.

Live Functions in LiveFlow

What are Live Functions all about?

Live Functions are custom-built Google Sheets functions, managed by LiveFlow, that give your spreadsheet superpowers. You can use Live Functions just like you use any other functions in your Google Sheets formulas, but, while most functions just let you manipulate data already in your sheet, Live Functions allow you to load live data (tons of it!) right from QuickBooks, in just a few keystrokes.

Which Live Functions can I use today?

=LF_QUICKBOOKS_TRANSACTIONS(company_name, start_date, end_date, columns_to_include, refresh_on_change)

This function does exactly what it says on the tin: it allows you to load all of a company’s transactions within a given date range.

Under the hood, it uses the Transaction Detail report (without any grouping), so you can expect to see the transactions ordered by date.

=LF_QUICKBOOKS_TRANSACTIONS_WITH_SPLITS(company_name, start_date, end_date, columns_to_include, refresh_on_change))

This function works in the same way as LF_QUICKBOOKS_TRANSACTIONS, but uses the Transaction List with Splits report (without any grouping) under the hood, so you can expect to see the transactions ordered by date.

=LF_QUICKBOOKS_GENERAL_LEDGER_TRANSACTIONS(company_name, start_date, end_date, columns_to_include, refresh_on_change)

This function works in the same way as LF_QUICKBOOKS_TRANSACTIONS, but uses the General Ledger report under the hood.

The General Ledger report is grouped by account, so the results will be ordered by account, and then by date. We leave out the group headers and subtotals though, so you’ll just get the transactions.

How do I use these Live Functions?

💡 NOTE: In order to use these Live Functions with your chosen company in a given spreadsheet, you must first import a report for that company in the same spreadsheet.

The function arguments include:

  1. The company name: in the below example this is "Demo Acme"

  2. The start date: in the below example, this is "1/23/2021"

  3. The end date: in the below example, this is "4/5/2021"

  4. Columns to include: In this argument you specify which columns to include, for instance "Date, Name, Account, Amount"

  5. Refresh on change: This argument determines when to refresh your data. We recommend setting this to TODAY(). That way your data will automatically be updated daily.

Understanding the Function Arguments

  • Argument: company_name [required]

    • Description: the name of the company for which you wish to list the transactions

      • The value does not need to exact match the name of the company, so "Demo Acme" will find the company named Demo Acme Inc. in LiveFlow's workspace.

      • In order to list transactions for your chosen company in a given spreadsheet, you must first import a report for that company in the same spreadsheet

  • Argument: start_date [required]

    • Description: The start of the date range for which to list transactions

  • Argument: end_date [required]

    • Description: The end of the date range for which to list transactions

  • Argument: columns [recommended]

    • Description: A comma-separated list of the columns you wish to include for the list of transactions

    • If you omit the argument, or set it to "", the function will return whatever columns are supported by default in the underlying report [not recommended]

    • We recommend that you select as few columns as you can get away with, since that will make responses faster, and thus less likely to time out

    • The order of the list does not affect the order of the columns returned from the function

  • Argument: refresh_key [recommended]

    • Description: This value allows you to control when the data is refreshed. Simply put: whenever this value changes, the list will be automatically refreshed

    • If you omit the argument, or set it as "", Google Sheets will decide when to refresh the transaction list [not recommended]

    • If you set the value to TODAY(), that value will change every day, so the function will refresh the transaction list at least once per day

    • If you point a reference to the Last Updated value in another report, then list will refresh whenever that report is refreshed. So, if the report refreshes every hour, then so too will the transaction list

  • Argument: include_info [optional]

    • Description: In most cases, you can ignore this option

    • If the value is FALSE or 0, then the info rows (the Workspace, Company, and Last updates rows) above the transaction list will be omitted

    • Otherwise the info rows will be included

  • Argument: include_headers [optional]

    • Description: In most cases, you can ignore this option

    • If the value is FALSE or 0, then the column headers row above the transaction list will be omitted

    • Otherwise, the column headers will be included

Known Limitations

Google Sheets limits the execution time of custom functions

  • Google Sheets custom functions must do their work within 30 seconds, or else the function will return an error

  • If the request to the QuickBooks API takes too long, then the Live Function won't be able to load any of your transactions

  • Workaround: Try selecting fewer columns, or try a shorter date range

.

QuickBooks limits the number of cells returned for a given report

  • QuickBooks limits the total number of cells returned for any report to 400,000 cells

  • If you need to select 10 columns when listing your transactions then you'll only be able to load 40,000 rows, but if you select 4 columns, then you'll be able to load 100,000 rows

  • If the transaction list for your selected date range has that many entries, it is quite likely to take longer than 30 seconds

  • Workaround: Try selecting fewer columns, or try a shorter date range.

How to handle Really Large reports

  • If you need to load really large lists of transactions then it is possible you will encounter one of the above issues due to the limitations of these functions

  • Workaround: Chunk the transactions by year, quarter or month, and rejoin the chunks using Google Sheets syntax { ... ; ... } to stack ranges on on top of the other

    • You can set the last two arguments include_info_rows and include_headers as false to prevent adding noise when combining the list chunks

={

LF_QUICKBOOKS_TRANSACTIONS("Demo", "2022-01-01", "2022-03-31", "<columns>", "<refresh-key>");

LF_QUICKBOOKS_TRANSACTIONS("Demo", "2022-04-01", "2022-06-30", "<columns>", "<refresh-key>", false, false);

LF_QUICKBOOKS_TRANSACTIONS("Demo", "2022-07-01", "2022-09-30", "<columns>", "<refresh-key>", false, false);

LF_QUICKBOOKS_TRANSACTIONS("Demo", "2022-10-01", "2022-12-31", "<columns>", "<refresh-key>", false, false)

}

Try Live Functions with our template

Did this answer your question?