Functions can be used to create formulas that manipulate data to create financial statements on Google Sheets with Bkper data.
Here's a list of all the functions available to fetch financial data from Bkper into Google Sheet, like transactions, accounts and balances.
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 |
Total Balances | BKPER_BALANCES_TOTAL | =BKPER_BALANCES_TOTAL(bookId, cache, query, expanded, transposed, hideNames) |
Period Balances | BKPER_BALANCES_PERIOD | =BKPER_BALANCES_PERIOD(bookId, cache, query, expanded, transposed, hideDates) |
Cumulative Balances | BKPER_BALANCES_CUMULATIVE | =BKPER_BALANCES_CUMULATIVE(bookId, cache, query, expanded, transposed, hideDates) |
Trial Balance | BKPER_BALANCES_TRIAL | =BKPER_BALANCES_TRIAL(bookId, cache, query, expanded, transposed, hideNames) |
Accounts | BKPER_ACCOUNTS | =BKPER_ACCOUNTS(bookId, cache, groups, properties) |
Groups | BKPER_GROUPS | =BKPER_GROUPS(bookId, cache, properties) |
Transactions | BKPER_TRANSACTIONS | =BKPER_TRANSACTIONS(bookId, cache, query, properties, ids) |
How to use it?
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.
BKPER_BALANCES_TOTAL
Fetch the total balances values from Accounts and Groups:
=BKPER_BALANCES_TOTAL(bookId, cache, query, expanded, transposed, hideNames)
Sample usage
=BKPER_BALANCES_TOTAL("agtzfmJrcGVyLWhyZHITCxIGTGVkZ2VyGICAgKD_4bMLDA", 1, “group:'Revenue' after:01/2016 before:01/2017”, TRUE, FALSE, FALSE)
Explaining the syntax:
bookId: string - The universal Book Id.
cache: number - Cache-control. Increase to clean cache and force fetch update.
query: string - The query.
expanded: TRUE, FALSE or number - Expand group tree. True to expand itself, -1 to expand all subgroups. -2 to expand all accounts. Any number to expand up to a specific level
transposed: TRUE or FALSE - Transpose the result
hideNames:TRUE or FALSE - Hide account/group names
Note:
- The result is ordered from the largest to the smallest amount.
BKPER_BALANCES_PERIOD
Fetch periodic balances from your Bkper book. Gives you the periodic account balance values related to a time range.
=BKPER_BALANCES_PERIOD(bookId, cache, query, expanded, transposed, hideDates)
Sample usage
=BKPER_BALANCES_PERIOD("agtzfmJrcGVyLWhyZHITCxIGTGVkZ2VyGICAgKD_4bMLDA", 1, “group:'Revenue' after:01/2016 before:01/2017”, TRUE, TRUE, FALSE)
Explaining the syntax:
bookId: string - The universal Book Id.
cache: number - Cache-control. Increase to clean cache and force fetch update.
query: string - The query.
expanded: TRUE, FALSE or number - Expand group tree. True to expand itself, -1 to expand all subgroups. -2 to expand all accounts. Any number to expand up to a specific level
transposed: TRUE or FALSE - Transpose the result
hideDates: TRUE or FALSE - Hide dates row/column
Note:
- The result is ordered by account names.
- If not referenced in the query the default period is monthly.
- The periodic balance values are fetched for the debits / credits in a range of time.
- Useful for P&L
BKPER_BALANCES_CUMULATIVE
Fetch cumulative account balances from your Bkper book. Gives you cumulative account balance value related to a time range.
=BKPER_BALANCES_CUMULATIVE(bookId, cache, query, expanded, transposed, hideDates)
Sample usage
=BKPER_BALANCES_CUMULATIVE("agtzfmJrcGVyLWhyZHITCxIGTGVkZ2VyGICAgKD_4bMLDA", 1, “group:'Assets' after:01/2016 before:01/2017”, TRUE, TRUE, FALSE)
Explaining the syntax:
bookId: string - The universal Book Id.
cache: number - Cache-control. Increase to clean cache and force fetch update.
query: string - The query.
expanded: TRUE, FALSE or number - Expand group tree. True to expand itself, -1 to expand all subgroups. -2 to expand all accounts. Any number to expand up to a specific level
transposed: TRUE or FALSE - Transpose the result
hideDates: TRUE or FALSE - Hide dates row/column
Note:
- The result is ordered by account names.
- With Asset and Liability account types, the balance value of the previous period is considered.
- With Incoming and Outgoing account types the balance value starts at 0 and accumulate over the fetched period.
- Useful for Balance Sheets
BKPER_BALANCES_TRIAL
Fetch the trial balances values from Accounts and Groups.
=BKPER_BALANCES_TRIAL(bookId, cache, query, expanded, transposed, hideNames)
Sample usage
=BKPER_BALANCES_TRIAL("agtzfmJrcGVyLWhyZHITCxIGTGVkZ2VyGICAgKD_4bMLDA", 1, “group:'Revenue' after:01/2016 before:01/2017”, TRUE, FALSE, FALSE)
Explaining the syntax:
bookId: string - The universal Book Id.
cache: number - Cache-control. Increase to clean cache and force fetch update.
query: string - The query.
expanded: TRUE, FALSE or number - Expand group tree. True to expand itself, -1 to expand all subgroups. -2 to expand all accounts. Any number to expand up to a specific level
transposed: TRUE or FALSE - Transpose the result
hideNames: TRUE or FALSE - Hide account/group name
BKPER_ACCOUNTS
Fetch the (Chart of) Accounts from your Bkper book. Gives you a listing of accounts and groups from your Bkper book:
=BKPER_ACCOUNTS(bookId, cache, groups, properties)
Sample usage
=BKPER_ACCOUNTS("agtzfmJrcGVyLWhyZHITCxIGTGVkZ2VyGICAgKD_4bMLDA", 1, TRUE, FALSE)
Explaining the syntax:
bookId: string - The universal Book Id.
cache: number - Cache-control. Increase to clean cache and force fetch update.
groups: TRUE or FALSE - True to include account groups.
properties: TRUE or FALSE - True to include custom account properties.
BKPER_GROUPS
Fetch the Groups from your Bkper book. Gives you a listing of groups (with parent groups, child groups and number of accounts) from your Bkper book:
=BKPER_GROUPS(bookId, cache, properties)
Sample usage
=BKPER_GROUPS("agtzfmJrcGVyLWhyZHITCxIGTGVkZ2VyGICAgKD_4bMLDA", 1, TRUE)
Explaining the syntax:
bookId: string - The universal Book Id.
cache: number - Cache-control. Increase to clean cache and force fetch update.
properties: TRUE or FALSE - True to include custom group properties.
BKPER_TRANSACTIONS
Fetch transactions from your Bkper book Gives you all the transactions according to the query provided:
BKPER_TRANSACTIONS(bookId, cache, query, properties, ids)
Sample usage
=BKPER_TRANSACTIONS("agtzfmJrcGVyLWhyZHITCxIGTGVkZ2VyGICAgKD_4bMLDA", 1, "acc:'Bank Account' after:01/2019", TRUE, FALSE)
Explaining the syntax:
bookId: string - The universal Book Id.
cache: number - Cache-control. Increase to clean cache and force fetch update.
query: string - The query.
properties: TRUE or FALSE - True to include custom transaction properties.
ids: TRUE or FALSE - True to include transaction ids.
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.