Google Form与Twilio集成问题求助：表单提交数据未显示

问题背景 #

已完成Twilio配置：向Twilio号码发送短信时，Twilio会触发Webhook到本地应用，姓名可正常显示在家庭屏幕。现尝试用Google Form替代短信，已创建Form及关联Sheet，设置了表单提交触发的Google Apps Script触发器。表单提交后，Sheet收集到响应并执行代码，日志显示请求返回200状态码，但Webhook响应为空，姓名无法显示；短信方式则正常工作。

代码与日志 #

现有代码 #

function onSubmit(e) {
  // Replace 'YOUR_TWILIO_ACCOUNT_SID' and 'YOUR_TWILIO_AUTH_TOKEN' with your Twilio credentials
  var twilioAccountSid = 'ACxxxxxxxxxxxxxxxxxxxxxxxxx';
  var twilioAuthToken = '92xxxxxxxxxxxxxxxxxxxxxxxxxxx2';

  // Replace 'YOUR_TWILIO_MESSAGING_SERVICE_SID' with your Twilio Messaging Service SID
  var twilioMessagingServiceSid = 'MGxxxxxxxxxxxxxxxxxxxxxxxxxd9';
  // Replace 'RESPONSE_INDEX' with the correct column index for Question 1
  if (e && e.values) {
    var response = e.values[1];
    Logger.log("Response from Question 1: " + response);
    // Rest of your code
  } else {
    Logger.log("Form submission event object is undefined or lacks 'values'.");
  }
  var twilioWebhookUrl = 'http://xxx.xxx.xx.x:8888/plugin.php?plugin=TwilioControl&page=TSMS.php&nopage=1';

  var payload = {
    body: response,
  };
  var options = {
    'method': 'post',
    'payload': payload
  };
  // Log before making the POST request to check if it's reached this point
  Logger.log("Before making the POST request.");

  try {
    var response = UrlFetchApp.fetch(twilioWebhookUrl, options);
    
    // Log the response from the webhook (optional)
    Logger.log("Webhook Response Code: " + response.getResponseCode());
    Logger.log("Webhook Response: " + response.getContentText());
    
    // Log after making the POST request to check if it's completed
    Logger.log("After making the POST request.");
    
    // Check if the response from Twilio indicates success or contains any error information
    if (response.getResponseCode() === 200) {
      Logger.log("Webhook request was successful.");
    } else {
      Logger.log("Webhook request failed with status code: " + response.getResponseCode());
    }
  } catch (error) {
    Logger.log("Error while making the POST request: " + error);
  }
}

执行日志 #

Deployment  Function  Type      Start Time                Duration    Status
Head        onSubmit Trigger    Sep 8, 2023,1:15:17 PM     0.388 s    Completed

Cloud logs
Sep 8, 2023, 1:15:17 PM Info    Response from Question 1: Bob
Sep 8, 2023, 1:15:17 PM Info    Before making the POST request.
Sep 8, 2023, 1:15:18 PM Info    Webhook Response Code: 200
Sep 8, 2023, 1:15:18 PM Info    Webhook Response:
Sep 8, 2023, 1:15:18 PM Info    After making the POST request.
Sep 8, 2023, 1:15:18 PM Info    Webhook request was successful.

问题根源 #

现有代码逻辑错误：直接向本地Webhook URL发送POST请求，而非通过Twilio API发送短信触发Webhook。原短信流程是手机→Twilio号码→Twilio触发Webhook到本地应用，本地应用依赖Twilio发送的标准Webhook参数（如Body、From、To等）来识别并显示姓名；而当前代码发送的请求仅包含body参数，缺少Twilio请求的必要标识，导致本地应用无法处理。

另外，代码中定义了Twilio的账号SID、Token和消息服务SID，但并未实际调用Twilio API，这些参数未被使用。

修复方案 #

修改代码，通过Twilio API发送短信，让Twilio触发Webhook到本地应用，保持和原短信流程一致：

function onSubmit(e) {
  var twilioAccountSid = 'ACxxxxxxxxxxxxxxxxxxxxxxxxx';
  var twilioAuthToken = '92xxxxxxxxxxxxxxxxxxxxxxxxxxx2';
  var twilioMessagingServiceSid = 'MGxxxxxxxxxxxxxxxxxxxxxxxxxd9';
  // 目标号码（根据你的Twilio配置调整，可设为接收短信后触发Webhook的号码）
  var toNumber = '+1234567890';
  // 发送者号码（或使用消息服务SID替换From字段）
  var fromNumber = '+0987654321';

  var response;
  if (e && e.values) {
    response = e.values[1];
    Logger.log("Response from Question 1: " + response);
  } else {
    Logger.log("Form submission event object is undefined or lacks 'values'.");
    return; // 参数异常时终止执行
  }

  // Twilio发送短信的API地址
  var twilioApiUrl = 'https://api.twilio.com/2010-04-01/Accounts/' + twilioAccountSid + '/Messages.json';
  
  // 构建符合Twilio要求的请求参数
  var payload = {
    Body: response, // 注意首字母大写，Twilio API要求的字段名
    To: toNumber,
    From: fromNumber
    // 若使用消息服务，替换为 MessagingServiceSid: twilioMessagingServiceSid
  };

  // 配置请求选项，包含认证信息
  var options = {
    method: 'post',
    payload: payload,
    headers: {
      Authorization: 'Basic ' + Utilities.base64Encode(twilioAccountSid + ':' + twilioAuthToken)
    }
  };

  try {
    var twilioResponse = UrlFetchApp.fetch(twilioApiUrl, options);
    Logger.log("Twilio API Response Code: " + twilioResponse.getResponseCode());
    Logger.log("Twilio API Response: " + twilioResponse.getContentText());

    if (twilioResponse.getResponseCode() === 201) {
      Logger.log("短信发送成功，Twilio将触发Webhook");
    } else {
      Logger.log("短信发送失败，状态码: " + twilioResponse.getResponseCode());
    }
  } catch (error) {
    Logger.log("调用Twilio API出错: " + error);
  }
}

额外优化点 #

1. 变量作用域：将response变量提升到if块外，避免未定义的风险。
2. 参数校验：当表单事件参数异常时，直接终止函数执行，避免后续错误。
3. 字段名匹配：Twilio API要求的字段名是首字母大写（如Body、To），需严格对应。
4. 认证方式：通过请求头传递Twilio的Base64编码认证信息，符合API要求。

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