How to fix formula parse error after "setformula" in google sheets?

Question

I am having issues with formula parse error after using the setformula in google script.

I have used the \ to escape double quotes " in the code, however after running the script the parse error shows on the formula. The formula itself should be correct because if i copy and paste manually everything works. I can either try to edit the formula in the "function line" after adding one space the formula parse error disappear.

Matt

matvyr.setFormula("=SUMIFS('Náklady'!G:G;'Náklady'!A:A; CONCATENATE(AG1; INDIRECT(\"RC[-31]\";FALSE));'Náklady'!H:H;AG7)");

Screenshot of Error before editing the formula

Just one space added to the formula code

Answer 1

Not directly related to the question, but if one is setting formulas in batch with setFormulasR1C1, and one or more of the formulas are missing (not set, for example), simply mapping getFormulasR1C1 to setFormulasR1C1 will cause the formula parsing #ERROR.

To solve this, one has to intercept cell values. As cells without formulas are represented by empty strings, one can take advantage of the || operator. What follows is a sample utility for copying formulas preserving cell references:

const copyFormulas = ({ source, target, indices = [], onError = console.warn } = {}) => {

    try {

        const valsToCopy = source.getValues();
        const toCopy = source.getFormulasR1C1();
        const toLeave = target.getFormulasR1C1();

        const copied = toCopy.map((row, ri) => row.map((cell, ci) => {
            if (!indices.includes(ci)) {
                return toLeave[ri][ci] || valsToCopy[ri][ci];
            }

            return cell || valsToCopy[ri][ci];
        }));

        target.setValues(copied);

        SpreadsheetApp.flush();

        return true;
    }
    catch (error) {
        onError(error);
        return false;
    }

};

Answer 2

What happens if you convert the remaining A1 notations to R1C1 notation and then use setFormulaR1C1 instead of setFormula? – tehhowch 22 hours ago

This comment solves the problem. If I rewrite it to the R1C1 completely it works properly.

Answer 3

Use English for functions names.

The first screenshot in the question body shows non-English fucntions names for INDIRECT and FALSE but the second function shows them in English.

Apparently the problem is that the function name traslation is done by the Google Sheets UI and not by the Google Sheets engine on the server side.

Answer 4

@Coverdale, this works for me without hit enter

function myFunctionNew(){
  var cel = SpreadsheetApp.getCurrentCell()
  cel.setFormula(`=CONCAT(SUBSTITUTE(B2," ",""),LEFT(SUBSTITUTE(G2," ",""),3))`)
}

@Boomer, this works for me without hit enter

function myFunctionNew(){
  var cel = SpreadsheetApp.getCurrentCell()
  cel.setFormula(`=SUMIFS('Náklady'!G:G,'Náklady'!A:A, CONCATENATE(AG1, INDIRECT("RC[-31]",FALSE)),'Náklady'!H:H,AG7)`);
}