google-sheets-macros - 递增字符串值谷歌表
问题描述
使用以下代码,我无法将谷歌表格中的值增加为加一。
function incrementCellValuesByOne() {
// Increments the values in all the cells in the active range (i.e., selected cells).
// Numbers increase by one, text strings get a "1" appended.
// Cells that contain a formula are ignored.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var activeRange = ss.getActiveRange();
var cell, cellValue, cellFormula;
// iterate through all cells in the active range
for (var cellRow = 1; cellRow <= activeRange.getHeight(); cellRow++) {
for (var cellColumn = 1; cellColumn <= activeRange.getWidth(); cellColumn++) {
cell = activeRange.getCell(cellRow, cellColumn);
cellFormula = cell.getFormula();
// if not a formula, increment numbers by one, or add "1" to text strings
// if the leftmost character is "=", it contains a formula and is ignored
// otherwise, the cell contains a constant and is safe to increment
// does not work correctly with cells that start with '=
if (cellFormula[0] != "=") {
cellValue = cell.getValue();
cellValue =+cellValue
cell.setValue(cellValue + 1);
}
}
}
}
例如“personalDataDOB_3”需要变为“personalDaTaDOB_4”我正在寻找一种快速的方法来做到这一点,因为现在我需要通过键入来替换值。
解决方案
您想将某个单元格的“personalDataDOB_3”修改为“personalDaTaDOB_4”。如果我的理解是正确的,那么这个修改呢?
修改点:
- 当检索到的“personalDataDOB_3”使用 转换为数字
cellValue =+cellValue
时,NaN
返回。所以即使加了 1,结果也是NaN
. - 如果要修改的字符串格式始终是“personalDataDOB_#”,那么用 分隔字符串怎么样
_
?
为了反映以上几点,请按如下方式修改您的脚本。
从 :
if (cellFormula[0] != "=") {
cellValue = cell.getValue();
cellValue =+cellValue
cell.setValue(cellValue + 1);
}
至 :
if (cellFormula[0] != "=") {
cellValue = cell.getValue();
var temp = cellValue.split("_"); // Added
temp[1] = Number(temp[1]) + 1; // Added
cell.setValue(temp.join("_")); // Modified
}
笔记 :
- 如果您要修改的字符串格式总是改变,请告诉我。
如果我误解了你的问题,我很抱歉。
推荐阅读
- linux - 系统调用 | 阅读 | 写 | 打开 | 关闭 | Linux
- c# - C# 中的 RegExp 从电子邮件正文中获取电子邮件中的主题行
- drake - 你如何在 Drake 的自定义植物中设置输入端口?
- android - how to query a specific string from a list of string in Android RoomDB
- arrays - How can I parse this nested JSON? - error
- django - Django - cascade on other condition
- python - How do I get the usable screen resolution?
- c - Recursively removing duplicate characters in a string
- amazon-web-services - What user am I when I upload a file to a S3 bucket with the AWS CLI?
- c++ - need help reading file contents into an array C++