Sure, here’s the translation: 你能帮忙编写一个脚本来向单元格中添加数字吗?

huangapple go评论62阅读模式
英文:

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 not 1.
  • 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:

huangapple
  • 本文由 发表于 2023年5月7日 12:09:51
  • 转载请务必保留本文链接:https://go.coder-hub.com/76192162.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定