如何用Apps Script将单元格值转整数并自动计算指定列差值？

原脚本存在的核心问题 #

• 误将数组元素当作Range对象调用setValues()：getValues()返回的是单元格值的二维数组，而非Range对象，无法使用Range的方法
• 逻辑未完全匹配需求：M列的计算逻辑与描述不符
• 逐单元格操作效率低下，应批量处理数据

修正后的代码 #

function calc() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName("Database");
  const range = sheet.getRange("datas");
  const values = range.getValues();

  for (let x = 0; x < values.length; x++) {
    // 转换为整数，避免字符串运算错误
    const balance = parseInt(values[x][7], 10); // H列：balance_qty
    const issue = parseInt(values[x][4], 10);   // E列：issue_qty
    const rPoint = parseInt(values[x][8], 10);  // I列：restock_point

    // 计算M列（剩余数量，索引12）
    let remainingQty;
    if (balance > rPoint) {
      remainingQty = balance - issue;
    } else {
      remainingQty = balance - rPoint;
    }
    values[x][12] = isNaN(remainingQty) ? "" : parseInt(remainingQty, 10);

    // 计算N列（补货数量，索引13）
    let restockQty = "";
    if (balance < issue) {
      restockQty = issue - balance;
    } else if (balance < 0) {
      restockQty = Math.abs(balance);
    }
    values[x][13] = isNaN(restockQty) ? "" : parseInt(restockQty, 10);
  }

  // 批量写回修改后的数据
  range.setValues(values);
}

关键修改说明 #

• 使用parseInt()强制转换为整数，确保数值运算的正确性
• 直接修改内存中的values数组，最后一次性写回表格，大幅提升执行效率
• 严格遵循需求逻辑：
  • M列：当balance_qty大于restock_point时，取balance_qty - issue_qty；否则取balance_qty - restock_point
  • N列：仅当balance_qty小于issue_qty时，计算issue_qty - balance_qty；额外处理balance_qty为负的情况
• 添加isNaN()判断，避免非数值数据导致的脚本错误

内容的提问来源于stack exchange，提问作者Coco