Notable md-calc custom js

md-calc.js

/*
Description: This function will evaluate and update markdown tables similar to excel
Just Copy and paste this gist into Notable's Custom JS Editor

Requirements: v1.9.0-alpha.6 or newer (older versions don't have the Custom JS Editor)

Table Example:
|Home Evaluation 2020||
| ----------------- | -------: |
| Tax Land Value | [C]=14726 |
| Tax Improvement Value | [C]=67700 |
| Tax Current Value | [C]=B1+B2 |
| Purchase Price | [C]=100000 |
| Home Value % | [P]{1}=B2/B3 |
| Home Value | [C]=B4*B5 |
| Land Value | [C]=B4*(1-B5) |
| Yearly Deduction | [C]=B6/27.5 |

Original Repo: https://github.com/kmccullen97/md-calc

Suported Features:
Basic Math: =(3+5)*2 -> 30
Cell References: =A1+B1
Formatting:
 - Currency: [C]=1156.54 -> $1,156.54
 - Percent: [P]=0.567 -> 56.7%
 - Decimal: {2}=3.14159 -> 3.14
*/

const mdCalc = function () {
  const generalRegex = /[{}[\]0-9CP]+?(?==)/g;
  const decimalRegex = /{([0-9]+?)}/g;
  const formatRegex = /\[([CP]+?)\]/g;
  const matchCellName = /[A-Z][0-9]+/g;
  const errorText = '!REF';

  const cellFormatParser = (cellValue) => {
    const formatSettings = {};
    const remainingCellValue = cellValue.replace(generalRegex, '');

    const match = cellValue.match(generalRegex);
    if (!match) {
      return [remainingCellValue, {}];
    }

    const formatText = match[0];

    const decimalMatch = formatText.match(decimalRegex);
    if (decimalMatch) {
      formatSettings.decimal = parseInt(
        decimalMatch[0].replace(/{|}/g, ''),
        10
      );
    }

    const formatMatch = formatText.match(formatRegex);
    if (formatMatch) {
      formatSettings.format = formatMatch[0].replace(/\[|\]/g, '');
    }

    return [remainingCellValue, formatSettings];
  };

  const formatCell = (cellValue, formats) => {
    let formattedCellValue = cellValue;

    if (formats.decimal && formats.format !== 'P') {
      formattedCellValue = cellValue.toFixed(formats.decimal);
    }

    if (formats.format) {
      switch (formats.format) {
        case 'C':
          formattedCellValue = formattedCellValue.toLocaleString('en-US', {
            style: 'currency',
            currency: 'USD',
          });
          break;
        case 'P': {
          let percent = formattedCellValue * 100;
          if (formats.decimal) {
            percent = percent.toFixed(formats.decimal);
          }
          formattedCellValue = `${percent}%`;
          break;
        }
        default:
          break;
      }
    }

    return formattedCellValue;
  };

  const convertPositionToCell = (row, col) =>
    String.fromCharCode(col + 65).toString() + (row + 1).toString();

  const convertCellToPosition = (cell) => {
    const col = cell.charCodeAt(0) - 65;
    const row = parseInt(cell.substr(1, cell.length - 1), 10) - 1;
    return [row, col];
  };

  const getValueByCell = (cell, table) => {
    if (table === null || table === undefined || table.length < 1) {
      throw new Error('Invalid table');
    }

    if (!cell.match(/^[A-Z][0-9]+/)) {
      throw new Error('Invalid cell');
    }

    const [row, col] = convertCellToPosition(cell);

    if (row > table.length - 1 || col > table[0].length - 1) {
      throw new Error('Invalid cell location');
    }

    return table[row][col];
  };

  const evaluateCell = (cell, table, prev = null, ref = false) => {
    let cellValue;
    try {
      cellValue = getValueByCell(cell, table);
    } catch (err) {
      return errorText;
    }

    if (typeof cellValue === 'number') {
      return cellValue;
    }

    if (!Number.isNaN(Number(cellValue))) {
      return parseFloat(cellValue);
    }

    if (cellValue.charAt(0) === '=') {
      const evaluatedCell = cellValue
        .replace(matchCellName, (match) => {
          if (match === cell || match === prev) {
            return errorText;
          }
          return evaluateCell(match, table, cell, true);
        })
        .replace('=', '');
      if (evaluatedCell.includes(errorText)) {
        return errorText;
      }
      return eval(evaluatedCell);
    }

    if (ref === true && typeof cellValue === 'string') {
      return errorText;
    }

    return cellValue;
  };

  const evaluate = (table) => {
    const updatedTable = table;
    updatedTable.forEach((row, i) => {
      row.forEach((_, j) => {
        const cell = convertPositionToCell(i, j);
        const newValue = evaluateCell(cell, updatedTable);
        updatedTable[i][j] = newValue;
      });
    });
    return updatedTable;
  };

  const getCellFormatting = (data) => {
    const newData = [];
    const formatting = [];

    data.forEach((row) => {
      const newDataRow = [];
      const formattingRow = [];
      row.forEach((cell) => {
        const [newCell, format] = cellFormatParser(cell);
        newDataRow.push(newCell);
        formattingRow.push(format);
      });
      newData.push(newDataRow);
      formatting.push(formattingRow);
    });

    return [newData, formatting];
  };

  const tables = document.getElementsByTagName('table');
  for (let i = 0; i < tables.length; i++) {
    const table = tables[i];
    const body = table.lastElementChild;

    const tableData = [];
    const rows = body.children;
    for (let j = 0; j < rows.length; j++) {
      const cells = rows[j].children;
      const newRow = [];
      for (let k = 0; k < cells.length; k++) {
        const cell = cells[k];
        newRow.push(cell.textContent);
      }
      tableData.push(newRow);
    }

    const [newData, formats] = getCellFormatting(tableData);

    const evaluatedData = evaluate(newData);

    const formattedData = evaluatedData.map((row, i) =>
      row.map((cell, j) => formatCell(cell, formats[i][j]))
    );

    for (let j = 0; j < rows.length; j++) {
      const cells = rows[j].children;
      for (let k = 0; k < cells.length; k++) {
        const cell = cells[k];
        cell.textContent = formattedData[j][k];
      }
    }
  }
};

document.addEventListener('preview:render', () => {
  mdCalc();
  console.log('md-calc ran.');
});