Google App脚本:尝试为电子表格单元格设置setFormula [英] Google App Script: Trying to setFormula for Spreadsheet cell
问题描述
我正在尝试使用cell.setFormula
函数为Google Spreadsheet单元格设置公式.
I am trying to set the formula for a Google Spreadsheet cell using the cell.setFormula
function.
这是我在函数中的代码.重要的代码行是我尝试setFormula
的最后一个.
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.
推荐答案
未插入+
运算符以连接字符串和name
变量.以下代码部分解决了该问题
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 <> ""))');
这篇关于Google App脚本:尝试为电子表格单元格设置setFormula的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!