如何将UI弹窗响应值写入Google Sheets指定列的空白单元格？

核心思路 #

1. 定位指定列的下一个空白行：针对目标列单独计算最后有数据的行，避免依赖整张表的getLastRow()，确保写入位置精准。
2. 批量写入替代单个setValue：用setValues()一次性写入多列数据，比多次调用setValue更高效，同时规避单元格定位错误引发的语法问题。
3. 优化弹窗逻辑：简化嵌套结构，每一步弹窗后立即判断用户操作，避免因变量作用域导致的未定义错误。

完整实现代码 #

function addData() {
  const ui = SpreadsheetApp.getUi();
  const sheet = SpreadsheetApp.getActiveSheet();
  
  // 辅助函数：获取指定列的下一个空白行号
  const getNextEmptyRow = (columnNum) => {
    const range = sheet.getRange(1, columnNum, sheet.getLastRow());
    const values = range.getValues();
    for (let i = values.length - 1; i >= 0; i--) {
      if (values[i][0] !== "") return i + 2; // 返回下一行的行号
    }
    return 1; // 列无数据时从第一行开始写入
  };

  // 第一组弹窗：收集F-I列数据
  const resultF = ui.prompt('Add Data', '请输入F列内容', ui.ButtonSet.OK);
  if (resultF.getSelectedButton() !== ui.Button.OK) {
    ui.alert('弹窗已关闭');
    return;
  }
  const textF = resultF.getResponseText();

  const resultG = ui.prompt('Enter Base Data', '请输入G列内容', ui.ButtonSet.OK_CANCEL);
  if (resultG.getSelectedButton() !== ui.Button.OK) {
    ui.alert('已取消数据添加');
    return;
  }
  const textG = resultG.getResponseText();

  const resultH = ui.prompt('Enter Input', '请输入H列内容', ui.ButtonSet.OK_CANCEL);
  if (resultH.getSelectedButton() !== ui.Button.OK) {
    ui.alert('已取消数据添加');
    return;
  }
  const textH = resultH.getResponseText();

  const resultI = ui.prompt('Enter Summary', '请输入I列内容', ui.ButtonSet.OK_CANCEL);
  if (resultI.getSelectedButton() !== ui.Button.OK) {
    ui.alert('已取消数据添加');
    return;
  }
  const textI = resultI.getResponseText();

  // 写入F-I列
  const targetRowF = getNextEmptyRow(6); // 6对应F列
  sheet.getRange(targetRowF, 6, 1, 4).setValues([[textF, textG, textH, textI]]);

  // 第二组弹窗：收集L-M列数据
  const resultL = ui.prompt('Add L Column Data', '请输入L列内容', ui.ButtonSet.OK_CANCEL);
  if (resultL.getSelectedButton() !== ui.Button.OK) {
    ui.alert('已取消L-M列数据添加');
    return;
  }
  const textL = resultL.getResponseText();

  const resultM = ui.prompt('Add M Column Data', '请输入M列内容', ui.ButtonSet.OK_CANCEL);
  if (resultM.getSelectedButton() !== ui.Button.OK) {
    ui.alert('已取消L-M列数据添加');
    return;
  }
  const textM = resultM.getResponseText();

  // 写入L-M列
  const targetRowL = getNextEmptyRow(12); // 12对应L列
  sheet.getRange(targetRowL, 12, 1, 2).setValues([[textL, textM]]);

  ui.alert('数据添加成功！');
}

关键说明 #

• getNextEmptyRow辅助函数：从目标列底部往上遍历，找到第一个非空单元格后返回下一行行号，确保写入到该列的首个空白位置。
• 批量写入逻辑：getRange(targetRow, startColumn, 1, numColumns)选中一行多列的区域，setValues([[val1, val2, ...]])通过二维数组一次性完成写入，减少脚本执行次数。
• 弹窗流程优化：每一步弹窗后立即校验用户操作，若取消则直接终止流程，避免深层嵌套导致的变量访问问题。

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