嵌入网站的Google表格HTML Service自定义对话框加载失败求助

Got it, let's tackle this head-on— that error is a common security restriction Google enforces, and there's a clear path to work around it for your use case.

Why This Happens #

Google Apps Script's native custom dialogs are tightly tied to the Google Sheets context. By default, their URLs have the X-Frame-Options: sameorigin header set, which means they can only load within Google's own domains (like sheets.google.com). When you embed your editable sheet in an external website, the browser blocks the dialog because the external domain doesn't match Google's.

Step-by-Step Fix #

The solution is to decouple your dialog's HTML content from the Sheets-specific dialog system and deploy it as a standalone Google Apps Script Web App instead. Here's how:

• Create a doGet function for your HTML content
  In your Apps Script project, add a function that serves your dialog's HTML file directly. Replace your-dialog-file with the name of your actual HTML file:

  function doGet() {
    return HtmlService.createHtmlOutputFromFile('your-dialog-file')
      .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL); // Critical for allowing external framing
  }
  

  The setXFrameOptionsMode line explicitly overrides the default sameorigin setting to let your content load in external frames.

• Deploy as a Web App

  1. Click the "Deploy" button in the top-right corner of the Apps Script editor, then select "New deployment".
  2. Set the deployment type to "Web app".
  3. Under "Execute as", choose "Me" (your account) or a service account if you need consistent execution across users.
  4. For "Who has access", select the appropriate level:
     • Use "Anyone, even anonymous" if your content is public or you handle authentication separately.
     • Use "Anyone within [your organization]" if only internal users need access.
  5. Click "Deploy", authorize the permissions if prompted, and copy the generated Web App URL.

• Update your embedded sheet's dialog trigger
  Instead of opening the native Sheets dialog, modify your script to load the Web App URL in a modal or iframe within your embedded sheet's context. For example, if you're using a button in the sheet to trigger the dialog, update the onClick action to point to the Web App URL instead of calling SpreadsheetApp.getUi().showModalDialog().

Important Security Notes #

• If you set access to "Anyone, even anonymous", double-check that your HTML content doesn't expose sensitive data. Add authentication checks if needed—you can use Session.getActiveUser() to verify logged-in Google users, or integrate Google Sign-In for external users.
• Always re-deploy your Web App as a new version whenever you update your HTML or script code to ensure changes take effect.
• Ensure your external website uses HTTPS (most modern sites do), as Google Web Apps only serve content over HTTPS and browsers block mixed-content loads.

Alternative Workaround (If You Need Sheets Context) #

If your dialog relies heavily on interacting with the sheet's data (like reading/writing cells), you can keep the dialog logic tied to Sheets but use a client-side iframe that communicates with the Web App via postMessage. This way, the Web App can act as a middleman to interact with the sheet's API while loading in the external frame.

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