UI to HTML, conversion will not write a date to the sheet part 2

Question

Further to my original question which I posted here: UI to HTML part 1 which was answered by @SandyGood (thanks Sandy) I have now posed myself another quandary.

MyForm.HTML code is:

<!-- Use a templated HTML printing scriptlet to import common stylesheet. -->
<?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>
<html>
  <body>
  <div>
  <!-- Page header title & 'completion warning -->
  <span class="grey"><b>ProReactive Log Form v3.0.74</b></span>
  <h4>Complete ALL fields to ensure the form is processed successfully</h4>

  <form role="form" action="mail.php" method="post">

                <input type="date" name="dateA" id="dateA" class="form-control" placeholder="Enter Date">

                <input type="text" name="site" list="depotslist" id="site" class="form-control" placeholder="Select depot/site">
                <datalist id="depotslist">
                <option value="one">
                <option value="two">
                </datalist>

                <input type="text" name="reporter" id="reporter" class="form-control" placeholder="Enter Name" style="width: 150px">

                <input type="text" name="phone" id="phone" class="form-control" placeholder="Phone number" style="width: 150px">

                <input type="text" name="source" list="sourcelist" id="source" class="form-control" placeholder="Select Source Code">
                <datalist id="sourcelist">
                <option value="one">
                <option value="two">
                </datalist>

                <input type="text" name="hazard" list="hazardlist" id="hazard" class="form-control" placeholder="Select Hazard Code">
                <datalist id="hazardlist">
                <option value="one">
                <option value="two">
                </datalist>

                <input type="text" name="brief" id="brief" class="form-control" placeholder="Enter BRIEF Details">

                <input type="text" name="describe" id="describe" class="form-control" placeholder="FULL Description & Location">

                <input type="text" name="priority" list="prioritylist" id="priority" class="form-control" placeholder="Select Priority" style="width: 150px">
                <datalist id="prioritylist">
                <option value="one">
                <option value="two">
                </datalist>

                <button type="share" id="submit-btn" onclick="runGoogleScript()" class="btn btn-default">Submit</button>
</form>

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

  <script>
  function onSuccess(argReturnValue){
      alert('was successful ' +argReturnValue);
      //ResetFields on Screen
      Document.getElementById("dateA").value = "";
      }

  function runGoogleScript() {
      console.log('v3.0.74 ran!');
      var d = document.getElementById("dateA").value;
      var inputDa = d.toString();
      var inputDe = document.getElementById("site").value;
      google.script.run.withSuccessHandler(onSuccess)
      .InsertInSS(inputDa);
      };
  </script>

and my .gs code is:

function InsertInSS(argPassedInName) {
  var ssKey = '1YEx1hwzCVyhrzIMa5U4EJ0SVDkUKQ-sJBo-Id38j5vw';
  var SS = SpreadsheetApp.openById(ssKey);
  var Sheet = SS.getSheetByName('LOG');
  Sheet.getRange(Sheet.getLastRow()+1,1,1).setValue(argPassedInName);
  }

but I am missing something as I can only manage to post the first field to the spreadsheet. I have tried adding in the names of the fields in the line

.InsertInSS(inputDa);

For example .InsertInSS(inputDa, inputDe); and I have tried replicating the line with the different inputs e.g.

.InsertInSS(inpuDa);
.InserInSS(inputDe);

and I have tried desperate line changes that didn't make a lot of sense so I reverted them as they didn't work either. What am I not doing?

Also, I want to start posting all the data in column 2 as column 1 needs to capture the userID.

var thisUser = Session.getActiveUser().getEmail());

But where should that be inserted?

EDIT 16th July

I have inserted the suggested code, but get errors:

 function runGoogleScript() {
      console.log('v3.0.74 ran!');
      var d = document.getElementById("dateA").value;
      var inputDa = d.toString();
      var inputDe = document.getElementById("site").value;
      var arrayToPass = [];
      arrayToPass.push(inputDa);
      arrayToPass.push(inputDe);
      google.script.run.withSuccessHandler(onSuccess)
      .InsertInSS(arrayToPass);
      };

function InsertInSS(myDataInAnArray) {

var i=0, thisValue="";
for (i=0;i<myDataInAnArray.length;i+=1) {
thisValue = myDataInAnArray[i];

  var ssKey = '1YEx1hwzCVyhrzIMa5U4EJ0SVDkUKQ-sJBo-Id38j5vw';
  var SS = SpreadsheetApp.openById(ssKey);
  var Sheet = SS.getSheetByName('LOG');

Sheet.getRange(Sheet.getLastRow()+1,1,1,1).setValue(thisValue);

}
}

Errors produced: if I run it as it is I get

TypeError: Cannot read property "length" from undefined. (line 4, file "")

If I add in the line var myDataInAnArray = [] I get the code to run but nothing happens in the spreadsheet. A log of the array contents is blank.

Edit 21st July I'm feeling close to defeat now. Annoyingly. I have been going round in circles and I know it is my complete lack of comprehension that is hobbling me. I have changed my code in the .HTML now to:

var arrayToPass = [];
  arrayToPass.push(inputDa);
  arrayToPass.push(inputDe);


  var myDataInAnArray = (arrayToPass)
  google.script.run.withSuccessHandler(onSuccess)
  Console.log(myDataInAnArray);
  .InsertInSS(myDataInAnArray);

and in the .gs to:

var i=0, thisValue="";
var myDataInAnArray = [arrayToPass]
for (i=0;i<myDataInAnArray.length;i+=1) {
thisValue = myDataInAnArray[i];

  var ssKey = '1YEx1hwzCVyhrzIMa5U4EJ0SVDkUKQ-sJBo-Id38j5vw';
  var SS = SpreadsheetApp.openById(ssKey);
  var Sheet = SS.getSheetByName('LOG');

Sheet.getRange(Sheet.getLastRow()+1,1,1).setValue(thisValue);

}
}

and all I can get is a new row entered into the spreadsheet with "undefined" in the first cell. I feel I have changed everything I can but I am so green and unskilled I admit I don't really know what I am doing. Why is the data not making it from the website form to the spreadsheet?

Do you actually want the form to make a POST request on submission? `
` So, right now, *both* a POST request is being made, *and* you are making a server call with `google.script.run` — Alan Wells, Jul 15 '15 at 15:30
Not really sure Sandy. I'm bagging code with little knowledge and bashing it to fit. What I want to do in pseudo code is: 1. create form with fields in webpage. 2. Allow fields to be populated (some with lists). 3. On clicking the submit button shove it all in a sheet with a time stamp & user id. — witham, Jul 15 '15 at 15:42
I'd remove all the attributes from the form tag, they aren't needed. So, `
` will become just `
` — Alan Wells, Jul 15 '15 at 15:48

score 0 · Answer 1 · answered Jul 15 '15 at 15:24

0

You tried:

.InsertInSS(inputDa, inputDe);

Which is one way to pass multiple values, but on the other end, you need matching variables to receive the multiple values:

function InsertInSS(argDa, argDe) {

There are other ways you could do it. You could put multiple values into an array, and pass the array:

var arrayToPass = [];
arrayToPass.push(inputDa);
arrayToPass.push(inputDe);

.InsertInSS(arrayToPass);

Server Code:

function InsertInSS(myDataInAnArray) {

  //Loop through the array
  var i=0, thisValue="";
  for (i=0;i<myDataInAnArray.length;i+=1) {
    thisValue = myDataInAnArray[i];
    //To Do - do something with this value

  }

answered Jul 15 '15 at 15:24

Alan Wells

30,746
15
104
152

The "arg" bit makes a bit of sense now. I'll try the array as it looks simpler, neater and smarter and come back to you. Thank you for the quick response! – witham Jul 15 '15 at 15:44
Still stuck and hopelessly going round in circles. Please see edit above but what on earth am I not doing to get the array from the html form to the spreadsheet? – witham Jul 21 '15 at 10:17
You've got a `Console.log();` statement in between the `google.script.run` code. That needs to be moved. There is what is called a "dot operator" that is used to reference a property inside of an object. The "dot operator" is just a period. The `.InsertInSS(myDataInAnArray);` line will not run because the `Console.log();` statement is in between `google.script.run` and `.InsertInSS(myDataInAnArray);` The semi-colon indicates an end of a statement. You are interrupting the `google.script.run` chain. The dot operator extends the "chain". The chain is running one process after another. – Alan Wells Jul 21 '15 at 14:13
Thanks Sandy, that still isn't working however. If I run the code from within the script editor it posts "undefined" and if I run if form the website button nothing happens still. – witham Jul 21 '15 at 14:22
No joy with this at all. Can anyone help? The array will not pass to the spreadsheet at all and I cannot figure out why. – witham Jul 23 '15 at 11:18
Go to my profile, and send me an email if you want. – Alan Wells Jul 23 '15 at 15:34

score 0 · Accepted Answer · answered Sep 06 '15 at 09:27

Well, it's been a slog, but I have finally done it!

In my form I have this code:

      function runGoogleScript() {
      console.log('v3.0.151 ran!');

      //First header entries
      var datA = document.getElementById("dateA").value;
      var inputDa = datA.toString();            
      var inputDe = document.getElementById("site").value;
      var inputRe = document.getElementById("reporter").value;
      var inputPh = document.getElementById("phone").value;

      //Second header entries
      var inputSo = document.getElementById("source").value;
      var inputHa = document.getElementById("hazard").value;
      var inputBr = document.getElementById("brief").value;
      var inputDs = document.getElementById("describe").value;
      var inputPr = document.getElementById("priority").value;

      google.script.run.withSuccessHandler(onSuccess)

      var arrayToPass = [inputDa, inputDe, inputRe, inputPh, inputSo, inputHa, inputBr, inputDs, inputPr];


      var myDataInAnArray = (arrayToPass)
      google.script.run.withSuccessHandler(onSuccess)
      .InsertInSS(myDataInAnArray);


console.log(arrayToPass);

   };

And in my .gs sheet I have this:

function InsertInSS(arrayToPass) {

var i=0, thisValue="";
var myDataInAnArray = [arrayToPass]
for (i=0;i<myDataInAnArray.length;i+=1) {
thisValue = myDataInAnArray[i];

  var ssKey = '1YEx1hwzCVyhrzIMa5U4EJ0SVDkUKQ-sJBo-Id38j5vw';
  var SS = SpreadsheetApp.openById(ssKey);
  var Sheet = SS.getSheetByName('LOG');

Sheet.getRange(Sheet.getLastRow()+1,1,1,9).setValues(myDataInAnArray);

Logger.log(myDataInAnArray);

} }

And it works. The line I was stuck with in the end turned out to be Sheet.getRange(Sheet.getLastRow()+1,1,1,9).setValues(myDataInAnArray); as I wasn't declaring the array I was creating so it couldn't write it anywhere.

Thanks to Sandy for the assistance and thanks to Google Apps Script [2nd Edition] (O'Reilly Media) for the explanation.

UI to HTML, conversion will not write a date to the sheet part 2

2 Answers2

Server Code:

Linked