How can I redirect back to my website after google apps script sheet submission?

Question

I just made my first website and linked my 'contact' page to google sheets using GAS, however, once the form is submitted it redirects to a 'script.googleusercontent.com' URL displaying '{"result":"success","row":8}' How can I automatically redirect this back to my original website? I used bootstrap studio to make my website

I tried adding

const sheetName = 'Sheet1'
const scriptProp = PropertiesService.getScriptProperties()

function initialSetup () {
  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  scriptProp.setProperty('key', activeSpreadsheet.getId())
}

function doPost (e) {
  const lock = LockService.getScriptLock()
  lock.tryLock(10000)

  try {
    const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    const sheet = doc.getSheetByName(sheetName)

    const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    const nextRow = sheet.getLastRow() + 1

    const newRow = headers.map(function(header) {
      return header === 'Date' ? new Date() : e.parameter[header]
    })

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  finally {
    lock.releaseLock()
  }

  
function doGet() {
  return HtmlService.createHtmlOutput(
    "<form action='submitdata.html' method='get' id='redirect'></form>" + 
    "<script>document.getElementById('redirect').submit();</script>");
}

}

to the end of google apps script, which did nothing. I just learned the basics with HTML and a bit of CSS as this is my first website, so something simple like this is going a little over my head. Simple explanations would be extremely helpful!

Answer 1

Thank you for adding more of your code. In your specific case, it sounds like you have a website, you added a simple form to your website to collect user information (ie name, email, etc), and you told your form to send the data to your Google Web App (maybe by specifying the Google Web App URL on the form submit feature), which should then process the data, and add the data to a Google Sheet. Please correct me if any of that is wrong.

You said you're new to some of this, so I'll be more verbose, but skip ahead if you already know this.

First let me quickly explain how Google Web Apps work (most of this information can be found on Google's own documentation). A Google Web App is basically a set of code that is exposed to the internet via a singular URL (something like https://script.google.com/macros/s/<<UNIQUE_ID>>/exec). Depending on the type of request that hits that URL, the Web App will function differently. If a GET request (which is generally just a request for some information/resource) hits the URL, then the Web App will attempt to run whatever code is within the doGet. If a POST request (which generally contains some type of new form data) hits the URL, then the Web App will attempt to run whatever code is within the doPost. (There are other types of requests, but Google Web Apps really only work on the Get/Post binary.) Also important to note that whatever is served/returned from the doGet/doPost is actually sandboxed within an iFrame (which is why you always see that This application was created by another user, not by Google header).

(Sort of a side point, I see that your doGet is actually nested inside your doPost. That won't work, doGet needs to be at the top level of the file for it to work. Regardless though, that code won't work as it'll just attempt to redirect from inside the iFrame and most sites do not allow themselves to be iFramed.)

With that in mind, your code seems to be working as it's written. When a user submits your contact form, the data gets sent to the Google Web App URL, and your Web App proceeds through the doPost code where it will attempt to append the data to your spreadsheet. The way you have your logic written, whether or not the data is successfully added to the spreadsheet, you will serve some simple text back to the user (via ContentService.createTextOutput), which is the '{"result":"success","row":8}' that you were referring to. Ideally, after processing the data, you want the user to be redirected back to your site, as opposed to them seeing this text. This was actually possible a couple years ago, but it seems like Google has tightened up their security a bit, and this is no longer possible (see their official notes, as well as this post and this post referring to the same thing). Basically, Google did this to protect the user from things like malicious auto-redirects and to promote a safer environment in their Web App ecosystem.

As an alternative to automatically redirecting back to your website, you can add a button that the user can click to send them back to your website. As long as there is still an explicit user action/gesture, Google is okay redirecting the user to a new page, they just don't want the browser to automatically redirect the user without their input. For example, you can add a new html file to your web app:

redirectSuccess.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
      <p>Thank you for submitting your information!</p>
      <button onclick="window.top.location.replace('<<YOUR_WEBSITE>>')" type="submit">Navigate to Other Page</button>
  </body>
</html>

Because of your try/catch you can also add another HTML file for if the process fails for whatever reason:

redirectError.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
      <p>Whoops sorry about that, something went wrong. Please try again alter.</p>
      <button onclick="window.top.location.replace('<<YOUR_WEBSITE>>')" type="submit">Navigate Back to Website</button>
  </body>
</html>

and then your doPost will will look like this (the rest of the file can remain as is):

function doPost(e) {
  const lock = LockService.getScriptLock()
  lock.tryLock(10000)

  try {
    const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    const sheet = doc.getSheetByName(sheetName)

    const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    const nextRow = sheet.getLastRow() + 1

    const newRow = headers.map(function (header) {
      return header === 'Date' ? new Date() : e.parameter[header]
    })

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

    const template = HtmlService.createTemplateFromFile("redirectSuccess");
    template.url = ScriptApp.getService().getUrl();
    return template.evaluate();
  }

  catch (e) {
    const template = HtmlService.createTemplateFromFile("redirectError");
    template.url = ScriptApp.getService().getUrl();
    return template.evaluate();
  }

  finally {
    lock.releaseLock()
  }
}

(Make sure that if you make any changes to your Web App, that you Deploy a new version of your Web App, and if the Web App URL changes, point your contact form to the new URL.)

Then after the users submits the form, they will be directed to the above redirect page and when the user clicks the button, they are taken back to your website. Of course this is not ideal and you'd definitely want to style the redirect page to be inline with your website. The only other real alternative would be if you had full control over the javascript on your Bootstrap Studios page, you could prevent the form from redirecting while still sending the data to your webapp (you can see some options here (the more recent answers are on the bottom), but I'm not sure if you have that level of control/experience.