问题

我刚刚创建了我的第一个网站，并使用Google Apps Script将我的“联系”页面链接到Google Sheets，但是一旦表单被提交，它会重定向到一个显示“script.googleusercontent.com” URL的地方，显示“{"result":"success","row":8}”。

如何自动将其重定向回我的原始网站？我使用Bootstrap Studio制作了我的网站。

我尝试添加以下代码到Google Apps Script的末尾，但没有效果：

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>");
}

这段代码没有起作用。由于这是我第一次制作网站，我只学了HTML和一点点CSS的基础知识，所以像这样的简单问题对我来说有点难理解。简单的解释将非常有帮助！

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>");
}
}

答案1

得分: 0

谢谢您添加更多的代码。在您的特定情况下，听起来您拥有一个网站，您在网站上添加了一个简单的表单来收集用户信息（例如姓名、电子邮件等），然后您告诉表单将数据发送到您的Google Web应用程序（可能是通过在表单提交功能中指定Google Web应用程序的URL），然后该应用程序应该处理数据并将数据添加到Google表格中。如果有任何错误，请纠正我。

您说您对其中一些内容不熟悉，所以我将更详细地解释，但如果您已经了解这些内容，请跳过。

首先，让我快速解释一下Google Web应用程序的工作原理（大部分信息可以在Google自己的文档中找到）。Google Web应用程序基本上是一组代码，通过一个唯一的URL（类似于 https://script.google.com/macros/s/<<UNIQUE_ID>>/exec）向互联网公开。根据击中该URL的请求类型，Web应用程序将以不同的方式运行。如果是 GET 请求（通常只是请求某些信息/资源），则Web应用程序将尝试运行位于 doGet 内的代码。如果是 POST 请求（通常包含某种新的表单数据），则Web应用程序将尝试运行位于 doPost 内的代码（还有其他类型的请求，但Google Web应用程序实际上只在Get/Post二进制上运行）。还要注意的是，从 doGet/doPost 服务/返回的任何内容实际上都在iframe中进行了隔离（这就是为什么您总是看到 "This application was created by another user, not by Google" 标头）。

（顺便说一句，我看到您的 doGet 实际上是嵌套在 doPost 内的。这不会起作用，doGet 需要在文件的顶层才能工作。不过，无论如何，那段代码不会起作用，因为它将尝试从iframe内部重定向，而大多数站点不允许自己被iframe包含。）

考虑到这一点，您的代码似乎按照其编写的方式运行。当用户提交您的联系表单时，数据将发送到Google Web应用程序的URL，您的Web应用程序将通过 doPost 代码处理数据，然后尝试将数据附加到您的电子表格中。根据您编写逻辑的方式，无论数据是否成功添加到电子表格中，您都将向用户返回一些简单的文本（通过 ContentService.createTextOutput），这就是您提到的 '{"result":"success","row":8}'。理想情况下，在处理数据后，您希望用户被重定向回您的网站，而不是看到这个文本。这在几年前是可能的，但似乎Google加强了安全性，这不再可能（请参阅官方说明，以及与此相关的此帖和此帖）。基本上，Google这样做是为了保护用户免受恶意自动重定向等行为的影响，并促进其Web应用程序生态系统的安全环境。

作为自动重定向回您的网站的替代方案，您可以添加一个按钮，用户可以点击该按钮以返回您的网站。只要仍然存在明确的用户操作/手势，Google就可以将用户重定向到新页面，他们只是不希望浏览器在没有用户输入的情况下自动重定向用户。例如，您可以在您的Web应用程序中添加一个新的HTML文件：

redirectSuccess.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
      <p>感谢您提交信息！</p>
      <button onclick="window.top.location.replace('<<YOUR_WEBSITE>>')" type="submit">导航到其他页面</button>
  </body>
</html>

由于您的try/catch，如果由于某种原因过程失败，您还可以为其添加另一个HTML文件：

redirectError.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
      <p>糟糕，出了点问题。请稍后再试。</p>
      <button onclick="window.top.location.replace('<<YOUR_WEBSITE>>')" type="submit">返回网站</button>
  </body>
</html>

然后，您的 doPost 将如下所示（文件的其余部分可以保持不变）：

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

<details>
<summary>英文:</summary>

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&#39;re new to some of this, so I&#39;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&#39;s own [documentation](https://developers.google.com/apps-script/guides/web)). 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/&lt;&lt;UNIQUE_ID&gt;&gt;/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&#39;t work, `doGet` needs to be at the top level of the file for it to work. Regardless though, that code won&#39;t work as it&#39;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&#39;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 `&#39;{&quot;result&quot;:&quot;success&quot;,&quot;row&quot;:8}&#39;` 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](https://chromestatus.com/feature/5629582019395584), as well as [this post](https://stackoverflow.com/questions/72648010/redirect-to-url-after-posting-to-google-sheet-google-apps-script) and [this post](https://stackoverflow.com/questions/69128777/google-script-about-redirect-not-working-and-appears-peculiar-error-what-happen) 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&#39;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](https://stackoverflow.com/questions/1263852/prevent-form-redirect-or-refresh-on-submit) (the more recent answers are on the bottom), but I'm not sure if you have that level of control/experience.
</details>