Google App Script Bounded to Spreadsheet

Question

what I' trying to accomplish is a Google Spreadsheet for a project management. I've got lots of cells in a grid where a user should select either the item was completed or not. Now this spreadsheet would be available only to a Project Manager. The way I imagined the process would work was that Project Manager selects particular cells and assigns them to a technician's email address. Script would then generate mobile friendly html UI and send it to the technician (I thought of Google forms but I want to create more customized UI). Technician would then select a checkbox after completing a task which would at the same time update the spreadsheet. Next time technician would open the UI it would populate all the checkboxes that previously were selected. The only way I've found that I could make it work was a google script web app bounded to a spreadsheet. I've created a test HTML file and .gs file:

.html file

<head>
    <base target="_top">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
</head>
<body>
   <h1> Web App Test </h1>
   <input type="button" value="Click Me" id="buttonclicked" onclick="getSomeData()"/> 
   <div id="output" class="current">output</div>
    
   <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js">
   </script>
   
   <script>
    
      
     function getSomeData()
     {       
       google.script.run
           .withSuccessHandler(onSuccess)
           .withFailureHandler(showError)
           .testForWebApp();     
       myLog("in WebAppTest.html getSomeData()");
     } 
     
     function onSuccess(testParam)
     {
       var div = document.getElementById('output');
       if (sectionName == null)
       div.innerHTML = "<p style='color:red;'>You didn't hit the script</p>";     
       else
       div.innerHTML = "<p style='color:white;'>" + testParam + "</p>";            
     }
      
     function showError()
     {
       var div = document.getElementById('output');
       
       div.innerHTML = "<p style='color:red;'>You didn't hit the script</p>";     
          
     }
   </script>
</body>

and .gs file:

function doGet() 
{
  return HtmlService.createHtmlOutputFromFile('WebAppTest')
      .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}

function testForWebApp()
{
  myLog("In testForWebApp()");
  var msg = "Yep you hit the script!";
  return msg;
}

function myLog(log)
{
//log = 'test';
  Logger.log(log);
  
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();  
  var sheet = spreadsheet.getSheetByName('log');
  var lastRow = sheet.getLastRow();
  
  sheet.insertRowBefore(1);
  var newLogDateRange = sheet.getRange(1, 1);
  var newLogTextRange = sheet.getRange(1, 2);
  
  var now = new Date();
  newLogDateRange.setValue(now);
  newLogTextRange.setValue(log)
}

When I published the app and followed the generated link I saw my html page with a Click Me button. The click event ran the getSomeData() function which called google.script.run function. The server side .testForWebApp() gotten executed because I've gotten a log entry from myLog() but the .withSuccessHandler or .withFailureHandler were never called. At the same time the myLog() that should be executed after google.script.run never run either. I definitely don't understand how it works and suspect that if I publish a script as a web app the HTML is not bounded to the script anymore, but I couldn't find any information about it online. Thanks for your help.

Jack Brown Jack Brown · Accepted Answer · 2017-04-28T03:22:02

Firstly, you cannot call server-side myLog() function from your client side javascript unless you call it using google.script.run.myLog() Therefore

myLog("in WebAppTest.html getSomeData()");

in your getSomeData() doesnt log anything in your google sheet

Secondly, this code in function onSuccess(testParam)

if (sectionName == null)

is causing your function to terminate prematurely, since there is no variable called sectionName defined.

Note: You can monitor all these errors in the console of your web browser.

Below is the modified code that should work as you intend it to Final code:

Web App Test

 function getSomeData()
 {       
   google.script.run
       .withSuccessHandler(onSuccess)
       .withFailureHandler(showError)
       .testForWebApp();     
   console.log("in WebAppTest.html getSomeData()");  //Log it on the browser console
 } 

 function onSuccess(testParam)
 {
   var div = document.getElementById('output');
   if (testParam == null)          // Changed it to testParam from sectionName, to check the value returned from testWebApp()
   div.innerHTML = "<p style='color:red;'>You didn't hit the script</p>";     
   else
   div.innerHTML = "<p style='color:black;'>Success:" + testParam + "</p>";            
 }

 function showError()
 {
   var div = document.getElementById('output');

   div.innerHTML = "<p style='color:red;'>You didn't hit the script</p>";     

 }

Edit

One last note, the below code would make the return text invisible as the text and background color would be the same color (white):

div.innerHTML = "<p style='color:white;'>Success:" + testParam + "</p>";

hence changed the text color to black in the final code

Hope that helps!

Google App Script Bounded to Spreadsheet

2 Answers

Web App Test