Skip to content
Bkper
app.header.homeapp.header.docs

Building Sheets Integrations

Go beyond the standard add-on with custom Google Sheets integrations using bkper-gs — build team-specific menus, automated import/export pipelines, scheduled balance reports, and two-way sync between spreadsheets and Bkper books.

The Bkper Add-on for Google Sheets lets users record transactions and fetch data with built-in functions. This page covers the next level: building custom Sheets integrations with bkper-gs — automated pipelines, custom menus, scheduled reports, and two-way sync.

See Apps Script Development first to set up bkper-gs and understand the fundamentals.

The boundary: add-on vs custom integrations

The built-in add-on covers the common cases well. Build a custom integration when:

  • You need a custom menu tailored to your team’s workflow
  • You want automated pipelines that run on a schedule without user interaction
  • You’re building a specialized report that the standard functions don’t cover
  • You need two-way sync between a spreadsheet and Bkper (data flowing both directions)
  • You’re distributing a custom add-on to your domain or organization

Custom menu functions

Add a Bkper-powered menu to any Google Sheet. Users can trigger operations directly from the spreadsheet without opening Bkper.

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Bkper')
    .addItem('Import expenses from this sheet', 'importExpenses')
    .addItem('Fetch account balances', 'fetchBalances')
    .addSeparator()
    .addItem('Sync all', 'syncAll')
    .addToUi();
}


function importExpenses() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Expenses');
  const book = BkperApp.getBook(getBookId());


  const rows = sheet.getDataRange().getValues().slice(1); // skip header
  const transactions = rows
    .filter(row => row[0] && row[1] && row[2])     // date, description, amount
    .map(row => `${row[1]} ${row[2]} ${row[0]}`);  // "description amount date"


  book.record(transactions);
  SpreadsheetApp.getUi().alert(`Imported ${transactions.length} transactions.`);
}

Automated data pipelines

Sheets → Bkper (import)

Pull structured data from a spreadsheet and create transactions in bulk. Useful for importing bank exports, expense reports, or any data that lives in Sheets first.

function importFromSheet() {
  const ss = SpreadsheetApp.openById('YOUR_SHEET_ID');
  const sheet = ss.getSheetByName('Transactions');
  const book = BkperApp.getBook('YOUR_BOOK_ID');


  const rows = sheet.getDataRange().getValues();
  const header = rows[0];
  const dateCol = header.indexOf('Date');
  const descCol = header.indexOf('Description');
  const amountCol = header.indexOf('Amount');
  const importedCol = header.indexOf('Imported');


  const toImport = [];


  for (let i = 1; i < rows.length; i++) {
    const row = rows[i];
    if (row[importedCol]) continue; // skip already imported


    const date = Utilities.formatDate(new Date(row[dateCol]), 'UTC', 'dd/MM/yyyy');
    toImport.push({
      row: i + 1,
      tx: `${row[descCol]} ${row[amountCol]} ${date}`,
    });
  }


  if (toImport.length === 0) return;


  book.record(toImport.map(item => item.tx));


  // Mark rows as imported
  for (const item of toImport) {
    sheet.getRange(item.row, importedCol + 1).setValue(true);
  }
}

Bkper → Sheets (export/reporting)

Write Bkper data into a spreadsheet for dashboards, analysis, or sharing with stakeholders who work in Sheets.

function exportBalancesToSheet() {
  const book = BkperApp.getBook('YOUR_BOOK_ID');
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Balances');


  sheet.clearContents();
  sheet.appendRow(['Account', 'Balance']);


  const accounts = book.getAccounts();
  for (const account of accounts) {
    if (account.isPermanent() && account.isActive()) {
      sheet.appendRow([account.getName(), account.getBalance()]);
    }
  }
}

Scheduled reporting

Use time-based triggers to run reports on a schedule — no user needs to be logged in.

function setupWeeklyReport() {
  // Run every Monday at 8am
  ScriptApp.newTrigger('generateWeeklyReport')
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(8)
    .create();
}


function generateWeeklyReport() {
  const book = BkperApp.getBook('YOUR_BOOK_ID');
  const ss = SpreadsheetApp.openById('YOUR_REPORT_SHEET_ID');
  const sheet = ss.getSheetByName('Weekly') || ss.insertSheet('Weekly');


  const lastWeek = new Date();
  lastWeek.setDate(lastWeek.getDate() - 7);
  const from = Utilities.formatDate(lastWeek, 'UTC', 'MM/dd/yyyy');


  sheet.clearContents();
  sheet.appendRow(['Description', 'Amount', 'Date', 'Account']);


  const iterator = book.getTransactions(`after:${from}`);
  while (iterator.hasNext()) {
    const tx = iterator.next();
    sheet.appendRow([
      tx.getDescription(),
      tx.getAmount(),
      tx.getDateFormatted(),
      tx.getCreditAccount()?.getName(),
    ]);
  }
}

Working with Custom Properties

Custom Properties let you attach metadata to Bkper entities (accounts, transactions). Use them as a sync key between Sheets and Bkper to avoid duplicates and enable updates.

// Store a Sheets row ID on a transaction as a custom property
function recordWithSheetId(book, txString, sheetRowId) {
  const transaction = book.newTransaction()
    .setDate(new Date())
    .setAmount(100)
    .setDescription(txString)
    .setProperty('sheet_row_id', sheetRowId);


  transaction.create();
}


// Later, look up transactions by their sheet row ID
function findBySheetId(book, sheetRowId) {
  const iterator = book.getTransactions(`properties.sheet_row_id:${sheetRowId}`);
  return iterator.hasNext() ? iterator.next() : null;
}

This pattern enables idempotent sync: check if a transaction already exists before creating it, and update rather than duplicate.

When to move beyond Sheets

Google Sheets is powerful, but it has limits. Consider a platform app when:

  • You need real-time event handling — platform apps get webhook events pushed instantly; Sheets triggers have latency and quota limits
  • You need a web UI outside of Sheets — platform apps get {appId}.bkper.app with full auth
  • Your automation needs to run at scale — Workers have no cold starts and higher execution limits than Apps Script
  • You want to publish to all Bkper users — platform apps appear in the Bkper app listing; Sheets add-ons have a separate distribution model