Using HTML file to Query a Google Spreadsheet using a Textbox for Query, using App Script

Question

I have a spreadsheet which I have more than 1000 lines, each column is a field, one of the columns has the field called Domain, "which will be the value I need to query", my application using HTML will need to query using a Text box the HTML to a function, to search the Spreadsheet and return the values of the same line in the HTML.

Now here is the HTML Code:

<html>
  <head>
    <link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/angular_material/0.10.0/angular-material.min.css">
    <link rel="stylesheet" href="https://storage.googleapis.com/code.getmdl.io/1.0.0/material.blue-green.min.css" /> 
    <script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
    <script src="//ajax.googleapis.com/ajax/libs/angularjs/1.3.15/angular.min.js"></script>
    <script src="//ajax.googleapis.com/ajax/libs/angularjs/1.3.15/angular-route.js"></script>
    <script src="//ajax.googleapis.com/ajax/libs/angularjs/1.3.15/angular-sanitize.js"></script>
    <script src="//ajax.googleapis.com/ajax/libs/angularjs/1.3.15/angular-animate.js"></script>
    <script src="//ajax.googleapis.com/ajax/libs/angularjs/1.3.15/angular-aria.min.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.3.15/angular-messages.min.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/angular_material/0.10.0/angular-material.min.js"></script>
    <script src="https://storage.googleapis.com/code.getmdl.io/1.0.0/material.min.js"></script>

    <?!= include('WebPage.js'); ?>
    <?!= include('Style'); ?>
  </head>
  <body ng-app="webApp">
    <div ng-controller="webAppCtrl">
      <div>
        <md-toolbar class="md-theme-light">
          <div class="md-toolbar-tools">
            <div>DeLight App</div>
          </div>
        </md-toolbar>
    <div id="body">
    <div layout="row" layout-align="center start" layout-margin layout-fill layout-padding>
      <div>
        <form action="#">
          <div class="mdl-textfield mdl-js-textfield mdl-textfield--floating-label">
            <input class="mdl-textfield__input" type="text" id="sample3" />
            <label class="mdl-textfield__label" for="sample3">Enter Domain</label>
          </div>
        </form>
      </div>
    </div>
   <div layout="row" layout-align="center start" layout-margin layout-fill layout-padding>
     <div id="leftContainer" class="md-whiteframe-z2" flex="50">
       <md-toolbar class="md-theme-light">
         <div class="md-toolbar-tools">
           <div>Customer Info</div>
         </div>
       </md-toolbar>
       Pull Customer Info here from Sheet
     </div>
     <div id= "rightContainer" class="md-whiteframe-z2" flex="50">
       <md-toolbar class="md-theme-light">
         <div class="md-toolbar-tools">
           <div>Task List</div>
         </div>
       </md-toolbar>
       <md-list-item>
         <p>Verified Domain Owner</p>
         <md-checkbox class="md-secondary"></md-checkbox>
       </md-list-item>
       <md-list-item>
         <p>User Creation</p>
         <md-checkbox class="md-secondary"></md-checkbox>
       </md-list-item>
       <md-list-item>
         <p>Dual Delivery</p>
         <md-checkbox class="md-secondary"></md-checkbox>
       </md-list-item>
     </div>
   </div>
     </div>
      </div>
    </div>
  </body>
</html>

Here is the Web App Function Scripts page:

function doGet(e) {
  return HtmlService.createTemplateFromFile('Index').evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle('Test')
}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .getContent();
}

function testSheet(){
  var ss = SpreadsheetApp.openById("abc123456");
  Logger.log(ss.getName());
}

The problem that I have is that I don't know how I can store the information that the user submits to the Textbox so I can later search my spreadsheet using a snippet I found here which is:

function test(){
 var sh = SpreadsheetApp.openById("abc123456");
 var data = sh.getDataRange().getValues(); // read all data in the sheet
 for(n=0;n<data.length;++n){ // iterate row by row and examine data in column A
 if(data[n][0].toString().match('searchvariable')=='searchvariable'){ data[n][5] = 'YES'};// if column A contains 'xyz' then set value in index [5] (is column F)
 }
 Logger.log(data)
 sh.getRange(1,1,data.length,data[0].length).setValues(data); // write back to the sheet
 }

Any Ideas how can I achieve this?

Thank you

Alan Wells · Accepted Answer · 2015-07-14T18:57:24.120

You need to get the data out of the form, then send the form values to a server side .gs script function using google.script.run.myFunctionName().

google.script.run - Client Side API

There are various ways that you can get the values out of the input fields. You can get the form as a whole, and send a modified form object to the server, or you can get the values individually.

If you get the form object, google changes the form object. The only way that a .gs function can get values out of the form object is by using the HTML name attribute. In other words, the only way you can get the form object strategy to work, is by giving every input field a name attribute with a name.

<input name='myNameGoesHere' class="mdl-textfield__input" type="text" id="sample3" />

The Google documentation shows putting the google.script.run code directly into the click attribute of the input button. You can try it that way, or put the code into a separate script tag.

<input type="button" value="Not Clicked"
    onclick="google.script.run
        .withSuccessHandler(updateButton)
        .withUserObject(this)
        .getEmail()" />

HTML code:

<script>
  //Put an anonymous function into the browsers window object
  window.callServer = function(theFormObject) {
    google.script.run
     .search(theFormObject);
</script> 

<form>
  <div class="mdl-textfield mdl-js-textfield mdl-textfield--floating-label">
    <input name='domain' class="mdl-textfield__input" type="text" id="sample3" />
    <label class="mdl-textfield__label" for="sample3">Enter Domain</label>
    <input type="button" value="Not Clicked" onclick="callServer(this.parent.parent)"/>
  </div>
</form>

Code.gs

function search(myForm) {
  //Look in VIEW menu, and LOGS menu Item to see if the form was passed in
  Logger.log('myForm: ' + myForm);
  var domain = myForm.domain;
  Logger.log('domain: ' + domain);
}

Hi Sorry to re ask but I think I mess up in something in here: '

Enter Domain

' — Rene A., Jul 14 '15 at 18:17
I don't know if that was correctly set up and on the gs I added a function called search(): function search() { var domain = index.html.form.domain; } — Rene A., Jul 14 '15 at 18:22
Hi Sorry to continue asking things, In the `.gs` I put the code you sent me `function search(myForm) { //Look in VIEW menu, and LOGS menu Item to see if the form was passed in Logger.log('myForm: ' + myForm); var domain = myForm.domain; Logger.log('domain: ' + domain); } function search(myForm) { //Look in VIEW menu, and LOGS menu Item to see if the form was passed in Logger.log('myForm: ' + myForm); var domain = myForm.domain; Logger.log('domain: ' + domain); }` — Rene A., Jul 16 '15 at 13:40
And I got an error saying: TypeError: Cannot read property "domain" from undefined. (line 21, file "Code") And I also put the code in HTML side: `` and modified the form to be name myform: — Rene A., Jul 16 '15 at 13:50
`

Enter Domain

` Any Ideas, I'm really sorry to keep asking — Rene A., Jul 16 '15 at 13:51
Maybe it has to do with `this.parent.parent`. I'm not sure it that's correct. Do some research on the key work `this`. [Stackoverflow this keyword](http://stackoverflow.com/questions/3127429/how-does-the-this-keyword-work) — Alan Wells, Jul 16 '15 at 23:25

Using HTML file to Query a Google Spreadsheet using a Textbox for Query, using App Script

1 Answers1

HTML code:

Code.gs