Google sheet web app search data from sheet error-CodePudding

I  want to search data from google sheet and show it in form  by web app.

PAN is unique and 5 digit number. When we enter PAN ,5 digit number to( PAN) input form and click update button then it should search data for PAN in sheet and if match then bring entire row to the web form, otherwise it show unavailable.

When we enter PAN number and click update button , it show wrong data in form. But when we check it by Logger.log() , it show right data .

I don't know and figure out why it show wrong data in web form when we click update button, Please help me and let me know what is the cause for this issue

function doGet(e)
{
  return HtmlService.createHtmlOutputFromFile("page");
}




 function searchData(pan)
    {
      var ss=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
      //var nameList=ss.getRange("A1:A").getValues().flat();
      //var panList=ss.getRange("B1:B").getValues().flat();
      //var aadharList=ss.getRange("c1:c").getValues().flat();
      //var emailList=ss.getRange("d1:d").getValues().flat();
      //var phnList=ss.getRange("e1:e").getValues().flat();
    
      var data=ss.getRange(1,1,ss.getLastRow(),5).getValues();
    
      var panList=data.map(function(r){return r[1];});
      var nameList=data.map(function(r){return r[0];});
      var aadharList=data.map(function(r){return r[2];});`enter code here`
      var emailList=data.map(function(r){return r[3];});
      var phnList=data.map(function(r){return r[4];});
    
      var index=panList.indexOf((pan));
      if(index>-1)
      {
      var name=nameList[index];
      var aadhar=aadharList[index];
      var email=emailList[index];
      var phone=phnList[index];
      return [name,pan,aadhar,email,phone]
      }
      else
      { return "unavailable"}
      
    }
    
    Logger.log(searchData(66666))


//html file..(page.html)
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <h1> Web App </h1>

    <label> Name </label>
    <input type="text" id="username">

    <label> PAN</label>
    <input type="number" id="userpan">

    <label> Aadhar </label>
    <input type="number" id="useraadhar">

    <label> Email </label>
    <input type="text" id="useremail">

    <label> Telepnoe </label>
    <input type="text" id="userphone">

    <button id="btn"> Update </button>

    <script>

      //document.getElementById("userpan").addEventListener("input",search);
      document.getElementById("btn").addEventListener("click",search);

      function search()
      {
        var pan=document.getElementById("userpan").value;
        if(pan.length==5)
        {
        google.script.run.withSuccessHandler(test).searchData(pan);

        function test(s)
        {
          document.getElementById("username").value=s[0];
          document.getElementById("userpan").value=pan;
          document.getElementById("useraadhar").value=s[2];
          document.getElementById("useremail").value=s[3];
          document.getElementById("userphone").value=s[4];
        }  
        }     
      }
    </script>
  </body>
</html>

CodePudding user response：

From But when we check it by Logger.log() , it show right data . and your showing script, I thought that the reason of your issue might be due to that the values of panList are the number while var pan=document.getElementById("userpan").value is the string. In this case, even when the inputted value to the input tag is the number 66666, the value of var pan=document.getElementById("userpan").value is the string. By this, var index=panList.indexOf((pan)); is always -1.

If your script is simply modified, please modify it as follows.

From:

var data=ss.getRange(1,1,ss.getLastRow(),5).getValues();

To:

var data = ss.getRange(1, 1, ss.getLastRow(), 5).getDisplayValues();

By this, when pan of 66666, which is the string, is sent from Javascript to Google Apps Script, data retrieved by getDisplayValues() is the string. By this, var index=panList.indexOf((pan)); can be worked.

Note:

I thought that your Google Apps Script might be able to reduce the process cost as follows.

  function searchData(pan) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    var range = sheet.getRange("B1:B"   sheet.getLastRow()).createTextFinder(pan).matchEntireCell(true).findNext();
    if (range) {
      return sheet.getRange(range.getRow(), 1, 1, 5).getValues()[0];
    }
    return "unavailable";
  }

Note:

When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".

From:

To:

Note:

Note:

References: