Request : HTML autofill values based on user selection from dropdown

Question

Google sheet has fields: Customer Name, Mobile, Email, Birthday, and anniversary

For Example Google Sheet has data like below : name: prasad, mobile 9999999, Email xxx@gmial.com Birthday:11/1/1990 Birthday:11/1/2020

In HTML: Customer name is dropdown, values will come from google sheet

I need if Customer name selected from dropdown rest all fields will fill based on the selection from google sheet

For example: if I select name prasad rest will fill as per google sheet in the above example

HTML Code :

<h3>Customer Details</h3>

   
 
      <label>Customer Name: : <br>
      <input list="Customer_Name"  name="Customer_Name" ></label>
      <datalist id="Customer_Name" >
      <option value="" style="display:none;"></option> 
        <?!= getVendors(); ?>
      </datalist></td> 

    
    Customer Mobie Number:<br>
    <input type="number" name="Customer_Mobie_Number">
    <br>
    
    Customer Email:<br>
    <input type="email" name="Customer_Email">
    <br>
    
   Birthday Date:<br>
    <input type="date" name="Birthday">
    <br>


JS:

    function getVendors() {
      var active = SpreadsheetApp.getActive();
      var sheet = active.getSheetByName("Cus_Details");
      var lastRow = sheet.getLastRow();
      var myRange = sheet.getRange("A2:A" + lastRow); 
      var data    = myRange.getValues();
      var optionsHTML = "";
      for (var i = 0; i < data.length; i+=1) {
        optionsHTML += '<option>' + data[i][0] + '</option>';
      };
      return optionsHTML;
    }

Answer 1

I believe your goal as follows.

• When the dropdown list of "Customer_Name" is changed, you want to retrieve the values from the row and put to each input tag of Mobile,Email,Birthday,anniversary.
• Your Spreadsheet has the header of Customer Name,Mobile,Email,Birthday,anniversary.

In this case, how about the following modification?

HTML & Javascript side:

<h3>Customer Details</h3>
<label>Customer Name: : <br>
  <input list="Customer_Name" id="list" name="Customer_Name"></label>
<datalist id="Customer_Name">
  <option value="" style="display:none;"></option>
  <?!= getVendors(); ?>
</datalist></td>
Customer Mobie Number:<br>
<input type="number" id="Customer_Mobie_Number" name="Customer_Mobie_Number">
<br>
Customer Email:<br>
<input type="email" id="Customer_Email" name="Customer_Email">
<br>
Birthday Date:<br>
<input type="date" id="Birthday" name="Birthday">
<br>
Anniversary Date:<br>
<input type="date" id="Anniversary" name="Anniversary">
<br>
<script>
  document.getElementById("list").addEventListener("change", setValues);

  function setValues(e) {
    google.script.run.withSuccessHandler(([b, c, d, e]) => {
      document.getElementById("Customer_Mobie_Number").value = b;
      document.getElementById("Customer_Email").value = c;
      document.getElementById("Birthday").value = d;
      document.getElementById("Anniversary").value = e;
    }).getValues(e.target.value);
  }
</script>

Google Apps Script side:

function getValues(e) {
  var active = SpreadsheetApp.getActive();
  var sheet = active.getSheetByName("Cus_Details");
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange("A2:A" + lastRow).createTextFinder(e).matchEntireCell(true).findNext();
  return range.offset(0, 1, 1, 4).getValues()[0].map(e => e instanceof Date ? e.toISOString().split("T")[0] : e);
}

Note:

• When I saw your HTML and your sample image, it seems that "Anniversary Date" and the tags below "Anniversary Date". So in this answer, I added an input tag for "Anniversary Date". This modification is a sample modified script. So, please modify this for your actual situation.

Added:

About the following your replying,

thanks for your time, it works as I requested but when DOB or Aniversary copying from google sheet to field it is tacking one day less (Ex:14/08/1990 in sheet, in input it s coming 13/08/1990)

In this case, how about modifying above script of getValues(e) as follows.

From:

return range.offset(0, 1, 1, 4).getValues()[0].map(e => e instanceof Date ? e.toISOString().split("T")[0] : e);

To:

return range.offset(0, 1, 1, 4).getValues()[0].map(e => e instanceof Date ? Utilities.formatDate(e, Session.getScriptTimeZone(), "yyyy-MM-dd") : e);

or

return range.offset(0, 1, 1, 4).getValues()[0].map(e => e instanceof Date ? Utilities.formatDate(e, active.getSpreadsheetTimeZone(), "yyyy-MM-dd") : e);