Search code examples
google-apps-scriptgoogle-sheetscorsgoogle-sites

Run a function published in Webapp from a Google site

I want to run a function in a webapp from a google site as onload function.

code.gs

function doGet(e){
  return HtmlService.createHtmlOutputFromFile("page"); 
}

function myfunc(datavalues) {
      var sheetURL = 'https://docs.google.com/spreadsheets/d/SHEET-ID-IS-HERE';
     // Current Active Sheet
     var activeGoogleSheet = SpreadsheetApp.openByUrl(sheetURL);
     var activeSheet = activeGoogleSheet.getActiveSheet();
      activeSheet.getRange(1,1).setValue(datavalues[0]);
      activeSheet.getRange(1,2).setValue(datavalues[1]);
     // ---------------------------------------------------- //
}

page.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
  
    <body>
  </body>
  
  
  <script>
       // function run from code.gs
       function runfunc(values){
                 google.script.run.myfunc(values);
       }
       
  </script>
</html>

Finally, i have following code embeded in google site :

<!DOCTYPE html>
<html>
<script src="https://script.google.com/a/macros/s/WEBAPPURL/exec"></script>
<script>
    function loadFunc(){
         var values = ['aaaa',123];
         runfunc(values);       
 }
</script>


<body onload='loadFunc()'>

<body/>
</html>

This should write the values to the google sheet, but it is not working. Is it even possible to run function like this from webapp? Is something run or is there an alternative?

Update on the problem: The code.gs is updated with doPost

code.gs

    function doGet(e){
     console.log("get request");
      return HtmlService.createHtmlOutputFromFile("page"); 
    }
    
    function myfunc(datavalues) {
          var sheetURL = 'https://docs.google.com/spreadsheets/d/SHEET-ID-IS-HERE';
         // Current Active Sheet
         var activeGoogleSheet = SpreadsheetApp.openByUrl(sheetURL);
         var activeSheet = activeGoogleSheet.getActiveSheet();
          activeSheet.getRange(1,1).setValue(datavalues[0]);
          activeSheet.getRange(1,2).setValue(datavalues[1]);
         // ---------------------------------------------------- //
    }
// Post method
function doPost(datavalues){
  console.log("post Request");
// check the parameters


 if(typeof e !== 'undefined')
  var datavalues =  JSON.stringify(JSON.parse(e.parameter)); 
  console.log(datavalues);
  var sheetURL = 'https://docs.google.com/spreadsheets/d/URL/';
     // Current Active Sheet
     var activeGoogleSheet = SpreadsheetApp.openByUrl(sheetURL);
     var activeSheet = activeGoogleSheet.getActiveSheet();
     // first empty row
     var dataRow = activeSheet.getDataRange().getLastRow()+1;
     // ---------------------------------------------------- //
     // writing data to the sheet
     // ---------------------------------------------------- //
          activeSheet.getRange(dataRow,1).setValue(datavalues['a']);
         activeSheet.getRange(dataRow,2).setValue(datavalues['b']);
     // ---------------------------------------------------- // 
   return ContentService.createTextOutput(JSON.stringify(e));
    }

Code embedded in google site :

<!DOCTYPE html>
<html>
<script>
function postRequest(url, data) {
  return fetch(url, {
    method: 'POST', 
    credentials: 'omit';
    headers: {'Accept': 'application/json',
    'Content-Type': 'application/json'
    }
    body:  JSON.stringify(data);
 });
}

function onloadfunc(){
console.log('loadding');
var data = {'a':'xxx','b':'ppp'};
postRequest('https://script.google.com/a/macros/s/WEBAPP/exec', data).then(function(response) {
    console.log("ok");
}).catch(function(error) {
    console.log(error);
}
</script>


<body onload="onloadfunc()">
<body/>


</html>

Update to both doPost and the function to postrequest. but still does not work.

The error in console:

Failed to load resource: the server responded with a status of 405 () Access to fetch at 'https://script.google.com/a/macros/s/WEBAPPURL/exec' from origin 'https://2008039421-atari-embeds.googleusercontent.com' has been blocked by CORS policy: Response to preflight request doesn't pass access control check: No 'Access-Control-Allow-Origin' header is present on the requested resource. If an opaque response serves your needs, set the request's mode to 'no-cors' to fetch the resource with CORS disabled.

I added mode:'no-cors' to the fetch and that runs the onloadfunc() now and runs the fetch. It gives me other error regarding the fetch or the function but now it gives me the following error.

Failed to load resource: the server responded with a status of 401 ()

The webapp is open to use for anyone in the network and the google site is also using it through the network. So it should not have problem with authentication. I do not know what is wrong now.

Latest error with JSON.stringify

<!DOCTYPE html>
<html>
<script>
function postRequest(url, data) {
  return fetch(url, {
    method: 'POST', 
    mode: 'no-cors',
    headers: {
    'Accept': 'application/x-www-form-urlencoded',
    'Content-Type': 'application/x-www-form-urlencoded'
    },
    credentials:'include',
    body: data
  });
}
function onloadfunc(){
console.log('loading');
var data = JSON.stringify({'a':'xxx','b':'ppp'});
 postRequest('https://script.google.com/a/WEBAPP/exec', data).then(function(response) {
 var div = document.getElementById('errorOutput');
      div.innerHTML = "response"+response;
        console.log("ok");
    }).catch(function(error) {
     var div = document.getElementById('errorOutput');
      div.innerHTML = "error"+error;
    console.log(error);});
}
</script>
<body onload="onloadfunc()">
<div id="errorOutput"></div>
</body>
</html>

code.gs

// Post method
function doPost(e){
  //console.log(JSON.stringify(e,null, 2));
  // check the parameters
  if(typeof e !== 'undefined')
  console.log(e);
  var datavalues = e.parameter; 
  var sheetURL = 'https://docs.google.com/spreadsheets/d/SHEETURL/';
     // Current Active Sheet
     var activeGoogleSheet = SpreadsheetApp.openByUrl(sheetURL);
     var activeSheet = activeGoogleSheet.getActiveSheet();
     // first empty row
     var dataRow = activeSheet.getDataRange().getLastRow()+1;
     // ---------------------------------------------------- //
     // writing data to the sheet
     // ---------------------------------------------------- //

          activeSheet.getRange(dataRow,1).setValue(datavalues.a);
     
         activeSheet.getRange(dataRow,2).setValue(datavalues.b);
     // ---------------------------------------------------- // 
   return ContentService.createTextOutput(JSON.stringify(e));
}

With this, the doPost is called and it works!!! Only problem now i have is my dictionary is passed wrong. In site it is {'a':'xxx','b':'ppp'} but the total argument passed looks like (result of console.log(e)) :

{parameter={{"a":"xxx","b":"ppp"}=}, contextPath=, contentLength=31, queryString=, parameters={{"a":"xxx","b":"ppp"}=[]}, postData=FileUpload}

Somehow extra = is added and then the dictionary is passed as it is.

Solution

  • Goal:

    • Run a specific function in a WebApp published in a domain using Google apps script with
      • Access: Anyone from domain
    • from
      • New Google sites published in the same domain.

    Issue:

    • CORS: Both the Google apps script web app(hereafter, Gas-webapp) and user code in Google sites(hereafter, Gs webapp) run in iframes of different origin- https://[DIFFERENT_URL].googleusercontent.com sandboxed in *google.com.

    Script Flow:

    • Use Fetch api to POST data from Google sites to web-app.
    • Receive POST request from Google sites and execute the function.
    • Response can't be received by the Gs webapp. In essence, this is a one way communication from Gs webapp to Gas-webapp.

    Required Reading:

    Notes:

    • Gas web-app by default provides the following CORS response header to any authenticated CORS request:

      Access-Control-Allow-Origin : *

    • However, If the requested is unauthenticated or if the script errors out, You're redirected to Google's login page/error page; Both pages doesn't have Access-Control-Allow-Origin set and access will be blocked from all web pages.

    • The only exception to the unauthenticated requests mentioned above is publishing Gas web-app with access: Anyone,even anonymous.

    • Gas web-app doesn't allow OPTIONS method. Hence, all requests that are preflighted will fail.

    • The only remaining CORS option compatible with gas-web app is Simple requests, that are not preflighted by the browser.

    • However, simple POST requests are essentially unauthenticated. In this script, We use the credentials option in the request to include third party credentials. Here, we are after User account's Google's credentials. By using this option, We are essentially authenticating using user account's Google's credentials. The following conditions must be satisfied to use this option:

      • The user must be logged in to Google, so as to have login cookies to send with the post request.
      • Third party cookies must be enabled in browser, as iframe is of different origin.

    • The request mode can be set to cors or no-cors.

    • However, If cors is set, according to the spec documentation, the following response header must be provided by the gas web-app to post request with credentials:

      Access-Control-Allow-Credentials

      This is not provided by the gas-webapp. Though the request will be send(as it is not preflighted), the response can't be received(not shared), i.e., doPost will still run, but response cannot be received by the gs-webapp.

    • If no-cors is set, fetch will return an "opaque filtered response"(a empty response).

      An opaque filtered response is a filtered response whose type is "opaque", URL list is the empty list, status is 0, status message is the empty byte sequence, header list is empty, body is null, and trailer is empty.

    • In essence, You can do a one way communication from gs-web-app to gas-web-app, when access is set to Anyone(not anonymous).

    • POST requests made with application/x-www-form-urlencoded must be a string of format: input1=1&input2=4. URLSearchParams could be used to craft such string from objects.

    Snippet:

        <!DOCTYPE html>
    <html>
    <script>
    function postRequest(url, data) {
      return fetch(url, {
        method: 'POST', 
        mode: 'no-cors',//or 'cors': Though request fails, doPost will execute
        headers: {
        'Accept': '*/*',//**Modified**
        'Content-Type': 'application/x-www-form-urlencoded'
        },
        credentials:'include',//Access Credentials in browser cookies
        body: data
      });
    }
    function onloadfunc(){
    console.log('loading');
    var data = (new URLSearchParams({'a':'xxx','b':'ppp'})).toString();//**MODIFIED**
     postRequest('https://script.google.com/a/WEBAPP/exec', data).then(function(response) {
    //************OPAQUE RESPONSE: Nothing will be received************
     var div = document.getElementById('errorOutput');
        response.text(res=>
          div.innerHTML = "response "+res)
            console.log("ok");
        }).catch(function(error) {
         var div = document.getElementById('errorOutput');
          div.innerHTML = "error"+error;
        console.log(error);});
    }
    </script>
    <body onload="onloadfunc()">
    <div id="errorOutput"></div>
    </body>
    </html>

    code.gs

        // Post method
    function doPost(e){
      console.log(JSON.stringify(e,null, 2));
      // check the parameters
      if(typeof e !== 'undefined')
      console.log(e);
      var datavalues = e.parameter; 
      var sheetURL = 'https://docs.google.com/spreadsheets/d/SHEETURL/';
         // Current Active Sheet
         var activeGoogleSheet = SpreadsheetApp.openByUrl(sheetURL);
         var activeSheet = activeGoogleSheet.getActiveSheet();
         // first empty row
         var dataRow = activeSheet.getDataRange().getLastRow()+1;
         // ---------------------------------------------------- //
         // writing data to the sheet
         // ---------------------------------------------------- //
  
              activeSheet.getRange(dataRow,1).setValue(datavalues.a);
         
             activeSheet.getRange(dataRow,2).setValue(datavalues.b);
         //WON'T BE RECEIVED BY the post requester anyway// 
       return ContentService.createTextOutput(JSON.stringify(e));
    }