PEG.js based parser for excel formula. It's only a simple prototype for evaluation. Test online at https://pegjs.org/online

excel-formula-parser.pegjs

// PEG.js based parser for excel formula.
// Executable on https://pegjs.org/online

{
    // Implementation of builtin core functionality and some demo stuff.
    // TODO: Remove demo stuff.
    const builtinFunctions = {
        /**
         * Returns a number (Demo function).
         * TODO: Remove demo function.
         * @returns {number}
         */
        "rnd": () => 5,

        /**
         * Returns boolean true.
         * @returns {boolean}
         */
        "true": () => true,

        /**
         * Returns boolean false.
         * @returns {boolean}
         */
        "false": () => false,

        /**
         * Returns today's date.
         * @returns {Date}
         */
        "today": () => new Date(),

        /**
         * Returns year of a date.
         * @param {Date} date
         * @returns {number}
         */
        "year": (date) => date.getFullYear(),

        /**
         * Returns month day of a date.
         * @param {Date} date
         * @returns {number}
         */
        "day": (date) => date.getDate(),

        /**
         * Returns date based on passed year month and day.
         * @param {number} year
         * @param {number} month
         * @param {number} day
         * @returns {Date}
         */
        "date": (year, month, day) => new Date(year, month - 1, day),
    };

    // Implementation of comparison operations.
    const comparisonFunctions = {
        /**
         * Compares if left and right value are equal.
         * @param {*} left
         * @param {*} right
         * @returns {boolean}
         */
        "=": (left, right) => left === right,

        /**
         * Compares if left and right value are not equal.
         * @param {*} left
         * @param {*} right
         * @returns {boolean}
         */
        "<>": (left, right) => left !== right,

        /**
         * Compares if left value is greater or equal with right value.
         * @param {*} left
         * @param {*} right
         * @returns {boolean}
         */
        "<=": (left, right) => left <= right,

        /**
         * Compares if left value is lower or equal with right value.
         * @param {*} left
         * @param {*} right
         * @returns {boolean}
         */
        ">=": (left, right) => left >= right,

        /**
         * Compares if left value is lower than right value.
         * @param {*} left
         * @param {*} right
         * @returns {boolean}
         */
        "<": (left, right) => left < right,

        /**
         * Compares if left value is greater than right value.
         * @param {*} left
         * @param {*} right
         * @returns {boolean}
         */
        ">": (left, right) => left > right,
    };

    /**
     * Resolves and calls builtin and by options provided functions.
     * @param {string} functionName
     * @param {Array} argumentList
     * @returns {*}
     */
    const callFunction = (functionName, argumentList) => {
        let result = null;

        if (builtinFunctions[functionName]) {
            result = builtinFunctions[functionName].apply({}, argumentList);
        } else if (options.functions && options.functions[functionName]) {
            result = options.functions[functionName].apply({}, argumentList);
        }

        return result;
    };

    /**
     * Resolves and calls comparison function based on comparison operator.
     * @param {string} comparisonOperator
     * @param {*} left
     * @param {*} right
     * @returns {boolean}
     */
    const callComparisonFunction = (comparisonOperator, left, right) => {
        let result = false;

        if (comparisonFunctions[comparisonOperator]) {
            result = comparisonFunctions[comparisonOperator].call({}, left, right);
        }

        return result;
    };

    /**
     * Resolves and returns variable value.
     * TODO: Return value is just for demo purposes and has to be replaced later.
     * @param {string} variableName
     * @returns {*}
     */
    const callVariable = (variableName) => {
        console.log(variableName);
        return 5;
    };

    /**
     * Calls console log function.
     * TODO: Console log call has to be deactivated or replaced in production mode later.
     */
    const debug = () => {
        //console.log.apply({}, arguments);
    };

    /**
     * Converts any value to string as represented by excel.
     * @param {*} value
     * @returns {string}
     */
    const toExcelString = (value) => {
        if (value === true) {
            value = "1";
        } else if (value === false) {
            value = "0";
        }
        return value;
    };

    /**
     * Returns a normalized identifier. E.g. an identifier could be a function name or a variable.
     * @param {string|array} identifier
     * @returns {string}
     */
    const normalizeIdentifier = (identifier) => {
        return identifier instanceof Array
            ? identifier.join("")
            : identifier.toLowerCase();
    };
}

ResultExpression
  = "="? _ expression:Expression _ {
      return expression;
   }

Expression
  = ComparativeExpressions

Literals
  = Boolean
  / Float 
  / Integer
  / String
  
GroupExpression
  = "(" _ expression:Expression _ ")" {
      return expression;
    }

PrimaryExpressions
  = ConditionalExpression
  / Literals
  / CallFunction
  / CallVariable
  / GroupExpression

PowerOperators
  = "^"

MultiplicativeOperators
  = "*"
  / "/"

AdditiveOperators
  = "+"
  / "-"

ConcatenativeOperators
  = "&"

ComparativeOperators
  = "="
  / "<>"
  / "<="
  / ">="
  / "<"
  / ">"

PowerExpression
  = head:PrimaryExpressions tail:(_ PowerOperators _ PrimaryExpressions)* {
      const result = tail.reduce((current, element) => {
        if (element[1] === "^") { return parseFloat((current ** element[3]).toFixed(15)); }
      }, head);
      debug("PowerExpressions", head, tail, result);
      return result;
    }

MultiplicativeExpressions
  = head:PowerExpression tail:(_ MultiplicativeOperators _ PowerExpression)* {
      const result = tail.reduce((current, element) => {
        if (element[1] === "*") { return parseFloat((current * element[3]).toFixed(15)); }
        if (element[1] === "/") { return parseFloat((current / element[3]).toFixed(15)); }
      }, head);
      debug("MultiplicativeExpressions", head, tail, result);
      return result;
    }

AdditiveExpressions
  = head:MultiplicativeExpressions tail:(_ AdditiveOperators _ MultiplicativeExpressions)* {
      const result = tail.reduce((current, element) => {
        if (element[1] === "+") { return parseFloat((current + element[3]).toFixed(15)); }
        if (element[1] === "-") { return parseFloat((current - element[3]).toFixed(15)); }
      }, head);
      debug("AdditiveExpressions", head, tail, result);
      return result;
    }

ConcatenativeExpressions
  = head:AdditiveExpressions tail:(_ ConcatenativeOperators _ AdditiveExpressions)* {
      const result = tail.reduce((current, element) => {
        return toExcelString(current) + "" + toExcelString(element[3]);
      }, head);
      debug("ConcatenateExpressions", head, tail, result);
      return result;
    }

ComparativeExpressions
  = head:ConcatenativeExpressions tail:(_ ComparativeOperators _ ConcatenativeExpressions)* {
      const result = tail.reduce((current, element) => {
        return callComparisonFunction(element[1], current, element[3]);
      }, head);
      debug("ComparisonExpression", head, tail, result);
      return result;
    }

_ "whitespace"
  = [ \t\n\r]*

Boolean
  = boolean:("true"i / "false"i) _ "()"? {
      return !!boolean;
    }

Float
  = [+-]? [0-9]+ "." [0-9]+ ("e"i [+-] [0-9]+)? {
  	  return parseFloat(text(), 10);
    }

Integer
  = [+-]? [0-9]+ ("e"i [+-] [0-9]+)? {
  	  return parseInt(text(), 10);
    }

String
  = _ '\"' str:([^'"\n]+) '\"' {
  	  return str.join("");
    }

Arguments
  = "(" argumentList:ArgumentList? ")" {
      return argumentList;
    }

ArgumentList
  = head:Expression argumentList:(_ "," _ Expression)* {
      const result = [head];
      for (const argument of argumentList) {
        result.push(argument[3]);
      }
      return result;
    }

ConditionalExpression
  = _ "if"i _ argumentList:Arguments {
      let result = null;
      if (!!argumentList[0]) {
        result = argumentList[1];
      } else {
        result = argumentList[2];
      }
      debug("ConditionalExpression", result);
      return result;
    }

CallFunction
  = _ functionName:[a-zA-Z0-9]+ _ argumentList:Arguments {
      functionName = normalizeIdentifier(functionName);
      const result = callFunction(functionName, argumentList);
      debug("FunctionExpression", functionName, result);
      return result;
    }

CallVariable
  = _ variableName:[a-zA-Z][a-zA-Z0-9!$]+ {
      variableName = normalizeIdentifier(variableName);
      const result = callVariable(variableName);
      debug("CallVariable", variableName, result);
      return result;
    }