Links Not Working After Submitting Responses in Google Form

Question

[Google Sheet][1][Google Sheet][2]My links are not pulling up when submitting responses in a google form. After the user enters data and clicks the submit it is suppose to direct the user to another page depending on if the address falls inside or outside of a 10 mile radius. Could someone assist me? I need to set up my form to go to the url after clicking submit based on my if statement. My code is below:

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>
</html>


Spreadsheet
[Google Sheet](https://i.stack.imgur.com/iUvnO.png)






I tried Deploy > test deployment and the links appear but when I go the the link to the form and hit submit the links will not work.


[Google Sheets][2]


  [1]: https://i.stack.imgur.com/aGi9A.png
  [2]: https://i.stack.imgur.com/sfSMD.png

Hi. Just to be clear, you want to redirect when the user clicks "Submit" **on the form**. Is that right? BUT you are using `onFormSubmit` which is triggered by the form response being received by the linked spreadsheet. Would you please provide a summary of your research for this question. Did you read [Redirect Google App Script on submit of form to sheet](https://stackoverflow.com/a/71182936/1330560) — Tedinoz, Dec 03 '22 at 06:19
Related: [Redirect to URL After Posting to Google Sheet - Google Apps Script](https://stackoverflow.com/a/72659604/1330560) — Tedinoz, Dec 03 '22 at 06:22
Related: [How do you run an Appscript on Form Submission?](https://stackoverflow.com/a/73213374/1330560) — Tedinoz, Dec 03 '22 at 06:26
Yes that is correct I want to redirect to another website after submission based on the if statement. I did google search and saw to use the onFormSubmit function. — Wendy Frazier, Dec 05 '22 at 00:05
FWIW: 10 Mile radius: your `IF` fails because the value in Column G is text (`directions.routes[0].legs[0].distance.text`) and you can't use IF to effectively compare text values. It would be better to return the _value_ (`directions.routes[0].legs[0].distance.value`) which is expressed in meters. Then convert this to miles - `var distanceValue = (directions.routes[0].legs[0].distance.value/1000*0.621371).toFixed(1)`, `ss.getRange(nextRow,7).setValue(distanceValue)`. Then change the "IF" 1) `if (miles <= 10) {` and 2) `} else if (miles > 10) {` — Tedinoz, Dec 05 '22 at 03:22

score 0 · Answer 1 · answered Dec 05 '22 at 04:42

There are two matters for you to consider.

user to be redirected to another page when they submit the form BUT The redirect option is no longer available in forms. Google has dropped support for redirects in Apps Script web apps..
even if you could re-direct from the Google Form, the redirect page depends on processing the form response in the linked spreadsheet - a classic chicken-and-egg scenario

You may wish to consider a workaround to this (perhaps an add-on, or a non-Google Form). A non-Google form would allow you to access the Maps API.

Links Not Working After Submitting Responses in Google Form

1 Answers1