英文:
Can u help write a script to add numebr to cells?
问题
我需要一个脚本,可以应用于Google Sheets中每个工作表的AB4:AB范围。它可以跳过空单元格,并在以数字开头的每个单元格前面添加“1”,但跳过以“1”开头的单元格。谢谢你的帮助!
这是一个示例链接:https://docs.google.com/spreadsheets/d/12Ux4BdcvWbtCzh9HMS0XIshetaENvxizQY47K4Q8QNQ/edit?usp=sharing
英文:
I need a script that can be applied to the AB4:AB range of each sheet in Google Sheets. It can skip blank cells, and add "1" to the beginning of each cell that starts with a number, but skip the cells that start with "1". Thanks for ur help!
Here's a sample: https://docs.google.com/spreadsheets/d/12Ux4BdcvWbtCzh9HMS0XIshetaENvxizQY47K4Q8QNQ/edit?usp=sharing
答案1
得分: 0
我相信你的目标如下。
- 你在 Google 电子表格的所有工作表中的单元格 "AB4:AB" 中有值。
- 当值的第一个字母是数字而不是 "1" 时,你想将
1
添加到每个值。 - 你希望使用 Google Apps Script 实现这个目标。
在这种情况下,以下示例脚本如何?
示例脚本:
function myFunction() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheets = ss.getSheets();
sheets.forEach(sheet => {
const range = sheet.getRange("AB4:AB" + sheet.getLastRow());
const values = range.getDisplayValues().map(([e]) => [!isNaN(e[0]) && e[0] != "1" ? `1${e}` : e]);
range.setValues(values);
});
}
-
运行此脚本时,列 "AB" 将被上述预期结果覆盖。
-
如果你想通过脚本排除工作表,请测试以下脚本。在这种情况下,除了
excludeSheets
之外的工作表都会被使用。function myFunction() { const excludeSheets = ["sheet1",,,,]; // 请设置要排除的工作表名称。 const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheets = ss.getSheets(); sheets.forEach(sheet => { if (excludeSheets.includes(sheet.getSheetName())) { return; } const range = sheet.getRange("AB4:AB" + sheet.getLastRow()); const values = range.getDisplayValues().map(([e]) => [!isNaN(e[0]) && e[0] != "1" ? `1${e}` : e]); range.setValues(values); }); }
-
虽然我找不到你的问题,但如果你希望在第一个字母是
(
时也添加1
,请按照下面的方式修改。-
从
const values = range.getDisplayValues().map(([e]) => [!isNaN(e[0]) && e[0] != "1" ? `1${e}` : e]);
-
到
const values = range.getDisplayValues().map(([e]) => [((!isNaN(e[0]) && e[0] != "1") || e[0] == "(") ? `1${e}` : e]);
-
参考:
英文:
I believe your goal is as follows.
- You have values in cells "AB4:AB" in all sheets in a Google Spreadsheet.
- You want to add
1
to each value when the 1st letter of the value is the number and not1
. - You want to achieve this using Google Apps Script.
In this case, how about the following sample script?
Sample script:
function myFunction() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheets = ss.getSheets();
sheets.forEach(sheet => {
const range = sheet.getRange("AB4:AB" + sheet.getLastRow());
const values = range.getDisplayValues().map(([e]) => [!isNaN(e[0]) && e[0] != "1" ? `1${e}` : e]);
range.setValues(values);
});
}
-
When this script is run, the column "AB" is overwritten by the above-expected result.
-
If you want to exclude the sheet using the script, please test the following script. In this case, the sheets except for
excludeSheets
are used.function myFunction() { const excludeSheets = ["sheet1",,,]; // Please set the exclude sheet names you want. const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheets = ss.getSheets(); sheets.forEach(sheet => { if (excludeSheets.includes(sheet.getSheetName())) { return; } const range = sheet.getRange("AB4:AB" + sheet.getLastRow()); const values = range.getDisplayValues().map(([e]) => [!isNaN(e[0]) && e[0] != "1" ? `1${e}` : e]); range.setValues(values); }); }
-
Although I cannot find your question, if you want to also add
1
when the 1st letter is(
, please modify as follows.-
From
const values = range.getDisplayValues().map(([e]) => [!isNaN(e[0]) && e[0] != "1" ? `1${e}` : e]);
-
To
const values = range.getDisplayValues().map(([e]) => [((!isNaN(e[0]) && e[0] != "1") || e[0] == "(") ? `1${e}` : e]);
-
Reference:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论