What is Bkper add-on for Google Sheets?
The Bkper add-on for Google Sheets lets you record transactions, accounts and groups from Google Sheets on your Bkper books. Vice versa you can also fetch Balance Values, Transactions and Chart of Accounts from your Bkper books into Google Sheets. So, you centralize consistent financial data on Bkper and use Google Sheets to create dynamic Financial Statements. The Bkper add-on for Google Sheets is an open source project on Github.
How to Install?
Once installed and authorized as described in the link above you can start to use the Bkper add-on and the Bkper Functions will be available on all your Google Sheets.
What you can do with Bkper add-on?
Record in your book: | Fetch to your Google Sheet: |
|
|
|
|
|
|
|
|
Record data from your Google Sheets on your book
You can record transactions, create accounts and create groups. See more below.
Record Transactions
Many of your everyday tools and institutions offer you the option to export data to Spreadsheet files. This makes Google Sheets ideal to upload this raw data and record it as consistent financial data on your Bkper books.
Open the Bkper add-on on your Google Sheet and follow these steps to record transactions on Bkper:
Select the book where you want to record the transactions.
Select Transactions on the Record tab.
Select the cells with data you want record.
Press the Record button.
Note: Each selected row on your Sheet generates one entry on Bkper. Once a row has been recorded its colored green.
Record Accounts
Google Sheets is also ideal to create accounts in batch on your Bkper books. Instead of manually creating each account on Bkper, you can create several accounts at once very quickly. You can define the account's Type and assign it to Groups at the same time.
Open the Bkper add-on on your Google Sheet and follow these steps:
Select the book where you want to create the accounts.
Select Accounts on the Record tab.
Select the cells with data you want record.
Press the Record button.
Note: Each selected row on your Sheet generates a new account on Bkper. Once the account has been created the row is colored following the account's type color.
Record Groups
Similar to the record of accounts, you can create groups in batch on your Bkper books very easily as well. Open the Bkper add-on on your Google Sheet and follow these steps:
Select the book where you want to create the groups.
Select Groups on the Record tab.
Select the cells with data you want record.
Press the Record button.
Note: Each selected row on your Sheet generates a new group on Bkper. Once the group has been created the row is colored grey.
Header functionalities to record Transactions, Accounts & Groups
By freezing the first row on your Google Sheet you unlock several functionalities that optimize your workflow when you record transactions, accounts and groups.
Freeze the first row:
Bkper System Transaction Properties
On the frozen first row, Column Titles that use a System Transaction Property will be interpreted as such by the add-on. System Transaction Properties are:
Date: The date of the new entry.
Amount: The amount of the new entry.
Origin/From: The From account of the new entry.
Destination/To: The To account of the new entry.
Description: The description of the entry.
Attachment: An external attachment link for the new entry.
BookId: The BookId where the new entry should be recorded. - This BookId overrules the book selected on the Add-on.
The Column Titles Origin/From or Destination/To complete the two accounts on the new entry in your book (and if an amount is provided) the entry will directly be posted as a transaction on your book (not as a draft).
Record Transactions with Custom Properties
In the frozen first row, Column Titles that are NOT System Transaction Properties will be interpreted as Transaction Custom Property keys.
Note: Custom Properties serve as structured data for Bots and Apps, for example, the Bkper Stock Bot that reads Quantities to keep an inventory. Also, Custom Properties keys have a max length of 25 characters and are normalize to lower case and underscores for spaces.
Bkper System Account Properties
On the frozen first row, Column Titles that use a System Account Property will be interpreted as such by the add-on. System Account Properties are:
Name: The name of the account to be created.
Type: The Type of the account to be created.
Group: The name of any Group the account to be created should be in. - An account can have multiple groups.
BookId: The BookId where the new accounts should be created. - This BookId overrules the book selected on the Add-on.
A Bkper Account must have a name in order to be created, meaning that the Column Title Name is necessary. If the Column Title Type is not present, the account will be created as type ASSET.
Record Accounts with Custom Properties
In the frozen first row, Column Titles that are NOT System Account Properties will be interpreted as Account Custom Property keys.
Bkper System Group Properties
On the frozen first row, Column Titles that use a System Group Property will be interpreted as such by the add-on. System Group Properties are:
Name: The name of the group to be created.
Parent: The name of the parent group of the group to be created.
BookId: The BookId where the new groups should be created. - This BookId overrules the book selected on the Add-on.
The Column Title Name is necessary since a Bkper Group must have a name in order to be created.
Record Groups with Custom Properties
In the frozen first row, Column Titles that are NOT System Group Properties will be interpreted as Group Custom Property keys.
Auto Record of Transactions
Activate Auto Record on a Tab, and each new row added to a Google Sheet is automatically recorded as a new entry on your Bkper book. This is especially handy when you automatically send data to your Google Sheet.
Open the sidebar from the Add-on menu.
Select the book where you want to record the transactions.
Prepare the data you want to record automatically (for example with a query).
4. Select Auto Record on the Bkper Sheet Add-on menu.
5. Toggle the Auto Record switch so it says YES.
6. New rows on the tab will be recorded on your Bkper book.
Note: When new rows are recorded, a pointer to the last recorded row is stored in the Spreadsheet document, so, deleting a row already recorded may let the pointer stale, pointing to a blank row, thus, not recording new lines until the pointer is reached again. Avoid deleting already recorded rows and, if you really need to delete a row already recorded, you can reset the pointer by turning off and turning on the Auto-Record for that tab.
Fetch Financial Data from Bkper
Fetch financial data from a Bkper Book
Google Sheets is a strong reporting and analysis tool that has proven itself amongst Spreadsheets solutions. Therefor you can fetch financial data from Bkper into your Google Sheet so you can prepare your statements and reports to share with your peers. The advantage is that your reports and statements are much cleaner and as Bkper Formulas stay connected with your books, they are fed with real time data.
Fetch data from Bkper
Open the Bkper add-on for Google Sheets.
Select the book you want to Fetch data from.
Click the Fetch Tab.
Select Balances (or Transactions).
Write or copy your query in the input field.
Press the Fetch button.
The Bkper Sheet Formula is inserted in the selected cell and it fetches the requested data from Bkper into your sheet.
Notes:
- In steps 1, 3 and 4 you generate the conditions for the Bkper Sheet Formula.
- Afterwords you can edit the Bkper Sheet Formula directly.
- Only Posted transactions will be fetched, drafts are not included.
Fetch your Transactions from a Bkper Book
Open the Bkper add-on for Google Sheets.
Select the book you want to Fetch the transactions from.
Click the Fetch Tab.
Select Transactions
Write a query (in this example: "before:2023")
Select optional parameters:
- Properties
- Function
- ValuesPress the Fetch button.
Fetch the Chart of Accounts or Groups from a Bkper Book
Similar to fetching data from your Bkper books, you can fetch the Chart of Accounts from your book. Although you can copy books directly, having the chart of accounts in a Google Sheet is useful to quickly reproduce books with modifications, by recording the chart of accounts (see bellow).
Open the Bkper add-on for Google Sheets.
Select the book you want to Fetch the Chart of Accounts from.
Click the Fetch Tab.
Select Accounts or Groups
Press the Fetch button.
The Bkper Sheet Formula is inserted in the selected cell and it fetches the Chart of Accounts from Bkper into your sheet.
Notes:
- By selecting Accounts, the Chart of Accounts is fetched in the format of Name, Type and Group columns. Archived accounts are not included.
- By selecting Groups, a list of groups is fetched in the format os Name, Type, Parent name, Children (the number of child groups that group has) and Accounts (the number of accounts in that group). Hidden groups are not included.
Fetch your Balances from a Bkper Book
Open the Bkper add-on for Google Sheets.
Select the book you want to Fetch the Chart of Accounts from.
Click the Fetch Tab.
Select Balances
Choose the type of data:
Total
Period
CumulativeAs well as select optional parameters (checkboxes):
Transposed
Function
ValuesPress the Fetch button.
Resources
Templates
Get inspired with this Google Sheet template to create your own financial report, fetching data from a Bkper Book.
Watch a Bkper Function video or Learn more about Bkper Sheets Formulas.
Cash flow, Balance Sheet, Profit & Loss statement
This Google Sheets template shows a report with a Cash flow, Balance Sheet, Profit & Loss statement and a Dashboard fetched from the Bkper template Simple General Ledger.