Search code examples
google-apps-scriptweb-applications

Apps script return values from server function to html form


I need help of professionals at Apps script. I have the project implemented by web-app. I wrote script on server-part

var url = "https://docs.google.com/spreadsheets/d/1s8l-8N8dI-GGJi_mmYs2f_88VBcnzWfv3YHgk1HvIh0/edit?usp=sharing";
var sprSRCH = SpreadsheetApp.openByUrl(url);
let sheetSRCHSSCC = sprSRCH.getSheetByName("PUTAWAY_TO");
 
function GetQ(){
  
  var QPLAN = sheetSRCHSSCC.getRange("M2:M").getValues().filter(String).length;
  var myArray = sheetSRCHSSCC.getRange("O2:O" + (QPLAN + 1)).getValues();
  
  var QFACT = 0;
   for (i = 0; i < myArray.length; i++) { 
     if (myArray[i] != "") {
       QFACT += 1
     }
   }
   
}

I need to return values from this function to inputs:
QFACT to FACT
QPLAN to PLAN
            <div class="input-field col s3">
              <input disabled value="" id="PLAN" type="text" >
              <label for="disabled">PLAN</label>
            </div>
            <div class="input-field col s3">
              <input disabled value="" id="FACT" type="text" >
              <label for="disabled">FACT</label>
            </div>

I will be grateful for the help. I'm new at this))


Solution

  • If you are using Apps Script deploying Web App, I can see 2 possibilities :

    1/ Get data at the loading of the page (and only at the loading) :

    In code.gs :

    function doGet() {
      var tmp = HtmlService.createTemplateFromFile('page');
      tmp.QFACT = "hello";
      tmp.PLAN = "World!";
      return tmp.evaluate();
    }
    

    In page.html :

    <html>
      <body>
        <h5><?= QFACT ?></h5>
        <h5><?= QPLAN ?></h5>
      </body>
    </html>
    

    2/ If you need to refresh the data by click button, or something else, you will need to operate diffently :

    Add a page-js.html to your project, and bind it at the end of your page.html

    <html>
      <body>
        <h5 id="QFACT"></h5>
        <h5 id="QPLAN"></h5>
      </body>
      <?!= include("page-js"); ?> 
    </html>
    

    then in your page-js.html :

    <script>
      function refresh() {
        google.script.run.withSuccessHandler(callback).refresh();
      }
    
      function callback(e) {
        document.getElementById('QPLAN').innerHTML = e.QPLAN;
        document.getElementById('QFACT').innerHTML = e.QFACT;
      }
    </script>
    

    and finally add the refresh() function in your code.gs :

      function refresh() {
        var obj = {};
    
        obj.QPLAN = "QPLAN";
        obj.QFACT = "QFACT";
    
        return obj;
      }