Mable Budget Tracker

mable_budget_tracker.gs

/***** Mable / NDIS Budget Tracker for Google Sheets *****
 * Paste this whole file into Extensions > Apps Script.
 * Run initMableTracker() once from the Apps Script editor.
 */

const TRACKER = {
  sheets: {
    summary: 'Summary',
    transactions: 'Transactions',
    config: 'Config',
    instructions: 'Instructions',
    importLog: 'Import Log'
  },
  defaultCategory: 'Uncategorised',
  txHeaders: [
    'Import ID', 'Imported At', 'Participant', 'Provider', 'Start Date', 'Start Time',
    'End Date', 'End Time', 'Hours', 'Agreed Rate', 'Total Cost', 'Paid Amount',
    'Category', 'Unique Key', 'Source Sheet', 'Week Starting', 'Month', 'Year', 'Notes'
  ],
  processedHeaders: [
    'Participant', 'Provider', 'Start Date', 'Start Time', 'End Date', 'End Time',
    'Hours', 'Agreed Rate', 'Total Cost', 'Paid Amount', 'Category', 'Unique Key'
  ],
  importLogHeaders: [
    'Import ID', 'Imported At', 'Source Sheet', 'Status', 'Rows In CSV', 'New Rows Added',
    'Existing Rows Ignored', 'Existing Amount Updates', 'Min Start Date', 'Max Start Date', 'Message'
  ],
  mableHeaders: {
    participant: 'Participant',
    provider: 'Care Worker',
    startDate: 'Start Date',
    startTime: 'Start Time',
    endDate: 'End Date',
    endTime: 'End Time',
    hours: 'Total Hours',
    agreedRate: 'Agreed Rate',
    totalCost: 'Total Cost Of Support',
    paidAmount: 'Paid Amount'
  },
  defaultCategories: [
    'Uncategorised',
    'Core Supports',
    'Assistance with Daily Life',
    'Assistance with Social & Community Participation',
    'Transport',
    'Consumables',
    'Improved Daily Living',
    'Support Coordination',
    'Plan Management',
    'Therapy',
    'Other'
  ]
};

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('NDIS Tracker')
    .addItem('Process active Mable import', 'processActiveMableImport')
    .addItem('Refresh summary', 'refreshSummary')
    .addItem('Apply provider defaults to uncategorised rows', 'applyProviderDefaultsToUncategorisedRows')
    .addSeparator()
    .addItem('Rebuild tracker sheets', 'initMableTracker')
    .addToUi();
}

function initMableTracker() {
  const ss = SpreadsheetApp.getActive();

  ensureTrackerSheets_();
  initialiseConfig_();
  initialiseTransactions_();
  initialiseImportLog_();
  initialiseInstructions_();
  refreshSummary();
  applyCoreCategoryDropdowns_();
  onOpen();

  ss.toast('NDIS tracker initialised. Use the NDIS Tracker menu after importing a Mable CSV.', 'NDIS Tracker', 8);
}

function processActiveMableImport() {
  const ss = SpreadsheetApp.getActive();
  ensureTrackerSheets_();

  const sourceSheet = ss.getActiveSheet();
  const sourceName = sourceSheet.getName();

  if (isCoreSheet_(sourceName)) {
    throw new Error('Click the newly imported Mable CSV sheet first, then run NDIS Tracker > Process active Mable import.');
  }

  const importId = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyyMMdd-HHmmss');
  let logRow = null;

  try {
    ss.toast('Reading Mable CSV...', 'NDIS Tracker', 5);
    const records = readMableRecordsFromSheet_(sourceSheet);

    if (records.length === 0) {
      throw new Error('No Mable rows found on the active sheet.');
    }

    logRow = logImportStarted_(importId, sourceName, records);
    SpreadsheetApp.flush();

    ss.toast('Processing ' + records.length + ' rows...', 'NDIS Tracker', 5);

    const txSheet = getOrCreateSheet_(TRACKER.sheets.transactions);
    initialiseTransactions_();

    const now = new Date();
    const existing = readExistingTransactions_();
    const config = readConfigState_();
    const rowsToAppend = [];
    const processedNewRows = [];
    const amountUpdates = [];

    let duplicateCount = 0;

    records.forEach(function (record) {
      const providerKey = normaliseKey_(record.provider);
      const existingTx = existing.byKey[record.uniqueKey];

      if (!config.providerDefaults[providerKey]) {
        config.providerDefaults[providerKey] = TRACKER.defaultCategory;
        config.providerAdditions[providerKey] = [record.provider, TRACKER.defaultCategory];
      }

      const defaultCategory = config.providerDefaults[providerKey] || TRACKER.defaultCategory;
      const category = existingTx ? existingTx.category : defaultCategory;

      config.categorySet[normaliseKey_(category)] = category;

      if (existingTx) {
        duplicateCount++;

        if (transactionAmountsDiffer_(existingTx, record)) {
          amountUpdates.push({ row: existingTx.row, totalCost: record.totalCost, paidAmount: record.paidAmount });
        }

        return;
      }

      rowsToAppend.push([
        importId,
        now,
        record.participant,
        record.provider,
        record.startDate,
        record.startTime,
        record.endDate,
        record.endTime,
        record.hours,
        record.agreedRate,
        record.totalCost,
        record.paidAmount,
        category,
        record.uniqueKey,
        sourceName,
        getWeekStart_(record.startDate),
        getMonthStart_(record.startDate),
        record.startDate.getFullYear(),
        ''
      ]);

      processedNewRows.push([
        record.participant,
        record.provider,
        record.startDate,
        record.startTime,
        record.endDate,
        record.endTime,
        record.hours,
        record.agreedRate,
        record.totalCost,
        record.paidAmount,
        category,
        record.uniqueKey
      ]);
    });

    writeConfigStateAdditions_(config);

    if (rowsToAppend.length > 0) {
      const startRow = txSheet.getLastRow() + 1;
      txSheet.getRange(startRow, 1, rowsToAppend.length, TRACKER.txHeaders.length).setValues(rowsToAppend);
      SpreadsheetApp.flush();
    }

    amountUpdates.forEach(function (update) {
      txSheet.getRange(update.row, 11, 1, 2).setValues([[update.totalCost, update.paidAmount]]);
    });

    ss.toast('Formatting import result...', 'NDIS Tracker', 5);

    sortTransactions_();
    formatTransactionsSheet_();
    rewriteProcessedImportSheet_(sourceSheet, sourceName, processedNewRows, records.length, duplicateCount, amountUpdates.length);
    applyCoreCategoryDropdowns_();
    applyCategoryDropdownToProcessedSheet_(sourceSheet);
    refreshSummary();
    updateImportLogFinished_(logRow, records.length, rowsToAppend.length, duplicateCount, amountUpdates.length, 'Complete');

    SpreadsheetApp.flush();

    SpreadsheetApp.getUi().alert(
      'Mable import processed\n\n' +
      'Rows in CSV: ' + records.length + '\n' +
      'New rows added: ' + rowsToAppend.length + '\n' +
      'Existing rows ignored: ' + duplicateCount + '\n' +
      'Existing amount updates: ' + amountUpdates.length + '\n\n' +
      'The active import sheet now contains only the new rows from this run.'
    );
  } catch (err) {
    if (logRow) {
      updateImportLogError_(logRow, err);
    }

    throw err;
  }
}

function refreshSummary() {
  ensureTrackerSheets_();
  initialiseConfig_();
  initialiseTransactions_();

  const sheet = getOrCreateSheet_(TRACKER.sheets.summary);
  const existingBudgets = readExistingSummaryBudgets_(sheet);
  const categories = readCategories_();

  breakAllMerges_(sheet);
  sheet.clear();
  sheet.setFrozenRows(0);
  sheet.setFrozenColumns(0);
  sheet.setHiddenGridlines(true);

  sheet.getRange('A1').setValue('NDIS Spend Summary');
  sheet.getRange('A2').setValue('Set budgets in the budget columns. Spend is calculated from Total Cost Of Support, not Paid Amount.');
  sheet.getRange('A3').setValue('Edit categories and provider defaults on the Config sheet. Categorising a provider once makes that category the future default for that provider.');

  sheet.getRange('A1:S1').merge().setFontWeight('bold').setFontSize(16).setBackground('#d9ead3');
  sheet.getRange('A2:S2').merge().setWrap(true).setBackground('#f3f6f4');
  sheet.getRange('A3:S3').merge().setWrap(true).setBackground('#f3f6f4');

  const headers = [
    'Category',
    'Weekly Budget', 'This Week', 'Previous Week', 'Week Δ', 'Weekly Remaining',
    'Monthly Budget', 'This Month', 'Previous Month', 'Month Δ', 'Monthly Remaining',
    'Annual Budget', 'YTD', 'Previous YTD', 'YTD Δ', 'Annual Remaining',
    'Rolling 12 Months', 'Previous Rolling 12', 'Rolling 12 Δ'
  ];

  const headerRow = 5;
  const firstDataRow = 6;

  sheet.getRange(headerRow, 1, 1, headers.length).setValues([headers]);
  sheet.getRange(headerRow, 1, 1, headers.length).setFontWeight('bold').setBackground('#b6d7a8').setWrap(true);

  if (categories.length > 0) {
    const dataRows = categories.map(function (category, i) {
      const row = firstDataRow + i;
      const budget = existingBudgets[category] || { weekly: '', monthly: '', annual: '' };

      return [
        category,
        budget.weekly,
        formulaThisWeek_(row),
        formulaPreviousWeek_(row),
        '=IF($A' + row + '="","",C' + row + '-D' + row + ')',
        '=IF($A' + row + '="","",IF($B' + row + '="","",$B' + row + '-C' + row + '))',
        budget.monthly,
        formulaThisMonth_(row),
        formulaPreviousMonth_(row),
        '=IF($A' + row + '="","",H' + row + '-I' + row + ')',
        '=IF($A' + row + '="","",IF($G' + row + '="","",$G' + row + '-H' + row + '))',
        budget.annual,
        formulaYtd_(row),
        formulaPreviousYtd_(row),
        '=IF($A' + row + '="","",M' + row + '-N' + row + ')',
        '=IF($A' + row + '="","",IF($L' + row + '="","",$L' + row + '-M' + row + '))',
        formulaRolling12_(row),
        formulaPreviousRolling12_(row),
        '=IF($A' + row + '="","",Q' + row + '-R' + row + ')'
      ];
    });

    sheet.getRange(firstDataRow, 1, dataRows.length, headers.length).setValues(dataRows);
  }

  const totalRow = firstDataRow + categories.length;

  sheet.getRange(totalRow, 1).setValue('TOTAL');
  sheet.getRange(totalRow, 1, 1, headers.length).setFontWeight('bold').setBackground('#d9ead3');

  if (categories.length > 0) {
    const lastDataRow = totalRow - 1;

    for (let col = 2; col <= headers.length; col++) {
      const colLetter = columnToLetter_(col);
      sheet.getRange(totalRow, col).setFormula('=SUM(' + colLetter + firstDataRow + ':' + colLetter + lastDataRow + ')');
    }
  }

  sheet.setFrozenRows(headerRow);

  sheet.getRange(1, 1, Math.max(totalRow, firstDataRow), headers.length).setVerticalAlignment('middle');
  sheet.getRange(firstDataRow, 2, Math.max(categories.length + 1, 1), headers.length - 1).setNumberFormat('$#,##0.00;-$#,##0.00;');
  sheet.getRange(firstDataRow, 1, Math.max(categories.length + 1, 1), headers.length).setBorder(true, true, true, true, true, true, '#d9ead3', SpreadsheetApp.BorderStyle.SOLID);

  sheet.setColumnWidth(1, 230);
  sheet.setColumnWidths(2, headers.length - 1, 115);

  sheet.getRange(firstDataRow, 2, Math.max(categories.length, 1), 1).setBackground('#fff2cc');
  sheet.getRange(firstDataRow, 7, Math.max(categories.length, 1), 1).setBackground('#fff2cc');
  sheet.getRange(firstDataRow, 12, Math.max(categories.length, 1), 1).setBackground('#fff2cc');

  applySummaryConditionalFormatting_(sheet, firstDataRow, Math.max(categories.length, 1));
}

function applyProviderDefaultsToUncategorisedRows() {
  ensureTrackerSheets_();

  const providerDefaults = readProviderDefaults_();
  const tx = getOrCreateSheet_(TRACKER.sheets.transactions);
  const lastRow = tx.getLastRow();

  if (lastRow < 2) return;

  const values = tx.getRange(2, 1, lastRow - 1, TRACKER.txHeaders.length).getValues();

  let changed = 0;

  values.forEach(function (row) {
    const provider = row[3];
    const currentCategory = String(row[12] || '').trim();
    const providerDefault = providerDefaults[normaliseKey_(provider)];

    if (providerDefault && isBlankOrUncategorised_(currentCategory)) {
      row[12] = providerDefault;
      changed++;
    }
  });

  if (changed > 0) {
    tx.getRange(2, 1, values.length, TRACKER.txHeaders.length).setValues(values);
  }

  applyCoreCategoryDropdowns_();
  refreshSummary();

  SpreadsheetApp.getActive().toast('Updated ' + changed + ' uncategorised transaction rows.', 'NDIS Tracker', 6);
}

function onEdit(e) {
  if (!e || !e.range) return;

  const sheet = e.range.getSheet();
  const sheetName = sheet.getName();
  const row = e.range.getRow();
  const col = e.range.getColumn();

  if (sheetName === TRACKER.sheets.config) {
    handleConfigEdit_(sheet, row, col);
    return;
  }

  if (row < 2) return;

  const headers = getHeaderMap_(sheet);
  const categoryCol = headers['category'];

  if (!categoryCol || col !== categoryCol) return;

  const category = String(e.range.getDisplayValue() || '').trim();

  if (!category) return;

  const providerCol = headers['provider'] || headers['care worker'];
  const uniqueKeyCol = headers['unique key'];

  if (!providerCol) return;

  const provider = String(sheet.getRange(row, providerCol).getDisplayValue() || '').trim();
  const uniqueKey = uniqueKeyCol ? String(sheet.getRange(row, uniqueKeyCol).getDisplayValue() || '').trim() : '';

  if (!provider) return;

  ensureCategory_(category);
  ensureProviderDefault_(provider, category);

  if (uniqueKey) {
    updateTransactionCategoryByUniqueKey_(uniqueKey, category);
  }

  updateUncategorisedTransactionsForProvider_(provider, category);
  applyCoreCategoryDropdowns_();
  applyCategoryDropdownToProcessedSheet_(sheet);
  refreshSummary();
}

function handleConfigEdit_(sheet, row, col) {
  if (row < 2) return;

  if (col === 1) {
    applyCoreCategoryDropdowns_();
    refreshSummary();
    return;
  }

  if (col === 4) {
    const provider = String(sheet.getRange(row, 3).getDisplayValue() || '').trim();
    const category = String(sheet.getRange(row, 4).getDisplayValue() || '').trim();

    if (provider && category) {
      ensureCategory_(category);
      updateUncategorisedTransactionsForProvider_(provider, category);
      applyCoreCategoryDropdowns_();
      refreshSummary();
    }
  }
}

function ensureTrackerSheets_() {
  getOrCreateSheet_(TRACKER.sheets.instructions);
  getOrCreateSheet_(TRACKER.sheets.summary);
  getOrCreateSheet_(TRACKER.sheets.config);
  getOrCreateSheet_(TRACKER.sheets.transactions);
  getOrCreateSheet_(TRACKER.sheets.importLog);
}

function initialiseConfig_() {
  const sheet = getOrCreateSheet_(TRACKER.sheets.config);

  sheet.getRange('A1').setValue('Categories');
  sheet.getRange('C1').setValue('Provider');
  sheet.getRange('D1').setValue('Default Category');
  sheet.getRange('F1').setValue('Setting');
  sheet.getRange('G1').setValue('Value');
  sheet.getRange('F2').setValue('Spend Amount Used');
  sheet.getRange('G2').setValue('Total Cost Of Support');
  sheet.getRange('F3').setValue('Week Starts On');
  sheet.getRange('G3').setValue('Monday');

  const existingCategories = readCategories_();
  const categorySet = {};

  existingCategories.forEach(function (category) {
    categorySet[normaliseKey_(category)] = true;
  });

  const additions = [];

  TRACKER.defaultCategories.forEach(function (category) {
    const key = normaliseKey_(category);

    if (!categorySet[key]) {
      additions.push([category]);
      categorySet[key] = true;
    }
  });

  if (additions.length > 0) {
    const row = firstEmptyRowInColumn_(sheet, 1, 2);
    sheet.getRange(row, 1, additions.length, 1).setValues(additions);
  }

  sheet.getRange('A1:G1').setFontWeight('bold').setBackground('#d9ead3');
  sheet.setFrozenRows(1);
  sheet.setColumnWidth(1, 280);
  sheet.setColumnWidth(3, 260);
  sheet.setColumnWidth(4, 280);
  sheet.setColumnWidth(6, 180);
  sheet.setColumnWidth(7, 180);

  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInRange(sheet.getRange('A2:A'), true)
    .setAllowInvalid(false)
    .build();

  sheet.getRange('D2:D1000').setDataValidation(rule);
}

function initialiseTransactions_() {
  const sheet = getOrCreateSheet_(TRACKER.sheets.transactions);
  const currentHeader = sheet.getRange(1, 1, 1, TRACKER.txHeaders.length).getDisplayValues()[0];

  if (currentHeader.join('|') !== TRACKER.txHeaders.join('|')) {
    sheet.getRange(1, 1, 1, TRACKER.txHeaders.length).setValues([TRACKER.txHeaders]);
  }

  formatTransactionsSheet_();
}

function initialiseImportLog_() {
  const sheet = getOrCreateSheet_(TRACKER.sheets.importLog);
  const currentHeader = sheet.getRange(1, 1, 1, TRACKER.importLogHeaders.length).getDisplayValues()[0];

  if (currentHeader.join('|') !== TRACKER.importLogHeaders.join('|')) {
    sheet.clear();
    sheet.getRange(1, 1, 1, TRACKER.importLogHeaders.length).setValues([TRACKER.importLogHeaders]);
  }

  sheet.getRange(1, 1, 1, TRACKER.importLogHeaders.length).setFontWeight('bold').setBackground('#d9ead3');
  sheet.setFrozenRows(1);
  sheet.setColumnWidths(1, TRACKER.importLogHeaders.length, 150);
  sheet.getRange('B:B').setNumberFormat('dd/mm/yyyy hh:mm');
  sheet.getRange('I:J').setNumberFormat('dd/mm/yyyy');
}

function initialiseInstructions_() {
  const sheet = getOrCreateSheet_(TRACKER.sheets.instructions);

  breakAllMerges_(sheet);
  sheet.clear();

  const rows = [
    ['NDIS / Mable Spend Tracker'],
    [''],
    ['Initial setup'],
    ['1. Paste this script into Extensions > Apps Script.'],
    ['2. Run initMableTracker() once from the Apps Script editor.'],
    ['3. Return to the spreadsheet. The NDIS Tracker menu will be available.'],
    [''],
    ['Weekly import'],
    ['1. Import the Mable CSV into this spreadsheet as a new sheet.'],
    ['2. Click the newly imported sheet.'],
    ['3. Run NDIS Tracker > Process active Mable import.'],
    ['4. The import sheet will be rewritten to show only new rows. Older rows from the full-history CSV are ignored so they are not double-counted.'],
    [''],
    ['Budgets and categories'],
    ['Set weekly, monthly and annual budgets on Summary.'],
    ['Edit allowed categories in Config column A.'],
    ['Edit provider defaults in Config columns C:D.'],
    ['When a Category is selected for a Provider on Transactions or a processed import sheet, that Provider default is saved for future imports.'],
    [''],
    ['Spend basis'],
    ['Summary uses Total Cost Of Support. This is deliberate because recent unpaid Mable rows can have Paid Amount = $0.00.']
  ];

  sheet.getRange(1, 1, rows.length, 1).setValues(rows);
  sheet.getRange('A1').setFontWeight('bold').setFontSize(16).setBackground('#d9ead3');
  sheet.getRange('A3').setFontWeight('bold');
  sheet.getRange('A8').setFontWeight('bold');
  sheet.getRange('A14').setFontWeight('bold');
  sheet.getRange('A20').setFontWeight('bold');
  sheet.setColumnWidth(1, 900);
  sheet.getRange(1, 1, rows.length, 1).setWrap(true).setVerticalAlignment('top');
}

function readMableRecordsFromSheet_(sheet) {
  const values = sheet.getDataRange().getDisplayValues();

  if (values.length < 2) return [];

  const headerInfo = findMableHeaderInfo_(values);
  const headers = headerInfo.headers;
  const startRow = headerInfo.row + 1;
  const records = [];

  for (let r = startRow; r < values.length; r++) {
    const row = values[r];

    if (!row.some(function (value) { return String(value || '').trim() !== ''; })) {
      continue;
    }

    const participant = getByHeader_(row, headers, TRACKER.mableHeaders.participant);
    const provider = getByHeader_(row, headers, TRACKER.mableHeaders.provider);
    const startDate = parseMableDate_(getByHeader_(row, headers, TRACKER.mableHeaders.startDate));
    const endDate = parseMableDate_(getByHeader_(row, headers, TRACKER.mableHeaders.endDate));

    if (!participant || !provider || !startDate || !endDate) continue;

    const startTime = normaliseTime_(getByHeader_(row, headers, TRACKER.mableHeaders.startTime));
    const endTime = normaliseTime_(getByHeader_(row, headers, TRACKER.mableHeaders.endTime));
    const hours = parseNumber_(getByHeader_(row, headers, TRACKER.mableHeaders.hours));
    const agreedRate = parseCurrency_(getByHeader_(row, headers, TRACKER.mableHeaders.agreedRate));
    const totalCost = parseCurrency_(getByHeader_(row, headers, TRACKER.mableHeaders.totalCost));
    const paidAmount = parseCurrency_(getByHeader_(row, headers, TRACKER.mableHeaders.paidAmount));

    const record = {
      participant: participant,
      provider: provider,
      startDate: startDate,
      startTime: startTime,
      endDate: endDate,
      endTime: endTime,
      hours: hours,
      agreedRate: agreedRate,
      totalCost: totalCost,
      paidAmount: paidAmount
    };

    record.uniqueKey = makeUniqueKey_(record);
    records.push(record);
  }

  return records;
}

function findMableHeaderInfo_(values) {
  const required = Object.keys(TRACKER.mableHeaders).map(function (key) {
    return normaliseHeader_(TRACKER.mableHeaders[key]);
  });

  for (let r = 0; r < Math.min(values.length, 20); r++) {
    const headerMap = {};

    values[r].forEach(function (header, index) {
      const normalised = normaliseHeader_(header);

      if (normalised) {
        headerMap[normalised] = index;
      }
    });

    const foundAll = required.every(function (header) {
      return headerMap[header] !== undefined;
    });

    if (foundAll) {
      return { row: r, headers: headerMap };
    }
  }

  throw new Error('Could not find the expected Mable CSV headers on the active sheet.');
}

function getByHeader_(row, headerMap, headerName) {
  const index = headerMap[normaliseHeader_(headerName)];

  return index === undefined ? '' : String(row[index] || '').trim();
}

function readExistingTransactions_() {
  const sheet = getOrCreateSheet_(TRACKER.sheets.transactions);
  const lastRow = sheet.getLastRow();
  const result = { byKey: {} };

  if (lastRow < 2) return result;

  const values = sheet.getRange(2, 1, lastRow - 1, TRACKER.txHeaders.length).getValues();

  values.forEach(function (row, index) {
    const uniqueKey = String(row[13] || '').trim();

    if (!uniqueKey) return;

    result.byKey[uniqueKey] = {
      row: index + 2,
      category: String(row[12] || '').trim() || TRACKER.defaultCategory,
      totalCost: Number(row[10]) || 0,
      paidAmount: Number(row[11]) || 0
    };
  });

  return result;
}

function transactionAmountsDiffer_(existingTx, record) {
  return Math.abs((existingTx.totalCost || 0) - (record.totalCost || 0)) > 0.004 ||
    Math.abs((existingTx.paidAmount || 0) - (record.paidAmount || 0)) > 0.004;
}

function readConfigState_() {
  const sheet = getOrCreateSheet_(TRACKER.sheets.config);
  const lastRow = Math.max(sheet.getLastRow(), 2);
  const categoryValues = sheet.getRange(2, 1, lastRow - 1, 1).getDisplayValues();
  const providerValues = sheet.getRange(2, 3, lastRow - 1, 2).getDisplayValues();
  const categorySet = {};
  const providerDefaults = {};

  categoryValues.forEach(function (row) {
    const category = String(row[0] || '').trim();

    if (category) {
      categorySet[normaliseKey_(category)] = category;
    }
  });

  providerValues.forEach(function (row) {
    const provider = String(row[0] || '').trim();
    const category = String(row[1] || '').trim();

    if (provider && category) {
      providerDefaults[normaliseKey_(provider)] = category;
    }
  });

  TRACKER.defaultCategories.forEach(function (category) {
    categorySet[normaliseKey_(category)] = category;
  });

  return {
    categorySet: categorySet,
    providerDefaults: providerDefaults,
    providerAdditions: {}
  };
}

function writeConfigStateAdditions_(config) {
  const sheet = getOrCreateSheet_(TRACKER.sheets.config);
  const existingCategories = readCategories_();
  const existingCategoryKeys = {};
  const categoryAdditions = [];

  existingCategories.forEach(function (category) {
    existingCategoryKeys[normaliseKey_(category)] = true;
  });

  Object.keys(config.categorySet).forEach(function (key) {
    if (!existingCategoryKeys[key]) {
      categoryAdditions.push([config.categorySet[key]]);
      existingCategoryKeys[key] = true;
    }
  });

  if (categoryAdditions.length > 0) {
    const row = firstEmptyRowInColumn_(sheet, 1, 2);
    sheet.getRange(row, 1, categoryAdditions.length, 1).setValues(categoryAdditions);
  }

  const providerAdditions = Object.keys(config.providerAdditions).map(function (key) {
    return config.providerAdditions[key];
  });

  if (providerAdditions.length > 0) {
    const row = firstEmptyRowInColumn_(sheet, 3, 2);
    sheet.getRange(row, 3, providerAdditions.length, 2).setValues(providerAdditions);
  }
}

function rewriteProcessedImportSheet_(sheet, originalName, rows, sourceCount, duplicateCount, amountUpdateCount) {
  breakAllMerges_(sheet);
  sheet.clear();
  sheet.setFrozenRows(0);
  sheet.setFrozenColumns(0);

  const title = 'Processed Mable Import';
  const info = 'Original sheet: ' + originalName +
    ' | Rows in CSV: ' + sourceCount +
    ' | New rows kept here: ' + rows.length +
    ' | Existing rows discarded: ' + duplicateCount +
    ' | Existing amount updates: ' + amountUpdateCount;

  sheet.getRange(1, 1).setValue(title);
  sheet.getRange(2, 1).setValue(info);

  sheet.getRange(1, 1, 1, TRACKER.processedHeaders.length).merge().setFontWeight('bold').setFontSize(14).setBackground('#d9ead3');
  sheet.getRange(2, 1, 1, TRACKER.processedHeaders.length).merge().setWrap(true).setBackground('#f3f6f4');
  sheet.getRange(4, 1, 1, TRACKER.processedHeaders.length).setValues([TRACKER.processedHeaders]);
  sheet.getRange(4, 1, 1, TRACKER.processedHeaders.length).setFontWeight('bold').setBackground('#b6d7a8');

  if (rows.length > 0) {
    sheet.getRange(5, 1, rows.length, TRACKER.processedHeaders.length).setValues(rows);
    sheet.getRange(5, 3, rows.length, 1).setNumberFormat('dd/mm/yyyy');
    sheet.getRange(5, 5, rows.length, 1).setNumberFormat('dd/mm/yyyy');
    sheet.getRange(5, 8, rows.length, 3).setNumberFormat('$#,##0.00;-$#,##0.00;');
  }

  sheet.setFrozenRows(4);
  sheet.setColumnWidths(1, TRACKER.processedHeaders.length, 145);
  sheet.setColumnWidth(2, 260);
  sheet.setColumnWidth(11, 280);

  try {
    sheet.hideColumns(12);
  } catch (err) {}
}

function logImportStarted_(importId, sourceName, records) {
  initialiseImportLog_();

  const sheet = getOrCreateSheet_(TRACKER.sheets.importLog);
  const dates = records.map(function (record) { return record.startDate; }).filter(Boolean);
  const minDate = dates.length ? new Date(Math.min.apply(null, dates.map(Number))) : '';
  const maxDate = dates.length ? new Date(Math.max.apply(null, dates.map(Number))) : '';
  const row = sheet.getLastRow() + 1;

  sheet.getRange(row, 1, 1, TRACKER.importLogHeaders.length).setValues([[
    importId,
    new Date(),
    sourceName,
    'Running',
    records.length,
    '',
    '',
    '',
    minDate,
    maxDate,
    ''
  ]]);

  return row;
}

function updateImportLogFinished_(row, rowsInCsv, newRows, duplicateCount, amountUpdateCount, message) {
  const sheet = getOrCreateSheet_(TRACKER.sheets.importLog);

  sheet.getRange(row, 4, 1, 8).setValues([[
    'Complete',
    rowsInCsv,
    newRows,
    duplicateCount,
    amountUpdateCount,
    sheet.getRange(row, 9).getValue(),
    sheet.getRange(row, 10).getValue(),
    message || ''
  ]]);
}

function updateImportLogError_(row, err) {
  const sheet = getOrCreateSheet_(TRACKER.sheets.importLog);

  sheet.getRange(row, 4).setValue('Error');
  sheet.getRange(row, 11).setValue(err && err.message ? err.message : String(err));
}

function applyCoreCategoryDropdowns_() {
  const rule = getCategoryValidationRule_();
  const tx = getOrCreateSheet_(TRACKER.sheets.transactions);
  const config = getOrCreateSheet_(TRACKER.sheets.config);
  const lastTxRow = Math.max(tx.getLastRow(), 2);

  tx.getRange(2, 13, Math.max(lastTxRow - 1, 1), 1).setDataValidation(rule);
  config.getRange('D2:D1000').setDataValidation(rule);
}

function applyCategoryDropdownToProcessedSheet_(sheet) {
  const headers = getHeaderMap_(sheet);

  if (!headers['category'] || !headers['unique key']) return;

  const lastRow = sheet.getLastRow();
  const firstDataRow = sheet.getName() === TRACKER.sheets.transactions ? 2 : 5;

  if (lastRow < firstDataRow) return;

  sheet.getRange(firstDataRow, headers['category'], lastRow - firstDataRow + 1, 1).setDataValidation(getCategoryValidationRule_());
}

function getCategoryValidationRule_() {
  const config = getOrCreateSheet_(TRACKER.sheets.config);

  return SpreadsheetApp.newDataValidation()
    .requireValueInRange(config.getRange('A2:A'), true)
    .setAllowInvalid(false)
    .build();
}

function readCategories_() {
  const sheet = getOrCreateSheet_(TRACKER.sheets.config);
  const lastRow = Math.max(sheet.getLastRow(), 2);
  const values = sheet.getRange(2, 1, lastRow - 1, 1).getDisplayValues();
  const categories = [];

  values.forEach(function (row) {
    const category = String(row[0] || '').trim();

    if (category && categories.indexOf(category) === -1) {
      categories.push(category);
    }
  });

  return categories;
}

function ensureCategory_(category) {
  const cleanCategory = String(category || '').trim();

  if (!cleanCategory) return;

  const sheet = getOrCreateSheet_(TRACKER.sheets.config);
  const categories = readCategories_();

  if (categories.indexOf(cleanCategory) !== -1) return;

  const row = firstEmptyRowInColumn_(sheet, 1, 2);
  sheet.getRange(row, 1).setValue(cleanCategory);
}

function readProviderDefaults_() {
  const sheet = getOrCreateSheet_(TRACKER.sheets.config);
  const lastRow = Math.max(sheet.getLastRow(), 2);
  const values = sheet.getRange(2, 3, lastRow - 1, 2).getDisplayValues();
  const map = {};

  values.forEach(function (row) {
    const provider = String(row[0] || '').trim();
    const category = String(row[1] || '').trim();

    if (provider && category) {
      map[normaliseKey_(provider)] = category;
    }
  });

  return map;
}

function ensureProviderDefault_(provider, category) {
  const cleanProvider = String(provider || '').trim();
  const cleanCategory = String(category || TRACKER.defaultCategory).trim() || TRACKER.defaultCategory;

  if (!cleanProvider) return;

  ensureCategory_(cleanCategory);

  const sheet = getOrCreateSheet_(TRACKER.sheets.config);
  const lastRow = Math.max(sheet.getLastRow(), 2);
  const values = sheet.getRange(2, 3, lastRow - 1, 2).getDisplayValues();
  const providerKey = normaliseKey_(cleanProvider);

  for (let i = 0; i < values.length; i++) {
    if (normaliseKey_(values[i][0]) === providerKey) {
      if (!String(values[i][1] || '').trim()) {
        sheet.getRange(i + 2, 4).setValue(cleanCategory);
      }

      return;
    }
  }

  const row = firstEmptyRowInColumn_(sheet, 3, 2);
  sheet.getRange(row, 3, 1, 2).setValues([[cleanProvider, cleanCategory]]);
}

function updateTransactionCategoryByUniqueKey_(uniqueKey, category) {
  const tx = getOrCreateSheet_(TRACKER.sheets.transactions);
  const lastRow = tx.getLastRow();

  if (lastRow < 2) return;

  const keys = tx.getRange(2, 14, lastRow - 1, 1).getDisplayValues();

  for (let i = 0; i < keys.length; i++) {
    if (String(keys[i][0] || '').trim() === uniqueKey) {
      tx.getRange(i + 2, 13).setValue(category);
      return;
    }
  }
}

function updateUncategorisedTransactionsForProvider_(provider, category) {
  const tx = getOrCreateSheet_(TRACKER.sheets.transactions);
  const lastRow = tx.getLastRow();

  if (lastRow < 2) return;

  const providerKey = normaliseKey_(provider);
  const values = tx.getRange(2, 1, lastRow - 1, TRACKER.txHeaders.length).getValues();

  let changed = false;

  values.forEach(function (row) {
    if (normaliseKey_(row[3]) === providerKey && isBlankOrUncategorised_(row[12])) {
      row[12] = category;
      changed = true;
    }
  });

  if (changed) {
    tx.getRange(2, 1, values.length, TRACKER.txHeaders.length).setValues(values);
  }
}

function isBlankOrUncategorised_(category) {
  const value = String(category || '').trim();

  return value === '' || normaliseKey_(value) === normaliseKey_(TRACKER.defaultCategory);
}

function readExistingSummaryBudgets_(sheet) {
  const budgets = {};
  const lastRow = sheet.getLastRow();

  if (lastRow < 6) return budgets;

  const values = sheet.getRange(6, 1, lastRow - 5, 12).getValues();

  values.forEach(function (row) {
    const category = String(row[0] || '').trim();

    if (!category || category === 'TOTAL') return;

    budgets[category] = {
      weekly: row[1] || '',
      monthly: row[6] || '',
      annual: row[11] || ''
    };
  });

  return budgets;
}

function sortTransactions_() {
  const sheet = getOrCreateSheet_(TRACKER.sheets.transactions);
  const lastRow = sheet.getLastRow();

  if (lastRow < 3) return;

  sheet.getRange(2, 1, lastRow - 1, TRACKER.txHeaders.length).sort([
    { column: 5, ascending: false },
    { column: 6, ascending: false },
    { column: 4, ascending: true }
  ]);
}

function formatTransactionsSheet_() {
  const sheet = getOrCreateSheet_(TRACKER.sheets.transactions);

  sheet.getRange(1, 1, 1, TRACKER.txHeaders.length).setFontWeight('bold').setBackground('#d9ead3').setWrap(true);
  sheet.setFrozenRows(1);
  sheet.setColumnWidths(1, TRACKER.txHeaders.length, 135);
  sheet.setColumnWidth(4, 260);
  sheet.setColumnWidth(13, 280);
  sheet.getRange('B:B').setNumberFormat('dd/mm/yyyy hh:mm');
  sheet.getRange('E:E').setNumberFormat('dd/mm/yyyy');
  sheet.getRange('G:G').setNumberFormat('dd/mm/yyyy');
  sheet.getRange('I:I').setNumberFormat('0.00');
  sheet.getRange('J:L').setNumberFormat('$#,##0.00;-$#,##0.00;');
  sheet.getRange('P:Q').setNumberFormat('dd/mm/yyyy');

  try {
    sheet.hideColumns(14);
  } catch (err) {}
}

function applySummaryConditionalFormatting_(sheet, firstDataRow, rowCount) {
  const rules = [];

  const negativeRemainingRule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberLessThan(0)
    .setBackground('#f4cccc')
    .setRanges([
      sheet.getRange(firstDataRow, 6, rowCount, 1),
      sheet.getRange(firstDataRow, 11, rowCount, 1),
      sheet.getRange(firstDataRow, 16, rowCount, 1)
    ])
    .build();

  rules.push(negativeRemainingRule);
  sheet.setConditionalFormatRules(rules);
}

function formulaThisWeek_(row) {
  return '=IF($A' + row + '="","",SUMIFS(Transactions!$K:$K,Transactions!$M:$M,$A' + row + ',Transactions!$E:$E,">="&(TODAY()-WEEKDAY(TODAY(),2)+1),Transactions!$E:$E,"<"&(TODAY()-WEEKDAY(TODAY(),2)+8)))';
}

function formulaPreviousWeek_(row) {
  return '=IF($A' + row + '="","",SUMIFS(Transactions!$K:$K,Transactions!$M:$M,$A' + row + ',Transactions!$E:$E,">="&(TODAY()-WEEKDAY(TODAY(),2)-6),Transactions!$E:$E,"<"&(TODAY()-WEEKDAY(TODAY(),2)+1)))';
}

function formulaThisMonth_(row) {
  return '=IF($A' + row + '="","",SUMIFS(Transactions!$K:$K,Transactions!$M:$M,$A' + row + ',Transactions!$E:$E,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),Transactions!$E:$E,"<"&EDATE(DATE(YEAR(TODAY()),MONTH(TODAY()),1),1)))';
}

function formulaPreviousMonth_(row) {
  return '=IF($A' + row + '="","",SUMIFS(Transactions!$K:$K,Transactions!$M:$M,$A' + row + ',Transactions!$E:$E,">="&EDATE(DATE(YEAR(TODAY()),MONTH(TODAY()),1),-1),Transactions!$E:$E,"<"&DATE(YEAR(TODAY()),MONTH(TODAY()),1)))';
}

function formulaYtd_(row) {
  return '=IF($A' + row + '="","",SUMIFS(Transactions!$K:$K,Transactions!$M:$M,$A' + row + ',Transactions!$E:$E,">="&DATE(YEAR(TODAY()),1,1),Transactions!$E:$E,"<"&TODAY()+1))';
}

function formulaPreviousYtd_(row) {
  return '=IF($A' + row + '="","",SUMIFS(Transactions!$K:$K,Transactions!$M:$M,$A' + row + ',Transactions!$E:$E,">="&DATE(YEAR(TODAY())-1,1,1),Transactions!$E:$E,"<"&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))+1))';
}

function formulaRolling12_(row) {
  return '=IF($A' + row + '="","",SUMIFS(Transactions!$K:$K,Transactions!$M:$M,$A' + row + ',Transactions!$E:$E,">="&EDATE(TODAY(),-12),Transactions!$E:$E,"<"&TODAY()+1))';
}

function formulaPreviousRolling12_(row) {
  return '=IF($A' + row + '="","",SUMIFS(Transactions!$K:$K,Transactions!$M:$M,$A' + row + ',Transactions!$E:$E,">="&EDATE(TODAY(),-24),Transactions!$E:$E,"<"&EDATE(TODAY(),-12)))';
}

function makeUniqueKey_(record) {
  return [
    record.participant,
    record.provider,
    formatDateKey_(record.startDate),
    record.startTime,
    formatDateKey_(record.endDate),
    record.endTime,
    String(record.hours)
  ].map(function (part) {
    return String(part || '').trim().replace(/\s+/g, ' ');
  }).join('|');
}

function parseMableDate_(value) {
  if (value instanceof Date && !isNaN(value.getTime())) {
    return new Date(value.getFullYear(), value.getMonth(), value.getDate());
  }

  const s = String(value || '').trim();

  if (!s) return null;

  const m = s.match(/^(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{2,4})$/);

  if (m) {
    const day = Number(m[1]);
    const month = Number(m[2]) - 1;
    let year = Number(m[3]);

    if (year < 100) {
      year += 2000;
    }

    return new Date(year, month, day);
  }

  const parsed = new Date(s);

  if (!isNaN(parsed.getTime())) {
    return new Date(parsed.getFullYear(), parsed.getMonth(), parsed.getDate());
  }

  return null;
}

function normaliseTime_(value) {
  const s = String(value || '').trim();
  const m = s.match(/^(\d{1,2}):(\d{2})/);

  if (!m) return s;

  return String(m[1]).padStart(2, '0') + ':' + m[2];
}

function parseCurrency_(value) {
  if (typeof value === 'number') return value;

  const s = String(value || '').replace(/,/g, '').replace(/[^0-9.\-]/g, '');

  if (!s || s === '-' || s === '.') return 0;

  return Number(s) || 0;
}

function parseNumber_(value) {
  if (typeof value === 'number') return value;

  const s = String(value || '').replace(/,/g, '').trim();

  return Number(s) || 0;
}

function getWeekStart_(date) {
  const d = new Date(date.getFullYear(), date.getMonth(), date.getDate());
  const daysSinceMonday = (d.getDay() + 6) % 7;

  d.setDate(d.getDate() - daysSinceMonday);

  return d;
}

function getMonthStart_(date) {
  return new Date(date.getFullYear(), date.getMonth(), 1);
}

function formatDateKey_(date) {
  return Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy-MM-dd');
}

function normaliseHeader_(value) {
  return String(value || '')
    .replace(/\u00a0/g, ' ')
    .trim()
    .toLowerCase()
    .replace(/\s+/g, ' ');
}

function normaliseKey_(value) {
  return String(value || '')
    .replace(/\u00a0/g, ' ')
    .trim()
    .toLowerCase()
    .replace(/\s+/g, ' ');
}

function getHeaderMap_(sheet) {
  const lastColumn = Math.max(sheet.getLastColumn(), 1);
  const possibleHeaderRows = [1, 4];

  for (let i = 0; i < possibleHeaderRows.length; i++) {
    const rowNumber = possibleHeaderRows[i];

    if (sheet.getLastRow() < rowNumber) continue;

    const headers = sheet.getRange(rowNumber, 1, 1, lastColumn).getDisplayValues()[0];
    const map = {};

    headers.forEach(function (header, index) {
      const normalised = normaliseHeader_(header);

      if (normalised) {
        map[normalised] = index + 1;
      }
    });

    if (map['category']) return map;
  }

  return {};
}

function getOrCreateSheet_(name) {
  const ss = SpreadsheetApp.getActive();

  return ss.getSheetByName(name) || ss.insertSheet(name);
}

function isCoreSheet_(name) {
  return Object.keys(TRACKER.sheets).some(function (key) {
    return TRACKER.sheets[key] === name;
  });
}

function firstEmptyRowInColumn_(sheet, column, startRow) {
  const maxRows = Math.max(sheet.getMaxRows(), startRow);
  const values = sheet.getRange(startRow, column, maxRows - startRow + 1, 1).getDisplayValues();

  for (let i = 0; i < values.length; i++) {
    if (!String(values[i][0] || '').trim()) {
      return startRow + i;
    }
  }

  sheet.insertRowsAfter(maxRows, 100);

  return maxRows + 1;
}

function columnToLetter_(column) {
  let temp = '';
  let letter = '';

  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }

  return letter;
}

function breakAllMerges_(sheet) {
  try {
    const range = sheet.getDataRange();

    if (range) {
      range.breakApart();
    }
  } catch (err) {
    // Ignore merge cleanup failures on empty/new sheets.
  }
}