Posted on by

How to Create Web App Report from Google Sheets

In this post, I demonstrate how to take a Web App and display an HTML report with input filters using Google Sheets. Also included in this video is the use of the QUERY formula for Google Sheets.

How-to Video:

Video Notes:

  • Legacy Apps Script Editor Used in Video.
  • Apps Script (Script Editor) is now located under the tab ‘Extensions’ instead of ‘Tools’ on Google Sheets.

Code in Video:

function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('WebApp');
}

function getResultsSQL(type, color, height1, height2){

  var url = '';  //Paste URL of GOOGLE SHEET
  var ss= SpreadsheetApp.openByUrl(url);
  var webAppSheet2 = ss.getSheetByName("Sheet2");
  var webAppSheet1 = ss.getSheetByName("Sheet1");
  var sqlStatement = 'select A, B, C where 1 = 1 ';
  
  if(type != '')
  {
    sqlStatement += " and A = '"+type+"' ";
  }

  if(color != '')
  {
    sqlStatement += " and B = '"+color+"' ";
  }

  if(height1 != '')
  {
    if(height2 == '')
    {
      height2 = height1;
    }
    
    sqlStatement += " and C >= "+height1+" and C <=  "+height2+" ";
  }
  
  var getLastRow = webAppSheet1.getLastRow(); 
  var tableRange = 'Sheet1!A1:C'+getLastRow;  
  var query = '=QUERY('+tableRange+',\"'+sqlStatement+'\")';
  var setResults = webAppSheet2.getRange(1, 1).setFormula(query); 
  var getResults = webAppSheet2.getDataRange().getValues();

  return getResults;

}
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script>
    function GetReport()
    {
      var type = document.getElementById("type").value;
      var color = document.getElementById("color").value;
      var height1 = document.getElementById("height1").value;
      var height2 = document.getElementById("height2").value;
      
      google.script.run.withSuccessHandler(function(ar) 
      {
      
      console.log(ar);
      
      var displayTable = '';
      displayTable = '<table border="1" cellpadding="5px" >';
      
      ar.forEach(function(item, index) 
      {   
        if(index == 0)
        {
          displayTable += "<tr>";
          displayTable += "<th>"+item[0]+"</th>";
          displayTable += "<th>"+item[1]+"</th>";
          displayTable += "<th>"+item[2]+"</th>";
          displayTable += "</tr>";          
        } 
        else
        {
          displayTable += "<tr>";
          displayTable += "<td>"+item[0]+"</td>";
          displayTable += "<td>"+item[1]+"</td>";
          displayTable += "<td>"+item[2]+"</td>";
          displayTable += "</tr>";    
        }
      });
      
      displayTable += '</table>';
      
      document.getElementById("dispalyTableHere").innerHTML = displayTable;
      
      }).getResultsSQL(type, color, height1, height2);
    
    }
    </script>
  </head>
  <body>
    Type:<input type="text" id="type" />
    Color:<input type="text" id="color" />
    Height:<input type="text" id="height1" size="5" />-<input type="text" id="height2" size="5" />
    <input type="button" value="Submit" onclick="GetReport()" /><br><br>
    <div id="dispalyTableHere" ></div>
  </body>
</html>

Related Posts

JSON Web App Convert Google Sheet into JSON Web App - In this post, I demonstrate how to convert a Google Sheet into a JSON Web App. The JSON Web App can be used as a web service to bring data into a website.
Sort Google Sheet Web App How to Sort Google Sheet on Google Web App - In this post, I demonstrate how to Sort a Google Sheet on a Google Web Application. It is set up for each Column to be Sorted in Ascending Order.
Filter Google Sheet in Web App How to Filter Google Sheet in Google Web App - In this post, I demonstrate how to Filter a Google Sheet in a Google Web Application using HTML and Google Apps Script.
Display Google Sheet in Web App How to Display Google Sheet in Web App - In this post, I demonstrate how to display a Google Sheet Table in a Web App using Google Apps Script.

Published by codewithcurt