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

Question

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

81 Views Asked by SoftTimur At 23 October 2023 at 00:37

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?

Original Q&A

There are 2 best solutions below

TheMaster On 23 October 2023 at 03:29

You can use window.postMessage to communicate.

Sample snippets:

modal.html

<script>
window.addEventListener('message',e =>{
  if(e.origin !== "https://www.myfrontend.com") return;
  const frontEndWindow = e.source;
  const data = JSON.parse(e.data);
  const {funcToRun, args} = data;
  //TODO: SECURITY: 
  // Check syntax of funcToRun, args
  // Check funcToRun against list of available functions 
  // Check args are as expected 

  //TODO Add failureHandler
  google.script.run.withSuccessHandler(res => {
    console.log(res);
    const iframeWindow = document.querySelector('#myFrame').contentWindow;
    iframeWindow.postMessage(({type:'success',response:res}),"https://www.myfrontend.com")
  })[funcToRun](...args)
  },false)
</script>
<iframe 
  id="myFrame"
  src="https://www.myfrontend.com/#/home"
  style="width: 100%; height: 800px; border: none"
</iframe>

modal.gs

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

frontEnd.com/index.html

window.parent.postMessage(
  (JSON.stringify({funcToRun: 'getData', args:[1,2]})), 
  'https://n-5fydpzckmar5yncflnjytuyfcusdcfogmgc2eti-1lu-script.googleusercontent.com')

**TheMaster** · Accepted Answer · 2023-10-23T05:58:51.593000

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/

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

1. Use createHtmlOutput and iframe

2. Use createHtmlOutputFromFile

There are 2 best solutions below

Sample snippets:

modal.html

modal.gs

frontEnd.com/index.html

Related Questions in GOOGLE-APPS-SCRIPT

Related Questions in GOOGLE-SHEETS

Related Questions in IFRAME

Related Questions in CORS

Related Questions in ADD-ON

Trending Questions

Popular # Hahtags

Popular Questions