Search code examples
google-apps-scriptgoogle-sheetsiframecorsadd-on

Host a website in dialog and ensure the communication with the google sheet and the backend

I'm making a Google Sheets add-on. The add-on opens a modeless dialog which hosts www.myfrontend.com which uses www.mybackend.com as the backend.

1. Use createHtmlOutput and iframe

To realize this, one way is to use iframe as follows:

var htmlOutput = HtmlService
    .createHtmlOutput('<iframe src="https://www.myfrontend.com/#/home" style="width: 100%; height: 800px; border: none;"></iframe>'
    .setWidth(600)
    .setHeight(800);
SpreadsheetApp.getUi().showModelessDialog(htmlOutput, 'My add-on');

By this approach, myfrontend.com can be shown in the dialog and call mybackend.com without any problem. However, it seems that google is not available in myfrontend.com; I cannot call google.script.run.withSuccessHandler(res).withFailureHandler(rej)... in myfrontend.com. As a consequence, myfrontend.com cannot communicate with the Google Sheet.

2. Use createHtmlOutputFromFile

Another way is to have another file like production.html in Apps Script, and use it as follows:

var htmlOutput = HtmlService
    .createHtmlOutputFromFile('production')
    .setWidth(600)
    .setHeight(800);
SpreadsheetApp.getUi().showModelessDialog(htmlOutput, 'My add-on');

production.html is copied from build/index.html of myfrontend.com, and changed slightly:

<!doctype html>
  <html lang="en">
    <head>
      <meta charset="utf-8"/>
      <base href="https://www.myfrontend.com/">
      <link rel="icon" href="/favicon.ico"/>
      <meta name="viewport" content="width=device-width,initial-scale=1"/>
      <meta name="theme-color" content="#ffffff"/>
      <meta name="description" content="Web site created using create-react-app"/>
      <link rel="apple-touch-icon" href="/logo192.png"/><link rel="manifest" href="/manifest.json"/>
      <meta charset="utf-8"/><meta charset="UTF-8"/><meta http-equiv="X-UA-Compatible" content="IE=Edge"/>
      <title>myApp</title>
      <script>window.location.hash = "#/home"</script>
      ... ...

By this approach, myfrontend.com can be shown in the dialog and it seems that google and google.script.run are available in myfrontend.com. However, when we sign in in myfrontend.com, the browser returns a CORS error:

[Error] Origin https://n-5fydpzckmar5yncflnjytuyfcusdcfogmgc2eti-1lu-script.googleusercontent.com is not allowed by Access-Control-Allow-Origin. Status code: 204
[Error] XMLHttpRequest cannot load https://www.mybackend.com/httpOnly/signin due to access control checks.

In app.js of www.backend.com, I have already enabled the CORS as follows, but the error still exists:

app.use(cors({
  origin: [/\.mybackend\.com$/, /\.myfrontend\.com$/, /localhost(:[0-9]*)?$/, /\.live\.com$/, "https://onedrive.live.com", /https:\/\/[\w-]+\.script\.googleusercontent\.com/],
  credentials: true,
}));

So does anyone know what's the correct way to host a website in modeless dialog, ensure the communication to the google sheet by google.script.run and the communication to the backend?

Solution

  • Your regex

    /https:\/\/[\w-]+\.script\.googleusercontent\.com/

    won't match

    https://n-5fydpzckmar5yncflnjytuyfcusdcfogmgc2eti-1lu-script.googleusercontent.com

    Note that there's no . between https://n-5fydpzckmar5yncflnjytuyfcusdcfogmgc2eti-1lu- and script.googleusercontent.com

    The correct regex would be

    /https:\/\/[\w\-]+script\.googleusercontent\.com/