Bkper Functions for Google Sheets

Bkper Functions fetch data from your Bkper Books directly into Google Sheets. The functions stay connected to your books, so any updates in Bkper are automatically reflected in your spreadsheet.

If you are new to Bkper or not very familiar with Google Sheet functions, the Bkper Add-on sidebar wizard can create the formulas for you.

Function Categories

Bkper offers two types of functions:

Balance Functions - Fetch aggregated balance values for reporting:

BKPER_BALANCES_TOTAL
BKPER_BALANCES_PERIOD
BKPER_BALANCES_CUMULATIVE
BKPER_BALANCES_TRIAL

Data Functions - Fetch lists of records with full details:

BKPER_ACCOUNTS
BKPER_GROUPS
BKPER_TRANSACTIONS

Balance Functions

These functions return balance values and are ideal for building financial statements.

Function	Purpose	Syntax
Total Balances	Total balance for a period	`=BKPER_BALANCES_TOTAL(bookId, cache, query, expanded, transposed, hideNames)`
Periodic Balances	Balance per period (monthly, yearly)	`=BKPER_BALANCES_PERIOD(bookId, cache, query, expanded, transposed, hideDates)`
Cumulative Balances	Running balance over time	`=BKPER_BALANCES_CUMULATIVE(bookId, cache, query, expanded, transposed, hideDates)`
Trial Balance	Debit and credit columns	`=BKPER_BALANCES_TRIAL(bookId, cache, query, expanded, transposed, hideNames)`

Data Functions

These functions return complete record listings. All available data is included automatically - IDs, groups, and custom properties are always fetched, making the functions simpler to use.

Function	Purpose	Syntax
Accounts	List of accounts	`=BKPER_ACCOUNTS(bookId, cache, group)`
Groups	List of groups	`=BKPER_GROUPS(bookId, cache)`
Transactions	List of transactions	`=BKPER_TRANSACTIONS(bookId, cache, query)`

What each Data Function returns

BKPER_ACCOUNTS returns: Account Id, Name, Type, Groups, and custom properties. Use the optional group parameter to filter accounts by a specific group.

BKPER_GROUPS returns: Group Id, Name, Type, Parent, Children count, Accounts count, and custom properties.

BKPER_TRANSACTIONS returns: Transaction Id, Status, Date, Origin, Destination, Description, Amount, Recorded at, custom properties, Remote Ids, and Attachments. When filtering by a permanent account, a Balance column is also included.

Common Parameters

All Bkper functions share two common parameters:

bookId - The unique identifier for your Bkper Book. You can find this in the book URL or copy it from the Bkper Add-on sidebar.
cache - A number used to control caching. Increase this value to force a fresh data fetch from Bkper.

How to use Bkper Functions

Install the Bkper Add-on for Google Sheets, open a spreadsheet, and type an equal sign (=) followed by the function name. Google Sheets will suggest available Bkper functions as you type.

Reorder results

Use the Google Sheets QUERY function to reorder Bkper function results. For example, given a Bkper function result in the range A2:B5:

=QUERY(A2:B5, "Select A, B order by A desc")

asc = A to Z (ascending)
desc = Z to A (descending)

Working Examples

Our template gallery contains working examples of Bkper Books with corresponding Financial Statements in Google Sheets.

In the Financial Statements that accompany the Simple General Ledger Template, cells with Bkper formulas are marked grey with a note indicating Bkper Formula.

Video Instructions

6 minute video explaining Bkper Functions

Limitations

Bkper Functions use Google Apps Script, which has a 30-second runtime limit per function call. This means you cannot fetch unlimited amounts of data in a single function.

If you hit this limit, consider:

Using balance functions instead of fetching all transactions
Narrowing your time range (e.g., after:$m-12 before:$m-6)
Using the CSV Export App for large data exports

See the full Google Apps Script quotas for more details.

Troubleshooting

For issues with Bkper Functions, see this article.

Note: Make sure your Book and Google Sheet use the same timezone. Different timezones can cause date discrepancies (d-1 or d+1) between the Sheet and Book.