Google Apps Script中Hkey=456分支无法写入Google Sheets问题

我开发的应用可向Google Sheets发送数据并追加ID与时间戳，通过Hkey参数区分写入目标：

• Hkey=123时，数据写入Stocks工作表，同时写入冗余日志表
• Hkey=456时，数据写入Orders工作表，同时写入冗余日志表

目前Hkey=123分支运行正常，但Hkey=456分支无法将数据写入Google Sheets，代码如下：

function doGet(e) {
  var ss = SpreadsheetApp.getActive();
  var StocksSheet = ss.getSheetByName("Stocks");
  var OrdersSheets = ss.getSheetByName("Orders");
  
  var scriptProperties = PropertiesService.getScriptProperties();
  var counter = Number(scriptProperties.getProperty('counter')) || 0;
  var formatDate = Utilities.formatDate(new Date(), "GMT+4", "ddMM");
  var zero = (counter < 365) ? '0' : '';
  var ID = "FS" + formatDate + zero + counter++;
  var IDP = "FP" + formatDate + zero + counter++;
  if (counter > 99) counter = 0;
  scriptProperties.setProperty('counter', counter);

  var TimeStamp = Utilities.formatDate(new Date(), "GMT+4", "dd-MM-yy HH:mm:ss");
  var LinkS = "https://drive.google.com/drive/xxxxxxxxxxxxxxxx";
  var LinkP = "https://drive.google.com/drive/u/xxxxxxxxxxxxxxxx";
  const { Region, Place, Staff, Shop_Representative, Product_Location, Date_Checked, Time_Checked, Remarks, Hkey, RemarksP, Date_Ordered, Time_Ordered, Date_of_Delivery } = e.parameter;
  const rowS = [TimeStamp, ID, Region, Place, Staff, Shop_Representative, Product_Location, Date_Checked, Time_Checked, Remarks, LinkS];
  const rowP = [TimeStamp, IDP, Region, Place, Staff, Shop_Representative, Date_Ordered, Time_Ordered, Date_of_Delivery, LinkP, RemarksP];

  if (Hkey == '123') {
    const res = [...Array(20)].reduce((ar, _, h) => {
      var i = h + 1;
      var Product = e.parameter['Product' + i];
      if (Product) {
        ar.push([...rowS, Product, ...['Filling', 'Quantity', 'Expiry'].map(f => e.parameter[`${f}${i}`] || "")]);
      }
      return ar;
    }, []);
        
    if (res.length == 0) {
      return ContentService.createTextOutput("Stock not uploaded, please retry");
    }
    
    try {
      StocksSheet.getRange(StocksSheet.getLastRow() + 1, 1, res.length, res[0].length).setValues(res);
      var StocksLogs = SpreadsheetApp.openById('xxxxxxxxxxxxxxxx').getSheetByName("Stocks_Logs");
      StocksLogs.getRange(StocksLogs.getLastRow() + 1, 1, res.length, res[0].length).setValues(res);
      return ContentService.createTextOutput("Your stock had been inputed with ID " + ID);
    } catch (error) {
      var StocksLogs = SpreadsheetApp.openById('xxxxxxxxxxxxxxxx').getSheetByName("Stocks_Logs");
      StocksLogs.getRange(StocksLogs.getLastRow() + 1, 1, res.length, res[0].length).setValues(res);
      return ContentService.createTextOutput("Database issue detected for stock with ID:" +ID+ ". Please coordinate with the administrator for resolution.");
    }    
  } 
  
  if(Hkey == '456') {
    const res = [...Array(12)].reduce((ar, _, h) => {
      var i = h + 1;
      var Product = e.parameter['Product' + i];
      if (Product) {
        ar.push([...rowP, Product, ...['Filling', 'Quantity', 'Discount'].map(f => e.parameter[`${f}${i}`] || "")]);
      }
      return ar;
    }, []);
        
    if (res.length == 0) {
      return ContentService.createTextOutput("Order not uploaded, please retry");
    }
    
    try {
      OrdersSheets.getRange(OrdersSheets.getLastRow() + 1, 1, res.length, res[0].length).setValues(res);
      var OrdersLogs = SpreadsheetApp.openById('xxxxxxxxxxxxxxxx').getSheetByName("Orders_Logs");
      OrdersLogs.getRange(OrdersLogs.getLastRow() + 1, 1, res.length, res[0].length).setValues(res);
      return ContentService.createTextOutput("Your order had been inputed with ID " + IDP);
    } catch (error) {
      var OrdersLogs = SpreadsheetApp.openById('xxxxxxxxxxxxxxxx').getSheetByName("Orders_Logs");
      OrdersLogs.getRange(OrdersLogs.getLastRow() + 1, 1, res.length, res[0].length).setValues(res);
      return ContentService.createTextOutput("Database issue detected for order with ID:" +IDP+ ". Please coordinate with the administrator for resolution.");
    } 
  } else {
    return ContentService.createTextOutput("Please check check your app version")
  }
}

1. 先添加错误日志定位具体问题 #

当前catch块未记录错误详情，无法直接知道失败原因。在Hkey=456分支的catch中添加日志：

catch (error) {
  Logger.log("Orders分支错误详情：" + error.toString()); // 新增日志
  var OrdersLogs = SpreadsheetApp.openById('xxxxxxxxxxxxxxxx').getSheetByName("Orders_Logs");
  OrdersLogs.getRange(OrdersLogs.getLastRow() + 1, 1, res.length, res[0].length).setValues(res);
  return ContentService.createTextOutput("Database issue detected for order with ID:" +IDP+ ". Please coordinate with the administrator for resolution.");
}

执行后在Google Apps Script编辑器中查看日志（顶部菜单→查看→日志），获取具体错误信息。

2. 验证工作表存在性与拼写 #

• 确认当前Spreadsheet中存在名为Orders的工作表，拼写完全一致（区分大小写）
• 确认日志表格ID对应的文件中存在Orders_Logs工作表，无拼写错误

3. 检查参数完整性 #

Hkey=456分支依赖Date_Ordered、Time_Ordered、Date_of_Delivery等参数，需确保应用端正确传递，避免出现undefined导致写入失败。可在res生成前添加校验：

if(Hkey == '456') {
  // 校验必填订单参数
  if(!Date_Ordered || !Time_Ordered || !Date_of_Delivery) {
    return ContentService.createTextOutput("Missing required order parameters");
  }
  // 后续res生成代码...
}

4. 确认列数匹配 #

Orders工作表的列数需与res数组每行的列数一致：rowP有11个元素，加上Product、Filling、Quantity、Discount，共15列。确保Orders工作表至少有15列，否则会因列数不匹配报错。

5. 优化分支逻辑（可选） #

当前else分支属于第二个if(Hkey=='456')，逻辑无问题但可调整为更清晰的结构：

if (Hkey == '123') {
  // Stocks分支代码
} else if(Hkey == '456') {
  // Orders分支代码
} else {
  return ContentService.createTextOutput("Please check your app version");
}

内容的提问来源于stack exchange，提问作者P.J