如何在Google Apps Script的doGet函数中调用afterFormSubmit函数

问题原因 #

你直接在doGet中调用afterFormSubmit无法运行的核心原因是：afterFormSubmit 函数依赖谷歌表单提交触发的原生事件对象e，该对象自带e.range、e.namedValues等专属属性，你在doGet中直接调用时没有传递符合格式的参数e，所以函数执行报错。

解决方案 #

可以在doGet中运行afterFormSubmit，只需要在匹配到对应审批行之后，手动构造符合要求的模拟事件对象e，再传入afterFormSubmit调用即可。

修改后的代码示例 #

首先微调afterFormSubmit，兼容doGet场景下传入的自定义表格对象，避免激活表匹配错误：

var DONE = "DONE";
var Approve = "Approve";
var Reject = "Reject";
var EMAIL_SENT = "EMAIL_SENT";
var FOR_RUN = "FOR_RUN";
var COMPLETE = "COMPLETE";

// 新增customSheet参数，优先用传入的表格，没有再取默认的表单响应表
function afterFormSubmit(e, customSheet) {
  const entryRow = e.range.getRow();
  const ss = customSheet || SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");

  var su = ss.getRange(entryRow, 14).getValue();
  var ku = ss.getRange(entryRow, 14).getValue();
  var check = ss.getRange(entryRow, 9).getValue();
  var checke = ss.getRange(entryRow, 16).getValue();
  var checkv = ss.getRange(entryRow, 17).getValue();

  if (check == "" && checke != EMAIL_SENT) {
    assignEditUrls();
    sendMail_Verification(e);
  }

  if (check == Approve && checkv != EMAIL_SENT) {
    const info = e.namedValues;
    const pdfFile = createPDF(info, e);

    ss.getRange(entryRow, 14).setValue(pdfFile.getUrl());
    ss.getRange(entryRow, 15).setValue(pdfFile.getName());

    Last_sendMail(e);
  }
  sendMail_Rejection(e);
}

然后修改doGet函数，在匹配到审批行、更新完状态之后，构造模拟e对象并调用afterFormSubmit：

function doGet(request) {
  Logger.log(request);

  var sheet = SpreadsheetApp.openById(request.parameters.ssId).getActiveSheet();
  var data = sheet.getDataRange().getValues();
  // 提前获取表头，用来构造和原生事件格式一致的namedValues
  var headers = sheet.getDataRange().offset(0, 0, 1).getValues()[0];

  var user = request.parameters.id;
  var status = request.parameters.status;
  var check = request.parameters.check;

  for (var i = 1; i < data.length; i++) {
    var check = sheet.getRange(i + 1, 12, 1, 1).getValue();
    if (data[i][0] == user[0] && check != DONE) {
      sheet.getRange(i + 1, 9, 1, 1).setValue(status[0]);

      var checkcc = sheet.getRange(i + 1, 9, 1, 1).getValue();
      if (data[i][0] == user[0] && check != DONE) {
        var date = Utilities.formatDate(
          new Date(),
          "GMT+08:00",
          "MM/dd/yyyy HH:mm:ss"
        );
        sheet.getRange(i + 1, 19, 1, 1).setValue("");
        sheet.getRange(i + 1, 13, 1, 1).setValue(date);
        sheet.getRange(i + 1, 12, 1, 1).setValue(DONE);

        // 构造模拟事件对象e，完全匹配原生表单提交事件的格式
        const targetRow = i + 1;
        const rowData = data[i];
        let namedValues = {};
        headers.forEach((header, idx) => {
          namedValues[header] = [rowData[idx]];
        });
        const mockE = {
          range: sheet.getRange(targetRow, 1, 1, sheet.getLastColumn()),
          namedValues: namedValues
        }
        // 调用afterFormSubmit
        afterFormSubmit(mockE, sheet);

        if (checkcc == Approve) {
          var result = "You have successfully Approved " + user + " request.";
          return ContentService.createTextOutput(result);
        } else if (checkcc == Reject) {
          var resultvc = "You have successfully Rejected " + user + " request.";
          return ContentService.createTextOutput(resultvc);
        }
      }
    }
  }
}

注意事项 #

• 确保表格的表头和谷歌表单的字段名完全一致，避免e.namedValues取值错误
• 如果createPDF、sendMail_Verification等自定义函数还用到了事件对象e的其他属性，按需在mockE中补充对应属性即可

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