英文:
Google Script Error .setFormula with REGEXREPLACE
问题
我正在尝试使用Google脚本替换电话号码中的连字符。
这是我的代码：
function FillFormulas() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('HubSpotSheet');
var lastRow = spreadsheet.getLastRow();
spreadsheet.getRange("F2").setFormula("=iferror(REGEXREPLACE(D2,"[[:punct:]]",""),D2)");
var fillDownRange = spreadsheet.getRange(2,6,(lastRow-1)); spreadsheet.getRange("F2").copyTo(fillDownRange);
}
然而，我遇到了一个错误 - 我应该如何编写公式以避免错误？
我尝试了不同的公式变体，但Google脚本也不接受其他变体。
英文:
I am trying to replace hyphens in phone numbers utilizing a Google Script.
Here is what I have:
function FillFormulas() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('HubSpotSheet');
var lastRow = spreadsheet.getLastRow();
spreadsheet.getRange("F2").setFormula("=iferror(REGEXREPLACE(D2,"[[:punct:]]", ""),D2)");
var fillDownRange = spreadsheet.getRange(2,6,(lastRow-1)); spreadsheet.getRange("F2").copyTo(fillDownRange);
}
However, I am getting an error - How do I write the formula in a way where I will not get an error?
I tried different variations of the formula, but Google Scripts did not accept the other variations either.
答案1
得分: 1
The problem is you need double quotes " within the formula so you can not start and end the formula with double quotes. Instead use single quotes '
Replace
spreadsheet.getRange("F2").setFormula("=iferror(REGEXREPLACE(D2,"[[:punct:]]", ""),D2)");
With
spreadsheet.getRange("F2").setFormula('=iferror(REGEXREPLACE(D2,"[[:punct:]]", ""),D2)');
英文:
The problem is you need double quotes " within the formula so you can not start and end the formula with double quotes. Instead use single quotes '
Replace
spreadsheet.getRange("F2").setFormula("=iferror(REGEXREPLACE(D2,"[[:punct:]]", ""),D2)");
With
spreadsheet.getRange("F2").setFormula('=iferror(REGEXREPLACE(D2,"[[:punct:]]", ""),D2)');
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论