Google Script WebApp html Search from Google Spreadsheet

Question

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.

See https://stackoverflow.com/questions/4143901/how-can-i-access-google-sheet-spreadsheets-only-with-javascript - there's a perfect answer from @Mike McKay — 1000Gbps, Dec 02 '19 at 17:56
Welcome to Stack Overflow. Please read this [Why can someone help me is not an actual question](https://meta.stackoverflow.com/questions/284236/why-is-can-someone-help-me-not-an-actual-question) — Anton Dementiev, Dec 02 '19 at 18:25

Cooper · Accepted Answer · 2019-12-03T00:30:01.113

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().

Thanks for the response. It worked just fine, returning the search string. I am retrieving the value of the result text area, but unable to break it down, for example, the string contains first name, last name, email, etc, how can I split this information to be able to move them to separate divs in my html? I tried many ways, but in vain. Thanks a lot! — Moustafa, Dec 02 '19 at 23:18
Sorry for the late response. My data has First Name, Last Name, Email, Phone, Reg. No., and Serial Nb. I modified the script to have the button get the search text, paste it in a search cell inside the sheet I am using, then this will get me only one result in the "text area id="found"" as the following: John,Doe,email@email.com,966540000000,34-FG-674347,AS2020-245133. How can I separate them? I want to pull each value to a separate div in my html. Thanks in advance. — Moustafa, Dec 05 '19 at 13:50
sObj.found is array. So First Name = sObj.found[0]; Last Name = sObj.found[1]; and so on. — Cooper, Dec 05 '19 at 14:44
You can use Logger.log or console.log to determine object structure. — Cooper, Dec 05 '19 at 15:05
Thank you for the help my dear. Excuse my questions, but I am not very expert with the whole thing, I tried to do that, but couldn't see where to put it. I tried in the html like and code.gs. Sorry again, your patience is appreciated. — Moustafa, Dec 08 '19 at 10:57
I managed to do it using split and splice methods. I hoped there was an easier method, though. Thank you for everything. @Cooper — Moustafa, Dec 08 '19 at 13:21

score 0 · Answer 2 · answered Dec 02 '19 at 17:49

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.

Google Script WebApp html Search from Google Spreadsheet

2 Answers2