# Building Sheets Integrations

The [Bkper Add-on for Google Sheets](https://bkper.com/docs/guides/google-sheets.md) 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](https://bkper.com/docs/build/google-workspace/apps-script.md) 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.

```js
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.`);
}
```

### 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.

```js
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.

```js
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.

```js
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.

```js
// 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](https://bkper.com/docs/build/apps/overview.md) 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

## Related

- [Apps Script Development](https://bkper.com/docs/build/google-workspace/apps-script.md) — Setting up `bkper-gs`, BkperApp patterns, triggers
- [The Bkper Platform](https://bkper.com/docs/build/apps/overview.md) — When to build a full platform app
- [Guides → Google Sheets Add-on](https://bkper.com/docs/guides/google-sheets.md) — End-user guide for the built-in add-on