Синхронізація операцій між Google Spreadsheet та Nomi

index.gs

/**
 * ІНСТРУКЦІЯ!
 *
 * 1. Створити новий документ Google електронна таблиця
 * 2. Перейти у розділ Розширення > Apps Script
 * 3. Скопіювати цей код повністю, вставити у Apps Script та зберегти
 * 4. Підставити свої email та ключ з розділу "Розробка" (https://nomi.tools/dev) нижче у код та зберегти
 * 5. Повернутись у документ та оновити сторінку
 * 6. Зʼявиться пункт меню Nomi у ньому натиснути "Завантажити довідники з Nomi"
 * 7. Створиться листи Transactions у якому буде таблиця для заповнення
 * 8. У колонці Дата задайте формат ДД.ММ.РРРР
 *
 * Після цього таблицю можна використовувати для внесення ручних операцій,
 * в колонці Статус буде автоматично проставлятись статус Pending. Для завантаження
 * операцій у Nomi потрібно натиснути пункт меню Nomi > Зберегти операції у Nomi.
 * Після збереження у операцій зміниться статус на Done.
 *
 * Колонка "Мітки" за замовчуванням містить лише 1 мітку, щоб зробити мультиселект
 * треба взяти редагування списку, натиснути "Додаткові параметри", у пункті
 * "Стиль відображення" вибрати "Чип" і стане активна галочка "Дозволити вибір кількох обʼєктів"
 * вибрати її та зберегти.
 */
const email = 'Ваш email';
const apiKey = 'Ключ з розділу "Розробка"';
const onlyPlanned = false; // змінити на true якщо через лист будуть вноситись лише планові операції

/****************************************************
 * CONFIG
 ****************************************************/

const SHEETS = {
  SETTINGS: 'Settings',
  SETTINGS_HELPER: '_settings_helper',
  TRANSACTIONS: 'Transactions',
};

const NOMI_API = {
  BASE_URL: 'https://api.nomi.tools/v1',
  ACCOUNTS: '/finance/accounts',
  PROJECTS: '/projects',
  COUNTERPARTIES: '/counterparties',
  CATEGORIES: '/finance/categories',
  TRANSACTIONS_BULK: '/finance/accounting/transactions/bulk',
  LABELS: '/custom-attributes/labels',
};

/**
 * ЄДИНЕ ДЖЕРЕЛО ІСТИНИ ДЛЯ КОЛОНОК
 * 1-based
 */
const TX_COL = {
  DATE: 1,              // A
  DATE_INCOME: 2,              // B
  ACCOUNT_NAME: 3,      // B
  AMOUNT: 4,            // C
  CATEGORY_NAME: 5,     // D
  COUNTERPARTY_NAME: 6, // E
  PROJECT_NAME: 7,      // F
  DESCRIPTION: 8,       // G
  TAGS: 9,              // H
  STATUS: 10,            // I

  ACCOUNT_ID: 11,       // J (hidden)
  CATEGORY_ID: 12,      // K (hidden)
  COUNTERPARTY_ID: 13,  // L (hidden)
  PROJECT_ID: 14,       // M (hidden)
};

/****************************************************
 * MENU
 ****************************************************/

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('𝓷 Nomi')
    .addItem('💾 Зберегти операції у Nomi', 'syncTransactionsToNomi')
    .addSeparator()
    .addItem('🔄 Завантажити довідники з Nomi', 'syncSettingsFromNomi')
    .addToUi();
}

/****************************************************
 * HELPERS
 ****************************************************/

function getOrCreateSheet(name) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  return ss.getSheetByName(name) || ss.insertSheet(name);
}

function sheetExists(name) {
  return !!SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
}

function getNomiAuthHeaders() {
  if (!email || !apiKey) {
    throw new Error('NOMI_EMAIL або NOMI_API_KEY не задані');
  }
  const encoded = Utilities.base64Encode(`${email}:${apiKey}`);
  return {
    Authorization: `Basic ${encoded}`,
    'Content-Type': 'application/json',
    Accept: 'application/json',
  };
}

function nomiRequest(path, { method = 'get', payload = null } = {}) {
  const res = UrlFetchApp.fetch(`${NOMI_API.BASE_URL}${path}`, {
    method: method.toUpperCase(),
    headers: getNomiAuthHeaders(),
    payload: payload ? JSON.stringify(payload) : undefined,
    muteHttpExceptions: true,
  });

  const code = res.getResponseCode();
  const body = res.getContentText();
  if (code < 200 || code >= 300) {
    Logger.log(body);
    throw new Error(`Nomi API error ${code}`);
  }
  return body ? JSON.parse(body) : {};
}

/****************************************************
 * SYNC SETTINGS
 ****************************************************/

function syncSettingsFromNomi() {
  const s = getOrCreateSheet(SHEETS.SETTINGS);
  s.clearContents();
  s.getRange(1, 1, 1, 3).setValues([['Type', 'Id', 'Name']]);

  const rows = [];

  (nomiRequest(NOMI_API.ACCOUNTS).accounts || [])
    .forEach(a => rows.push(['account', a.id, a.name]));

  (nomiRequest(NOMI_API.PROJECTS).projects || [])
    .forEach(p => rows.push(['project', p.id, p.name]));

  (nomiRequest(NOMI_API.COUNTERPARTIES).counterparties || [])
    .forEach(c => rows.push(['counterparty', c.id, c.name]));

  (nomiRequest(NOMI_API.CATEGORIES).categories || [])
    .forEach(cat => {
      if (cat.type === 'income' || cat.type === 'outcome') {
        rows.push([cat.type, cat.id, cat.name]);
      }
    });

  (nomiRequest(NOMI_API.LABELS).labels || [])
    .forEach(l => rows.push(['label', l.id, l.text]));

  s.getRange(2, 1, rows.length, 3).setValues(rows);
  s.setFrozenRows(1);
  s.getRange('A1:C1').setFontWeight('bold');
  s.autoResizeColumns(1, 3);

  buildSettingsHelper();

  s.hideSheet();

  initTransactionsSheet();
}

/****************************************************
 * SETTINGS HELPER
 ****************************************************/

function buildSettingsHelper() {
  const h = getOrCreateSheet(SHEETS.SETTINGS_HELPER);
  h.clearContents();

  h.getRange(1, 1).setFormula('=FILTER(Settings!C:C; Settings!A:A="account")');
  h.getRange(3001, 1).setFormula('=FILTER(Settings!C:C; Settings!A:A="counterparty")');
  h.getRange(5001, 1).setFormula('=FILTER(Settings!C:C; Settings!A:A="project")');
  h.getRange(7001, 1).setFormula('=FILTER(Settings!C:C; Settings!A:A="income")');
  h.getRange(9001, 1).setFormula('=FILTER(Settings!C:C; Settings!A:A="outcome")');
  h.getRange(11001, 1).setFormula('=FILTER(Settings!C:C; Settings!A:A="label")');

  h.hideSheet();
}

/****************************************************
 * TRANSACTIONS INIT
 ****************************************************/

function initTransactionsSheet() {
  if (sheetExists(SHEETS.TRANSACTIONS)) {
    // ❗️Якщо лист вже є — нічого не робимо
    return;
  }

  const t = getOrCreateSheet(SHEETS.TRANSACTIONS);

  t.getRange(1, 1, 1, 14).setValues([[
    'Дата',
    'Дата нарахування',
    'Рахунок',
    'Сума',
    'Стаття',
    'Контрагент',
    'Проєкт',
    'Опис',
    'Мітки',
    'Статус',
    'AccountId',
    'CategoryId',
    'CounterpartyId',
    'ProjectId',
  ]]);

  t.setFrozenRows(1);
  t.getRange('A1:M1').setFontWeight('bold');
  //t.autoResizeColumns(1, 14);

  applyFormats(t);
  applyDropdowns(t);
  applyIdFormulas(t);
  hideSystemColumns(t);
}

/****************************************************
 * FORMATS
 ****************************************************/

function applyFormats(t) {
  t.getRange('A2:A').clearFormat().setNumberFormat('dd.mm.yyyy');
  t.getRange('D2:D').setNumberFormat('#,##0.00');

  t.setConditionalFormatRules([
    SpreadsheetApp.newConditionalFormatRule()
      .whenNumberGreaterThan(0)
      .setFontColor('#0B8043')
      .setRanges([t.getRange('D2:D')])
      .build(),
    SpreadsheetApp.newConditionalFormatRule()
      .whenNumberLessThan(0)
      .setFontColor('#D93025')
      .setRanges([t.getRange('D2:D')])
      .build(),
  ]);
}

/****************************************************
 * DROPDOWNS
 ****************************************************/

function applyDropdowns(t) {
  const h = getOrCreateSheet(SHEETS.SETTINGS_HELPER);
  const max = t.getMaxRows();

  const rule = r => SpreadsheetApp.newDataValidation()
    .requireValueInRange(r, true)
    .setAllowInvalid(false)
    .build();

  t.getRange(2, TX_COL.ACCOUNT_NAME, max - 1, 1)
    .setDataValidation(rule(h.getRange(1, 1, 1500, 1)));

  t.getRange(2, TX_COL.COUNTERPARTY_NAME, max - 1, 1)
    .setDataValidation(rule(h.getRange(2001, 1, 1500, 1)));

  t.getRange(2, TX_COL.PROJECT_NAME, max - 1, 1)
    .setDataValidation(rule(h.getRange(4001, 1, 1500, 1)));

  t.getRange(2, TX_COL.TAGS, max - 1, 1)
    .setDataValidation(rule(h.getRange(10001, 1, 1500, 1)));

  const statusRule = SpreadsheetApp.newDataValidation()
    .requireValueInList(['Pending', 'Done'], true)
    .setAllowInvalid(false)
    .build();

  t.getRange(2, TX_COL.STATUS, max - 1, 1).setDataValidation(statusRule);
}

/****************************************************
 * ID FORMULAS
 ****************************************************/

function applyIdFormulas(t) {
  const max = t.getMaxRows();

  t.getRange(2, TX_COL.ACCOUNT_ID, max - 1, 1)
    .setFormula('=IFERROR(INDEX(Settings!B:B; MATCH(B2; Settings!C:C; 0)); "")');

  t.getRange(2, TX_COL.CATEGORY_ID, max - 1, 1)
    .setFormula('=IFERROR(INDEX(Settings!B:B; MATCH(D2; Settings!C:C; 0)); "")');

  t.getRange(2, TX_COL.COUNTERPARTY_ID, max - 1, 1)
    .setFormula('=IFERROR(INDEX(Settings!B:B; MATCH(E2; Settings!C:C; 0)); "")');

  t.getRange(2, TX_COL.PROJECT_ID, max - 1, 1)
    .setFormula('=IFERROR(INDEX(Settings!B:B; MATCH(F2; Settings!C:C; 0)); "")');
}

/****************************************************
 * HIDE SYSTEM COLUMNS
 ****************************************************/

function hideSystemColumns(t) {
  [TX_COL.ACCOUNT_ID, TX_COL.CATEGORY_ID, TX_COL.COUNTERPARTY_ID, TX_COL.PROJECT_ID]
    .forEach(c => t.hideColumns(c));
}

/****************************************************
 * CATEGORY DROPDOWN BY AMOUNT
 ****************************************************/

function applyCategoryDropdownForRow(row, t) {
  if (row < 2) return;

  const h = getOrCreateSheet(SHEETS.SETTINGS_HELPER);
  const amount = Number(t.getRange(row, TX_COL.AMOUNT).getValue());
  const isIncome = isNaN(amount) || amount >= 0;

  const listRange = isIncome
    ? h.getRange(6001, 1, 1500, 1)
    : h.getRange(8001, 1, 1500, 1);

  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInRange(listRange, true)
    .setAllowInvalid(false)
    .build();

  t.getRange(row, TX_COL.CATEGORY_NAME).setDataValidation(rule);
}

/****************************************************
 * MULTISELECT TAGS
 ****************************************************/

function applyMultiSelect(cell, newValue) {
  if (!newValue) return;

  const old = cell.getValue();
  if (!old) {
    cell.setValue(newValue);
    return;
  }

  const values = old.split(',').map(v => v.trim());
  if (!values.includes(newValue)) {
    values.push(newValue);
  }

  cell.setValue(values.join(', '));
}

/****************************************************
 * onEdit
 ****************************************************/

function onEdit(e) {
  const s = e.source.getActiveSheet();
  if (s.getName() !== SHEETS.TRANSACTIONS) return;

  const row = e.range.getRow();
  const col = e.range.getColumn();
  if (row < 2) return;

  if (col === TX_COL.AMOUNT) {
    applyCategoryDropdownForRow(row, s);
    s.getRange(row, TX_COL.CATEGORY_NAME).clearContent();
  }

  if (col === TX_COL.TAGS && e.value) {
    applyMultiSelect(e.range, e.value);
  }

  if (col !== TX_COL.STATUS) {
    s.getRange(row, TX_COL.STATUS).setValue('Pending');
  }
}

/****************************************************
 * SYNC TRANSACTIONS
 ****************************************************/

function syncTransactionsToNomi() {
  const sheet = getOrCreateSheet(SHEETS.TRANSACTIONS);
  const data = sheet.getDataRange().getValues();
  if (data.length < 2) return;

  const headers = data[0];
  const statusIdx = headers.indexOf('Статус');

  const rows = data.slice(1)
    .map((row, i) => ({ row, index: i + 2 }))
    .filter(r => r.row[statusIdx] === 'Pending');

  if (!rows.length) return;

  const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  const sheetId = sheet.getSheetId();

  const payload = rows.map(r => {
    const get = name => r.row[headers.indexOf(name)];
    const amount = Number(get('Сума'));
    const isPlanned = onlyPlanned ? true : undefined;
    const isDifferentIncomeDate = get('Дата нарахування') && get('Дата нарахування') != get('Дата');
    const incomeDate = isDifferentIncomeDate ?
         Utilities.formatDate(new Date(get('Дата нарахування')), 'Europe/Kyiv', 'yyyy-MM-dd') 
         : Utilities.formatDate(new Date(get('Дата')), 'Europe/Kyiv', 'yyyy-MM-dd');

    return {
      isPlanned,
      date: Utilities.formatDate(new Date(get('Дата')), 'Europe/Kyiv', 'yyyy-MM-dd'),
      amount: amount,
      isDifferentIncomeDate,
      type: amount >= 0 ? 'income' : 'outcome',
      accountId: get('AccountId'),
      categoryId: get('CategoryId'),
      counterpartyId: get('CounterpartyId') || null,
      projectId: get('ProjectId') || null,
      description: get('Опис') || 'Без коментаря',
      incomeDate,
      tags: get('Мітки')
        ? String(get('Мітки'))
            .split(',')
            .map(t => ({ text: t.trim() }))
            .filter(t => t.text)
        : [],
      externalId:
        `https://docs.google.com/spreadsheets/d/${spreadsheetId}` +
        `/edit#gid=${sheetId}&range=A${r.index}`,
    };
  });

  nomiRequest(NOMI_API.TRANSACTIONS_BULK, { method: 'post', payload });

  rows.forEach(r => {
    sheet.getRange(r.index, statusIdx + 1).setValue('Done');
  });
}