Google Sheets用户表单批量添加计算行问题求助

现有可正常运行的Google Sheets用户表单UI，原本仅支持添加1行输入数据。现在需要实现批量添加多行带计算后的数据：

• 第一行：保留原输入的基础数据
• 第二行：将id为incomeeur的输入值乘以Sheet2中H6单元格的百分比
• 第三行：将id为incomeeur的输入值乘以Sheet2中H7单元格的百分比

尝试过以下代码但未生效：

const ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet2');
const incomeeur3 = ws.getRange('H6').getValue();
const incomeeur2 = document.getElementById("incomeeur").getValue();
const incomeeur4 = incomeeur2 * incomeeur3;

现有代码文件 #

1. HTML文件代码 #

<!doctype html>
<html lang="en">
<body>
<div class="container">
<div>
<div class="form-group">
<label for="docnumber">№ doc</label>
<input type="text" class="form-control" id="docnumber" 
placeholder="Вул./місто/клієнт/виріб">
</div>

<div class="form-group">
<label for="whereandhow">sender</label>
<select class="form-control" id="whereandhow">
</select>
</div>

<div class="form-group">
<label for="comment">comment</label>
<input type="text" class="form-control" id="comment" placeholder="Введіть примітку">
</div>
<div class="form-group">
<label for="incomeeur">income, €</label>
<input type="text" class="form-control" id="incomeeur" placeholder="Cума, €">
</div>

<button class="btn btn-primary btn-lg btn-block" id="mainButton">Додати</button>
</div>
</div>

<script>
  function afterButtonClicked(){
  var docnumber1 = document.getElementById("docnumber");
  var whereandhow1 = document.getElementById("whereandhow");
  var comment1 = document.getElementById("comment");
  var incomeeur1 = document.getElementById("incomeeur");
  var incomeusd1 = document.getElementById("incomeusd");
  var incomeuah1 = document.getElementById("incomeuah");
  var eurrate1 = document.getElementById("eurrate");
  var usdrate1 = document.getElementById("usdrate");
  var expenseeur1 = document.getElementById("expenseeur");
  var expenseusd1 = document.getElementById("expenseusd");
  var expenseuah1 = document.getElementById("expenseuah");
  var expensetype1 = document.getElementById("expensetype");

  var rowData = {docnumber1: docnumber1.value,whereandhow1: whereandhow1.value,comment1: comment1.value,incomeeur4: incomeeur4.value,incomeusd1: incomeusd1.value,incomeuah1: incomeuah1.value,eurrate1: eurrate1.value,usdrate1: usdrate1.value,expenseeur1: expenseeur1.value,expenseusd1: expenseusd1.value,expenseuah1: expenseuah1.value,expensetype1: expensetype1.value};
google.script.run.withSuccessHandler(afterSubmit).addNewRow(rowData)
  }
 </script>
 </body>
 </html>

2. GS文件代码 #

function addNewRow(rowData) {
    const currentDate = Utilities.formatDate(new Date(), "GMT+2", "dd.MM.yy");
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const ws = ss.getSheetByName('sheet1');
    ws.appendRow([currentDate,rowData.docnumber1,rowData.whereandhow1,rowData.comment1,rowData.incomeeur4,rowData.incomeusd1,rowData.incomeuah1,rowData.eurrate1,rowData.usdrate1,rowData.expenseeur1,rowData.expenseusd1,rowData.expenseuah1,rowData.expensetype1]);
return true;
}

问题核心：你之前的代码错误地在客户端HTML脚本中调用了仅能在服务器端GS中运行的SpreadsheetAppAPI，导致代码失效。以下是修正后的完整实现：

步骤1：修改HTML脚本，获取输入值并请求服务器计算百分比 #

更新HTML中的afterButtonClicked函数，先获取输入值，再调用服务器端函数获取Sheet2的百分比，计算后生成3行数据提交：

<script>
  // 绑定按钮点击事件
  document.getElementById("mainButton").addEventListener("click", afterButtonClicked);
  
  function afterButtonClicked(){
    // 获取表单输入值
    const docnumber = document.getElementById("docnumber").value;
    const whereandhow = document.getElementById("whereandhow").value;
    const comment = document.getElementById("comment").value;
    const incomeeur = parseFloat(document.getElementById("incomeeur").value) || 0;
    
    // 调用服务器端函数获取Sheet2的百分比
    google.script.run.withSuccessHandler(function(rates) {
      // 生成3行待插入数据
      const rows = [
        // 第一行：原始输入数据
        {
          docnumber: docnumber,
          whereandhow: whereandhow,
          comment: comment,
          incomeeur: incomeeur
        },
        // 第二行：乘以H6单元格的百分比
        {
          docnumber: docnumber,
          whereandhow: whereandhow,
          comment: `${comment} (×${rates.h6Rate})`,
          incomeeur: incomeeur * rates.h6Rate
        },
        // 第三行：乘以H7单元格的百分比
        {
          docnumber: docnumber,
          whereandhow: whereandhow,
          comment: `${comment} (×${rates.h7Rate})`,
          incomeeur: incomeeur * rates.h7Rate
        }
      ];
      // 提交多行数据到服务器
      google.script.run.withSuccessHandler(afterSubmit).addMultipleRows(rows);
    }).getSheet2Rates();
  }
  
  // 提交成功后的回调函数
  function afterSubmit() {
    // 清空表单
    document.getElementById("docnumber").value = "";
    document.getElementById("comment").value = "";
    document.getElementById("incomeeur").value = "";
    alert("数据已批量添加！");
  }
</script>

步骤2：更新GS代码，添加获取百分比和批量插入行的函数 #

替换原有的addNewRow函数，新增两个服务器端函数：

// 获取Sheet2中H6和H7的百分比值
function getSheet2Rates() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName('sheet2');
  return {
    h6Rate: ws.getRange('H6').getValue() || 0,
    h7Rate: ws.getRange('H7').getValue() || 0
  };
}

// 批量插入多行数据到Sheet1
function addMultipleRows(rows) {
  const currentDate = Utilities.formatDate(new Date(), "GMT+2", "dd.MM.yy");
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName('sheet1');
  
  // 将每行数据转换为Sheet1所需的数组格式
  const rowsToAppend = rows.map(row => [
    currentDate,
    row.docnumber,
    row.whereandhow,
    row.comment,
    row.incomeeur,
    // 其他未用到的字段留空，可根据实际需求补充
    "", "", "", "", "", "", ""
  ]);
  
  // 批量插入数据（比多次调用appendRow效率更高）
  ws.getRange(ws.getLastRow() + 1, 1, rowsToAppend.length, rowsToAppend[0].length).setValues(rowsToAppend);
  
  return true;
}

关键说明 #

• 客户端HTML脚本无法直接访问Google Sheets API，必须通过google.script.run调用服务器端GS函数获取数据
• 使用setValues批量插入替代多次appendRow，大幅提升操作效率
• 增加了数值转换和默认值处理，避免空输入导致的计算错误

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