Read data from spreadsheet

I want to utilize the spreadsheet as a DB.
I would like to search by putting a value in index.html's [selectindex] and display the results in [text1], [text2], but it doesn't work as well as I want.
I would appreciate it if someone could help me.

code.gs

function doGet(){
  startpl = HtmlService.createTemplateFromFile('index.html');
  return tpl.evaluate();

}

functionSelectData(form){


  varSPREAD_ID='1j5TH87Wv9Uz8KpQXXXXXXXXXXXXXXXXXX5ZJKg';
  varSHEET_NAME = 'Sheet 1';
  ss=SpreadsheetApp.openById(SPREAD_ID); // Get Spread
  sheet=ss.getSheetByName(SHEET_NAME);// Get sheet

  Logger.log(ss);

  // Check from row 1 to row 30 sequentially
  for(vari=1;i<20;i++){
    varval=sheet.getRange(i,1).getValue();
    if(val==form.selectindex){
      // Returns the value in the second column
      return sheet.getRange(i,2).getValue();
      return sheet.getRange(i,3).getValue();
      vardata1 = sheet.getRange(i,2).getValue();
      date2 = sheet.getRange(i,3).getValue();

    }
  }

  // If there are no search results
  return "not found";
}

index.html

<!DOCTYPE html>
<html>
<head>
<title> Spread Read Test</title>
<!--- Client Script-->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<script>
  $(function(){
    $('#edit input [type="submit"]').click(function(e){
      e.preventDefault();
      google.script.run.withSuccessHandler(eventEnd).SelectData(this.parentNode);
    });
  });
  // Called when SelectData() is complete
  function eventEnd(serverResult){


   Show // Msg
 // window.confirm(serverResult);
   document.form1.text1.value=(serverResult);

  }
</script>

</head>

<!--Main body-->
<body>
 <divid="edit">

      <form name="form1">
        <input id="selectindex" name="selectindex" type="text" value="">br>
        <input id="selectindex" name="text1" type="text" value="">
        <input id="selectindex" name="text2" type="text" value="">          
        <input id="submitButton" name="submitButton" type="submit" value="display spread content">


      </form>

 </div>
</body>
</html>

javascript google-apps-script

2022-09-30 21:12

1 Answers

Is the id written incorrectly? At least id should be a unique convention within one html.

I tried changing to id=text1 and it worked

<form name="form1">
      <input id="selectindex" name="selectindex" type="text" value="">
      <br>
      <input id="text1" type="text" value="">
      <input id="text2" type="text" value="">
      <input id="submitButton" name="submitButton" type="submit" value="display spread content">
    </form>

On the gs side, change to return multiple values

functionSelectData(form){
  varSPREAD_ID='xxxxxxxxxxxxxx';
  varSHEET_NAME = 'Sheet 1';
  ss=SpreadsheetApp.openById(SPREAD_ID); // Get Spread
  sheet=ss.getSheetByName(SHEET_NAME);// Get sheet

  Logger.log(ss);

  var values = [ ];

  // Check from row 1 to row 30 sequentially
  for(vari=1;i<20;i++){
    varval=sheet.getRange(i,1).getValue();
    if(val==form.selectindex){
      values.push(sheet.getRange(i,2).getValue());
      values.push(sheet.getRange(i,3).getValue());
    }
  }
  return values;
}

Change to get multiple values on js side of html

Called when

//SelectData() is complete
function eventEnd(serverResult){      
  document.form1.text1.value=serverResult[0];
  document.form1.text2.value=serverResult[1];
}

2022-09-30 21:12

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656