英文:
Replace text in a function in Google Sheet (with script)
问题
我试图替换表格中的字符串。对于所有文本值，它都运行正常，但是我在处理函数时出现问题。
我有一些导入的电话号码，但不幸的是，它们前面会有=+。
所以数值不是555-8181，而是=+555-8181。
当我尝试使用Replace时，它不起作用，可能是因为这些值存储为函数，而不是字符串，但我可能错了。
有什么提示可以提供吗？
我尝试过的示例：
function RemoveErrorInPhonenumber() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetWithNumbers");
var range = sheet.getRange("Z:AG");
var rngA = range.getValues();
for(var i=1;i<rngA.length;i++)
{
rngA[i][0]=rngA[i][0].toString().replace("+", "");
}
range.setValues(rngA);
};
英文:
I'm trying to replace strings in a sheet. It works just fine for all my text values, but I have a problem with functions.
I have phone numbers that are imported but unfortunately, I get a =+ placed in front of them.
So the values are not 555-8181, its =+555-8181.
When I try to use for example Replace it doesn't work, likely because the values are stored as a function, and not a string, but I can be wrong.
Any hints on what to do?
Example of what I have tried:
function RemoveErrorInPhonenumber() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetWithNumbers");
var range = sheet.getRange("Z:AG");
var rngA = range.getValues();
for(var i=1;i<rngA.length;i++)
{
rngA[i][0]=rngA[i][0].toString().replace("+", "");
}
range.setValues(rngA);
};
</details>
# 答案1
**得分**: 2
替换 `range.getValues();` 为 [`range.getFormulas();`][1]
----------------------------------------------------------------
然后实现
`rngA[i][0]=rngA[i][0].toString().replace(""+"", """).replace(""="", """);`
> 还有两个建议：
+ 如果要遍历所有列 Z:AG，需要创建两个嵌套的 `for 循环`：
```lang:js
for(var i=1;i<rngA.length;i++) {
for(var j=1;j<rngA.length;j++) {
rngA[i][j]=...
}
}
- 不要遍历整个范围
Z:AG- 这会使您的代码变得非常慢。而是遍历到最后包含数据的行：
var range = sheet.getRange(""Z1:AG"+sheet.getLastRow());
英文:
Replace range.getValues(); through range.getFormulas();
And then implement
rngA[i][0]=rngA[i][0].toString().replace("+", "").replace("=", "");
> Two more suggestions:
- If you want to loop through all of your columns Z:AG, you need to create two nested
for loops:
for(var i=1;i<rngA.length;i++) {
for(var j=1;j<rngA.length;j++) {
rngA[i][j]=...
}
}
- Do not loop through the whole range
Z:AG- this will make your code very slow. Instead, loop until the last data containing row:
var range = sheet.getRange("Z1:AG"+sheet.getLastRow());
答案2
得分: 0
建议将字符串转换为数值（但首先必须删除**-**）。
https://support.google.com/docs/answer/3094220?hl=en
https://support.google.com/docs/answer/3094285?hl=en
英文:
I suggest converting the strings into values (but you will have to remove the - first).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论