Add TemplateFormulaCell : Possibility to add a template for a custom formula

[Siemienik]

As @jasonclake requested on a Gitter channel

@Siemienik Is there a way to template a custom formula
I have a loop starting on row 2 running to row 6 (see image)

Transform something like

#! =SUMPRODUCT( B${test.__startOutput.r}:B${test.__endOutput.r}, C${test.__endOutput.r}:C${test.__endOutput.r}) / SUM(B${test.__startOutput.r}:B${test.__endOutput.r})

Into:

=SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6)

[Siemienik]

I see common assumptions with another proposal #152

[Siemienik]

I made the first research about using eval function and it looks promising

[jasonclake]

Found this technique on stackoverflow - not as flexible as eval() but would cause less security concern maybe? I have not tested or considered how it would fit in the scheme of things.

https://stackoverflow.com/questions/29182244/convert-a-string-to-a-template-string
From: https://stackoverflow.com/users/560114/matt-browne

const regex = /\${[^{]+}/g;

export default function interpolate(template, variables, fallback) {
    return template.replace(regex, (match) => {
        const path = match.slice(2, -1).trim();
        return getObjPath(path, variables, fallback);
    });
}

//get the specified property or nested property of an object
function getObjPath(path, obj, fallback = '') {
    return path.split('.').reduce((res, key) => res[key] || fallback, obj);
}

Usage

const replacements = {
   name: 'Bob',
   age: 37
}

interpolate('My name is ${name}, and I am ${age}.', replacements)

[Siemienik]

I move this issue into issues list and mark as important, that means I will do first in my free time.

The TemplateFormulaCell will be matched by:

#= [FORMULA_TEMPLATE]