Google表单提交后无法按地址半径跳转指定链接求助

我在用Google Form时碰到个问题：用户提交表单后，没法根据填写地址和指定地点的距离是否在10英里内跳转对应页面。测试部署的链接能正常跳转，但通过正式表单链接提交时就失效了。我需要实现表单提交后按if逻辑跳转指定URL，相关代码和配置如下：

Apps Script 代码 #

function onFormSubmit(e) {

  var ss = SpreadsheetApp.getActiveSheet();
  //ss.getRange('A2:I500').clear();

  var range = ss.getDataRange();
  var cells = range.getValues();

  var latitudes = [];
  var longitudes = [];
  var formatted_addresses = [];

  for (var i = 1; i < cells.length; i++) {
    var address = cells[i][2];
    var geocoder = Maps.newGeocoder().geocode(address);
    var res = geocoder.results[0];

    var lat = lng = 2;
    var formatted_address = '';
    if (res) {
      lat = res.geometry.location.lat;
      lng = res.geometry.location.lng;
      formatted_address = res.formatted_address;
    }

    latitudes.push([lat]);
    longitudes.push([lng]);
    formatted_addresses.push([formatted_address]);
  }
  ss.getRange('D2')
  .offset(0, 0, latitudes.length)
  .setValues(latitudes);
  ss.getRange('E2')
  .offset(0, 0, longitudes.length)
  .setValues(longitudes);
  ss.getRange('I2')
  .offset(0, 0, formatted_addresses.length)
  .setValues(formatted_addresses)

  //var ss = SpreadsheetApp.getActiveSpreadsheet();
  //var mapSheet = ss.getSheetByName("AddressValidation");

  //var start = { "lat" : "33.6608243" , "lng" : "-84.4954219" };
  //var end = { "lat" : ""+ss.getRange('D2').getValue()+"", "lng" : ""+ss.getRange('E2').getValue()+"" };

  var start = "3400 North Desert Drive, Atlanta, GA 30344";
  var end = ss.getRange('I2').getValue();
  

  var directions = Maps.newDirectionFinder()
  .setOrigin(start)
  .setDestination(end)
  .setMode(Maps.DirectionFinder.Mode.DRIVING)
  .getDirections();


  //ss.getRange('G2:H500').clear();

  var nextRow = ss.getLastRow();

  for (var i = 0; i < directions.routes[0].legs.length; i++)
  {
    //var endAddress = directions.routes[0].legs[i].end_address;
    //var startAddress = directions.routes[0].legs[i].start_address;
    var distance = directions.routes[0].legs[i].distance.text;
    var duration = directions.routes[0].legs[i].duration.text;
    
    //mapSheet.getRange(nextRow,1).setValue(startAddress);
    //mapSheet.getRange(nextRow,2).setValue(endAddress);
    ss.getRange(nextRow,7).setValue(distance);
    ss.getRange(nextRow,8).setValue(duration);

    
    
    nextRow++;

    
  }
doGet();
  
}

function doGet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var miles = ss.getRange('G2').getValue()
  if (miles <= '10 mi') { 
  return HtmlService.createHtmlOutput(
    "<script>window.location.href='https://acfb.oasisinsight.net/kiosk/dcfc4868851a83a86531050375959fda31b7a56709586645bde82250c9cb5a70/';</script>")
  } else if (miles > "10 mi") {
    return HtmlService.createHtmlOutputFromFile('Index');
}

}

Index.html 文件内容 #

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="refresh" content="2;url=https://www.acfb.org/get-help/" />
    <base target="_top">
  </head>
  <body>
    <h1 style="color:red">This location is not eligible for home delivery!</h1>
    <a href="https://www.acfb.org/get-help/" target="_blank">Click this link to continue</a>
  </body>
</html>

相关表格截图 #

• 表格数据截图：
• 另一表格截图：

测试情况 #

我尝试通过「Deploy > test deployment」生成的链接测试，跳转功能正常；但用户通过正式表单链接提交数据时，跳转完全失效。

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