Transactions Function
BKPER_TRANSACTIONS
Fetch transactions from your Bkper Book into Google Sheets. The function returns all transactions matching your query, with full details including IDs, status, custom properties, and attachments.
Syntax
=BKPER_TRANSACTIONS(bookId, cache, query)
Parameters
bookId - The universal Book Id
cache - Increase this number to clear cache and fetch fresh data
query - The search query to filter transactions
Sample usage
=BKPER_TRANSACTIONS("agtzfmJrcGVyLWhyZHITCxIGTGVkZ2VyGICAgKD_4bMLDA", 1, "acc:'Bank Account' after:01/2019")
Output Columns
The function returns the following columns:
Column | Description |
Transaction Id | Unique identifier for each transaction |
Status | Current state: DRAFT, UNCHECKED, CHECKED, or TRASHED |
Date | Transaction date |
Origin | The account where the amount comes from |
Destination | The account where the amount goes to |
Description | Transaction description |
Amount | Transaction amount |
Balance | Running balance (only when filtering by a permanent account) |
Recorded at | Date and time the transaction was created |
Custom properties | Any custom properties on the transaction |
Remote Ids | External system identifiers, if any |
Attachments | URLs or file attachments linked to the transaction |
Transaction Status
Each transaction has one of four status values:
DRAFT - Transaction recorded but not yet posted
UNCHECKED - Transaction posted but not yet verified
CHECKED - Transaction posted and verified
TRASHED - Transaction moved to trash
You can filter by status in your query using is:draft, is:unchecked, is:checked, or is:trashed.
Balance Column
The Balance column only appears when your query filters by a single permanent account (Asset, Liability, or Equity). It shows the running balance of that account at each transaction.
For example, this query will include the Balance column:
=BKPER_TRANSACTIONS(bookId, 1, "acc:'Bank Account'")
This query will not include Balance (filtering by an Income/Expense account):
=BKPER_TRANSACTIONS(bookId, 1, "acc:'Office Supplies'")