CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
Moderate Dionysianism
Moderate Dionysianism

Reputation: 13

Formula parse error after “setformula” in google sheets

I got stuck with formula parse error after using the setFormula in google script. Here's my function:

function CreateLookupID(){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetforconcat = spreadsheet.getSheetByName("REPORT");

    sheetforconcat.insertColumnBefore(1);

    var lastrow = sheetforconcat.getLastRow();
    var idrange = sheetforconcat.getRange(2,1,lastrow);
    idrange.setFormula('=CONCAT(SUBSTITUTE(B2," ",""),LEFT(SUBSTITUTE(G2," ",""),3)');
};

It produces this: parse error

I just need to stand in the formula bar and hit "Enter" for it to work: works when I hit enter

Tried rewriting in R1C1 notation, but then in stopped working at all even 'manually': error

I also tried replacing commas with semicolons - it didn't help.
What am I missing here?

Upvotes: 1

Views: 512

Answers (1)

CMB
CMB

Reputation: 5163

The formula in the script lacks a closing parenthesis:

function CreateLookupID(){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetforconcat = spreadsheet.getSheetByName("REPORT");

    sheetforconcat.insertColumnBefore(1);

    var lastrow = sheetforconcat.getLastRow();
    var idrange = sheetforconcat.getRange(2,1,lastrow);
    idrange.setFormula('=CONCAT(SUBSTITUTE(B2," ",""),LEFT(SUBSTITUTE(G2," ",""),3))');
};

Entering the formula on the cell resolves the missing ) automatically, but the script would not.

Applying in sample sheet:

enter image description here

Upvotes: 2

Related Questions