CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Rynardt
Rynardt

Reputation: 5557

Google App Script: Trying to setFormula for Spreadsheet cell

I am trying to set the formula for a Google Spreadsheet cell using the cell.setFormula function.

This is my code from the function. The line of code of importance is the last one where I try to setFormula.

//Create new empty project sheet
function copySheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var temp = ss.getSheetByName("Project_Template");
  var name = Browser.inputBox("Create New Project", "Enter name for new project", Browser.Buttons.OK_CANCEL);
  if (name != "cancel") {
    ss.insertSheet(name,1, {template:temp})
    //Add project name to project code cell
    var newSheet = ss.getSheetByName(name);
    var cell = newSheet.getRange(3, 2);
    cell.setValue(name);
    //Update formula in placemarkers sheet
    var rowNum = ss.getNumSheets() - 3;
    var formulaSheet = ss.getSheetByName("Placemarkers");
    var formulaCell = formulaSheet.getRange(rowNum, 1);
    formulaCell.setFormula('=if(isna(filter('name'!AH3:AH3,'name'!AH3:AH3 <> ""))=true,"",filter('name'!AH3:AH3,'name'!AH3:AH3 <> ""))');
  }
}

When I try to save the script, I get the following error message:

Missing ) after argument list. (line 103)

I am sure it has to do with the combination of quotation and double quotation marks. I have not been able to get it working without an error message. Any help would be appreciated.

Upvotes: 2

Views: 36216

Answers (2)

Pavel Adamus
Pavel Adamus

Reputation: 21

.. it is not working to me.

cell.setFormula("=COUNTIF('name'!B14:B71,"G:*")");
cell.setFormula("=COUNTIF(' + name + '!B14:B71,"G:*")");

I guess that problem is with " in the setFormula's input.

It takes the " in the content of formula as a terminator of its parameter field and awaits ) symbol to enclose method call.

Solution:

Insert \ before " in the setFormula string:

cell.setFormula("=COUNTIF('name'!B14:B71,\"G:*\")");

_

Upvotes: 2

megabyte1024
megabyte1024

Reputation: 8650

The + operator is not inserted to join string and the name variable. The following code part fixes the problem

formulaCell.setFormula('=if(isna(filter(' + name + '!AH3:AH3,' + name + '!AH3:AH3 <> ""))=true,"",filter(' + name + '!AH3:AH3,' + name + '!AH3:AH3 <> ""))');

Upvotes: 8

Related Questions