The Bkper add-on for Google Sheets lets you record transactions 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.

Content

  • Install the Bkper Add-on for Google Sheets
  • Record Transactions.
  • Header functionalities on Recording Transactions.
  • Auto Record.
  • Fetch financial data from your Bkper books.
  • Fetch the chart of accounts
  • Record accounts & Groups.
  • Templates.
  • Cash flow, Balance Sheet, Profit & Loss statement.

Install the Bkper Add-on for Google Sheets.

  1. Install Bkper from the Google Workspace Marketplace, click on the install button on the top right.
  2. Open the Add-on for Bkper from the Google sheets menu Add-ons

The first time you access the Add-on you must authorize the access from your Google Sheet to Bkper for your user account.

Once installed and authorized, the Add-on for Bkper and the Bkper Functions will be available on all your Google Sheets.

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,

  1. Select the book where you want to record the transactions.
  2. Select the cells with data you want record.
  3. Press the record button on the Record tab.

Note: Each selected row on your Sheet generates one entry on Bkper.

Once a row has been recorded its colored green.

Header functionalities to Record Transactions

On Freezing the first row on your Google Sheet you unlock several functionalities that optimize your workflow when you record transactions.

Freeze the first row

Create accounts in Groups

With a Column Title equal to a Group Name in your book, on recording Bkper will check for the Accounts existence, in case it does not yet exist, Bkper creates the new Account and adds it to that Group.

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.

Transaction System properties are:

Date: The date of the new entry.

Amount: The amount of the new entry.

Origin/From: The From account on the entry.

Destination/To: The To account on the entry.

Description: The description of the entry.

Attachment: An external link for the 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).

Custom Transaction Properties

In the frozen first row, Column Titles that are NOT System Transaction Properties will be interpreted as Custom Transaction Property Keys.

Note: - Custom Transaction Properties serve as structured data for Bots or Apps, for example a Stock bot that interprets Quantities and Values to keep an inventory.

- Custom Transaction Property keys have a max length of 20 characters and are normalize to lower case and underscores for spaces.

Assist a video on how to batch record transaction properties from Google Sheets

Auto Record

Activate Auto Record on a Tab, and each new row added to a Google Sheet is automatically recorded as a new entry in your Bkper Book. This is especially handy when you automatically send data to your Google Sheet.

  1. Open the sidebar from the Add-on menu.
  2. Select the book where you want to record the transactions.
  3. 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.

Assist a video on how to use Auto Record.

Fetch financial data from Bkper books.

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.

  1. Select the book you want to Fetch data from.
  2. Click the Fetch Tab.
  3. Write or Copy your query in the input field.
  4. Make your selection of the data you want to Fetch.
  5. Press the Fetch button.
  6. The Bkper Sheet Formula is inserted in the selected cell and it fetches the requested data from Bkper into your sheet.

Note: - In step 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 the Chart of Accounts 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.

  1. Select the book you want to Fetch the Chart of Accounts from.
  2. Click the Fetch Tab.
  3. Select Accounts.
  4. Press the Fetch button.
  5. The Bkper Sheet Formula is inserted in the selected cell and it fetches the Chart of accounts from Bkper into your sheet.

The Chart of Account is fetched in the format of:

Account Name

Account Type

Groups it is part of in separate columns

Record Accounts & Groups.

Model your Chart of Accounts on Your Google Sheet in the format of:

The first column with the Account Name

The second column with the Account Type

The rest of the columns with the Groups the account is part of.

Open the Bkper Add-on for Google Sheets.

  1. Select the book where you want to Record an Account and its Groups.
  2. Click the Record Tab.
  3. Select Accounts.
  4. Select the row with the account details.
  5. Press the Record button.

Templates

Get inspired with this Google Sheet template to create your own financial report, fetching data from a Bkper book.

Assist 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.  Simple General Ledger Report


 


Did this answer your question?