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:
The company name: in the below example this is "Demo Acme"
The start date: in the below example, this is "1/23/2021"
The end date: in the below example, this is "4/5/2021"
Columns to include: In this argument you specify which columns to include, for instance "Date, Name, Account, Amount"
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 otherYou can set the last two arguments
include_info_rowsandinclude_headersas 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)
}