Google Sheets AppScript：基于G列值插入公式，空白时停止执行

需求说明 #

• 需通过AppScript为表格的B列和C列插入公式
• 脚本执行规则：仅在对应行G列存在值时运行，遇到G列空白行立即停止，直到G列输入新值后再继续执行
• 现有代码可插入正确公式，但未实现G列空白时停止的逻辑，且存在函数重名错误

现有代码（存在问题） #

const ss = SpreadsheetApp.getActiveSpreadsheet();
const tabRFQ = ss.getSheetByName('RFQ DATA ENTRY TEST');

const lastRow = tabRFQ.getDataRange().getValues().length;

const dataB = tabRFQ.getRange(3, 2, lastRow - 2, 1).getValues();
const dataC = tabRFQ.getRange(3, 3, lastRow - 2, 1).getValues();

function insertFormulaB() {
  dataB.forEach((row, i) => {
    if (row[0] === '') {
      let formula = `=IF(G${i + 2}=G${i + 3},B${i + 2},(B${i + 2}+1))`;
      tabRFQ.getRange(i + 3, 2).setValue(formula);
    }
  }
  )
};

function insertFormulaB() {
  dataB.forEach((row, i) => {
    if (row[0] === '') {
      let formula = `=IF(G${i + 2}=G${i + 3},C${i + 2},1))`;
      tabRFQ.getRange(i + 3, 3).setValue(formula);
    }
  }
  )
}

修改后的代码 #

function insertFormulaB() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const tabRFQ = ss.getSheetByName('RFQ DATA ENTRY TEST');
  const lastRow = tabRFQ.getLastRow();
  
  // 获取G列数据（从第2行开始到最后一行）
  const dataG = tabRFQ.getRange(2, 7, lastRow - 1, 1).getValues();
  const dataB = tabRFQ.getRange(2, 2, lastRow - 1, 1).getValues();

  for (let i = 1; i < dataG.length; i++) {
    // 检查当前行G列是否为空，为空则停止循环
    if (dataG[i][0] === '') {
      break;
    }
    // 仅当B列单元格为空时插入公式
    if (dataB[i][0] === '') {
      const formula = `=IF(G${i + 1}=G${i + 2},B${i + 1},(B${i + 1}+1))`;
      tabRFQ.getRange(i + 2, 2).setFormula(formula);
    }
  }
}

function insertFormulaC() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const tabRFQ = ss.getSheetByName('RFQ DATA ENTRY TEST');
  const lastRow = tabRFQ.getLastRow();
  
  // 获取G列数据（从第2行开始到最后一行）
  const dataG = tabRFQ.getRange(2, 7, lastRow - 1, 1).getValues();
  const dataC = tabRFQ.getRange(2, 3, lastRow - 1, 1).getValues();

  for (let i = 1; i < dataG.length; i++) {
    // 检查当前行G列是否为空，为空则停止循环
    if (dataG[i][0] === '') {
      break;
    }
    // 仅当C列单元格为空时插入公式
    if (dataC[i][0] === '') {
      // 修正原公式多余的右括号
      const formula = `=IF(G${i + 1}=G${i + 2},C${i + 1},1)`;
      tabRFQ.getRange(i + 2, 3).setFormula(formula);
    }
  }
}

关键修改说明 #

1. 修正函数重名问题：将第二个insertFormulaB改为insertFormulaC，避免执行冲突
2. 新增G列检查逻辑：遍历过程中先判断对应行G列值，为空则立即终止循环，符合停止执行的需求
3. 优化数据获取：将表格对象和数据读取逻辑移到函数内部，确保每次执行都获取最新数据，避免全局变量缓存旧数据
4. 修正公式错误：移除原C列公式中多余的右括号，解决公式语法报错问题
5. 改用for循环：forEach无法中途终止循环，改用for循环实现遇到空白行停止的逻辑

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