CODEWITHSUNDEEP
searchgoogle-apps-scriptgoogle-sheets
Moustafa
Moustafa

Reputation: 27

Google Script WebApp html Search from Google Spreadsheet

I had a web app with a form that sends data to a Google Spreadsheet. Now I want to create another html page with a search function to pull data from the Google sheet and display it on my html page. Is there a way to do that? PLEASE HELP. I have looked everywhere but no codes were helpful. Thanks in advance.

Upvotes: 1

Views: 3795

Answers (2)

Cooper
Cooper

Reputation: 64042

A search dialog and webapp

Code.gs:

function findNeedleInHaystack(sObj) {
  var sObj=sObj||{};
  sObj.column=sObj.column||1;
  sObj.needle=sObj.needle||22;
  sObj.haystack=sObj.haystack||'Sheet1';
  sObj.startrow=sObj.startrow||2;
  sObj.id=sObj.id||'Spreadsheet ID';
  sObj.found="No results found";
  var ss=SpreadsheetApp.openById(sObj.id);
  var sh=ss.getSheetByName(sObj.haystack);
  var rg=sh.getRange(sObj.startrow,1,sh.getLastRow()-sObj.startrow+1,sh.getLastColumn());
  var v=rg.getValues();
  for(var i=0;i<v.length;i++) {
    if(v[i][sObj.column-1]==sObj.needle) {
     sObj.found=v[i];
     break; 
    }
  }
  return sObj;
}

function needleInHaystackDialog() {
  var userInterface=HtmlService.createHtmlOutputFromFile("aq6").setWidth(800);
  SpreadsheetApp.getUi().showModelessDialog(userInterface, "Needle In Haystack")
}

function doGet() {
  return HtmlService.createHtmlOutputFromFile("aq6").setWidth(600);
}

aq5.html:

<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
</head>
<body>
<br /><input id="txt1" type="text" placeholder="Sheet Name" />
<br /><input id="txt2" type="text" placeholder="Search String" />
<br /><input id="txt3" type="text" placeholder="Search Column Number" />
<br /><input id="txt4" type="text" placeholder="Start Row" />
<br /><input id="txt5" type="text" placeholder="Spreadsheet ID" />
<br /><textarea id="found" rows="2" cols="50"></textarea>,
<br /><input type="button" value="Search" onClick="search();" />
<script>
  function search() {
    var s1=$('#txt1').val();
    var s2=$('#txt2').val();
    var s3=$('#txt3').val();
    var s4=$('#txt4').val();
    var s5=$('#txt5').val();
    google.script.run
    .withSuccessHandler(function(sObj){
      $('#found').val(sObj.found);
    })
    .findNeedleInHaystack({haystack:s1,needle:s2,column:s3,startrow:s4,id:s5});

  }
</script>
</body>
</html>

I tested the dialog version. It worked the first time. You can test and debug the doGet().

Upvotes: 2

Adam Stevenson
Adam Stevenson

Reputation: 727

Are you looking to build and host this page externally (i.e. on your own website/server outside of Google's environment), or do you want to build a webpage that is coded, managed, and hosted within G Suite?

If it's the former, you'll need to work with the Google Sheets API to pull data from your spreadsheet.

If it's the latter, read up on Web Apps and HTML Service for Apps Script, which allows you to build and host basic web apps in G Suite.

If you need some more examples to work from, try the search terms "host web app google apps script" in your favorite search engine.

Upvotes: 0

Related Questions