CODEWITHSUNDEEP
formsgoogle-apps-scriptgoogle-sheets
Sean W
Sean W

Reputation: 407

webapp form value to spreadsheet

I have a webapp form and I'm looking for a way to push the value of the form into google spreadsheet.

My current attempt is to assign a name to each form input (a1,a2,a3...) then attempt to iterate the form values into an array like this:

Code.gs

function doGet(e) {
return HtmlService
.createTemplateFromFile('Index')
.evaluate()
.setSandboxMode(HtmlService.SandboxMode.NATIVE)
}

function writeForm(form) {

var ss = 
SpreadsheetApp.openById('1bKrGjBV*****');
var sheet = ss.getSheets()[0]; 
var data = ss.getDataRange().getValues();
var input = [''];
var ndata = form

for(var j=0;j<data.length;j++){
var value = form.a+j
input.push(value)
}
for(var k=0;k<data.length;k++){
sheet.getRange(k+1,4).setValue(ndata[k]);
}
var range = sheet.getRange(1, 5);
}


function getData(){

return SpreadsheetApp
.openById('1bKrGjBV*****')
.getSheets()[0]
.getDataRange()
.getValues(); 
}

Index.html

 <body>
 <center><h1>Produce Inventory Form </h1></center>
 <style>
 table,th,td{border:1px solid black;}
 </style>
 <? var data = getData(); ?>
 <form id="invform">
 <input type = "submit" value="Submit" onclick 
 ="google.script.run.writeForm(this.parentNode)">
 <table align="center">
 <tr><th>Code</th><th>Name</th><th>ChName</th><th>On Hand</th></tr>
 <? for(var i=0;i<data.length;i++){ ?>
 <tr>
 <th><?= data[i][0] ?></th>
 <th><?= data[i][1] ?></th>
 <th><?= data[i][2] ?></th>
 <th><input type = "text" style="width:40px" min="0" maxlength="3" name=a<?
 =i ?>>
 </tr>
 <? } ?>
 </table>
 </body>

With this method, the problem I'm getting into is

 var value = form.a+j

Doesn't do what I was hoping for, which is to assign a variable to form.a1, form.a2, form.a3, ... then push it into an array.

I'm pretty sure there's a better way but I have yet to find a solution. Apologize for the messy code, but I was focused on getting the result.

Upvotes: 0

Views: 212

Answers (1)

Tanaike
Tanaike

Reputation: 201378

How about a following modification?

Modification points :

  1. For Index.html, </form> is missing.
  2. About form.a+j, you can retrieve the values using form["a" + j].
  3. ndata is JSON like {a1: "value", a2: "value"}. So when ndata is imported to cells, it becomes undefined.
  4. When several values are imported to cells, the importing efficiency becomes higher by using setValues().

When these are reflected to your script, the modified script is as follows. The values inputted to forms are imported to "D1:D" of spreadsheet.

Modified script :

Code.gs

function doGet(e) {
  return HtmlService
  .createTemplateFromFile('Index')
  .evaluate()
  .setSandboxMode(HtmlService.SandboxMode.NATIVE)
}

function writeForm(form) {
  var ss = SpreadsheetApp.openById('1bKrGjBV*****');
  var sheet = ss.getSheets()[0];
  var data = ss.getDataRange().getValues();
  var input = []; // Modified
  // var ndata = form
  for(var j=0;j<data.length;j++){
    var value = form["a" + j]; // Modified
    input.push([value]) // Modified
  }
  sheet.getRange(1, 4, input.length, input[0].length).setValues(input); // Added
  // for(var k=0;k<data.length;k++) {
  //   sheet.getRange(k+1,4).setValue(ndata[k]);
  // }
  // var range = sheet.getRange(1, 5);
}

function getData(){
  return SpreadsheetApp
   .openById('1bKrGjBV*****')
   .getSheets()[0]
   .getDataRange()
   .getValues();
}

Index.html

<body>
  <center><h1>Produce Inventory Form </h1></center>
  <style>
    table,th,td{border:1px solid black;}
  </style>
  <? var data = getData(); ?>
  <form id="invform">
    <input type = "submit" value="Submit" onclick="google.script.run.writeForm(this.parentNode)">
    <table align="center">
      <tr><th>Code</th><th>Name</th><th>ChName</th><th>On Hand</th></tr>
      <? for(var i=0;i<data.length;i++){ ?>
      <tr>
        <th><?= data[i][0] ?></th>
        <th><?= data[i][1] ?></th>
        <th><?= data[i][2] ?></th>
        <th><input type = "text" style="width:40px" min="0" maxlength="3" name=a<?= i ?>>
      </tr>
      <? } ?>
    </table>
  </form> <!-- Added -->
</body>

If I misunderstand your question, I'm sorry.

Upvotes: 1

Related Questions