Pull data to a html page from Google Sheets based on user input

0
votes

I have a Google spreadsheet with several rows of data in four columns whose headings are

Employee ID | Name | Age | Designation |

I want to create a web App (html page with form elements) in which if I put in the Employee ID value (which is unique) in a text field and click Submit, the other three details (viz. Name, Age, Designation) of only that particular Employee are displayed in a table below in that same page.

I was not able to see any examples where select parts of the sheet are returned as a table based on user input value.

Would appreciate help.

2
google-apps-script

2 Answers

0
votes

While I will not be able to code your web app, you can start by researching client-server communication in HTML. The google.script.run API is the most basic call, you can start on that.

You may want to research on scriptlets as well. Basically this allows you to execute Apps Script code in your HTML using the tags <? and ?>.

The templated HTML documentation provides some examples that you can expand to suit your need.

You may also want to study some examples here.

0
votes

Try something like this:

Note I haven't tested this so some tweaking may be required.

HTML:

<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <div id="msg"></div><!-- You can add css to hide these and then turn them on with js as required -->
    <div id="found"></div>
    <form>
      <input type="text" name="id" placeholder="Enter Employee ID" /><br />
      <input type="button" value="Search" onClick="processForm(this.parentNode);" />
    </form>
    <script>
      function processForm(obj) {
        google.script.run
        .withSuccessHandler((obj)=>{
          if(!obj.msg) {
            document.getElementById('found').innerHTML=`EmployeeId: ${obj.id} Name: ${obj.n} Age: ${obj.a} Designation: ${obj.d}`;
          }else{
            document.getElementById('found').innerHTML=`Message: ${obj.msg}`;
          }
        })
        .search(obj);
      }
      console.log("My Code");
    </script>
  </body>
</html>

GS:

function mysearch(obj) {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Sheet1');
  const [hA, ...data]=sh.getDataRange().getValues();
  let idx={};
  let found=false;
  hA.forEach((h,i)=>{idx[h]=i;});
  for(var i=0;i<data.length;i++) {
    if(data[i][idx['EmployeeID']]==obj.id) {
      Logger.log({id:obj.id,n:data[i][idx['Name']],a:data[i][idx['Age']],d:data[i][idx['Designation']]});
      found=true;
      break;
    }
  }
  if(found){
    return {id:obj.id,n:data[i][idx['Name']],a:data[i][idx['Age']],d:data[i][idx['Designation']]};
  } else {
    return {msg:"Not found"}
  }
}

function launchMyNewDialog() {
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutputFromFile('ah1'),'test');
}