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:

    Issue:

    Script Flow:

    Required Reading:

    Notes:

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