Bkper Functions are used to fetch data from Bkper Books. The functions stay connected to Bkper Books so updates to your books are reflected in the data fetched into your Google Sheet.
If you are new to Bkper or you are not very familiar with Google Sheet Functions you might want to fetch your first data with the Bkper Add-on side bar wizard that creates the Bkper Formulas for you.
List of all the Bkper functions:
BKPER_BALANCES_TOTAL
BKPER_BALANCES_PERIOD
BKPER_BALANCES_CUMULATIVE
BKPER_BALANCES_TRIAL
BKPER_ACCOUNTS
BKPER_GROUPS
BKPER_TRANSACTIONS
| Name | Syntax |
BKPER_BALANCES_TOTAL | =BKPER_BALANCES_TOTAL(bookId, cache, query, expanded, transposed, hideNames) | |
BKPER_BALANCES_PERIOD | =BKPER_BALANCES_PERIOD(bookId, cache, query, expanded, transposed, hideDates) | |
BKPER_BALANCES_CUMULATIVE | =BKPER_BALANCES_CUMULATIVE(bookId, cache, query, expanded, transposed, hideDates) | |
BKPER_BALANCES_TRIAL | =BKPER_BALANCES_TRIAL(bookId, cache, query, expanded, transposed, hideNames) | |
BKPER_ACCOUNTS | =BKPER_ACCOUNTS(bookId, cache, groups, properties) | |
BKPER_GROUPS | =BKPER_GROUPS(bookId, cache, properties) | |
BKPER_TRANSACTIONS | =BKPER_TRANSACTIONS(bookId, cache, query, properties, ids) |
How to use Bkper Functions
Install Bkper add-on for Google Sheets.
Open a spreadsheet.
Type an equal sign (=) in a cell and type in the function you want to use.
Note: You may see suggested formulas and ranges based on your data.
Reorder results
Use the Google Sheets Query function with "order by" to reorder Bkper function resultsets.
For example, given a Bkper Function on cell A1, with a resultset on the data range A2:B5, on a different cell, you can reorder it like this:
=QUERY(A2:B5, "Select A, B order by A desc")
Ordering in the query function
asc = A -> Z
desc = Z -> A
A working Book and Financial Statement as Example
On our template gallery you can copy working examples of Bkper Books and their corresponding Financial Statements in Google Sheets.
Cells with Bkper formulas are marked grey with a note indicating Bkper Formula on the Financial Statements that comes with the Simple General Ledger Template,
The dynamically constructed Queries are marked with a note indicating the cell where it is used in a Bkper formula.
Video Instructions
Limitations
Since Bkper Functions are developed with Google Apps Script it is subject to its limitations, which you can find here in more detail. For Bkper functions this translates into a custom function runtime limit of 30 seconds, meaning that you cannot fetch endless amounts of data to your Google Sheets.
In case you cannot fetch the data you need within this limit (such as all your historical transactions) you might want to review your process and see if you can work with balance values only, you can work with smaller time ranges (after: $m-12 before: $m-6) and if you really need to export that much data (back up) you can recur to the export CSV APP.
Troubleshooting
If you have any issues editing the Bkper Functions, please head to this article.
Note: Ensure that the timezones of your Book and Google Sheet are the same. If they differ, date discrepancies (such as d-1 or d+1) may appear between the Sheet and the Book.