Google Sheets + Apps Script + Webapps: Pull and edit an existing row data and update row

Question

Hi I'm trying to pull the data from a google sheet from the row that matches the selected ID number to edit the data then click on the update button so the matching row gets updated in google sheet here's a link to my google sheet. https://docs.google.com/spreadsheets/d/1eaEfRtjMz7kyQfyXZHLuBhSz91SiUJzq1J2QJexJxy4/edit?ts=5fc42833#gid=0

here's my gs code

  function doGet(request) {
  return HtmlService.createTemplateFromFile('Index').evaluate();
 
}

/* @Include JavaScript and CSS Files */
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}
/* @Process Form */



function getCustDetail(search) {
  
  var ss = SpreadsheetApp.getActive()
  var ws = ss.getSheetByName("Data");
  var idvCustData = ws.getRange(2,1,ws.getLastRow(),8).getValues();
  var custIdList = idvCustData.map(function(r){ return r[0]; });
  var custDateList = idvCustData.map(function(r){ return r[2]; });
  var custclientList = idvCustData.map(function(r){ return r[3]; });
  var custlocationList = idvCustData.map(function(r){ return r[4]; });
  var custother1List = idvCustData.map(function(r){ return r[5]; });
  var custother2List = idvCustData.map(function(r){ return r[6]; });
 
  
  
  var searchPostion = custIdList.indexOf(search);
  var custDetail = {};
  custDetail.custId = custIdList[searchPostion];
  custDetail.custDate = custDateList[searchPostion];
  custDetail.custclient = custclientList[searchPostion];
  custDetail.custlocation = custlocationList[searchPostion];
  custDetail.custother1 = custother1List[searchPostion];
  custDetail.custother2 = custother2List[searchPostion];

  Logger.log(searchPostion);
  Logger.log(custDetail);
  Logger.log(search);
  
  if (searchPostion > -1){
  
  return  custDetail;
   
  } else {
    return '-';
 }
}

here's my JS code

Here's my HTML code

    

    
        
        
        
        
        
    
    
    
    
    
        
        
        
        
     
     
        
   
   
   
   
     
     
  
    
     
     
       

    
   
      
        
            
                
                    
                      Ticket Form
                      
                      
           
           
           
           
           

              
               
               ID
               
              
              
             
              
              
               Ticket Date
               
              
              
               
                 Client
                 
               
                           
               
                 Location
                 
               
             
   
          
             
             
               Other1
               
              
              
              
                 Other2

Tanaike · Accepted Answer

Modification points:

At Google Apps Script side:
- When I saw the scripts in your shared Spreadsheet, it seems that the function getCustDetail doesn't return the values. But, your script in your question returns custDetail.
- At getCustDetail, you have already set the value of search. So in this case, the value is always used. Please be careful this.
- When the value is put to the input with type="date", the value is required to be yyyy-MM-dd.
- Even when custDetail is returned, custDetail() at Javascript side doesn't use the argument.
- Even when custDetail uses the argument from Google Apps Script side, the keys returned from Google Apps Script are different from them at Javascript side.
At Javascript side:
- In your shared Spreadsheet, var data = google.script.run.getCustDetail(); is used. But in the current stage, google.script.run returns no values. Please be careful this.
- When you want to set the value to the text input, please use value instead of innerHTML.

When above points are reflected to your script, it becomes as follows.

Modified script:

Google Apps Script side:

Please modify getCustDetail as follows.

function getCustDetail(search) {
  search = Number(search);
  var ss = SpreadsheetApp.getActive();
  var ws = ss.getSheetByName("Data");
  var idvCustData = ws.getRange(2,1,ws.getLastRow(),8).getValues();
  var custIdList = idvCustData.map(function(r){ return r[0]; });
  var custDateList = idvCustData.map(function(r){ return r[2]; });
  var custclientList = idvCustData.map(function(r){ return r[3]; });
  var custlocationList = idvCustData.map(function(r){ return r[4]; });
  var custother1List = idvCustData.map(function(r){ return r[5]; });
  var custother2List = idvCustData.map(function(r){ return r[6]; });
  var searchPostion = custIdList.indexOf(search);
  var custDetail = {};
  custDetail.Id = custIdList[searchPostion];
  custDetail.Date = custDateList[searchPostion] && Utilities.formatDate(custDateList[searchPostion], Session.getScriptTimeZone(), "yyyy-MM-dd");
  custDetail.client = custclientList[searchPostion];
  custDetail.location = custlocationList[searchPostion];
  custDetail.other1 = custother1List[searchPostion];
  custDetail.other2 = custother2List[searchPostion];
  if (searchPostion > -1){
    return  custDetail;
  } else {
    return null;
  }
}

In this case, it supposes that IDs of the column "A" in Spreadsheet are always the number. Please be careful this.

Javascript side:

Please modify custDetail as follows.

function custDetail(cust){
  if (cust) {
    document.getElementById("Id").value = cust.Id;
    document.getElementById("Date").value = cust.Date;
    document.getElementById("client").value = cust.client;
    document.getElementById("location").value = cust.location;
    document.getElementById("other1").value = cust.other1;
    document.getElementById("other2").value = cust.other2;
    M.updateTextFields();
  }
}

Note:

When you modified the script of Web Apps, please redeploy the Web Apps as new version. By this, the latest script is reflected to the Web Apps. Please be careful this.